gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/tkt-31338dca7e.test (about) 1 # 2009 December 16 2 # 3 # The author disclaims copyright to this source code. In place of 4 # a legal notice, here is a blessing: 5 # 6 # May you do good and not evil. 7 # May you find forgiveness for yourself and forgive others. 8 # May you share freely, never taking more than you give. 9 # 10 #*********************************************************************** 11 # This file implements regression tests for SQLite library. 12 # 13 # This file implements tests to verify that ticket [31338dca7e] has been 14 # fixed. Ticket [31338dca7e] demonstrates problems with the OR-clause 15 # optimization in joins where the WHERE clause is of the form 16 # 17 # (x AND y) OR z 18 # 19 # And the x and y subterms from from different tables of the join. 20 # 21 22 set testdir [file dirname $argv0] 23 source $testdir/tester.tcl 24 25 do_test tkt-31338-1.1 { 26 db eval { 27 CREATE TABLE t1(x); 28 CREATE TABLE t2(y); 29 INSERT INTO t1 VALUES(111); 30 INSERT INTO t1 VALUES(222); 31 INSERT INTO t2 VALUES(333); 32 INSERT INTO t2 VALUES(444); 33 SELECT * FROM t1, t2 34 WHERE (x=111 AND y!=444) OR x=222 35 ORDER BY x, y; 36 } 37 } {111 333 222 333 222 444} 38 39 do_test tkt-31338-1.2 { 40 db eval { 41 CREATE INDEX t1x ON t1(x); 42 SELECT * FROM t1, t2 43 WHERE (x=111 AND y!=444) OR x=222 44 ORDER BY x, y; 45 } 46 } {111 333 222 333 222 444} 47 48 do_test tkt-31338-2.1 { 49 db eval { 50 CREATE TABLE t3(v,w); 51 CREATE TABLE t4(x,y); 52 CREATE TABLE t5(z); 53 INSERT INTO t3 VALUES(111,222); 54 INSERT INTO t3 VALUES(333,444); 55 INSERT INTO t4 VALUES(222,333); 56 INSERT INTO t4 VALUES(444,555); 57 INSERT INTO t5 VALUES(888); 58 INSERT INTO t5 VALUES(999); 59 60 SELECT * FROM t3, t4, t5 61 WHERE (v=111 AND x=w AND z!=999) OR (v=333 AND x=444) 62 ORDER BY v, w, x, y, z; 63 } 64 } {111 222 222 333 888 333 444 444 555 888 333 444 444 555 999} 65 66 do_test tkt-31338-2.2 { 67 db eval { 68 CREATE INDEX t3v ON t3(v); 69 CREATE INDEX t4x ON t4(x); 70 SELECT * FROM t3, t4, t5 71 WHERE (v=111 AND x=w AND z!=999) OR (v=333 AND x=444) 72 ORDER BY v, w, x, y, z; 73 } 74 } {111 222 222 333 888 333 444 444 555 888 333 444 444 555 999} 75 76 77 # Ticket [2c2de252666662f5459904fc33a9f2956cbff23c] 78 # 79 do_test tkt-31338-3.1 { 80 foreach x [db eval {SELECT name FROM sqlite_master WHERE type='table'}] { 81 db eval "DROP TABLE $x" 82 } 83 db eval { 84 CREATE TABLE t1(a,b,c,d); 85 CREATE TABLE t2(e,f); 86 INSERT INTO t1 VALUES(1,2,3,4); 87 INSERT INTO t2 VALUES(10,-8); 88 CREATE INDEX t1a ON t1(a); 89 CREATE INDEX t1b ON t1(b); 90 CREATE TABLE t3(g); 91 INSERT INTO t3 VALUES(4); 92 CREATE TABLE t4(h); 93 INSERT INTO t4 VALUES(5); 94 95 SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h 96 WHERE (a=1 AND h=4) 97 OR (b IN ( 98 SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2) 99 GROUP BY e 100 )); 101 } 102 } {4 1 2 3 4 {}} 103 do_test tkt-31338-3.2 { 104 db eval { 105 SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h 106 WHERE (a=1 AND h=4) 107 OR (b=2 AND b NOT IN ( 108 SELECT x+1 FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2) 109 GROUP BY e 110 )); 111 } 112 } {4 1 2 3 4 {}} 113 do_test tkt-31338-3.3 { 114 db eval { 115 SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h 116 WHERE (+a=1 AND h=4) 117 OR (b IN ( 118 SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2) 119 GROUP BY e 120 )); 121 } 122 } {4 1 2 3 4 {}} 123 do_test tkt-31338-3.4 { 124 db eval { 125 SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h 126 WHERE (a=1 AND h=4) 127 OR (+b IN ( 128 SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2) 129 GROUP BY e 130 )); 131 } 132 } {4 1 2 3 4 {}} 133 134 do_test tkt-31338-3.5 { 135 db eval { 136 CREATE TABLE t5(a,b,c,d,e,f); 137 CREATE TABLE t6(g,h); 138 CREATE TRIGGER t6r AFTER INSERT ON t6 BEGIN 139 INSERT INTO t5 140 SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h 141 WHERE (a=1 AND h=4) 142 OR (b IN ( 143 SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2) 144 GROUP BY e 145 )); 146 END; 147 INSERT INTO t6 VALUES(88,99); 148 SELECT * FROM t5; 149 } 150 } {4 1 2 3 4 {}} 151 152 do_test tkt-31338-3.6 { 153 db eval { 154 INSERT INTO t1 VALUES(2,4,3,4); 155 INSERT INTO t1 VALUES(99,101,3,4); 156 INSERT INTO t1 VALUES(98,97,3,4); 157 SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h 158 WHERE (a=1 AND h=4) 159 OR (b IN ( 160 SELECT x+a FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2) 161 GROUP BY e 162 )); 163 } 164 } {4 2 4 3 4 {} 4 99 101 3 4 {}} 165 166 do_test tkt-31338-3.7 { 167 db eval { 168 SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h 169 WHERE (a=1 AND h=4) 170 OR (b IN ( 171 SELECT x FROM (SELECT e+f+a AS x, e FROM t2 ORDER BY 1 LIMIT 2) 172 GROUP BY e 173 )); 174 } 175 } {4 2 4 3 4 {} 4 99 101 3 4 {}} 176 177 178 finish_test