#137·textql

Magicify() sometimes turnes double quotes into single quotes

Author: 9072997Created Mar 9, 2022Updated Mar 9, 2022

In SQLite, "this is a column name" and 'this is a string literal'. The lines are blured a little bit (see SQLite quirk #8), but the critical point is that "this" and 'this' do not mean the same thing. Sometimes textql converts "this" to 'this', which changes the behavior of the query. In the example queries below it means that rather than getting 1000s of diffrent names (1 per row), I get 1000s of copies of the string Parent First Name (1 per row). Strange changes are able to trigger/fix this behavior. For example:

This works as expected:

sql
SELECT trim("Parent First Name") AS "FirstName", "Parent Email" NOT IN (SELECT "Student Email" FROM source) AS notin FROM source

This gives me the string Parent First Name over and over. Note that the only difference is that I didn't specify an output column name:

sql
SELECT trim("Parent First Name"), "Parent Email" NOT IN (SELECT "Student Email" FROM source) AS notin FROM source

I was able to track the issue down to the Magicify() function. My guess would be that the magic breaks double quoted column names, and if you accidentally trigger the magic, it breaks the query (and also makes it lower case for some reason, which is fine, but odd)

go
// this query gets passed through unaltered
sql = Magicify(`SELECT trim("Parent First Name") AS "FirstName", "Parent Email" NOT IN (SELECT "Student Email" FROM source) AS notin FROM source`, "source")
fmt.Println(sql)
// SELECT trim("Parent First Name") AS "FirstName", "Parent Email" NOT IN (SELECT "Student Email" FROM source) AS notin FROM source

// this query gets mangled
sql := Magicify(`SELECT trim("Parent First Name"), "Parent Email" NOT IN (SELECT "Student Email" FROM source) AS notin FROM source`, "source")
fmt.Println(sql)
// select trim('Parent First Name'), 'Parent Email' not in (select 'Student Email' from source) as notin from source