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:
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:
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)
// 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
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 stringParent First Name(1 per row). Strange changes are able to trigger/fix this behavior. For example:This works as expected:
This gives me the string
Parent First Nameover and over. Note that the only difference is that I didn't specify an output column name: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)