SQLGlot Implementation for Text2SQL Hallucination Detection - Technical Consultation

[jubinben98]

Hi @tobymao, @georgesittas, @geooo109, and SQLGlot maintainers,

We truly appreciate the excellent work on SQLGlot, it has been invaluable for our project. We are currently developing a hallucination detection module for our agentic framework that leverages SQLGlot to validate LLM-generated SQL queries against ground truth metadata which includes table and column information.

Our approach encompasses three validation layers:

1. Syntax Validation

Implementation: sqlglot.parse_one(sql_query) with exception handling for parse failures.
Status: Successfully implemented.

2. Dialect Validation

Current Approach: For the Dialect validation, we were using the sqlglot.parse_one(sql_query, read=target_dialect). The idea was to parse the SQL query using a target-dialect, if the parsing fails using the target-dialect, we will flag the dialect validation error.
Issue: We did a POC on this and found out we were not able implement this approach, as the parse_one function is parsing the SQL-query regardless of the target-dialect we are passing in the read argument.

Query 1: Could you please tell me what we are doing wrong? If possible could you please suggest a better way of going about this?

3. Schema Validation

Current Approach: For the Columns and tables validation we are using the following approach:

LLm-SQL-query
	| 
	-> Conver SQL to AST
		| 
		-> Extract the table names (`ast.find_all(sqlglot.expression.Table)`)  
	       Extract the column names (`ast.find_all(sqlglot.expression.Column)`) 
	       Extract the column aliases (`ast.find_all(sqlglot.expression.Alias)`)
	       Extract the table aliases (`ast.find_all(sqlglot.expression.TableAlias)`)
	        |
	        -> Remove the aliases from the column names
	           Remove the aliases from the table names
	             |
	             -> Compare the extracted column names and table names with the ground truth and complete the validation.

Query-2: How does this approach look? Are there any issues that you might foresee with this approach?
Query-3: Can SQLGlot work efficiently and accurately with complex and lengthy SQL queries?
Query-4: Do you have any recommendations on how to make this solution more robust or scalable (if required)?

Thank you for your time and consideration, we and look forward to your insights.

[georgesittas]

Hi @jubinben98, I suggest that you post this question in our Slack channel– "GitHub Discussions" isn't as actively monitored.

In any case...

• Note that SQLGlot is not a syntax validator. It recognizes certain families of syntax errors, but not everything
• Look into sqlglot.optimizer.qualify, I think it may solve some of your problems
• Yes, SQLGlot is generally quite efficient, but of course it's bounded by Python's runtime so keep that in mind