Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SQLDatabase chain having issue running queries on the database after connecting #1103

Closed
nanditamohan opened this issue Feb 17, 2023 · 4 comments · Fixed by #1129
Closed

SQLDatabase chain having issue running queries on the database after connecting #1103

nanditamohan opened this issue Feb 17, 2023 · 4 comments · Fixed by #1129

Comments

@nanditamohan
Copy link

nanditamohan commented Feb 17, 2023 •
edited

Langchain SQLDatabase and using SQL chain is giving me issues in the recent versions. My goal has been this:

  • Connect to a sql server (say, Azure SQL server) using mssql+pyodbc driver (also tried mssql+pymssql driver)
    connection_url = URL.create( "mssql+pyodbc", query={"odbc_connect": conn} )
    sql_database = SQLDatabase.from_uri(connection_url)

  • Use this sql_database to create a SQLSequentialChain (also tried SQLChain)
    chain = SQLDatabaseSequentialChain.from_llm( llm=self.llm, database=sql_database, verbose=False, query_prompt=chain_prompt)

  • Query this chain

However, in the most recent version of langchain 0.0.88, I get this issue:
image

And in the previous version 0.0.86, I was getting this:
image

A few days back, this worked - but I didn't track which version that was so I have been unable to make this work. Please help look into this.
@jzluo jzluo mentioned this issue Feb 17, 2023
Merged
@gocampo
Copy link

gocampo commented Feb 17, 2023

I found the same issue. It doesn't happen in 0.0.84. I still can't figure out why it's happening.

@jzluo
Copy link
Contributor

jzluo commented Feb 17, 2023

Hi, this issue was introduced in #1081, and should be fixed by #1129. #1081 introduced a way to get the table definition statements, but in a sqlite3-specific way, so it broke compatibility with other database systems.

