Re: [sqlalchemy] handling of empty list of bindparam expanding

[Mike Bayer]

id like to move to github completely and I wish bitbucket had a way to
turn off pull requests (they don't).   So please use github pull
requests, thanks!

On Thu, Mar 8, 2018 at 9:45 AM,   wrote:
>
>> 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.
>
>
> I naively thought that the statement 'SELECT 1 WHERE 1 != 1" would work on
> any DB, but it seems it won't be the case as it is a syntax error on mariaDB
> (and I guess on some other DB then).
>
> However any BD implementing IN predicate accepting a subquery as the list
> must implement a coherent way to deal with empty list on IN (or at least
> send a proper error message), as a subquery producting an empty result is a
> perfectly valid query.
>
> So this "trick" might be adaptable to all DB (I didn't managed to find the
> doc of IN for synbase and oracle so I can't tell for them).
> Moreover sqlite, postgres, myqsl and mariaDB agree on the (NULL IN () ==
> False) question, so letting the DB create the empty list shouldn't lead too
> much to a "SQLAlchemy is not abstracting differences between the
> databases!!".
>
> The statement "SELECT 1 FROM ((SELECT 1) as placeholder_table) WHERE 1!=1;"
> is more promising, as (sqlite, postgres, myqsl and mariaDB) all accept it,
> and any DB that accept a select query as a valid table too (it looks like
> Microsoft SQL server will accept it too, can't say for firebird, oracle or
> sybase as I found niether a doc or a way to make it run).
>
>
> What is the procedure to propose a PR (with tests obviously) ? Issues are
> supposed to be in bitbucket, but most PR are on the github
>
> --
> Nicolas Rolin
>
>
> --
> 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.

Re: [sqlalchemy] handling of empty list of bindparam expanding

[nicolas . rolin]

> 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. 
>

I naively thought that the statement 'SELECT 1 WHERE 1 != 1" would work on 
any DB, but it seems it won't be the case as it is a syntax error on 
mariaDB (and I guess on some other DB then).

However any BD implementing IN predicate accepting a subquery as the list 
must implement a coherent way to deal with empty list on IN (or at least 
send a proper error message), as a subquery producting an empty result is a 
perfectly valid query.

So this "trick" might be adaptable to all DB (I didn't managed to find the 
doc of IN for synbase and oracle so I can't tell for them).
Moreover sqlite, postgres, myqsl and mariaDB agree on the (NULL IN () == 
False) question, so letting the DB create the empty list shouldn't lead too 
much to a "SQLAlchemy is not abstracting differences between the 
databases!!".

The statement "SELECT 1 FROM ((SELECT 1) as placeholder_table) WHERE 1!=1;" 
is more promising, as (sqlite, postgres, myqsl and mariaDB) all accept it, 
and any DB that accept a select query as a valid table too (it looks like 
Microsoft SQL server will accept it too, can't say for firebird, oracle or 
sybase as I found niether a doc or a way to make it run).


What is the procedure to propose a PR (with tests obviously) ? Issues are 
supposed to be in bitbucket, but most PR are on the github

-- 
Nicolas Rolin


-- 
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.

Re: [sqlalchemy] handling of empty list of bindparam expanding

[Mike Bayer]

On Wed, Mar 7, 2018 at 11:09 AM,   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.

Re: [sqlalchemy] handling of empty list of bindparam expanding

[nicolas . rolin]

More crazy DUAL : 

MariaDB [(none)]> SELECT 2 IN (SELECT 1 from DUAL WHERE 1 != 1);
++
| 2 IN (SELECT 1 from DUAL WHERE 1 != 1) |
++
|  0 |
++
1 row in set (0.00 sec)


MariaDB [(none)]> SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1);
++
| 2 IN (SELECT 2 from DUAL WHERE 1 != 1) |
++
|  1 |
++
1 row in set (0.00 sec)

Looks like the where is ignored

I would strongly discourage using "select x from DUAL where condition" in 
any tests

-- 
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.

Re: [sqlalchemy] handling of empty list of bindparam expanding

[nicolas . rolin]

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
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.

Re: [sqlalchemy] handling of empty list of bindparam expanding

[Mike Bayer]

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  wrote:
> On Wed, Mar 7, 2018 at 8:20 AM,   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.

Re: [sqlalchemy] handling of empty list of bindparam expanding

[Mike Bayer]

On Wed, Mar 7, 2018 at 8:20 AM,   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.

Re: [sqlalchemy] handling of empty list of bindparam expanding

[Mike Bayer]

On Wed, Mar 7, 2018 at 8:20 AM,   wrote:
> Hello everyone,
>
> While toying around with the cool new features of 1.2, I noticed that the
> expanding of a bindparam in a "in" doesn't accept empty list.
>
> This is documented, and the error message is pretty clear, but the behavior
> itself is pretty inconsistent with the other "in" syntax :

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)


>
> from sqlalchemy import *
> metadata = MetaData()
> engine = create_engine('sqlite://')
> user = Table('user', metadata,
> Column('user_id', Integer, primary_key=True),
> )
>
> id_list = []
> metadata.create_all(engine)
> stmt_1 = select([user]).where(
> user.c.user_id.in_(id_list)
> )
>
> stmt_2 = select([user]).where(
> user.c.user_id.in_(bindparam('foo', expanding=True))
> )
>
> engine.execute(stmt_2, {"foo": id_list})
>
> Here
> engine.execute(stmt_1)
> returns the expected result (where the "in empty list" is replaced by false)
>
> And
> engine.execute(stmt_2, {"foo": id_list})
> returns an error
>
> If I'm not mistaken the first statement already use a tricks to simulate an
> empty list as print(stmt_1) returns
> SELECT "user".user_id FROM "user" WHERE 1 != 1
>
> If my understantding of sqlalchemy is correct in the second statement the
> query
>
> SELECT user.user_id \nFROM user \nWHERE user.user_id IN ([EXPANDING_foo])'
>
> is aready "compiled" and there is a replacement of the string
> "[EXPANDING_foo]" by the actual list put in the bindparam.

that is correct.


>
> Would it be possible in the case where the list is empty replace it by a
> query returning an empty list instead of throwing an error ?
> (https://bitbucket.org/zzzeek/sqlalchemy/src/beab362b2e892fe11d0526064eabd136d3b1f8ee/lib/sqlalchemy/engine/default.py?at=master=file-view-default#default.py-735)

it means the statement would have to be like:


SELECT user.user_id FROM user WHERE [EXPANDING_EXPR]user.user_id IN
([EXPANDING_foo])[/EXPANDING_EXPR]'

so that the whole thing can be replaced.Note EXPANDING_EXPR can be
nested too. We'd be building a whole recursive descent parser into
the most performance critical part of the statement invocation.
I'm not in a hurry to do this but feel free to work on this or
alternate ideas.   The ratio of complexity to benefit seems quite
poor.

> For example "select 1 where false" returns and empty list and this works on
> at least postgres
> (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.