LLM Truncates SQL Query Results When Using LangChain SQL Agent

[r78v10a07]

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

from sqlalchemy import create_engine
from langchain.agents import create_sql_agent
from langchain_openai import AzureChatOpenAI
from langchain.sql_database import SQLDatabase
from langchain_community.agent_toolkits import SQLDatabaseToolkit

sql_server = "server.database.windows.net"
sql_db = "mydatabase"
sql_user = "user"
sql_pass = "pass"

# Database in Azure SQL with one single table named Person
# CREATE TABLE Person (
#        PersonID INT IDENTITY(1,1) PRIMARY KEY,
#        Name VARCHAR(255) NOT NULL,
#        Lastname VARCHAR(255) NOT NULL,
#        Age INT NOT NULL,
# );
# Then insert more than 1000 person with 120 children (Age < 18)

connectionString = f"mssql+pyodbc://{sql_user}:{sql_pass}@{sql_server}/{sql_db}?driver=ODBC+Driver+18+for+SQL+Server"
db_engine = create_engine(connectionString)

db = SQLDatabase(db_engine, view_support=True, schema="dbo")
azurellm = AzureChatOpenAI(
    deployment_name="gpt-4o",
    model_name="gpt-4o",
    api_version="2024-08-01-preview",
    temperature=0.1
)
toolkit = SQLDatabaseToolkit(db=db, llm=azurellm)
agent_executor = create_sql_agent(
    llm=azurellm,
    toolkit=toolkit,
    verbose=True,
    agent_type="openai-tools"
)
agent("give me a numbered list of all children in the data")

# The LLM return a truncated list instead of returning a list with all children.

# If we ask instead:
agent("give me a numbered list of all children in the data. I know that there are 120 children, return all")

# The LLM will return the full list.

Description

We followed the LangChain tutorial to query our Azure SQL database using LangChain and OpenAI through a SQL Agent. While it generally works fine, we've encountered an issue where the Large Language Model (LLM) truncates the expected answer.

• SQL Query Translation: We ask a question in natural language, which is correctly translated into a SQL query by the system.

• Result Truncation: The SQL query returns 120 rows, but the LLM's response includes information from only the first 10-15 rows, then adds a phrase like "... and many more."

• Example Scenario:

  • We have a table called Person with 120 children (where age < 18).
  • When we ask the LLM to return the names of all children, it returns only the first few names followed by "... and many more."
  • However, if we modify our prompt to include: "Give me a list of all children. I know that there are 120, return all," the LLM returns the full list.

Attempts to Resolve:

• We've experimented with modifying the prompt instructions in various ways.
• Despite these changes, the LLM continues to return a truncated answer when requesting to return a full list.

Request for Assistance:

• Can you provide advice or point us toward someone who could help us figure out how to get the LLM to return the full answer without truncation?
• Is this issue related to how the LangChain agent presents the data to the LLM, or is it a limitation or behavior of the LLM itself?

Additional Information:

• Any insights into adjusting the LangChain agent or prompt engineering to prevent this truncation would be greatly appreciated.
• We're looking to understand whether this is a common issue and the best practices to handle large result sets with LLMs in LangChain.

Thank you for your assistance.

System Info

System Information

OS: Linux
OS Version: #18~22.04.1-Ubuntu SMP Thu Oct 3 19:57:42 UTC 2024
Python Version: 3.11.10 | packaged by conda-forge | (main, Oct 16 2024, 01:27:36) [GCC 13.3.0]

Package Information

langchain_core: 0.3.14
langchain: 0.3.6
langchain_community: 0.3.2
langsmith: 0.1.135
langchain_cohere: 0.3.1
langchain_experimental: 0.3.2
langchain_mistralai: 0.2.0
langchain_openai: 0.2.2
langchain_pinecone: 0.2.0
langchain_text_splitters: 0.3.0
langgraph: 0.2.38

Optional packages not installed

langserve

Other Dependencies

aiohttp: 3.9.5
async-timeout: Installed. No version info available.
cohere: 5.11.2
dataclasses-json: 0.6.7
httpx: 0.27.2
httpx-sse: 0.4.0
jsonpatch: 1.33
langgraph-checkpoint: 2.0.1
langgraph-sdk: 0.1.33
numpy: 1.26.4
openai: 1.51.2
orjson: 3.10.7
packaging: 24.1
pandas: 2.2.3
pinecone-client: 5.0.1
pydantic: 2.9.2
pydantic-settings: 2.5.2
PyYAML: 6.0.2
requests: 2.32.3
requests-toolbelt: 1.0.0
SQLAlchemy: 2.0.36
tabulate: 0.9.0
tenacity: 8.5.0
tiktoken: 0.8.0
tokenizers: 0.20.1
typing-extensions: 4.12.2

[zzw0109]

Hello, did you get the solution for your issue? I face the similar issue, the sql agent truncates the tables and just return first row and last row or it returns with some of columns