hwchase17 pushed a commit that referenced this issue Feb 17, 2023
@jzluo
fix for database compatibility when getting table DDL (#1129)
c39ef70 
#1081 introduced a method to get DDL (table definitions) in a manner
specific to sqlite3, thus breaking compatibility with other non-sqlite3
databases. This uses the sqlite3 command if the detected dialect is
sqlite, and otherwise uses the standard SQL `SHOW CREATE TABLE`. This
should fix #1103.
@nanditamohan
Copy link
Author

nanditamohan commented Feb 23, 2023 •
edited

@hwchase17 @jzluo @gocampo
Does the fix need a particular version of sqlalchemy or other things? I think the wrong query is being fed into the driver (pymssql in my case)
I am getting this error with 0.0.92:

e6042b3c-d9df-4fba-82c6-ba8ab9816f14,BakerAgent Exception: (pymssql._pymssql.ProgrammingError) (102, b"Incorrect syntax near '1'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
[SQL:  SELECT MIN(DOB) as "Minimum Date of Birth" FROM Customers LIMIT 1]```

@jzluo jzluo mentioned this issue Feb 23, 2023
Merged
@jzluo
Copy link
Contributor

jzluo commented Feb 23, 2023 •
edited

@nanditamohan Hi, this is an issue with the prompt, not SQLAlchemy. #1242 should fix this

hwchase17 pushed a commit that referenced this issue Feb 23, 2023
@jzluo
Don't instruct LLM to use the LIMIT clause, which is incompatible wit…
8a35811 
…h SQL Server (#1242)

The current prompt specifically instructs the LLM to use the `LIMIT`
clause. This will cause issues with MS SQL Server, which uses `SELECT
TOP` instead of `LIMIT`. The generated SQL will use `LIMIT`; the
instruction to "always limit... using the LIMIT clause" seems to
override the "create a syntactically correct mssql query to run"
portion. Reported here:
#1103 (comment)

I don't have access to a SQL Server instance to test, but removing that
part of the prompt in OpenAI Playground results in the correct `SELECT
TOP` syntax, whereas keeping it in results in the `LIMIT` clause, even
when instructing it to generate syntactically correct mssql. It's also
still correctly using `LIMIT` in my MariaDB database. I think in this
case we can assume that the model will select the appropriate method
based on the dialect specified.

In general, it would be nice to be able to test a suite of SQL dialects
for things like dialect-specific syntax and other issues we've run into
in the past, but I'm not quite sure how to best approach that yet.
zachschillaci27 pushed a commit to zachschillaci27/langchain that referenced this issue Mar 8, 2023
@jzluo @zachschillaci27
fix for database compatibility when getting table DDL (langchain-ai#1129
d48db18 
)

langchain-ai#1081 introduced a method to get DDL (table definitions) in a manner
specific to sqlite3, thus breaking compatibility with other non-sqlite3
databases. This uses the sqlite3 command if the detected dialect is
sqlite, and otherwise uses the standard SQL `SHOW CREATE TABLE`. This
should fix langchain-ai#1103.
zachschillaci27 pushed a commit to zachschillaci27/langchain that referenced this issue Mar 8, 2023
@jzluo @zachschillaci27
Don't instruct LLM to use the LIMIT clause, which is incompatible wit…
fbbf768 
…h SQL Server (langchain-ai#1242)

The current prompt specifically instructs the LLM to use the `LIMIT`
clause. This will cause issues with MS SQL Server, which uses `SELECT
TOP` instead of `LIMIT`. The generated SQL will use `LIMIT`; the
instruction to "always limit... using the LIMIT clause" seems to
override the "create a syntactically correct mssql query to run"
portion. Reported here:
langchain-ai#1103 (comment)

I don't have access to a SQL Server instance to test, but removing that
part of the prompt in OpenAI Playground results in the correct `SELECT
TOP` syntax, whereas keeping it in results in the `LIMIT` clause, even
when instructing it to generate syntactically correct mssql. It's also
still correctly using `LIMIT` in my MariaDB database. I think in this
case we can assume that the model will select the appropriate method
based on the dialect specified.

In general, it would be nice to be able to test a suite of SQL dialects
for things like dialect-specific syntax and other issues we've run into
in the past, but I'm not quite sure how to best approach that yet.
mikeknoop pushed a commit to zapier/langchain-nla-util that referenced this issue Mar 9, 2023
@jzluo
Don't instruct LLM to use the LIMIT clause, which is incompatible wit…
c7356e9 
…h SQL Server (#1242)

The current prompt specifically instructs the LLM to use the `LIMIT`
clause. This will cause issues with MS SQL Server, which uses `SELECT
TOP` instead of `LIMIT`. The generated SQL will use `LIMIT`; the
instruction to "always limit... using the LIMIT clause" seems to
override the "create a syntactically correct mssql query to run"
portion. Reported here:
langchain-ai/langchain#1103 (comment)

I don't have access to a SQL Server instance to test, but removing that
part of the prompt in OpenAI Playground results in the correct `SELECT
TOP` syntax, whereas keeping it in results in the `LIMIT` clause, even
when instructing it to generate syntactically correct mssql. It's also
still correctly using `LIMIT` in my MariaDB database. I think in this
case we can assume that the model will select the appropriate method
based on the dialect specified.

In general, it would be nice to be able to test a suite of SQL dialects
for things like dialect-specific syntax and other issues we've run into
in the past, but I'm not quite sure how to best approach that yet.
ZinedineDumas added a commit to ZinedineDumas/React-Python that referenced this issue Jul 17, 2023
@ZinedineDumas @dashesy
Don't instruct LLM to use the LIMIT clause, which is incompatible wit…
16450c8 
…h SQL Server (#1242)

The current prompt specifically instructs the LLM to use the `LIMIT`
clause. This will cause issues with MS SQL Server, which uses `SELECT
TOP` instead of `LIMIT`. The generated SQL will use `LIMIT`; the
instruction to "always limit... using the LIMIT clause" seems to
override the "create a syntactically correct mssql query to run"
portion. Reported here:
langchain-ai/langchain#1103 (comment)

I don't have access to a SQL Server instance to test, but removing that
part of the prompt in OpenAI Playground results in the correct `SELECT
TOP` syntax, whereas keeping it in results in the `LIMIT` clause, even
when instructing it to generate syntactically correct mssql. It's also
still correctly using `LIMIT` in my MariaDB database. I think in this
case we can assume that the model will select the appropriate method
based on the dialect specified.

In general, it would be nice to be able to test a suite of SQL dialects
for things like dialect-specific syntax and other issues we've run into
in the past, but I'm not quite sure how to best approach that yet.
@dosubot dosubot bot mentioned this issue Sep 1, 2023
Closed
14 tasks
@dosubot dosubot bot mentioned this issue Oct 25, 2023
Closed
14 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

fix for database compatibility when getting table DDL jzluo/langchain
3 participants
@gocampo @jzluo @nanditamohan