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: |
2ff3b25f40fd117c8a2da1d1a3625f6b |
| 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: 2ea4a9f75f user: drh tags: trunk) | |
Context
|
2014-03-20
| ||
| 15:14 | Fix harmless compiler warnings. (check-in: b1435f26b0 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: 2ff3b25f40 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: e68b427afb user: drh tags: trunk) | |
Changes
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
|