create_sql_agent is confusing instruction steps (Observation)

[telekosmos]

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

llm = HuggingFaceEndpoint(
    repo_id="meta-llama/Meta-Llama-3-8B-Instruct",
    task="text-generation",
    max_new_tokens=512,
    do_sample=False,
    repetition_penalty=1.03,
    temperature=0.01
)

_AGENT_FORMAT_INSTRUCTIONS="""
## Use exactly the following format. Don't merge one step content with the next one:

- 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 [{tool_names}].
- Action Input: the input to the action. DO NOT INCLUDE THE WORD 'Observation'.
- Observation: the result of the action.
... (this Thought/Action/Action Input/Observation can repeat N times)
- Final Thought: I now know the final answer.
- Final Answer: the final answer to the original input question.
- SQL Query used to get the Answer: the final sql query used for the final answer

"""

def answer_query_agent(question: str):
  db = SQLDatabase(engine=sqlalchemy_engine)
  toolkit = SQLDatabaseToolkit(db=db, llm=llm)
  output_parser = StructuredChatOutputParserWithRetries.from_llm(llm=the_llm)

  agent_executor: AgentExecutor = create_sql_agent(
    agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,
    llm=llm,
    toolkit=toolkit,
    format_instructions=_AGENT_FORMAT_INSTRUCTIONS,
    verbose=True,
    agent_executor_kwargs={"handle_parsing_errors": True},
    max_iterations=15,
    top_k=15,
    output_parser=output_parser)

  try:
    resp: Dict[str, Any] = agent_executor.invoke({"input": question})
    print(f'** Executor result **\n\n{resp}')
  except OutputParserException as e:
    print(f"Error parsing output: {e}")
    if e.send_to_llm:
        # Optionally, send the observation and llm_output back to the model
        print(f"Observation: {e.observation}")
        print(f"LLM Output: {e.llm_output}")

  return resp

Description

Hi

I'm trying to query a simple database (just company and sales tables) but using the code outlined above I'm getting multiple times with different models a confusing output like it's following:

> Entering new SQL Agent Executor chain...
 I need to find out which companies have a commitment with sustainability.
Action: sql_db_schema
Action Input: company, sustainability_commitment
ObservationError: table_names {'sustainability_commitment\nObservation'} not found in database The table 'ustainability_commitment' does not exist. I need to find the correct table name.
Action: sql_db_list_tables
Action Input: 
Observationcompany, sales The table 'company' exists, but I still don't know the name of the table related to sustainability.
Action: sql_db_schema
Action Input: company, sales
ObservationError: table_names {'sales\nObservation'} not found in database The table 'ales' does not exist either. I need to find the correct table name.
Action: sql_db_list_tables
Action Input: 
Observationcompany, sales The table 'company' exists, but I still don't know the name of the table related to sustainability.
Action: sql_db_schema
Action Input: company
ObservationError: table_names {'company\nObservation'} not found in database The table 'company' does not exist either. I need to find the correct table name.
...

So, it is confusing the word Observation with the value of the Action Input and hence starting a chained mess. I've tried many things, like set suffix='' or being more restrictive in the instructions but the Observation word keeps getting stuck to the Action Input. Is there any way to get rid of this issue via parameter or prompting or this is a bug or a lack of model performance?

It is working ok using AzureChatOpenAI, but we prefer other models for this.

This is very similar (even the same) to #21652 (unanswered)

System Info

System Information
------------------
> OS:  Linux
> OS Version:  #202-Ubuntu SMP Fri Apr 26 12:29:36 UTC 2024
> Python Version:  3.12.1 (main, Jun 29 2024, 18:54:41) [GCC 9.4.0]

Package Information
-------------------
> langchain_core: 0.2.11
> langchain: 0.2.6
> langchain_community: 0.2.6
> langsmith: 0.1.83
> langchain_chroma: 0.1.2
> langchain_experimental: 0.0.62
> langchain_huggingface: 0.0.3
> langchain_openai: 0.1.14
> langchain_text_splitters: 0.2.2

Packages not installed (Not Necessarily a Problem)
--------------------------------------------------
The following packages were not found:

> langgraph
> langserve

[dosubot[bot]]

To address the issue of the word "Observation" being incorrectly included in the action inputs when using the create_sql_agent, you can customize the stop sequence to ensure that the agent stops processing when it encounters the word "Observation:". This can help prevent it from being included in the action inputs.

