model creating own column name

#50
by abeotttyy - opened

I used the following prompt:
prompt=f"""

Task

Generate a MSSQL query to answer the following question: What is the age of Smith?

Database Schema

This query will run on a database whose schema is represented in this string:

Table 'students' ##\n\nSchema:\n('StudentID', 'AutoField')\n('FirstName', 'TextField')\n('LastName', 'TextField')\n('Age', 'IntegerField')

SQL

Given the database schema, here is the SQL query that answers What is the age of Smith?:

"""

I am getting the following sql

SELECT s.age FROM students AS s WHERE s.last_name = 'Smith'


LastName ------------->> changed to ------------------->>last_name

Temp solution, Lower all the characters 
LastName------->>lastname

@abeotttyy two things in the prompt are likely causing the invented columns.

First, the schema is passed as Django field tuples. sqlcoder expects CREATE TABLE statements, so give it DDL instead, for example: CREATE TABLE students (StudentID INT PRIMARY KEY, FirstName VARCHAR(100), LastName VARCHAR(100), Age INT);

Second, Smith is ambiguous between FirstName and LastName. Add a short hint such as "Smith is a last name" or a one-line comment on the column.

Also, sqlcoder-7b-2 was tuned mainly on PostgreSQL. For SQL Server, check the output for LIMIT versus TOP, or generate PostgreSQL and convert it with sqlglot.

Sign up or log in to comment