Apparent bug in LangChain.js langchain/tools/sql QuerySqlTool when joining a table and a view

[DanTahir]

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

import { DataSource } from "typeorm";
import { QuerySqlTool } from "langchain/tools/sql";
import { SqlDatabase } from "langchain/sql_db";

const datasource = new DataSource({
  type: "postgres",
  host: process.env.DB_HOST,
  port: _.toNumber(process.env.DB_PORT),
  username: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME
});

const executeQuery = async () => {

  const query = `
    SELECT i."severity",
           AVG(c."speed_avg_am") as avg_morning_speed,
           AVG(c."speed_avg_pm") as avg_evening_speed,
           COUNT(*) as incident_count
    FROM "public"."inrix_incidents_copy" i
    JOIN "public"."corridor_monthly_summary_vw" c
      ON i."corridorLabel" = c."corridorlabel"
     AND DATE_TRUNC('month', i."date") = c."date"
    GROUP BY i."severity"
    ORDER BY i."severity" DESC
    LIMIT 100;
  `;
  const db = await SqlDatabase.fromDataSourceParams({
    appDataSource: datasource,
    includesTables: ["inrix_incidents_copy", "ccta_projects_all_records_vw", "corridor_monthly_summary_vw"],
  });
  const executeQueryTool = new QuerySqlTool(db);
  return { result: await executeQueryTool.invoke(query) };
};

executeQuery();

Description

I'm trying to use langchain's QuerySqlTool to run an AI-generated SQL query that joins a table and a view.

When I run the same query directly against my database, I get results, but when I run the query in langchain it returns no results. Not an error, just no results.

The tool returns results for either the table or the view searched separately, but not for the join query.

System Info

platform mac
node v20.15.1
langchain 0.3.10

[DanTahir]

Apparently the query doesn't work in all SQL clients. It works in DBeaver but not in PGAdmin.

The following revised query works with the QuerySqlTool:

SELECT i."severity", AVG(c."speed_avg_am") as avg_morning_speed, AVG(c."speed_avg_pm") as avg_evening_speed, COUNT(*) as incident_count FROM "public"."inrix_incidents_copy" i JOIN "public"."corridor_monthly_summary_vw" c ON i."corridorLabel" = c."corridorlabel" AND DATE_TRUNC('month', i."date") = DATE_TRUNC('month', c."date") GROUP BY i."severity" ORDER BY i."severity" DESC LIMIT 100;

So maybe this is less a bug than a quirk of DBeaver allowing me to run something I shouldn't be able to run, but I'll leave this here in case it's of interest to the devs.