SQL Agent - Difference in sql_db_query_checker and sql_db_query

[kumarhanu7]

Checked other resources

• I added a very descriptive title to this question.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.

Commit to Help

• I commit to help with one of those options 👆

Example Code

few_shot_prompt = FewShotPromptTemplate(
    example_selector = example_selector,
    example_prompt=PromptTemplate.from_template(
        "User input: {input}\nSQL query: {query}"
    ),
    input_variables=["input", "dialect", "top_k"],
    prefix=system_prefix,
    suffix=""
)

full_prompt = ChatPromptTemplate.from_messages(
    [
        SystemMessagePromptTemplate(prompt=few_shot_prompt),
        ("human", "{input}"),
        MessagesPlaceholder("agent_scratchpad"),
    ]
)

agent_with_few_shots = create_sql_agent(
    llm=llm,
    db=db,
    prompt=full_prompt,
    verbose=True,
    agent_type="tool-calling",
    stream_runnable = False,
    max_iterations=5,
    agent_executor_kwargs = {"return_intermediate_steps": True},
    top_k = 2
)


agent_with_few_shots.invoke({
    "input": "list all the quantities in point of sale data of the STOCK of STORE_NAME"})

Description

{
"10": {
"quantity": "10",
.....
}
}

Invoking: sql_db_query_checker with `{'query': "SELECT JSON_UNQUOTE(JSON_EXTRACT(point_of_sales, '$.*.quantity')) AS quantity FROM stock WHERE store = 'STORE_NAME'

Whereas below is the actual query gets executed resulted [(None,)]

Invoking: sql_db_query with `{'query': "SELECT JSON_UNQUOTE(JSON_EXTRACT(point_of_sales, '$.[*].quantity')) AS quantity FROM stock WHERE store = 'STORE_NAME'

What is the reason actual executed query ('$..' vs '$.[].) generated differently? Please help to me fix.

System Info

langchain==0.2.16
windows
Python 3.11.0

[dosubot[bot]]

The difference in the actual executed query ('$..' vs '$.[].') generated by sql_db_query_checker and sql_db_query is due to the use of the query checker tool. When use_query_checker is set to True, the query_checker_chain is used to validate and potentially modify the initial SQL command generated by the LLM. This can result in different query formats.

To fix this issue, you can ensure that the query checker tool generates the desired query format by customizing the query_checker_prompt or by disabling the query checker tool if it is not needed.

Here is how you can disable the query checker tool:

db_chain = SQLDatabaseChain.from_llm(OpenAI(), db, use_query_checker=False)

Or, if you need to customize the query_checker_prompt to ensure it generates the correct query format:

from langchain_core.prompts.prompt import PromptTemplate

custom_query_checker_prompt = PromptTemplate(
    template="Your custom prompt template here",
    input_variables=["query", "dialect"]
)

db_chain = SQLDatabaseChain.from_llm(
    OpenAI(), db, 
    use_query_checker=True, 
    query_checker_prompt=custom_query_checker_prompt
)

By adjusting these settings, you can control the format of the executed query ^[1].

To continue talking to Dosu, mention @dosu.