/ Check-in [e68b427a]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Convert expressions of the form "X IN (?)" with exactly one value on the RHS of the IN into equality tests: "X=?". Add test cases to verify that statements work correctly on this corner case. Fix for ticket [e39d032577df6942].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e68b427afbc82e201c64474117851aa4c9eb0c92
User & Date: drh 2014-03-20 13:26:47
References
2014-03-20
17:03
The "x IN (?)" optimization in check-ins [2ff3b25f40] and [e68b427afb] is incorrect, as demonstrated by the in4-5.1 test case in this check-in. The "COLLATE binary" that was being added to the RHS of IN was overriding the implicit collating sequence of the LHS. This change defines the EP_Generic expression node property that blocks all affinity or collating sequence information in the expression subtree and adds that property to the expression taken from RHS of the IN operator. check-in: 2ea4a9f7 user: drh tags: trunk
Context
2014-03-20
14:56
Previous check-in is not quite correct. "x IN (?)" is not exactly the same as "x==?" do to collation and affinity issues. The correct converstion should be to "x==(+? COLLATE binary)". The current check-in fixes this problem and provides test cases. Ticket [e39d032577df69] check-in: 2ff3b25f user: drh tags: trunk
13:26
Convert expressions of the form "X IN (?)" with exactly one value on the RHS of the IN into equality tests: "X=?". Add test cases to verify that statements work correctly on this corner case. Fix for ticket [e39d032577df6942]. check-in: e68b427a user: drh tags: trunk
12:17
Fix an unnecessarily obtuse use of a bitmask flag. check-in: ca314081 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

  1016   1016         **      expr1 NOT IN ()
  1017   1017         **
  1018   1018         ** simplify to constants 0 (false) and 1 (true), respectively,
  1019   1019         ** regardless of the value of expr1.
  1020   1020         */
  1021   1021         A.pExpr = sqlite3PExpr(pParse, TK_INTEGER, 0, 0, &sqlite3IntTokens[N]);
  1022   1022         sqlite3ExprDelete(pParse->db, X.pExpr);
         1023  +    }else if( Y->nExpr==1 ){
         1024  +      /* Expressions of the form:
         1025  +      **
         1026  +      **      expr1 IN (?1)
         1027  +      **      expr1 NOT IN (?2)
         1028  +      **
         1029  +      ** with exactly one value on the RHS can be simplified to:
         1030  +      **
         1031  +      **      expr1 == ?1
         1032  +      **      expr1 <> ?2
         1033  +      */
         1034  +      Expr *pRHS = Y->a[0].pExpr;
         1035  +      Y->a[0].pExpr = 0;
         1036  +      sqlite3ExprListDelete(pParse->db, Y);
         1037  +      A.pExpr = sqlite3PExpr(pParse, N ? TK_NE : TK_EQ, X.pExpr, pRHS, 0);
  1023   1038       }else{
  1024   1039         A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0);
  1025   1040         if( A.pExpr ){
  1026   1041           A.pExpr->x.pList = Y;
  1027   1042           sqlite3ExprSetHeight(pParse, A.pExpr);
  1028   1043         }else{
  1029   1044           sqlite3ExprListDelete(pParse->db, Y);

Changes to src/where.c.

  4005   4005         if( ExprHasProperty(pExpr, EP_xIsSelect) ){
  4006   4006           /* "x IN (SELECT ...)":  TUNING: the SELECT returns 25 rows */
  4007   4007           nIn = 46;  assert( 46==sqlite3LogEst(25) );
  4008   4008         }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
  4009   4009           /* "x IN (value, value, ...)" */
  4010   4010           nIn = sqlite3LogEst(pExpr->x.pList->nExpr);
  4011   4011         }
         4012  +      assert( nIn>0 );  /* RHS always has 2 or more terms...  The parser
         4013  +                        ** changes "x IN (?)" into "x=?". */
  4012   4014         pNew->rRun += nIn;
  4013   4015         pNew->u.btree.nEq++;
  4014   4016         pNew->nOut = nRowEst + nInMul + nIn;
  4015   4017       }else if( pTerm->eOperator & (WO_EQ) ){
  4016   4018         assert(
  4017   4019           (pNew->wsFlags & (WHERE_COLUMN_NULL|WHERE_COLUMN_IN|WHERE_SKIPSCAN))!=0
  4018   4020           || nInMul==0

Changes to test/in4.test.

   154    154   } {}
   155    155   do_test in4-3.11 {
   156    156     execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()}
   157    157   } {1 1 1}
   158    158   do_test in4-3.12 {
   159    159     execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()}
   160    160   } {}
          161  +
          162  +# Tests for "... IN (?)" and "... NOT IN (?)".  In other words, tests
          163  +# for when the RHS of IN is a single expression.  This should work the
          164  +# same as the == and <> operators.
          165  +#
          166  +do_execsql_test in4-3.21 {
          167  +  SELECT * FROM t3 WHERE x=10 AND y IN (10);
          168  +} {10 10 10}
          169  +do_execsql_test in4-3.22 {
          170  +  SELECT * FROM t3 WHERE x IN (10) AND y=10;
          171  +} {10 10 10}
          172  +do_execsql_test in4-3.23 {
          173  +  SELECT * FROM t3 WHERE x IN (10) AND y IN (10);
          174  +} {10 10 10}
          175  +do_execsql_test in4-3.24 {
          176  +  SELECT * FROM t3 WHERE x=1 AND y NOT IN (10);
          177  +} {1 1 1}
          178  +do_execsql_test in4-3.25 {
          179  +  SELECT * FROM t3 WHERE x  NOT IN (10) AND y=1;
          180  +} {1 1 1}
          181  +do_execsql_test in4-3.26 {
          182  +  SELECT * FROM t3 WHERE x NOT IN (10) AND y NOT IN (10);
          183  +} {1 1 1}
          184  +
          185  +# The query planner recognizes that "x IN (?)" only generates a
          186  +# single match and can use this information to optimize-out ORDER BY
          187  +# clauses.
          188  +#
          189  +do_execsql_test in4-3.31 {
          190  +  DROP INDEX t3i1;
          191  +  CREATE UNIQUE INDEX t3xy ON t3(x,y);
          192  +
          193  +  SELECT *, '|' FROM t3 A, t3 B
          194  +   WHERE A.x=10 AND A.y IN (10)
          195  +     AND B.x=1 AND B.y IN (1);
          196  +} {10 10 10 1 1 1 |}
          197  +do_execsql_test in4-3.32 {
          198  +  EXPLAIN QUERY PLAN
          199  +  SELECT *, '|' FROM t3 A, t3 B
          200  +   WHERE A.x=10 AND A.y IN (10)
          201  +     AND B.x=1 AND B.y IN (1);
          202  +} {~/B-TREE/}  ;# No separate sorting pass
          203  +do_execsql_test in4-3.33 {
          204  +  SELECT *, '|' FROM t3 A, t3 B
          205  +   WHERE A.x IN (10) AND A.y=10
          206  +     AND B.x IN (1) AND B.y=1;
          207  +} {10 10 10 1 1 1 |}
          208  +do_execsql_test in4-3.34 {
          209  +  EXPLAIN QUERY PLAN
          210  +  SELECT *, '|' FROM t3 A, t3 B
          211  +   WHERE A.x IN (10) AND A.y=10
          212  +     AND B.x IN (1) AND B.y=1;
          213  +} {~/B-TREE/}  ;# No separate sorting pass
          214  +
          215  +# An expression of the form "x IN (?,?)" creates an ephemeral table to
          216  +# hold the list of values on the RHS.  But "x IN (?)" does not create
          217  +# an ephemeral table.
          218  +#
          219  +do_execsql_test in4-3.41 {
          220  +  SELECT * FROM t3 WHERE x IN (10,11);
          221  +} {10 10 10}
          222  +do_execsql_test in4-3.42 {
          223  +  EXPLAIN
          224  +  SELECT * FROM t3 WHERE x IN (10,11);
          225  +} {/OpenEphemeral/}
          226  +do_execsql_test in4-3.43 {
          227  +  SELECT * FROM t3 WHERE x IN (10);
          228  +} {10 10 10}
          229  +do_execsql_test in4-3.44 {
          230  +  EXPLAIN
          231  +  SELECT * FROM t3 WHERE x IN (10);
          232  +} {~/OpenEphemeral/}
          233  +do_execsql_test in4-3.45 {
          234  +  SELECT * FROM t3 WHERE x NOT IN (10,11);
          235  +} {1 1 1}
          236  +do_execsql_test in4-3.46 {
          237  +  EXPLAIN
          238  +  SELECT * FROM t3 WHERE x NOT IN (10,11);
          239  +} {/OpenEphemeral/}
          240  +do_execsql_test in4-3.47 {
          241  +  SELECT * FROM t3 WHERE x NOT IN (10);
          242  +} {1 1 1}
          243  +do_execsql_test in4-3.48 {
          244  +  EXPLAIN
          245  +  SELECT * FROM t3 WHERE x NOT IN (10);
          246  +} {~/OpenEphemeral/}
          247  +
          248  +
   161    249   
   162    250   finish_test