On Wed, Mar 7, 2018 at 11:09 AM,  <nicolas.ro...@tiime.fr> wrote:
> Thanks for taking the time to answer my ignorant remark, I hadn't the full
> history on this question.
>
>
> Le mercredi 7 mars 2018 15:12:34 UTC+1, Mike Bayer a écrit :
>>
>> it is quite inconsistent.  But that's where we are - SQL doesn't
>> support empty "IN" and "expanding IN" only does a search and replace
>> of the "()" part of the IN expression, which is what many DBAPI
>> drivers do as well.    the "empty IN" that you can do normally is
>> itself something that only works as of 1.2 if you view the history of
>> that change
>> (http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#the-in-not-in-operator-s-empty-collection-behavior-is-now-configurable-default-expression-simplified)
>
>
> Reading the link I found something that might interest you, as I think
> postgres, mariaDB and mysql do agree on the NULL in () question.
> I pulled the lastest docker images for mysql and mariadb, and got
> respectively :
>
>
>
> mysql> SELECT NULL IN (SELECT 1 WHERE 1 != 1);
> +---------------------------------+
> | NULL IN (SELECT 1 WHERE 1 != 1) |
> +---------------------------------+
> |                               0 |
> +---------------------------------+
> 1 row in set (0.00 sec)
>
> it looks like mariaDB no longer accept a where clause without a from clause,
> so I had to add a random table

I'm not going to choose a random table like that.   the DB needs to
support running this kind of query correctly.  This is just PG and
MySQL.   we have all the others to see how badly they screw this up
also.



> MariaDB [information_schema]> SELECT NULL IN (SELECT 1 from
> information_schema.user_variables WHERE 1 != 1);
> +------------------------------------------------------------------------+
> | NULL IN (SELECT 1 from information_schema.user_variables WHERE 1 != 1) |
> +------------------------------------------------------------------------+
> |                                                                      0 |
> +------------------------------------------------------------------------+
> 1 row in set (0.00 sec)
>
> by using DUAL (i didn't knew it before
> MariaDB [information_schema]> 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.00 sec)
>
> Conclusion :
> - all 3 db agree on the fact that NULL in () is False
> - DUAL in mariaDB does some wierd s**t on tricky queries
>
>
>
> --
> 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