/ Check-in [2ff3b25f]
Login

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

Overview
Comment: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]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:2ff3b25f40fd117c8a2da1d1a3625f6b167b7b16
User & Date: drh 2014-03-20 14:56: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
15:14
Fix harmless compiler warnings. check-in: b1435f26 user: drh tags: trunk
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

  1024   1024         /* Expressions of the form:
  1025   1025         **
  1026   1026         **      expr1 IN (?1)
  1027   1027         **      expr1 NOT IN (?2)
  1028   1028         **
  1029   1029         ** with exactly one value on the RHS can be simplified to:
  1030   1030         **
  1031         -      **      expr1 == ?1
  1032         -      **      expr1 <> ?2
         1031  +      **      expr1 == (+?1 COLLATE binary)
         1032  +      **      expr1 <> (+?2 COLLATE binary)
  1033   1033         */
  1034         -      Expr *pRHS = Y->a[0].pExpr;
         1034  +      static const Token collBin = { "binary", 6 };
         1035  +      Expr *pRHS = sqlite3ExprAddCollateToken(pParse, Y->a[0].pExpr, &collBin);
  1035   1036         Y->a[0].pExpr = 0;
  1036   1037         sqlite3ExprListDelete(pParse->db, Y);
         1038  +      pRHS = sqlite3PExpr(pParse, TK_UPLUS, pRHS, 0, 0);
  1037   1039         A.pExpr = sqlite3PExpr(pParse, N ? TK_NE : TK_EQ, X.pExpr, pRHS, 0);
  1038   1040       }else{
  1039   1041         A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0);
  1040   1042         if( A.pExpr ){
  1041   1043           A.pExpr->x.pList = Y;
  1042   1044           sqlite3ExprSetHeight(pParse, A.pExpr);
  1043   1045         }else{

Changes to test/in4.test.

   240    240   do_execsql_test in4-3.47 {
   241    241     SELECT * FROM t3 WHERE x NOT IN (10);
   242    242   } {1 1 1}
   243    243   do_execsql_test in4-3.48 {
   244    244     EXPLAIN
   245    245     SELECT * FROM t3 WHERE x NOT IN (10);
   246    246   } {~/OpenEphemeral/}
          247  +
          248  +# Make sure that when "x IN (?)" is converted into "x==?" that collating
          249  +# sequence and affinity computations do not get messed up.
          250  +#
          251  +do_execsql_test in4-4.1 {
          252  +  CREATE TABLE t4a(a TEXT, b TEXT COLLATE nocase, c);
          253  +  INSERT INTO t4a VALUES('ABC','abc',1);
          254  +  INSERT INTO t4a VALUES('def','xyz',2);
          255  +  INSERT INTO t4a VALUES('ghi','ghi',3);
          256  +  SELECT c FROM t4a WHERE a=b ORDER BY c;
          257  +} {3}
          258  +do_execsql_test in4-4.2 {
          259  +  SELECT c FROM t4a WHERE b=a ORDER BY c;
          260  +} {1 3}
          261  +do_execsql_test in4-4.3 {
          262  +  SELECT c FROM t4a WHERE (a||'')=b ORDER BY c;
          263  +} {1 3}
          264  +do_execsql_test in4-4.4 {
          265  +  SELECT c FROM t4a WHERE (a||'')=(b||'') ORDER BY c;
          266  +} {3}
          267  +do_execsql_test in4-4.5 {
          268  +  SELECT c FROM t4a WHERE a IN (b) ORDER BY c;
          269  +} {3}
          270  +do_execsql_test in4-4.6 {
          271  +  SELECT c FROM t4a WHERE (a||'') IN (b) ORDER BY c;
          272  +} {3}
          273  +
          274  +
          275  +do_execsql_test in4-4.11 {
          276  +  CREATE TABLE t4b(a TEXT, b NUMERIC, c);
          277  +  INSERT INTO t4b VALUES('1.0',1,4);
          278  +  SELECT c FROM t4b WHERE a=b;
          279  +} {4}
          280  +do_execsql_test in4-4.12 {
          281  +  SELECT c FROM t4b WHERE b=a;
          282  +} {4}
          283  +do_execsql_test in4-4.13 {
          284  +  SELECT c FROM t4b WHERE +a=b;
          285  +} {4}
          286  +do_execsql_test in4-4.14 {
          287  +  SELECT c FROM t4b WHERE a=+b;
          288  +} {}
          289  +do_execsql_test in4-4.15 {
          290  +  SELECT c FROM t4b WHERE +b=a;
          291  +} {}
          292  +do_execsql_test in4-4.16 {
          293  +  SELECT c FROM t4b WHERE b=+a;
          294  +} {4}
          295  +do_execsql_test in4-4.17 {
          296  +  SELECT c FROM t4b WHERE a IN (b);
          297  +} {}
          298  +do_execsql_test in4-4.18 {
          299  +  SELECT c FROM t4b WHERE b IN (a);
          300  +} {4}
          301  +do_execsql_test in4-4.19 {
          302  +  SELECT c FROM t4b WHERE +b IN (a);
          303  +} {}
          304  +
          305  +
   247    306   
   248    307   
   249    308   
   250    309   finish_test