Repository: ploomber/jupysql (language: python)

Issue:
Pandas 2.1.0 incompatible with DuckDB
there is some incompatibility issue between the latest version of DuckDB and pandas 2.0.1. More context in this [comment](https://github.com/ploomber/jupysql/pull/883#issuecomment-1723316180)


Discussion / hints:

Fix patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index f45aec1bb..1daf8fbeb 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -2,6 +2,7 @@
 
 ## 0.10.5dev
 * [Fix] Look into ~/.jupysql/config for config if pyproject.toml does not have a SqlMagic section (#911)
+* [Fix] Update to be compatible with DuckDB v0.9.0 (#897) and Pandas 2.1.0 (#890) 
 
 ## 0.10.4 (2023-11-28)
 
diff --git a/setup.py b/setup.py
index 6ba8edd0a..e1ba5911a 100644
--- a/setup.py
+++ b/setup.py
@@ -32,14 +32,14 @@
 DEV = [
     "flake8",
     "pytest",
-    "pandas==2.0.3",
+    "pandas",  # previously pinned to 2.0.3
     "polars==0.17.2",  # 04/18/23 this breaks our CI
     "pyarrow",
     "invoke",
     "pkgmt",
     "twine",
     # tests
-    "duckdb<0.9.0",
+    "duckdb",
     "duckdb-engine",
     "pyodbc",
     # sql.plot module tests


Test patch:
diff --git a/src/tests/baseline_images/test_magic_plot/pie_one_col.png b/src/tests/baseline_images/test_magic_plot/pie_one_col.png
index 75d1a2669..8decc41c8 100644
Binary files a/src/tests/baseline_images/test_magic_plot/pie_one_col.png and b/src/tests/baseline_images/test_magic_plot/pie_one_col.png differ
diff --git a/src/tests/baseline_images/test_magic_plot/pie_one_col_null.png b/src/tests/baseline_images/test_magic_plot/pie_one_col_null.png
index 75d1a2669..8decc41c8 100644
Binary files a/src/tests/baseline_images/test_magic_plot/pie_one_col_null.png and b/src/tests/baseline_images/test_magic_plot/pie_one_col_null.png differ
diff --git a/src/tests/baseline_images/test_magic_plot/pie_one_col_num.png b/src/tests/baseline_images/test_magic_plot/pie_one_col_num.png
index c3ce3f31a..2c3edfd98 100644
Binary files a/src/tests/baseline_images/test_magic_plot/pie_one_col_num.png and b/src/tests/baseline_images/test_magic_plot/pie_one_col_num.png differ
diff --git a/src/tests/test_magic.py b/src/tests/test_magic.py
index 858f6649b..41e36266f 100644
--- a/src/tests/test_magic.py
+++ b/src/tests/test_magic.py
@@ -2483,7 +2483,7 @@ def test_query_comment_after_semicolon(ip, query, expected):
 
 
 Original error message from DB driver:
-(duckdb.CatalogException) Catalog Error: Table with name snip does not exist!
+(duckdb.duckdb.CatalogException) Catalog Error: Table with name snip does not exist!
 Did you mean "temp"?
 LINE 1: SELECT * FROM snip;
                       ^
@@ -2500,7 +2500,7 @@ def test_query_comment_after_semicolon(ip, query, expected):
 
 
 Original error message from DB driver:
-(duckdb.CatalogException) Catalog Error: Table with name tem does not exist!
+(duckdb.duckdb.CatalogException) Catalog Error: Table with name tem does not exist!
 Did you mean "temp"?
 LINE 1: SELECT * from tem;
                       ^
@@ -2520,7 +2520,7 @@ def test_query_comment_after_semicolon(ip, query, expected):
 
 
 Original error message from DB driver:
-(duckdb.CatalogException) Catalog Error: Table with name snip does not exist!
+(duckdb.duckdb.CatalogException) Catalog Error: Table with name snip does not exist!
 Did you mean "temp"?
 LINE 1: SELECT * FROM snip;
                       ^
@@ -2537,7 +2537,7 @@ def test_query_comment_after_semicolon(ip, query, expected):
 
 
 Original error message from DB driver:
-(duckdb.CatalogException) Catalog Error: Table with name s does not exist!
+(duckdb.duckdb.CatalogException) Catalog Error: Table with name s does not exist!
 Did you mean "temp"?
 LINE 1: SELECT * FROM s;
                       ^
@@ -2555,7 +2555,7 @@ def test_query_comment_after_semicolon(ip, query, expected):
 
 
 Original error message from DB driver:
-(duckdb.CatalogException) Catalog Error: Table with name snippet does not exist!
+(duckdb.duckdb.CatalogException) Catalog Error: Table with name snippet does not exist!
 Did you mean "pg_type"?
 LINE 1: SELECT * FROM snippet;
                       ^
diff --git a/src/tests/test_magic_plot.py b/src/tests/test_magic_plot.py
index 28b62557f..cd8aed92f 100644
--- a/src/tests/test_magic_plot.py
+++ b/src/tests/test_magic_plot.py
@@ -444,6 +444,7 @@ def test_bar_one_col_h(load_data_one_col, ip):
     ip.run_cell("%sqlplot bar -t data_one.csv -c x -o h")
 
 
+@pytest.mark.xfail(reason="DuckDB v0.9.0 bug")
 @_cleanup_cm()
 @image_comparison(
     baseline_images=["bar_one_col_num_h"], extensions=["png"], remove_text=True
@@ -452,6 +453,7 @@ def test_bar_one_col_num_h(load_data_one_col, ip):
     ip.run_cell("%sqlplot bar -t data_one.csv -c x -o h -S")
 
 
+@pytest.mark.xfail(reason="DuckDB v0.9.0 bug")
 @_cleanup_cm()
 @image_comparison(
     baseline_images=["bar_one_col_num_v"], extensions=["png"], remove_text=True
@@ -685,6 +687,7 @@ def test_histogram_with_table_in_schema(ip_with_schema_and_table, cmd, conn):
     ip_with_schema_and_table.run_cell(cmd)
 
 
+@pytest.mark.xfail(reason="DuckDB v0.9.0 bug")
 @pytest.mark.parametrize(
     "cmd, conn",
     [
@@ -716,6 +719,7 @@ def test_bar_with_table_in_schema(ip_with_schema_and_table, cmd, conn):
     ip_with_schema_and_table.run_cell(cmd)
 
 
+@pytest.mark.xfail(reason="DuckDB v0.9.0 bug")
 @pytest.mark.parametrize(
     "cmd, conn",
     [
diff --git a/src/tests/test_parse.py b/src/tests/test_parse.py
index e00c275c3..99a76843a 100644
--- a/src/tests/test_parse.py
+++ b/src/tests/test_parse.py
@@ -771,7 +771,7 @@ def test_connections_file_get_default_connection_url(tmp_empty, content, expecte
             "ell",
         ),
         (
-            "select 'hello'[:-1]",
+            "select 'hello'[:-2]",
             "hell",
         ),
     ],
diff --git a/src/tests/test_resultset.py b/src/tests/test_resultset.py
index aa493965f..775df43c7 100644
--- a/src/tests/test_resultset.py
+++ b/src/tests/test_resultset.py
@@ -750,11 +750,11 @@ def test_pivot_dataframe_conversion_results(ip, df_type, library, equal_func):
 
     # Assert result matches expected
     expected_result = {
-        "Country": ["NL", "US", "US"],
-        "Name": ["Amsterdam", "Seattle", "New York City"],
-        "2000": [1005.0, 564.0, 8015.0],
-        "2010": [1065.0, 608.0, 8175.0],
-        "2020": [1158.0, 738.0, 8772.0],
+        "Country": ["US", "US", "NL"],
+        "Name": ["New York City", "Seattle", "Amsterdam"],
+        "2000": [8015.0, 564.0, 1005.0],
+        "2010": [8175.0, 608.0, 1065.0],
+        "2020": [8772.0, 738.0, 1158.0],
     }
     expected = getattr(library, "DataFrame")(expected_result)
     assert getattr(result, equal_func)(expected)