SQL Agent consistently missing last quote ' : a bug in the node?

Afamocc · November 2, 2024, 9:52pm

Describe the problem/error/question

When using SQL agent node, even with models that works in other platforms for this scope, there is a consistent issue: when making SQL queries, the agent always misses the last ’ (as can be seen from the last node output below). It is also aware of this issue and fixes it, but the query is always done wrong. There must be something internal to the node that’s failing?

What is the error message (if any)?

Human: You are an agent designed to interact with an SQL database.

Given an input question, generate a syntactically correct sqlite query to retrieve the answer. Unless the user specifies a number of examples, limit your query to at most 10 results using the LIMIT clause. Use ordering by relevant columns to return the most informative examples in the database.

Guidelines for Query Generation and Execution:

Check Available Tables: Before querying, always check available tables with list-tables-sql if the relevant table(s) are unknown.

Validate Query Structure: Always validate your query for correct syntax and ensure quotes, parentheses, and keywords are closed and accurate before executing.

Error Handling: If a query returns an error, analyze the error, correct the syntax, and verify that your fix is applied in the next attempt. Only proceed with execution if the query passes validation.

Execution Rules:

Do not retrieve all columns; only select relevant columns based on the question.

Avoid DML operations (INSERT, UPDATE, DELETE, DROP, etc.)—use only SELECT statements.

If the question is unrelated to the database content, simply return “I don’t know.”

query-sql: Input to this tool is a detailed and correct SQL query, output is a result from the database.
If the query is not correct, an error message will be returned.
If an error is returned, rewrite the query, check the query, and try again.
info-sql: Input to this tool is a comma-separated list of tables, output is the schema and sample rows for those tables.
Be sure that the tables actually exist by calling list-tables-sql first!

Example Input: "table1, table2, table3.
list-tables-sql: Input is an empty string, output is a comma-separated list of tables in the database.
query-checker: Use this tool to double check if your query is correct before executing it.
Always use this tool before executing a query with query-sql!

Use the following format in your response:

Question: the input question you must answer
Thought: you should always think about what to do
Action: the action to take, should be one of [query-sql,info-sql,list-tables-sql,query-checker]
Action Input: the input to the action
Observation: the result of the action
… (this Thought/Action/Action Input/Observation can repeat N times)
Thought: I now know the final answer
Final Answer: the final answer to the original input question

query-sql: Input to this tool is a detailed and correct SQL query, output is a result from the database.
If the query is not correct, an error message will be returned.
If an error is returned, rewrite the query, check the query, and try again.
info-sql: Input to this tool is a comma-separated list of tables, output is the schema and sample rows for those tables.
Be sure that the tables actually exist by calling list-tables-sql first!

Example Input: "table1, table2, table3.
list-tables-sql: Input is an empty string, output is a comma-separated list of tables in the database.
query-checker: Use this tool to double check if your query is correct before executing it.
Always use this tool before executing a query with query-sql!

Use the following format in your response:

Question: the input question you must answer
Thought: you should always think about what to do
Action: the action to take, should be one of [query-sql,info-sql,list-tables-sql,query-checker]
Action Input: the input to the action
Observation: the result of the action
… (this Thought/Action/Action Input/Observation can repeat N times)
Thought: I now know the final answer
Final Answer: the final answer to the original input question

Begin!
Chat History:

Question: Mail, phone and address for customer Emma Jones?
Thought: I should look at the tables in the database to see what I can query.
Action: list-tables-sql
Action Input:
Observation: albums, artists, customers, employees, genres, invoices, invoice_items, media_types, playlists, playlist_track, tracks
Thought:The relevant table for customer information is likely the ‘customers’ table. I will check its schema and sample rows to confirm if it contains the required fields.
Action: info-sql
Action Input: customers
Observation: CREATE TABLE customers (
CustomerId INTEGER NOT NULL, FirstName NVARCHAR(40) NOT NULL, LastName NVARCHAR(20) NOT NULL, Company NVARCHAR(80) , Address NVARCHAR(70) , City NVARCHAR(40) , State NVARCHAR(40) , Country NVARCHAR(40) , PostalCode NVARCHAR(10) , Phone NVARCHAR(24) , Fax NVARCHAR(24) , Email NVARCHAR(60) NOT NULL, SupportRepId INTEGER )
SELECT * FROM “customers” LIMIT 3;
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 [email protected] 3
2 Leonie Köhler null Theodor-Heuss-Straße 34 Stuttgart null Germany 70174 +49 0711 2842222 null [email protected] 5
3 François Tremblay null 1498 rue Bélanger Montréal QC Canada H2G 1A7 +1 (514) 721-4711 null [email protected] 3

Thought:The ‘customers’ table contains the fields ‘FirstName’, ‘LastName’, ‘Address’, and ‘Phone’, which are relevant to finding Emma Jones’s contact information. I will now create a query to find this specific customer.

