I had acidentially written this code
SELECT COUNT(*) "Table0" WHERE "Column0" = ? LIMIT 1;
When I meant to write this code:
SELECT COUNT(*) FROM "Table0" WHERE "Column0" = ? LIMIT 1;
I was really surprised that the first line compiled without giving any kind of error. I have checked the syntax on in the SQLite docs, and it doesn't seem like this should compile, but it does. What is going on here? How is this getting interpreted by SQLite?
The incorrect line always returns 0, even if there is a match in the table.
Also, interesting, is that LibSQL actually does fail, but with a message about "Column0" not existing... perhaps this will make more sense once we understand how it is getting interpreted...
The AS keyword is optional when specifying an alias, so SELECT COUNT(*) "Table0" means the same as SELECT COUNT(*) AS "Table0".
The default table when you don’t specify a FROM has one row and no columns. A more typical way to use it is with expressions like SELECT 1 + 1 or SELECT EXISTS (…).
Because it isn’t a resolvable name, "Column0" is treated as a string due to a MySQL compatibility misfeature that’s gradually being removed from SQLite; your LibSQL was apparently configured with that option disabled, or maybe it was dropped from the fork entirely. (You can confirm this by passing 'Column0' as the parameter value and seeing the result go from 0 to 1.)