/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/parse.y.

1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034

1035
1036

1037
1038
1039
1040
1041
1042
1043
      /* Expressions of the form:
      **
      **      expr1 IN (?1)
      **      expr1 NOT IN (?2)
      **
      ** with exactly one value on the RHS can be simplified to:
      **
      **      expr1 == ?1
      **      expr1 <> ?2
      */
      Expr *pRHS = Y->a[0].pExpr;

      Y->a[0].pExpr = 0;
      sqlite3ExprListDelete(pParse->db, Y);

      A.pExpr = sqlite3PExpr(pParse, N ? TK_NE : TK_EQ, X.pExpr, pRHS, 0);
    }else{
      A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0);
      if( A.pExpr ){
        A.pExpr->x.pList = Y;
        sqlite3ExprSetHeight(pParse, A.pExpr);
      }else{







|
|

|
>


>







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

Changes to test/in4.test.

240
241
242
243
244
245
246
247


248






















249



































250
do_execsql_test in4-3.47 {
  SELECT * FROM t3 WHERE x NOT IN (10);
} {1 1 1}
do_execsql_test in4-3.48 {
  EXPLAIN
  SELECT * FROM t3 WHERE x NOT IN (10);
} {~/OpenEphemeral/}






























































finish_test








>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
do_execsql_test in4-3.47 {
  SELECT * FROM t3 WHERE x NOT IN (10);
} {1 1 1}
do_execsql_test in4-3.48 {
  EXPLAIN
  SELECT * FROM t3 WHERE x NOT IN (10);
} {~/OpenEphemeral/}

# Make sure that when "x IN (?)" is converted into "x==?" that collating
# sequence and affinity computations do not get messed up.
#
do_execsql_test in4-4.1 {
  CREATE TABLE t4a(a TEXT, b TEXT COLLATE nocase, c);
  INSERT INTO t4a VALUES('ABC','abc',1);
  INSERT INTO t4a VALUES('def','xyz',2);
  INSERT INTO t4a VALUES('ghi','ghi',3);
  SELECT c FROM t4a WHERE a=b ORDER BY c;
} {3}
do_execsql_test in4-4.2 {
  SELECT c FROM t4a WHERE b=a ORDER BY c;
} {1 3}
do_execsql_test in4-4.3 {
  SELECT c FROM t4a WHERE (a||'')=b ORDER BY c;
} {1 3}
do_execsql_test in4-4.4 {
  SELECT c FROM t4a WHERE (a||'')=(b||'') ORDER BY c;
} {3}
do_execsql_test in4-4.5 {
  SELECT c FROM t4a WHERE a IN (b) ORDER BY c;
} {3}
do_execsql_test in4-4.6 {
  SELECT c FROM t4a WHERE (a||'') IN (b) ORDER BY c;
} {3}


do_execsql_test in4-4.11 {
  CREATE TABLE t4b(a TEXT, b NUMERIC, c);
  INSERT INTO t4b VALUES('1.0',1,4);
  SELECT c FROM t4b WHERE a=b;
} {4}
do_execsql_test in4-4.12 {
  SELECT c FROM t4b WHERE b=a;
} {4}
do_execsql_test in4-4.13 {
  SELECT c FROM t4b WHERE +a=b;
} {4}
do_execsql_test in4-4.14 {
  SELECT c FROM t4b WHERE a=+b;
} {}
do_execsql_test in4-4.15 {
  SELECT c FROM t4b WHERE +b=a;
} {}
do_execsql_test in4-4.16 {
  SELECT c FROM t4b WHERE b=+a;
} {4}
do_execsql_test in4-4.17 {
  SELECT c FROM t4b WHERE a IN (b);
} {}
do_execsql_test in4-4.18 {
  SELECT c FROM t4b WHERE b IN (a);
} {4}
do_execsql_test in4-4.19 {
  SELECT c FROM t4b WHERE +b IN (a);
} {}





finish_test