[Bug]: SQLTableRetrieverQueryEngine => "sql" at the start of most SQL statements

[nick-youngblut]

Bug Description

SQLTableRetrieverQueryEngine is generating SQL statements with "sql" at the start. For example:

sql
SELECT room$raw.name$ AS room_name, freezer$raw.name$ AS freezer_name
FROM freezer$raw
JOIN room$raw ON freezer$raw.location_id$ = room$raw.id
WHERE freezer$raw.archived$ = false AND room$raw.archived$ = false
ORDER BY room$raw.name$;

This results in GPT-4o stating "It appears there was an error in the SQL query provided".

Version

0.10.36

Steps to Reproduce

Follow https://docs.llamaindex.ai/en/stable/examples/index_structs/struct_indices/SQLIndexDemo/, but place in a streamlit chat app.

Relevant Logs/Tracbacks

No response

[dosubot]

It looks like the issue with the "sql" prefix in the SQL statements generated by SQLTableRetrieverQueryEngine might be related to the context_str_prefix parameter used in the initialization of the NLSQLRetriever. This parameter, if incorrectly set to include "sql", could lead to the observed behavior.

To address this, please check the configuration of the context_str_prefix in your setup to ensure it doesn't unintentionally include "sql". This parameter is meant to prefix the context string passed to the language model for SQL query generation and should be carefully configured to avoid such issues.

For a deeper dive, the configuration is found in the initialization of SQLTableRetrieverQueryEngine, specifically in the setup of the NLSQLRetriever. Here's a snippet for reference:

self._sql_retriever = NLSQLRetriever(
    sql_database,
    text_to_sql_prompt=text_to_sql_prompt,
    context_query_kwargs=context_query_kwargs,
    table_retriever=table_retriever,
    context_str_prefix=context_str_prefix,
    service_context=service_context,
    sql_only=sql_only,
)

Ensure that the context_str_prefix does not include "sql" unless it's intended for your specific use case. If adjusting this does not resolve the issue, further details about your configuration and the steps you're following would be helpful for a more targeted solution.

Details

Sources

• llama-index-core/llama_index/core/indices/struct_store/sql_query.py
• llama-index-legacy/llama_index/legacy/indices/struct_store/sql_query.py
• llama-index-legacy/llama_index/legacy/indices/struct_store/sql_retriever.py
• docs/docs/CHANGELOG.md

About Dosu
This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.