there's also the NULL issue with this one: psql (9.6.7)
test=# SELECT NULL in (SELECT 1 WHERE 1!=1); ?column? ---------- f (1 row) vs. MariaDB [(none)]> SELECT NULL IN (SELECT 1 FROM DUAL WHERE 1!=1); +-----------------------------------------+ | NULL IN (SELECT 1 FROM DUAL WHERE 1!=1) | +-----------------------------------------+ | NULL | +-----------------------------------------+ 1 row in set (0.01 sec) On Wed, Mar 7, 2018 at 10:43 AM, Mike Bayer <[email protected]> wrote: > On Wed, Mar 7, 2018 at 8:20 AM, <[email protected]> wrote: >> For example "select 1 where false" returns and empty list and this works on >> at least postgres > > oh ok, yes there's this also. This returns different results on > MySQL vs. Postgresql: > > psql (9.6.7) > Type "help" for help. > > test=# SELECT 1 in (SELECT 1 WHERE 1!=1); > ?column? > ---------- > f > (1 row) > > MariaDB [(none)]> SELECT 1 FROM DUAL WHERE 1!=1; > Empty set (0.00 sec) > > MariaDB [(none)]> SELECT 1 IN (SELECT 1 FROM DUAL WHERE 1!=1); > +--------------------------------------+ > | 1 IN (SELECT 1 FROM DUAL WHERE 1!=1) | > +--------------------------------------+ > | 1 | > +--------------------------------------+ > 1 row in set (0.00 sec) > > you can see the mistake MySQL is making: > > MariaDB [(none)]> SELECT 5 IN (SELECT 1 FROM DUAL WHERE 1!=1); > +--------------------------------------+ > | 5 IN (SELECT 1 FROM DUAL WHERE 1!=1) | > +--------------------------------------+ > | 0 | > +--------------------------------------+ > 1 row in set (0.00 sec) > > > but sure, if you can get the "empty set" expression to exist (which I > don't know is possible in general) then sure. Feel free to provide > tests / PRs. > > > >> (https://www.postgresql.org/message-id/[email protected]) >> and mysql. >> >> TLDR; query producing empty lists such as "select 1 where false" may be used >> as tricks to resovle the non handling of empty IN expression >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >> description. >> --- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
