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 <mike...@zzzcomputing.com> wrote:
> On Wed, Mar 7, 2018 at 8:20 AM,  <nicolas.ro...@tiime.fr> 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/20060609130037.32155.qm...@web37915.mail.mud.yahoo.com)
>> 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 sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Reply via email to