Aliased function result with the same name issue
(1) By Max (Maxulite) on 2021-04-19 09:17:37 [link] [source]
Hi, I noticed a little unexpected way Sqlite treats field aliases and would like to make sure this is by design or not.
In a query like this
SELECT MyFunction(MyField) as MyField Where MyField<>''
(when the name of the field and the alias are the same) it looks like the condition is evaluated against the original MyField value, not the result of MyFunction. I checked this with the version 3.35.4. The simplest way to reproduce this is by using Trim function against several rows of repeated space symbols.
I also checked this in MySql (some old version) and seems like the condition of the query
SELECT Trim(Txt) as Txt FROM `testspaces` where Txt<>''
references the result of the function, not the original field.
A sidenote fun fact: recently I found the page with sqlite requirements (https://www.sqlite.org/requirements.html) is a very good source of Sqlite knowledge that can be queried alternatively to usual searches like web search or site search. This is due to the fact that it contains the facts about Sqlite (3243 as of today) in concentrated form so if you're lucky to formulate the query with the right terms, you will get a small set of rows that can be easily digested. I mention this because before posting I queried a table created from today requirements against "%alias%name%" and didn't find anything related so if the issue is by design then it would be great that a new requirement explaining this will appear in the requirements.
(2) By Larry Brasfield (larrybr) on 2021-04-19 15:24:08 in reply to 1 [link] [source]
The issue you raise, (whether an alias can hide the column name in expressions), does not appear to be addressed in the docs.
The behavior you see matches what PostgreSQL does. Hence, even if it was not intended originally, (which I doubt), it will be intended once SQLite's management becomes aware of the issue.
Expect to see a doc update on this.
(3) By Max (Maxulite) on 2021-04-19 16:37:42 in reply to 2 [link] [source]
Thanks for the info about PostgreSQL. Knowing how often PostgreSQL was mentioned as a point of reference It is more likely the things would probably stay as they are now.
Also in the following query Sqlite chooses the column value, not the function result (MySql chooses the Trim result)
select trim(txt) as txt from testspaces join (select '' as foo) on txt <> foo
Does it match PostgreSQL?
(4) By Max (Maxulite) on 2021-04-20 09:01:46 in reply to 2 [link] [source]
I had a vague thought that this was discussed before and finally found that the referencing of aliases in where and join clauses is not in the SQL standard. Actually both PostgreSQL and MySQL doesn't like it. I was wrong about MySql since I didn't test a query with a changed alias name. So, current tests for
select trim(txt) as txtalias from testspaces where txtalias <> ''
Unknown column 'txtalias' in 'where clause'PostgreSql
column "txtalias" does not exist
So, as long as it's an sqlite-only feature, it follows its own rules, no question asked.
My vague feeling was probably originated from an answer at the maillist from Igor Tandetnik many years ago that even quoted elsewhere sometimes without attribution
Per SQL standard, column aliases can be referenced in ORDER BY, GROUP BY and HAVING clauses. As an extension, SQLite also allows them in WHERE and JOIN ON clauses, but again, such usage is non-standard (though very convenient at times). Neither the standard nor SQLite implementation allow referencing aliases in the SELECT clause.
Still can't find anything related at sqlite.org (including requirements)
(5) By jake on 2021-04-20 11:06:00 in reply to 4 [source]
Here is a comment made by Richard Hipp back in 2015 on using aliased columns in a
This is not valid SQL, actually. For clarity, here is the (invalid) SQL reformatted:
SELECT a+b AS x FROM t1 WHERE x=99;
You are not suppose to be able to access the "x" alias within the WHERE clause.
Yes, I know that SQLite allows this. But it does so grudgingly, for historical reasons. It is technically not valid. Note that none of MySQL, Oracle, PostgreSQL, nor SQLServer allow statements like the above.
Because the SQL is technically not valid, I am less inclined to spend a lot of time trying to optimize it.
I really wish there was a way for me to fix this historical permissiveness in SQLite without breaking millions of (miscoded) iPhone/Android apps. I'd do so if I could.
(6) By Max (Maxulite) on 2021-04-20 12:04:24 in reply to 5 [link] [source]
Thanks, jake, this is a good source of information.
I also thought for a while and suspect that the issue I mentioned might be introduced intentionally.
So we have two variant of alias-enabled queries
- The ones that work only in sqlite producing errors on other dbmses. Here sqlite is free to behave like it prefers
- The ones that work both in sqlite and other dbmses because the names of alias(es) match(es) the names of columns. So in order for such queries to produce the same results in sqlite as everywhere, Sqlite must pick the column value, not the aliased value.
I also hope that this "feature" will finally get some attention in the documentation.
(7) By jake on 2021-04-20 13:05:03 in reply to 6 [link] [source]
Note that MySQL is not using the expression alias in a
WHERE clause, but just the original column value. Confusion might arise due to the use of the
TRIM function and the way MySQL string collations trim trailing spaces.
Refer to this documentation for MySQL 5.6:
Trailing Space Handling in Comparisons
Nonbinary strings have PAD SPACE behavior for all collations, including _bin collations. Trailing spaces are insignificant in comparisons:
mysql> SET NAMES utf8 COLLATE utf8_bin; mysql> SELECT 'a ' = 'a'; +------------+ | 'a ' = 'a' | +------------+ | 1 | +------------+
For binary strings, all bytes are significant in comparisons, including trailing spaces:
mysql> SET NAMES binary; mysql> SELECT 'a ' = 'a'; +------------+ | 'a ' = 'a' | +------------+ | 0 | +------------+