Challenges involving multiple SQL JOINs

[jpmckinney]

I followed the instructions on this page: https://python.langchain.com/docs/modules/agents/toolkits/sql_database

My code is:

from langchain.agents import create_sql_agent
from langchain.agents.agent_toolkits import SQLDatabaseToolkit
from langchain.sql_database import SQLDatabase
from langchain.llms.openai import OpenAI
from langchain.agents import AgentExecutor
from langchain.agents.agent_types import AgentType
from langchain.chat_models import ChatOpenAI

db = SQLDatabase.from_uri("sqlite:///Chinook.db")

toolkit_llm = OpenAI(
    temperature=0,
)

agent_llm = ChatOpenAI(
    temperature=0,
    model="gpt-3.5-turbo-0613",
)

toolkit = SQLDatabaseToolkit(db=db, llm=toolkit_llm)

agent_executor = create_sql_agent(
    llm=agent_llm,
    toolkit=toolkit,
    verbose=True,
    agent_type=AgentType.OPENAI_FUNCTIONS,
)

I then run:

agent_executor.run("What is the total milliseconds across all tracks, except those by AC/DC?")

I expect a query like:

SELECT SUM(Milliseconds) FROM Track JOIN Album ON Track.AlbumId = Album.AlbumId JOIN Artist ON Album.ArtistId = Artist.ArtistId WHERE Artist.Name != 'AC/DC';

Instead it tries the following (abbreviated output):

Invoking: `sql_db_list_tables` with `{}`
Invoking: `sql_db_schema` with `Track`
Invoking: `sql_db_query` with `SELECT SUM(Milliseconds) AS TotalMilliseconds FROM Track WHERE ArtistId != (SELECT ArtistId FROM Artist WHERE Name = 'AC/DC')`
  Error: (sqlite3.OperationalError) no such column: ArtistId
Invoking: `sql_db_schema` with `Artist`
Invoking: `sql_db_query` with `SELECT SUM(Milliseconds) AS TotalMilliseconds FROM Track WHERE TrackId NOT IN (SELECT TrackId FROM Track JOIN Artist ON Track.ArtistId = Artist.ArtistId WHERE Artist.Name = 'AC/DC')`
  Error: (sqlite3.OperationalError) no such column: Track.ArtistId
Invoking: `sql_db_query_checker` with `SELECT SUM(Milliseconds) AS TotalMilliseconds FROM Track WHERE TrackId NOT IN (SELECT TrackId FROM Track JOIN Artist ON Track.ArtistId = Artist.ArtistId WHERE Artist.Name = 'AC/DC')`
  SELECT SUM(Milliseconds) AS TotalMilliseconds FROM Track WHERE TrackId NOT IN (SELECT TrackId FROM Track JOIN Artist ON Track.ArtistId = Artist.ArtistId WHERE Artist.Name = 'AC/DC' AND TrackId IS NOT NULL)
Invoking: `sql_db_query` with `SELECT SUM(Milliseconds) AS TotalMilliseconds FROM Track WHERE TrackId NOT IN (SELECT TrackId FROM Track JOIN Artist ON Track.ArtistId = Artist.ArtistId WHERE Artist.Name = 'AC/DC' AND TrackId IS NOT NULL)`
  Error: (sqlite3.OperationalError) no such column: Track.ArtistId
Invoking: `sql_db_query` with `SELECT SUM(Milliseconds) AS TotalMilliseconds FROM Track WHERE TrackId NOT IN (SELECT TrackId FROM Track JOIN Artist ON Track.ArtistId = Artist.ArtistId WHERE Artist.Name = 'AC/DC')`
  Error: (sqlite3.OperationalError) no such column: Track.ArtistId
Invoking: `sql_db_query` with `SELECT SUM(Milliseconds) AS TotalMilliseconds FROM Track WHERE TrackId NOT IN (SELECT TrackId FROM Track JOIN Artist ON Track.ArtistId = Artist.ArtistId WHERE Artist.Name != 'AC/DC')`
  Error: (sqlite3.OperationalError) no such column: Track.ArtistId
Invoking: `sql_db_query` with `SELECT SUM(Milliseconds) AS TotalMilliseconds FROM Track WHERE TrackId NOT IN (SELECT TrackId FROM Track JOIN Artist ON Track.ArtistId = Artist.ArtistId WHERE Artist.Name <> 'AC/DC')`
  Error: (sqlite3.OperationalError) no such column: Track.ArtistId
Invoking: `sql_db_query` with `SELECT SUM(Milliseconds) AS TotalMilliseconds FROM Track WHERE TrackId NOT IN (SELECT TrackId FROM Track JOIN Artist ON Track.ArtistId = Artist.ArtistId WHERE Artist.Name != 'AC/DC')`
  Error: (sqlite3.OperationalError) no such column: Track.ArtistId
Invoking: `sql_db_query` with `SELECT SUM(Milliseconds) AS TotalMilliseconds FROM Track WHERE TrackId NOT IN (SELECT TrackId FROM Track JOIN Artist ON Track.ArtistId = Artist.ArtistId WHERE Artist.Name <> 'AC/DC')`
  Error: (sqlite3.OperationalError) no such column: Track.ArtistId
Invoking: `sql_db_query` with `SELECT SUM(Milliseconds) AS TotalMilliseconds FROM Track WHERE TrackId NOT IN (SELECT TrackId FROM Track JOIN Artist ON Track.ArtistId = Artist.ArtistId WHERE Artist.Name != 'AC/DC')`
  Error: (sqlite3.OperationalError) no such column: Track.ArtistId
Invoking: `sql_db_query` with `SELECT SUM(Milliseconds) AS TotalMilliseconds FROM Track WHERE TrackId NOT IN (SELECT TrackId FROM Track JOIN Artist ON Track.ArtistId = Artist.ArtistId WHERE Artist.Name != 'AC/DC')`
  Error: (sqlite3.OperationalError) no such column: Track.ArtistId
Invoking: `sql_db_query` with `SELECT SUM(Milliseconds) AS TotalMilliseconds FROM Track WHERE TrackId NOT IN (SELECT TrackId FROM Track JOIN Artist ON Track.ArtistId = Artist.ArtistId WHERE Artist.Name != 'AC/DC')`
  Error: (sqlite3.OperationalError) no such column: Track.ArtistId

(Note that the Chinook.db database has foreign keys for all relevant IDs - it is definitely possible to determine how the Track table can join to the Artist table using only the foreign key information.)

Is there any way to provide a hint to the agent, so that it performs the JOINs correctly? Is there some configuration I'm missing, or is there an LLM or chat model that performs better?