| # 2018-07-26 |
| # |
| # The author disclaims copyright to this source code. In place of |
| # a legal notice, here is a blessing: |
| # |
| # May you do good and not evil. |
| # May you find forgiveness for yourself and forgive others. |
| # May you share freely, never taking more than you give. |
| # |
| #*********************************************************************** |
| # This file implements regression tests for SQLite library. The |
| # focus of this file is testing the WHERE-clause constant propagation |
| # optimization. |
| # |
| set testdir [file dirname $argv0] |
| source $testdir/tester.tcl |
| set ::testprefix whereL |
| |
| do_execsql_test 100 { |
| CREATE TABLE t1(a INT PRIMARY KEY, b, c, d, e); |
| CREATE TABLE t2(a INT PRIMARY KEY, f, g, h, i); |
| CREATE TABLE t3(a INT PRIMARY KEY, j, k, l, m); |
| CREATE VIEW v4 AS SELECT * FROM t2 UNION ALL SELECT * FROM t3; |
| } |
| do_eqp_test 110 { |
| SELECT * FROM t1, v4 WHERE t1.a=?1 AND v4.a=t1.a; |
| } { |
| QUERY PLAN |
| |--MATERIALIZE xxxxxx |
| | `--COMPOUND QUERY |
| | |--LEFT-MOST SUBQUERY |
| | | `--SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (a=?) |
| | `--UNION ALL |
| | `--SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (a=?) |
| |--SCAN SUBQUERY xxxxxx |
| `--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (a=?) |
| } |
| |
| # The scan of the t1 table goes first since that enables the ORDER BY |
| # sort to be omitted. This would not be possible without constant |
| # propagation because without it the t1 table would depend on t3. |
| # |
| do_eqp_test 120 { |
| SELECT * FROM t1, t2, t3 |
| WHERE t1.a=t2.a AND t2.a=t3.j AND t3.j=5 |
| ORDER BY t1.a; |
| } { |
| QUERY PLAN |
| |--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (a=?) |
| |--SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (a=?) |
| `--SCAN TABLE t3 |
| } |
| |
| # Constant propagation in the face of collating sequences: |
| # |
| do_execsql_test 200 { |
| CREATE TABLE c3(x COLLATE binary, y COLLATE nocase, z COLLATE binary); |
| CREATE INDEX c3x ON c3(x); |
| INSERT INTO c3 VALUES('ABC', 'ABC', 'abc'); |
| SELECT * FROM c3 WHERE x=y AND y=z AND z='abc'; |
| } {ABC ABC abc} |
| |
| # If the constants are blindly propagated, as shown in the following |
| # query, the wrong answer results: |
| # |
| do_execsql_test 201 { |
| SELECT * FROM c3 WHERE x='abc' AND y='abc' AND z='abc'; |
| } {} |
| |
| # Constant propagation caused an incorrect answer in the following |
| # query. (Reported by Bentley system on 2018-08-09.) |
| # |
| do_execsql_test 300 { |
| CREATE TABLE A(id INTEGER PRIMARY KEY, label TEXT); |
| CREATE TABLE B(id INTEGER PRIMARY KEY, label TEXT, Aid INTEGER); |
| CREATE TABLE C( |
| id INTEGER PRIMARY KEY, |
| xx INTEGER NOT NULL, |
| yy INTEGER, |
| zz INTEGER |
| ); |
| CREATE UNIQUE INDEX x2 ON C(yy); |
| CREATE UNIQUE INDEX x4 ON C(yy, zz); |
| INSERT INTO A(id) VALUES(1); |
| INSERT INTO B(id) VALUES(2); |
| INSERT INTO C(id,xx,yy,zz) VALUES(99,50,1,2); |
| SELECT 1 |
| FROM A, |
| (SELECT id,xx,yy,zz FROM C) subq, |
| B |
| WHERE A.id='1' |
| AND A.id=subq.yy |
| AND B.id=subq.zz; |
| } {1} |
| do_execsql_test 301 { |
| SELECT 1 |
| FROM A, |
| (SELECT id,xx,yy,zz FROM C) subq, |
| B |
| WHERE A.id=1 |
| AND A.id=subq.yy |
| AND B.id=subq.zz; |
| } {1} |
| do_execsql_test 302 { |
| SELECT 1 |
| FROM A, |
| (SELECT id,yy,zz FROM C) subq, |
| B |
| WHERE A.id='1' |
| AND A.id=subq.yy |
| AND B.id=subq.zz; |
| } {1} |
| |
| # 2018-10-25: Ticket [cf5ed20f] |
| # Incorrect join result with duplicate WHERE clause constraint. |
| # |
| do_execsql_test 400 { |
| CREATE TABLE x(a, b, c); |
| CREATE TABLE y(a, b); |
| INSERT INTO x VALUES (1, 0, 1); |
| INSERT INTO y VALUES (1, 2); |
| SELECT x.a FROM x JOIN y ON x.c = y.a WHERE x.b = 1 AND x.b = 1; |
| } {} |
| |
| finish_test |