Action: query-checker
Action Input: SELECT FirstName, LastName, Address, Phone FROM customers WHERE FirstName = “Emma” AND LastName = "Jones
Observation: The provided SQL query does not contain any of the specific issues mentioned in your list (NOT IN with NULL values, UNION instead of UNION ALL, BETWEEN for exclusive ranges, data type mismatch, improper quoting of identifiers, incorrect number of arguments for functions, casting to an incorrect data type, or using improper columns for joins). However, there is a minor syntax issue: the query is missing a closing parenthesis at the end.

Here’s the corrected version of your SQL query:
SELECT FirstName, LastName, Address, Phone
FROM customers
WHERE FirstName = "Emma" AND LastName = "Jones";
Note that in most modern databases (like PostgreSQL or MySQL), you can use either single quotes (') or double quotes (") for string literals. However, it’s generally considered a good practice to use single quotes for strings to avoid any potential issues with identifier quoting.

So, if we follow the best practices and use single quotes for string literals, the query would be:
SELECT FirstName, LastName, Address, Phone
FROM customers
WHERE FirstName = 'Emma' AND LastName = 'Jones';
This version of the SQL statement should work without any issues.
Thought:The corrected query has been provided by the query-checker. I will now use this corrected query to retrieve Emma Jones’s contact information.

Action: query-sql
Action Input: SELECT FirstName, LastName, Address, Phone FROM customers WHERE FirstName = ‘Emma’ AND LastName = 'Jones
Observation: QueryFailedError: SQLITE_ERROR: unrecognized token: “'Jones”
Thought:The error is due to the missing closing parenthesis in the SQL query. Let’s correct that and re-execute.

Action: query-sql
Action Input: SELECT FirstName, LastName, Address, Phone FROM customers WHERE FirstName = ‘Emma’ AND LastName = 'Jones
Observation: QueryFailedError: SQLITE_ERROR: unrecognized token: “'Jones”
Thought:The issue is still present due to the missing closing parenthesis. Additionally, there’s an extra quotation mark in “Jones”. Let’s correct this.

Action: query-sql
Action Input: SELECT FirstName, LastName, Address, Phone FROM customers WHERE FirstName = ‘Emma’ AND LastName = 'Jones
Observation: QueryFailedError: SQLITE_ERROR: unrecognized token: “'Jones”
Thought:The issue persists because the SQL query is still not correctly formatted. It appears there’s an extra quotation mark within “Jones”. Let me correct this and also ensure I include all necessary components of a valid SELECT statement.

Action: query-sql
Action Input:
SELECT FirstName, LastName, Address, Phone FROM customers WHERE FirstName = 'Emma' AND LastName = 'Jones'
``
Observation: QueryFailedError: SQLITE_ERROR: unrecognized token: "```sql
SELECT FirstName, LastName, Address, Phone FROM customers WHERE FirstName = 'Emma' AND LastName = 'Jones'
``"
Thought:The issue is due to the incorrect formatting of the query input. The action input should not include backticks or line breaks for a single SQL statement.

Action: query-sql
Action Input: SELECT FirstName, LastName, Address, Phone FROM customers WHERE FirstName = 'Emma' AND LastName = 'Jones
Observation: QueryFailedError: SQLITE_ERROR: unrecognized token: "'Jones"
Thought:The issue is still due to the missing closing parenthesis and an extra quotation mark. Let me correct that.

Action: query-sql
Action Input: SELECT FirstName, LastName, Address, Phone FROM customers WHERE FirstName = 'Emma' AND LastName = 'Jones
Observation: QueryFailedError: SQLITE_ERROR: unrecognized token: "'Jones"
Thought:The issue is due to the missing closing parenthesis and an extra quotation mark at the end of "Jones". Let me correct that by removing the unnecessary characters.

Action: query-sql
Action Input: SELECT FirstName, LastName, Address, Phone FROM customers WHERE FirstName = 'Emma' AND LastName = 'Jones
Observation: QueryFailedError: SQLITE_ERROR: unrecognized token: "'Jones"
Thought:The issue seems to be arising from the way the input is formatted in the query-sql action. Since we're dealing with a string literal, let's ensure that there are no extra characters or formatting issues that might cause this error.

Let me correct and reformat the SQL query:

Action: query-sql
Action Input: SELECT FirstName, LastName, Address, Phone FROM customers WHERE FirstName = 'Emma' AND LastName = 'Jones
Observation: QueryFailedError: SQLITE_ERROR: unrecognized token: "'Jones"
Thought:The issue appears to be arising from the way the input is formatted in the query-sql action. The error message suggests there might be an extra character or incorrect formatting, but upon closer inspection, it seems like a generic response to any syntax errors.

Let's take another approach and simplify our SQL query:

Action: query-checker
Action Input: SELECT FirstName, LastName, Address, Phone FROM customers WHERE FirstName = 'Emma' AND LastName = 'Jones
Observation: The SQL query you provided is:

```sql
SELECT FirstName, LastName, Address, Phone
FROM customers
WHERE FirstName = 'Emma' AND LastName = 'Jones'
After reviewing this query for common mistakes, I can confirm that it does not contain any of the issues listed:

It doesn’t use NOT IN with NULL values.

It doesn’t use UNION or UNION ALL.

It doesn’t use BETWEEN for exclusive ranges.

The data types in the WHERE clause look correct (assuming FirstName and LastName are strings).

Identifiers are properly quoted (in this case, they’re not necessary as there are no special characters or reserved words used as identifiers).

There are no functions being called with an incorrect number of arguments.

No casting to a specific data type is needed based on the given query.

It doesn’t perform any joins.

The original query is correct and does not require any changes. Here it is again:
SELECT FirstName, LastName, Address, Phone
FROM customers
WHERE FirstName = 'Emma' AND LastName = 'Jones'
However, please note that this SQL statement expects the LastName value to include a trailing space in the query string because of how you wrote “Jones” (without a closing quote), which might be an unintentional mistake. If it was indeed intentional and there’s supposed to be a trailing space after ‘Jones’, then the query is fine as is, otherwise, you should add a missing closing quote:
SELECT FirstName, LastName, Address, Phone
FROM customers
WHERE FirstName = 'Emma' AND LastName = 'Jones'
If ‘Jones’ was indeed meant to have no trailing spaces and there’s an extra space in your SQL query, it would look like this instead (assuming you forgot the closing quote):
SELECT FirstName, LastName, Address, Phone
FROM customers
WHERE FirstName = 'Emma' AND LastName = 'Jones'
Make sure that the data stored for LastName matches exactly what is specified in the query to avoid potential issues. If there’s a possibility of extra spaces in your table entries or if you want to account for such cases, consider trimming whitespace:
SELECT FirstName, LastName, Address, Phone
FROM customers
WHERE FirstName = 'Emma' AND TRIM(LastName) = 'Jones'
This last version ensures that any leading or trailing spaces are removed from LastName before comparison.
Thought:The issue seems to be with how the query is being input into the action. Let’s try a simpler approach and ensure there are no extra characters.

Action: query-sql
Action Input: SELECT FirstName, LastName, Address, Phone FROM customers WHERE FirstName = ‘Emma’ AND LastName = 'Jones
Observation: QueryFailedError: SQLITE_ERROR: unrecognized token: “'Jones”
Thought:The issue seems to be arising from the way the input is formatted in the query-sql action. It appears that there might be an extra space or character at the end of “Jones”. Let’s correct this by ensuring the SQL query is properly formatted.

Action: query-checker
Action Input: SELECT FirstName, LastName, Address, Phone FROM customers WHERE FirstName = ‘Emma’ AND LastName = 'Jones
Observation: The given SQL query does not contain any of the specified common mistakes:
SELECT FirstName, LastName, Address, Phone
FROM customers
WHERE FirstName = 'Emma' AND LastName = 'Jones';
This query is looking for records in the customers table where the first name is 'Emma' and the last name is 'Jones'. It does not use NOT IN with NULL values, UNION or UNION ALL, BETWEEN, has no data type mismatch issues (the comparison seems to be correct), properly quotes string literals, uses the correct number of arguments for predicates (none required here as it’s just a simple equality check), and there are no joins involved.

However, one thing that stands out is that the query might not work correctly if LastName is stored without a trailing space. The provided query assumes that ‘Jones’ will have an exact match including any trailing spaces in the database. If this is a concern, you could consider using the TRIM function:
SELECT FirstName, LastName, Address, Phone
FROM customers
WHERE FirstName = 'Emma' AND TRIM(LastName) = 'Jones';
But if we strictly adhere to what was asked and only correct common mistakes from your list (which are not present in this query), the original query is correct. So here’s the original:
SELECT FirstName, LastName, Address, Phone
FROM customers
WHERE FirstName = 'Emma' AND LastName = 'Jones';
If there were any other potential issues or optimizations you’d like to discuss, feel free to ask!

Please share your workflow

Information on your n8n setup

**n8n version: 1.64.2
**Database (default: SQLite): SQLITE
**n8n EXECUTIONS_PROCESS setting (default: own, main): own
**Running n8n via (Docker, npm, n8n cloud, desktop app): docker
**Operating system: win 11

Jon · November 13, 2024, 9:35am

Hey @Afamocc,

Do you have an example of the chat input you are using? I have tried running your worklfow after finding a sqlite database and it appears to be working for me.

system · February 11, 2025, 9:36am

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.