Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem causing SQLite not to use stat4 or stat3 data to analyze constraints of the form "column = expr COLLATE collation" (those with an explicit COLLATE on the non-column side of the comparison operator). |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
1e86d81d46c9da6aaee0c6938ee40933 |
| User & Date: | dan 2013-08-31 17:21:26.324 |
Context
|
2013-08-31
| ||
| 18:36 | Cleanup of the windows VFS, including added support for Cygwin, fixes for compiler warnings under unusual configurations, and improved diagnostic error messages. (check-in: c94933f132 user: drh tags: trunk) | |
| 17:21 | Fix a problem causing SQLite not to use stat4 or stat3 data to analyze constraints of the form "column = expr COLLATE collation" (those with an explicit COLLATE on the non-column side of the comparison operator). (check-in: 1e86d81d46 user: dan tags: trunk) | |
| 14:56 | Reenable a test accidentally disabled when the stat4 branch was merged. (check-in: d9fadc8fa6 user: dan tags: trunk) | |
Changes
Changes to src/vdbemem.c.
| ︙ | ︙ | |||
1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 |
struct ValueNewStat4Ctx alloc;
alloc.pParse = pParse;
alloc.pIdx = pIdx;
alloc.ppRec = ppRec;
alloc.iVal = iVal;
if( !pExpr ){
pVal = valueNew(pParse->db, &alloc);
if( pVal ){
sqlite3VdbeMemSetNull((Mem*)pVal);
*pbOk = 1;
}
}else if( pExpr->op==TK_VARIABLE
| > > > | 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 |
struct ValueNewStat4Ctx alloc;
alloc.pParse = pParse;
alloc.pIdx = pIdx;
alloc.ppRec = ppRec;
alloc.iVal = iVal;
/* Skip over any TK_COLLATE nodes */
pExpr = sqlite3ExprSkipCollate(pExpr);
if( !pExpr ){
pVal = valueNew(pParse->db, &alloc);
if( pVal ){
sqlite3VdbeMemSetNull((Mem*)pVal);
*pbOk = 1;
}
}else if( pExpr->op==TK_VARIABLE
|
| ︙ | ︙ |
Changes to test/analyze9.test.
| ︙ | ︙ | |||
417 418 419 420 421 422 423 424 425 426 |
do_eqp_test 9.4.1 {
SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
} {/t1 USING INDEX i2/}
set value_d [expr 99]
do_eqp_test 9.4.2 {
SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
} {/t1 USING INDEX i1/}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 |
do_eqp_test 9.4.1 {
SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
} {/t1 USING INDEX i2/}
set value_d [expr 99]
do_eqp_test 9.4.2 {
SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
} {/t1 USING INDEX i1/}
#-------------------------------------------------------------------------
# Check that the planner takes stat4 data into account when considering
# "IS NULL" and "IS NOT NULL" constraints.
#
do_execsql_test 10.1.1 {
DROP TABLE IF EXISTS t3;
CREATE TABLE t3(a, b);
CREATE INDEX t3a ON t3(a);
CREATE INDEX t3b ON t3(b);
}
do_test 10.1.2 {
for {set i 1} {$i < 100} {incr i} {
if {$i>90} { set a $i } else { set a NULL }
set b [expr $i % 5]
execsql "INSERT INTO t3 VALUES($a, $b)"
}
execsql ANALYZE
} {}
do_eqp_test 10.1.3 {
SELECT * FROM t3 WHERE a IS NULL AND b = 2
} {/t3 USING INDEX t3b/}
do_eqp_test 10.1.4 {
SELECT * FROM t3 WHERE a IS NOT NULL AND b = 2
} {/t3 USING INDEX t3a/}
do_execsql_test 10.2.1 {
DROP TABLE IF EXISTS t3;
CREATE TABLE t3(x, a, b);
CREATE INDEX t3a ON t3(x, a);
CREATE INDEX t3b ON t3(x, b);
}
do_test 10.2.2 {
for {set i 1} {$i < 100} {incr i} {
if {$i>90} { set a $i } else { set a NULL }
set b [expr $i % 5]
execsql "INSERT INTO t3 VALUES('xyz', $a, $b)"
}
execsql ANALYZE
} {}
do_eqp_test 10.2.3 {
SELECT * FROM t3 WHERE x = 'xyz' AND a IS NULL AND b = 2
} {/t3 USING INDEX t3b/}
do_eqp_test 10.2.4 {
SELECT * FROM t3 WHERE x = 'xyz' AND a IS NOT NULL AND b = 2
} {/t3 USING INDEX t3a/}
#-------------------------------------------------------------------------
# Check that stat4 data is used correctly with non-default collation
# sequences.
#
foreach {tn schema} {
1 {
CREATE TABLE t4(a COLLATE nocase, b);
CREATE INDEX t4a ON t4(a);
CREATE INDEX t4b ON t4(b);
}
2 {
CREATE TABLE t4(a, b);
CREATE INDEX t4a ON t4(a COLLATE nocase);
CREATE INDEX t4b ON t4(b);
}
} {
drop_all_tables
do_test 11.$tn.1 { execsql $schema } {}
do_test 11.$tn.2 {
for {set i 0} {$i < 100} {incr i} {
if { ($i % 10)==0 } { set a ABC } else { set a DEF }
set b [expr $i % 5]
execsql { INSERT INTO t4 VALUES($a, $b) }
}
execsql ANALYZE
} {}
do_eqp_test 11.$tn.3 {
SELECT * FROM t4 WHERE a = 'def' AND b = 3;
} {/t4 USING INDEX t4b/}
if {$tn==1} {
set sql "SELECT * FROM t4 WHERE a = 'abc' AND b = 3;"
do_eqp_test 11.$tn.4 $sql {/t4 USING INDEX t4a/}
} else {
set sql "SELECT * FROM t4 WHERE a = 'abc' COLLATE nocase AND b = 3;"
do_eqp_test 11.$tn.5 $sql {/t4 USING INDEX t4a/}
set sql "SELECT * FROM t4 WHERE a COLLATE nocase = 'abc' AND b = 3;"
do_eqp_test 11.$tn.6 $sql {/t4 USING INDEX t4a/}
}
}
foreach {tn schema} {
1 {
CREATE TABLE t4(x, a COLLATE nocase, b);
CREATE INDEX t4a ON t4(x, a);
CREATE INDEX t4b ON t4(x, b);
}
2 {
CREATE TABLE t4(x, a, b);
CREATE INDEX t4a ON t4(x, a COLLATE nocase);
CREATE INDEX t4b ON t4(x, b);
}
} {
drop_all_tables
do_test 12.$tn.1 { execsql $schema } {}
do_test 12.$tn.2 {
for {set i 0} {$i < 100} {incr i} {
if { ($i % 10)==0 } { set a ABC } else { set a DEF }
set b [expr $i % 5]
execsql { INSERT INTO t4 VALUES(X'abcdef', $a, $b) }
}
execsql ANALYZE
} {}
do_eqp_test 12.$tn.3 {
SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'def' AND b = 3;
} {/t4 USING INDEX t4b/}
if {$tn==1} {
set sql "SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'abc' AND b = 3;"
do_eqp_test 12.$tn.4 $sql {/t4 USING INDEX t4a/}
} else {
set sql {
SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'abc' COLLATE nocase AND b = 3
}
do_eqp_test 12.$tn.5 $sql {/t4 USING INDEX t4a/}
set sql {
SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3
}
do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/}
}
}
finish_test
|