Repository: ploomber/jupysql (python)

Issue:
error when validating if a table exists
I found an error when validating if a table exists, because it doesn't check if a CTE has been stored:

```sql
%%sql --save many_passengers --no-execute
SELECT *
FROM taxi
WHERE passenger_count > 3
-- remove top 1% outliers for better visualization
AND trip_distance < 18.93
```

Then, using:

```sql
%sqlplot histogram --table many_passengers --column trip_distance --with many_passengers
```

Throws this error:

```
ValueError: There is no table with name 'many_passengers' in the default schema
If you need help solving this issue, send us a message: https://ploomber.io/community
```

Even though I already stored `many_passengers` using `--save`


Note: this is blocking the release

cc @idomic @yafimvo 




Pull request:
suggestion for stored query added
## Describe your changes
Suggest user a missing `--with` argument

## Issue number
Closes #355 

## Checklist before requesting a review

- [X] Performed a self-review of my code
- [X] Formatted my code with [`pkgmt format`](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#lintingformatting)
- [X] Added [tests](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#testing) (when necessary).
- [ ] Added documentation in the [docstring](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#documenting-changes-and-new-features) and [changelog](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#changelog) (when needed)



<!-- readthedocs-preview jupysql start -->
----
:books: Documentation preview :books:: https://jupysql--361.org.readthedocs.build/en/361/

<!-- readthedocs-preview jupysql end -->

Patch:
diff --git a/src/sql/util.py b/src/sql/util.py
index bb75bb4..2994657 100644
--- a/src/sql/util.py
+++ b/src/sql/util.py
@@ -94,12 +94,21 @@ def is_table_exists(
                     f"There is no table with name {table!r} in the default schema"
                 )
 
-            suggestions = difflib.get_close_matches(invalid_input, expected)
+            if table in list(store):
+                # Suggest user use --with when given table
+                # is in the store
+                suggestion_message = (
+                    ", but there is a stored query."
+                    f"\nDid you miss passing --with {table}?"
+                )
+                err_message = f"{err_message}{suggestion_message}"
+            else:
+                suggestions = difflib.get_close_matches(invalid_input, expected)
 
-            if len(suggestions) > 0:
-                _suggestions_string = pretty_print(suggestions, last_delimiter="or")
-                suggestions_message = f"\nDid you mean : {_suggestions_string}"
-                err_message = f"{err_message}{suggestions_message}"
+                if len(suggestions) > 0:
+                    _suggestions_string = pretty_print(suggestions, last_delimiter="or")
+                    suggestions_message = f"\nDid you mean : {_suggestions_string}"
+                    err_message = f"{err_message}{suggestions_message}"
 
             raise ValueError(err_message)
 
@@ -147,21 +156,22 @@ def _is_table_exists(table: str, with_: str) -> bool:
     """
     Runs a SQL query to check if table exists
     """
-
     identifiers = Connection.get_curr_identifiers()
-
-    for iden in identifiers:
-        if isinstance(iden, tuple):
-            query = "SELECT * FROM {0}{1}{2} WHERE 1=0".format(iden[0], table, iden[1])
-        else:
-            query = "SELECT * FROM {0}{1}{0} WHERE 1=0".format(iden, table)
-        try:
-            if with_:
-                query = str(store.render(query, with_=with_))
-            query = sql.connection.Connection._transpile_query(query)
-            sql.run.raw_run(Connection.current, query)
-            return True
-        except Exception:
-            pass
+    if with_:
+        return table in list(store)
+    else:
+        for iden in identifiers:
+            if isinstance(iden, tuple):
+                query = "SELECT * FROM {0}{1}{2} WHERE 1=0".format(
+                    iden[0], table, iden[1]
+                )
+            else:
+                query = "SELECT * FROM {0}{1}{0} WHERE 1=0".format(iden, table)
+            try:
+                query = sql.connection.Connection._transpile_query(query)
+                sql.run.raw_run(Connection.current, query)
+                return True
+            except Exception:
+                pass
 
     return False


Test patch:
diff --git a/src/tests/test_util.py b/src/tests/test_util.py
index 57278fa..6f387e7 100644
--- a/src/tests/test_util.py
+++ b/src/tests/test_util.py
@@ -7,6 +7,64 @@ EXPECTED_NO_TABLE_IN_DEFAULT_SCHEMA = (
     "There is no table with name {0!r} in the default schema"
 )
 EXPECTED_NO_TABLE_IN_SCHEMA = "There is no table with name {0!r} in schema {1!r}"
+EXPECTED_STORE_SUGGESTIONS = (
+    "but there is a stored query.\nDid you miss passing --with {0}?"
+)
+
+
+@pytest.mark.parametrize(
+    "store_table, query",
+    [
+        ("a", "%sqlcmd columns --table {}"),
+        ("bbb", "%sqlcmd profile --table {}"),
+        ("c_c", "%sqlplot histogram --table {} --column x"),
+        ("d_d_d", "%sqlplot boxplot --table {} --column x"),
+    ],
+)
+def test_missing_with(ip, store_table, query):
+    ip.run_cell(
+        f"""
+        %%sql --save {store_table} --no-execute
+        SELECT *
+        FROM number_table
+        """
+    ).result
+
+    query = query.format(store_table)
+    out = ip.run_cell(query)
+
+    expected_store_message = EXPECTED_STORE_SUGGESTIONS.format(store_table)
+
+    error_message = str(out.error_in_exec)
+    assert isinstance(out.error_in_exec, ValueError)
+    assert str(expected_store_message).lower() in error_message.lower()
+
+
+@pytest.mark.parametrize(
+    "store_table, query",
+    [
+        ("a", "%sqlcmd columns --table {} --with {}"),
+        ("bbb", "%sqlcmd profile --table {} --with {}"),
+        ("c_c", "%sqlplot histogram --table {} --with {} --column x"),
+        ("d_d_d", "%sqlplot boxplot --table {} --with {} --column x"),
+    ],
+)
+def test_no_errors_with_stored_query(ip, store_table, query):
+    ip.run_cell(
+        f"""
+        %%sql --save {store_table} --no-execute
+        SELECT *
+        FROM number_table
+        """
+    ).result
+
+    query = query.format(store_table, store_table)
+    out = ip.run_cell(query)
+
+    expected_store_message = EXPECTED_STORE_SUGGESTIONS.format(store_table)
+    error_message = str(out.error_in_exec)
+    assert not isinstance(out.error_in_exec, ValueError)
+    assert str(expected_store_message).lower() not in error_message.lower()
 
 
 @pytest.mark.parametrize(
@@ -25,6 +83,7 @@ EXPECTED_NO_TABLE_IN_SCHEMA = "There is no table with name {0!r} in schema {1!r}
 def test_bad_table_error_message(ip, table, query, suggestions):
     query = query.format(table)
     out = ip.run_cell(query)
+
     expected_error_message = EXPECTED_NO_TABLE_IN_DEFAULT_SCHEMA.format(table)
 
     error_message = str(out.error_in_exec)