Here is an example of how you can customize the stop sequence:

from langchain_core.language_models import BaseLanguageModel
from langchain_core.prompts import ChatPromptTemplate
from langchain_core.tools import BaseTool
from langchain.agents.structured_chat.output_parser import StructuredChatOutputParserWithRetries
from langchain.agents import create_sql_agent, AgentExecutor
from langchain.agents.agent_types import AgentType
from langchain_community.agent_toolkits.sql.toolkit import SQLDatabaseToolkit
from langchain_community.tools.sql_database.tool import SQLDatabase
import sqlalchemy

# Define your LLM, tools, and prompt
llm = HuggingFaceEndpoint(
    repo_id="meta-llama/Meta-Llama-3-8B-Instruct",
    task="text-generation",
    max_new_tokens=512,
    do_sample=False,
    repetition_penalty=1.03,
    temperature=0.01
)

# Define the SQL database and toolkit
engine = sqlalchemy.create_engine('sqlite:///your_database.db')
db = SQLDatabase(engine=engine)
toolkit = SQLDatabaseToolkit(db=db, llm=llm)

# Define the output parser
output_parser = StructuredChatOutputParserWithRetries.from_llm(llm=llm)

# Customize the stop sequence to handle "Observation"
stop_sequence = ["Observation:"]

# Create the agent with the customized stop sequence
agent_executor: AgentExecutor = create_sql_agent(
    agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,
    llm=llm,
    toolkit=toolkit,
    format_instructions=_AGENT_FORMAT_INSTRUCTIONS,
    verbose=True,
    agent_executor_kwargs={"handle_parsing_errors": True},
    max_iterations=15,
    top_k=15,
    output_parser=output_parser,
    stop_sequence=stop_sequence
)

def answer_query_agent(question: str):
    try:
        resp: Dict[str, Any] = agent_executor.invoke({"input": question})
        print(f'** Executor result **\n\n{resp}')
    except OutputParserException as e:
        print(f"Error parsing output: {e}")
        if e.send_to_llm:
            # Optionally, send the observation and llm_output back to the model
            print(f"Observation: {e.observation}")
            print(f"LLM Output: {e.llm_output}")

    return resp

By setting the stop_sequence to ["Observation:"], you ensure that the agent stops processing at the word "Observation:", preventing it from being included in the action inputs. This should help mitigate the issue you're experiencing ^[1][2].

To continue talking to Dosu, mention @dosu.

[telekosmos]

@dosu result was the same (actually no matter the value I set in stop_sequence, messing around with the Observation things:

> Entering new SQL Agent Executor chain...
 I need to find out which companies have a commitment with sustainability.
Action: sql_db_schema
Action Input: company, sustainability_commitment
ObservationError: table_names {'sustainability_commitment\nObservation'} not found in database The table 'ustainability_commitment' does not exist. I need to find the correct table name.
Action: sql_db_list_tables
...

any other hint or idea?

[dosubot[bot]]

To resolve the issue of the word "Observation" being incorrectly included in the action inputs, you should ensure that the stop_sequences parameter in the HuggingFaceEndpoint class includes "Observation:". This will help the model to stop generating text when it encounters the word "Observation:", thus preventing it from being included in the action inputs.

Here is the relevant code snippet:

from langchain_core.language_models.llms import HuggingFaceEndpoint

llm = HuggingFaceEndpoint(
    endpoint_url="http://localhost:8010/",
    max_new_tokens=200,
    stop_sequences=["Observation:", "Human:"],
    huggingfacehub_api_token="your-api-key"
)

generated = llm.invoke(prompt)
print(generated)

By including "Observation:" in the stop_sequences list, the model will stop generating text at the point where it encounters "Observation:", thus resolving the issue ^[1][2][3].

Additionally, you can use debugging tools provided by LangChain to trace and debug the model's behavior. Setting the global debug flag to True will cause all LangChain components to print the inputs they receive and outputs they generate, which can help you identify where the issue occurs.

from langchain.globals import set_debug

set_debug(True)

agent.run("Your query here")

This will provide detailed logs of the model's actions and outputs, helping you to further diagnose and resolve the issue ^[4].

To continue talking to Dosu, mention @dosu.