use_query_checker for VertexAI fails

[steven-levey-sp]

System Info

langchain==0.0.180 google-cloud-aiplatform==1.25.0 SQLAlchemy==2.0.15 duckdb==0.8.0 duckdb-engine==0.7.3

Running inside GCP Vertex AI Notebook (Jupyter Lab essentially jupyterlab==3.4.8) python 3.7

Who can help?

@Jflick58 @lkuligin @hwchase17

Information

• [X] The official example notebooks/scripts
• [ ] My own modified scripts

Related Components

• [ ] LLMs/Chat Models
• [ ] Embedding Models
• [ ] Prompts / Prompt Templates / Prompt Selectors
• [ ] Output Parsers
• [ ] Document Loaders
• [ ] Vector Stores / Retrievers
• [ ] Memory
• [ ] Agents / Agent Executors
• [ ] Tools / Toolkits
• [X] Chains
• [ ] Callbacks/Tracing
• [ ] Async

Reproduction

1. Create the Vertex AI LLM (using latest version of LangChain)

`from langchain.llms import VertexAI

palmllm = VertexAI(model_name='text-bison@001', max_output_tokens=256, temperature=0.2, top_p=0.1, top_k=40, verbose=True)`

2. Setup the db engine for duckdb in this case

engine = create_engine("duckdb:///dw.db")

2. Then create the chain using SQLDatabaseChain (Note the use of use_query_checker=True)

`#Setup the DB db = SQLDatabase(engine=engine,metadata=MetaData(bind=engine),include_tables=[table_name])

#Setup the chain db_chain = SQLDatabaseChain.from_llm(palmllm,db,verbose=True,use_query_checker=True,prompt=PROMPT,return_intermediate_steps=True,top_k=3)`

