How to guide the AI shot at correct table?

[ray-linn]

Describe the bug
I created many views as flat tables and hope this could simplify the table relationship, but as a result, there are many duplicated field existing in tables, and sometime AI will generate SQL over wrong table, and lost the result.

To Reproduce
Steps to reproduce the behavior:

1. I defines a lookup table for working shift . ddl as following .

vn.train(ddl='''
        --- A lookup table to query shift,start time and end time over company ---
        CREATE TABLE [HumanResources].[Shift] (
        [ShiftID]      TINYINT      IDENTITY (1, 1) NOT NULL,
        [Name]         NVARCHAR (50) NOT NULL,
        [StartTime]    TIME (7)     NOT NULL,
        [EndTime]      TIME (7)     NOT NULL,
        [ModifiedDate] DATETIME     NOT NULL);
        ''')

2. in the View ".vEmployeeDepartmentHistory", there is a duplicated field "shift" (inner join to Shift table), because I do not like expose inner join to Vanna, I list the DDL as following.

       self.vn.train(ddl='''
.....
            [Shift]            NVARCHAR (50) NOT NULL,
.....
        ''')

3. i aslo add documents to tell LLM, the [shift] here is assigned to employee
   'Field Shift of table HumanResources.vEmployeeDepartmentHistory is the shift that assigned to an individual employee。'

4. A simple question is asked -
   vn.ask(question='''What is the shift duration for each shift?''')

Expected behavior
I suppose it will select something from HumanResources.Shift table, however , incorrect SQL is generated as "

SELECT Name, datediff(hour, StartTime, EndTime, this could be any of the following fields: DepartmentID, DepartmentName, or DepartmentName of the employee."...

Error logs/Screenshots
SELECT Name, datediff(hour, StartTime, EndTime, this could be any of the following fields: DepartmentID, DepartmentName, or DepartmentName of the employee."...

Desktop (please complete the following information where):
Vanna 0.5.4
Model CodeGemma, WizardCoder,

Additional context
Once i removed the "shift" from ".vEmployeeDepartmentHistory" DDL, everything is ok.

[zainhoda]

Different LLMs have weird behaviors. All Vanna does is control the context -- we have no control over how the LLM behaves. For best results use a GPT-4 class model.

[Molrn]

A solution I found to adress this table selection issue was to add an intermediate prompt which replaces the front. This prompt first sends all of the table names to the LLM and asks for the relevent tables to adress the user question. Then, the DDLs of the tables selected by the LLM are sent in the standard prompt. It can be time consuming, but it has proven on my side to be much more effective when dealing with a large amount of tables