On 2018/05/16 6:21 PM, Dominique Devienne wrote:
On Wed, May 16, 2018 at 6:12 PM Clemens Ladisch <clem...@ladisch.de> wrote:
Stephen Chrzanowski wrote:
On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch <clem...@ladisch.de>
wrote:
SELECT action FROM blocked WHERE email = ?
UNION ALL
SELECT 'OK'
LIMIT 1;
Out of curiosity, where's the guarantee that the OK won't be displayed if
an email is found?
The "LIMIT 1" makes the DB stop after the first returned row.
It applies to the entire compound query, not only to the second SELECT.
But does SQL guarantee the first result-set (above the UNION ALL)
must be before the second one (below UNION ALL)? --DD
It always does for a UNION ALL query (not necessarily for UNION only) as
long as there is no ORDER BY clause, BUT, this is not guaranteed
behaviour, just happy accident.
To quote the COMPOUND SELECT documentation:
"When three or more simple SELECTs are connected into a compound SELECT,
they group from left to right. In other words, if "A", "B" and "C" are
all simple SELECT statements, (A op B op C) is processed as ((A op B) op
C)."
But then the Order-By documentation states:
"If a SELECT statement that returns more than one row does not have an
ORDER BY clause, the order in which the rows are returned is undefined.
Or, if a SELECT statement does have an ORDER BY clause, then the list of
expressions attached to the ORDER BY determine the order in which rows
are returned to the user."
The way I do these kinds of queries is force the order, like so:
SELECT 1 AS Idx, action FROM blocked WHERE email = ?
UNION ALL
SELECT 2, 'OK'
ORDER BY 1 LIMIT 1;
This is guaranteed to work always.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