4. Run a query against the chain (Notice the SQLQuery: The query is correct) (It is as if its trying to execute "The query is correct" as SQL"

> Entering new SQLDatabaseChain chain... How many countries are there SQLQuery:The query is correct.

This is the error returned:

ProgrammingError: (duckdb.ParserException) Parser Error: syntax error at or near "The" LINE 1: The query is correct. ^ [SQL: The query is correct.] (Background on this error at: https://sqlalche.me/e/14/f405)

IMPORTANT:

• If I remove the "use_query_checker=True" then everything works well.
• If I use the OpenAI LLM and dont change anything (except the LLM), then it works with the "use_query_checker=True" setting.

This relates to #5049

Expected behavior

I believe the intention of that flag "use_query_checker=True" is to validate the SQL and allow the chain to recover from a simple syntax error.

[lkuligin]

@steven-levey-sp Could you provide a fully reproducible example, please?

[steven-levey-sp]

@lkuligin I'll have to think about how to do that. You need to have access to Vertex AI via a service account and a GCP Account. I cant expose the work environment. I'll see if I can setup some kind of sandbox.

[lkuligin]

@steven-levey-sp just share the end-to-end code without exposing any credentials, please. It should be enough.

[marrry]

Hi @lkuligin, I've run into the same issue. Here is the full code:

!pip -q install langchain google-cloud-aiplatform

import os
from langchain.llms import VertexAI
from langchain import SQLDatabase, SQLDatabaseChain

GOOGLE_APPLICATION_CREDENTIALS = "<your_sa_file>.json"

if not os.path.exists(GOOGLE_APPLICATION_CREDENTIALS):
  print("Warning: The KEY file (JSON) cannot be found. ")

os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = GOOGLE_APPLICATION_CREDENTIALS


llm = VertexAI()
db = SQLDatabase.from_uri("sqlite:///Chinook.sqlite")
db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True, use_query_checker=True)
db_chain.run("How many employees are there?")

And the error log is as follows:

> Entering new  chain...
How many employees are there?
SQLQuery:The query is correct.
---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
[/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py](https://localhost:8080/#) in _exec_single_context(self, dialect, context, statement, parameters)
   1964                 if not evt_handled:
-> 1965                     self.dialect.do_execute(
   1966                         cursor, str_statement, effective_parameters, context

15 frames
[/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/default.py](https://localhost:8080/#) in do_execute(self, cursor, statement, parameters, context)
    920     def do_execute(self, cursor, statement, parameters, context=None):
--> 921         cursor.execute(statement, parameters)
    922 

OperationalError: near "The": syntax error

The above exception was the direct cause of the following exception:

OperationalError                          Traceback (most recent call last)
[<ipython-input-14-2397be6ef658>](https://localhost:8080/#) in <cell line: 1>()
----> 1 db_chain.run("How many employees are there?")

[/usr/local/lib/python3.10/dist-packages/langchain/chains/base.py](https://localhost:8080/#) in run(self, callbacks, tags, *args, **kwargs)
    288             if len(args) != 1:
    289                 raise ValueError("`run` supports only one positional argument.")
--> 290             return self(args[0], callbacks=callbacks, tags=tags)[_output_key]
    291 
    292         if kwargs and not args:

[/usr/local/lib/python3.10/dist-packages/langchain/chains/base.py](https://localhost:8080/#) in __call__(self, inputs, return_only_outputs, callbacks, tags, include_run_info)
    164         except (KeyboardInterrupt, Exception) as e:
    165             run_manager.on_chain_error(e)
--> 166             raise e
    167         run_manager.on_chain_end(outputs)
    168         final_outputs: Dict[str, Any] = self.prep_outputs(

[/usr/local/lib/python3.10/dist-packages/langchain/chains/base.py](https://localhost:8080/#) in __call__(self, inputs, return_only_outputs, callbacks, tags, include_run_info)
    158         try:
    159             outputs = (
--> 160                 self._call(inputs, run_manager=run_manager)
    161                 if new_arg_supported
    162                 else self._call(inputs)

[/usr/local/lib/python3.10/dist-packages/langchain/chains/sql_database/base.py](https://localhost:8080/#) in _call(self, inputs, run_manager)
    179             # improvement of few shot prompt seeds
    180             exc.intermediate_steps = intermediate_steps  # type: ignore
--> 181             raise exc
    182 
    183     @property

[/usr/local/lib/python3.10/dist-packages/langchain/chains/sql_database/base.py](https://localhost:8080/#) in _call(self, inputs, run_manager)
    149                     {"sql_cmd": checked_sql_command}
    150                 )  # input: sql exec
--> 151                 result = self.database.run(checked_sql_command)
    152                 intermediate_steps.append(str(result))  # output: sql exec
    153                 sql_cmd = checked_sql_command

[/usr/local/lib/python3.10/dist-packages/langchain/sql_database.py](https://localhost:8080/#) in run(self, command, fetch)
    347                 else:
    348                     connection.exec_driver_sql(f"SET search_path TO {self._schema}")
--> 349             cursor = connection.execute(text(command))
    350             if cursor.returns_rows:
    351                 if fetch == "all":

[/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py](https://localhost:8080/#) in execute(self, statement, parameters, execution_options)
   1410             raise exc.ObjectNotExecutableError(statement) from err
   1411         else:
-> 1412             return meth(
   1413                 self,
   1414                 distilled_parameters,

[/usr/local/lib/python3.10/dist-packages/sqlalchemy/sql/elements.py](https://localhost:8080/#) in _execute_on_connection(self, connection, distilled_params, execution_options)
    481             if TYPE_CHECKING:
    482                 assert isinstance(self, Executable)
--> 483             return connection._execute_clauseelement(
    484                 self, distilled_params, execution_options
    485             )

[/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py](https://localhost:8080/#) in _execute_clauseelement(self, elem, distilled_parameters, execution_options)
   1633             linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1634         )
-> 1635         ret = self._execute_context(
   1636             dialect,
   1637             dialect.execution_ctx_cls._init_compiled,

[/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py](https://localhost:8080/#) in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1842             )
   1843         else:
-> 1844             return self._exec_single_context(
   1845                 dialect, context, statement, parameters
   1846             )

[/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py](https://localhost:8080/#) in _exec_single_context(self, dialect, context, statement, parameters)
   1982 
   1983         except BaseException as e:
-> 1984             self._handle_dbapi_exception(
   1985                 e, str_statement, effective_parameters, cursor, context
   1986             )

[/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py](https://localhost:8080/#) in _handle_dbapi_exception(self, e, statement, parameters, cursor, context, is_sub_exec)
   2337             elif should_wrap:
   2338                 assert sqlalchemy_exception is not None
-> 2339                 raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
   2340             else:
   2341                 assert exc_info[1] is not None

[/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py](https://localhost:8080/#) in _exec_single_context(self, dialect, context, statement, parameters)
   1963                             break
   1964                 if not evt_handled:
-> 1965                     self.dialect.do_execute(
   1966                         cursor, str_statement, effective_parameters, context
   1967                     )

[/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/default.py](https://localhost:8080/#) in do_execute(self, cursor, statement, parameters, context)
    919 
    920     def do_execute(self, cursor, statement, parameters, context=None):
--> 921         cursor.execute(statement, parameters)
    922 
    923     def do_execute_no_params(self, cursor, statement, context=None):

OperationalError: (sqlite3.OperationalError) near "The": syntax error
[SQL: The query is correct.]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

[dosubot[bot]]

Hi, @steven-levey-sp! I'm Dosu, and I'm here to help the LangChain team manage their backlog. I wanted to let you know that we are marking this issue as stale.

From what I understand, the issue you reported is related to the use_query_checker=True flag in the SQLDatabaseChain class of the LangChain library. Enabling this flag throws a syntax error when executing a valid SQL query. There has been some discussion between lkuligin and yourself on how to provide a reproducible example, and marrry has shared a code snippet that reproduces the issue. aczyzewski has also confirmed experiencing the same problem.

Before we close this issue, we wanted to check with you if it is still relevant to the latest version of the LangChain repository. If it is, please let us know by commenting on the issue. Otherwise, feel free to close the issue yourself, or it will be automatically closed in 7 days.

Thank you for your contribution to the LangChain project!