Re: [Sqlalchemy-tickets] [sqlalchemy] #1628: warn when in_() used with no args

[sqlalchemy <mi...@zz...>]

#1628: warn when in_() used with no args
----------------------------+-----------------------------------------------
    Reporter:  zzzeek       |       Owner:  zzzeek               
        Type:  enhancement  |      Status:  new                  
    Priority:  medium       |   Milestone:  0.6.0                
   Component:  sql          |    Severity:  trivial - <10 minutes
  Resolution:               |    Keywords:                       
Status_field:  in queue     |  
----------------------------+-----------------------------------------------
Comment (by guest):

 Why is it rewritten to `col != col`? That's an expression that can never
 be true --- unless perhaps you use it on a weird type which overloads the
 `!=`-operator, and even in that case I would not expect `col.in_([])` to
 result in those. The comment in _in_impl indicates it really is expected
 that it won't ever return anything.

 Yet, it is *not* something a query planner will attempt to prove is a
 contradiction. However, if it had been rewritten to simply `False`, that's
 something a query planner can easily reason is a contradiction, and thus
 never execute the sub-plan.

 Example:

 {{{
 alex => explain analyze select * from (select generate_series(0,1000000))
 as i where i != i;
                                             QUERY PLAN
 ---------------------------------------------------------------------------------------------------
  Subquery Scan i  (cost=0.00..0.03 rows=1 width=4) (actual
 time=1493.582..1493.582 rows=0 loops=1)
    Filter: (i.* <> i.*)
    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual
 time=0.010..294.954 rows=1000001 loops=1)
  Total runtime: 1493.619 ms
 (4 rows)

 alex=> explain analyze select * from (select generate_series(0,1000000))
 as i where false;
                                      QUERY PLAN
 ------------------------------------------------------------------------------------
  Result  (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=0
 loops=1)
    One-Time Filter: false
    ->  Result  (cost=0.00..0.01 rows=1 width=0) (never executed)
  Total runtime: 0.022 ms
 (4 rows)

 }}}

 I propose to change the implementation to return a `False`-expression
 instead of `col != col` --- with or without warning.

 – AlexB

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1628#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python