| 1 |
# 2005 September 19 |
| 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 for left outer joins containing ON |
| 14 |
# clauses that restrict the scope of the left term of the join. |
| 15 |
# |
| 16 |
|
| 17 |
set testdir [file dirname $argv0] |
| 18 |
source $testdir/tester.tcl |
| 19 |
set testprefix join5 |
| 20 |
|
| 21 |
|
| 22 |
do_test join5-1.1 { |
| 23 |
execsql { |
| 24 |
BEGIN; |
| 25 |
CREATE TABLE t1(a integer primary key, b integer, c integer); |
| 26 |
CREATE TABLE t2(x integer primary key, y); |
| 27 |
CREATE TABLE t3(p integer primary key, q); |
| 28 |
INSERT INTO t3 VALUES(11,'t3-11'); |
| 29 |
INSERT INTO t3 VALUES(12,'t3-12'); |
| 30 |
INSERT INTO t2 VALUES(11,'t2-11'); |
| 31 |
INSERT INTO t2 VALUES(12,'t2-12'); |
| 32 |
INSERT INTO t1 VALUES(1, 5, 0); |
| 33 |
INSERT INTO t1 VALUES(2, 11, 2); |
| 34 |
INSERT INTO t1 VALUES(3, 12, 1); |
| 35 |
COMMIT; |
| 36 |
} |
| 37 |
} {} |
| 38 |
do_test join5-1.2 { |
| 39 |
execsql { |
| 40 |
select * from t1 left join t2 on t1.b=t2.x and t1.c=1 |
| 41 |
} |
| 42 |
} {1 5 0 {} {} 2 11 2 {} {} 3 12 1 12 t2-12} |
| 43 |
do_test join5-1.3 { |
| 44 |
execsql { |
| 45 |
select * from t1 left join t2 on t1.b=t2.x where t1.c=1 |
| 46 |
} |
| 47 |
} {3 12 1 12 t2-12} |
| 48 |
do_test join5-1.4 { |
| 49 |
execsql { |
| 50 |
select * from t1 left join t2 on t1.b=t2.x and t1.c=1 |
| 51 |
left join t3 on t1.b=t3.p and t1.c=2 |
| 52 |
} |
| 53 |
} {1 5 0 {} {} {} {} 2 11 2 {} {} 11 t3-11 3 12 1 12 t2-12 {} {}} |
| 54 |
do_test join5-1.5 { |
| 55 |
execsql { |
| 56 |
select * from t1 left join t2 on t1.b=t2.x and t1.c=1 |
| 57 |
left join t3 on t1.b=t3.p where t1.c=2 |
| 58 |
} |
| 59 |
} {2 11 2 {} {} 11 t3-11} |
| 60 |
|
| 61 |
# Ticket #2403 |
| 62 |
# |
| 63 |
do_test join5-2.1 { |
| 64 |
execsql { |
| 65 |
CREATE TABLE ab(a,b); |
| 66 |
INSERT INTO "ab" VALUES(1,2); |
| 67 |
INSERT INTO "ab" VALUES(3,NULL); |
| 68 |
|
| 69 |
CREATE TABLE xy(x,y); |
| 70 |
INSERT INTO "xy" VALUES(2,3); |
| 71 |
INSERT INTO "xy" VALUES(NULL,1); |
| 72 |
} |
| 73 |
execsql {SELECT * FROM xy LEFT JOIN ab ON 0} |
| 74 |
} {2 3 {} {} {} 1 {} {}} |
| 75 |
do_test join5-2.2 { |
| 76 |
execsql {SELECT * FROM xy LEFT JOIN ab ON 1} |
| 77 |
} {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}} |
| 78 |
do_test join5-2.3 { |
| 79 |
execsql {SELECT * FROM xy LEFT JOIN ab ON NULL} |
| 80 |
} {2 3 {} {} {} 1 {} {}} |
| 81 |
do_test join5-2.4 { |
| 82 |
execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 0} |
| 83 |
} {} |
| 84 |
do_test join5-2.5 { |
| 85 |
execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 0} |
| 86 |
} {} |
| 87 |
do_test join5-2.6 { |
| 88 |
execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 0} |
| 89 |
} {} |
| 90 |
do_test join5-2.7 { |
| 91 |
execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 1} |
| 92 |
} {2 3 {} {} {} 1 {} {}} |
| 93 |
do_test join5-2.8 { |
| 94 |
execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 1} |
| 95 |
} {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}} |
| 96 |
do_test join5-2.9 { |
| 97 |
execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 1} |
| 98 |
} {2 3 {} {} {} 1 {} {}} |
| 99 |
do_test join5-2.10 { |
| 100 |
execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE NULL} |
| 101 |
} {} |
| 102 |
do_test join5-2.11 { |
| 103 |
execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE NULL} |
| 104 |
} {} |
| 105 |
do_test join5-2.12 { |
| 106 |
execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE NULL} |
| 107 |
} {} |
| 108 |
|
| 109 |
# Ticket https://sqlite.org/src/tktview/6f2222d550f5b0ee7ed37601 |
| 110 |
# Incorrect output on a LEFT JOIN. |
| 111 |
# |
| 112 |
do_execsql_test join5-3.1 { |
| 113 |
DROP TABLE IF EXISTS t1; |
| 114 |
DROP TABLE IF EXISTS t2; |
| 115 |
DROP TABLE IF EXISTS t3; |
| 116 |
CREATE TABLE x1(a); |
| 117 |
INSERT INTO x1 VALUES(1); |
| 118 |
CREATE TABLE x2(b NOT NULL); |
| 119 |
CREATE TABLE x3(c, d); |
| 120 |
INSERT INTO x3 VALUES('a', NULL); |
| 121 |
INSERT INTO x3 VALUES('b', NULL); |
| 122 |
INSERT INTO x3 VALUES('c', NULL); |
| 123 |
SELECT * FROM x1 LEFT JOIN x2 LEFT JOIN x3 ON x3.d = x2.b; |
| 124 |
} {1 {} {} {}} |
| 125 |
do_execsql_test join5-3.2 { |
| 126 |
DROP TABLE IF EXISTS t1; |
| 127 |
DROP TABLE IF EXISTS t2; |
| 128 |
DROP TABLE IF EXISTS t3; |
| 129 |
DROP TABLE IF EXISTS t4; |
| 130 |
DROP TABLE IF EXISTS t5; |
| 131 |
CREATE TABLE t1(x text NOT NULL, y text); |
| 132 |
CREATE TABLE t2(u text NOT NULL, x text NOT NULL); |
| 133 |
CREATE TABLE t3(w text NOT NULL, v text); |
| 134 |
CREATE TABLE t4(w text NOT NULL, z text NOT NULL); |
| 135 |
CREATE TABLE t5(z text NOT NULL, m text); |
| 136 |
INSERT INTO t1 VALUES('f6d7661f-4efe-4c90-87b5-858e61cd178b',NULL); |
| 137 |
INSERT INTO t1 VALUES('f6ea82c3-2cad-45ce-ae8f-3ddca4fb2f48',NULL); |
| 138 |
INSERT INTO t1 VALUES('f6f47499-ecb4-474b-9a02-35be73c235e5',NULL); |
| 139 |
INSERT INTO t1 VALUES('56f47499-ecb4-474b-9a02-35be73c235e5',NULL); |
| 140 |
INSERT INTO t3 VALUES('007f2033-cb20-494c-b135-a1e4eb66130c', |
| 141 |
'f6d7661f-4efe-4c90-87b5-858e61cd178b'); |
| 142 |
SELECT * |
| 143 |
FROM t3 |
| 144 |
INNER JOIN t1 ON t1.x= t3.v AND t1.y IS NULL |
| 145 |
LEFT JOIN t4 ON t4.w = t3.w |
| 146 |
LEFT JOIN t5 ON t5.z = t4.z |
| 147 |
LEFT JOIN t2 ON t2.u = t5.m |
| 148 |
LEFT JOIN t1 xyz ON xyz.y = t2.x; |
| 149 |
} {007f2033-cb20-494c-b135-a1e4eb66130c f6d7661f-4efe-4c90-87b5-858e61cd178b f6d7661f-4efe-4c90-87b5-858e61cd178b {} {} {} {} {} {} {} {} {}} |
| 150 |
do_execsql_test join5-3.3 { |
| 151 |
DROP TABLE IF EXISTS x1; |
| 152 |
DROP TABLE IF EXISTS x2; |
| 153 |
DROP TABLE IF EXISTS x3; |
| 154 |
CREATE TABLE x1(a); |
| 155 |
INSERT INTO x1 VALUES(1); |
| 156 |
CREATE TABLE x2(b NOT NULL); |
| 157 |
CREATE TABLE x3(c, d); |
| 158 |
INSERT INTO x3 VALUES('a', NULL); |
| 159 |
INSERT INTO x3 VALUES('b', NULL); |
| 160 |
INSERT INTO x3 VALUES('c', NULL); |
| 161 |
SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b; |
| 162 |
} {} |
| 163 |
|
| 164 |
# Ticket https://sqlite.org/src/tktview/c2a19d81652f40568c770c43 on |
| 165 |
# 2015-08-20. LEFT JOIN and the push-down optimization. |
| 166 |
# |
| 167 |
do_execsql_test join5-4.1 { |
| 168 |
SELECT * |
| 169 |
FROM ( |
| 170 |
SELECT 'apple' fruit |
| 171 |
UNION ALL SELECT 'banana' |
| 172 |
) a |
| 173 |
JOIN ( |
| 174 |
SELECT 'apple' fruit |
| 175 |
UNION ALL SELECT 'banana' |
| 176 |
) b ON a.fruit=b.fruit |
| 177 |
LEFT JOIN ( |
| 178 |
SELECT 1 isyellow |
| 179 |
) c ON b.fruit='banana'; |
| 180 |
} {apple apple {} banana banana 1} |
| 181 |
do_execsql_test join5-4.2 { |
| 182 |
SELECT * |
| 183 |
FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana') |
| 184 |
LEFT JOIN (SELECT 1) ON fruit='banana'; |
| 185 |
} {apple {} banana 1} |
| 186 |
|
| 187 |
#------------------------------------------------------------------------- |
| 188 |
do_execsql_test 5.0 { |
| 189 |
CREATE TABLE y1(x, y, z); |
| 190 |
INSERT INTO y1 VALUES(0, 0, 1); |
| 191 |
CREATE TABLE y2(a); |
| 192 |
} |
| 193 |
|
| 194 |
do_execsql_test 5.1 { |
| 195 |
SELECT count(z) FROM y1 LEFT JOIN y2 ON x GROUP BY y; |
| 196 |
} 1 |
| 197 |
|
| 198 |
do_execsql_test 5.2 { |
| 199 |
SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x GROUP BY y; |
| 200 |
} 1 |
| 201 |
|
| 202 |
do_execsql_test 5.3 { |
| 203 |
CREATE VIEW v1 AS SELECT x, y, z FROM y1; |
| 204 |
SELECT count(z) FROM v1 LEFT JOIN y2 ON x GROUP BY y; |
| 205 |
} 1 |
| 206 |
|
| 207 |
do_execsql_test 5.4 { |
| 208 |
SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x |
| 209 |
} 1 |
| 210 |
|
| 211 |
do_execsql_test 5.5 { |
| 212 |
SELECT * FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x |
| 213 |
} {0 0 1 {}} |
| 214 |
|
| 215 |
#------------------------------------------------------------------------- |
| 216 |
# |
| 217 |
reset_db |
| 218 |
do_execsql_test 6.1 { |
| 219 |
CREATE TABLE t1(x); |
| 220 |
INSERT INTO t1 VALUES(1); |
| 221 |
|
| 222 |
CREATE TABLE t2(y INTEGER PRIMARY KEY,a,b); |
| 223 |
INSERT INTO t2 VALUES(1,2,3); |
| 224 |
CREATE INDEX t2a ON t2(a); |
| 225 |
CREATE INDEX t2b ON t2(b); |
| 226 |
} |
| 227 |
|
| 228 |
do_execsql_test 6.2 { |
| 229 |
SELECT * FROM t1 LEFT JOIN t2 ON a=2 OR b=3 WHERE y IS NULL; |
| 230 |
} {} |
| 231 |
|
| 232 |
do_execsql_test 6.3.1 { |
| 233 |
CREATE TABLE t3(x); |
| 234 |
INSERT INTO t3 VALUES(1); |
| 235 |
CREATE TABLE t4(y, z); |
| 236 |
SELECT ifnull(z, '!!!') FROM t3 LEFT JOIN t4 ON (x=y); |
| 237 |
} {!!!} |
| 238 |
|
| 239 |
do_execsql_test 6.3.2 { |
| 240 |
CREATE INDEX t4i ON t4(y, ifnull(z, '!!!')); |
| 241 |
SELECT ifnull(z, '!!!') FROM t3 LEFT JOIN t4 ON (x=y); |
| 242 |
} {!!!} |
| 243 |
|
| 244 |
# 2019-02-08 https://sqlite.org/src/info/4e8e4857d32d401f |
| 245 |
reset_db |
| 246 |
do_execsql_test 6.100 { |
| 247 |
CREATE TABLE t1(aa, bb); |
| 248 |
CREATE INDEX t1x1 on t1(abs(aa), abs(bb)); |
| 249 |
INSERT INTO t1 VALUES(-2,-3),(+2,-3),(-2,+3),(+2,+3); |
| 250 |
SELECT * FROM (t1) |
| 251 |
WHERE ((abs(aa)=1 AND 1=2) OR abs(aa)=2) |
| 252 |
AND abs(bb)=3 |
| 253 |
ORDER BY +1, +2; |
| 254 |
} {-2 -3 -2 3 2 -3 2 3} |
| 255 |
|
| 256 |
#------------------------------------------------------------------------- |
| 257 |
# |
| 258 |
reset_db |
| 259 |
do_execsql_test 7.0 { |
| 260 |
CREATE TABLE t1(x); |
| 261 |
INSERT INTO t1 VALUES(1); |
| 262 |
} |
| 263 |
|
| 264 |
do_execsql_test 7.1 { |
| 265 |
CREATE TABLE t2(x, y, z); |
| 266 |
CREATE INDEX t2xy ON t2(x, y); |
| 267 |
WITH s(i) AS ( |
| 268 |
SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000 |
| 269 |
) |
| 270 |
INSERT INTO t2 SELECT i/10, i, NULL FROM s; |
| 271 |
ANALYZE; |
| 272 |
} |
| 273 |
|
| 274 |
do_eqp_test 7.2 { |
| 275 |
SELECT * FROM t1 LEFT JOIN t2 ON ( |
| 276 |
t2.x = t1.x AND (t2.y=? OR (t2.y=? AND t2.z IS NOT NULL)) |
| 277 |
); |
| 278 |
} { |
| 279 |
QUERY PLAN |
| 280 |
|--SCAN t1 |
| 281 |
`--MULTI-INDEX OR |
| 282 |
|--INDEX 1 |
| 283 |
| `--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN |
| 284 |
`--INDEX 2 |
| 285 |
`--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN |
| 286 |
} |
| 287 |
|
| 288 |
do_execsql_test 7.3 { |
| 289 |
CREATE TABLE t3(x); |
| 290 |
INSERT INTO t3(x) VALUES(1); |
| 291 |
CREATE INDEX t3x ON t3(x); |
| 292 |
|
| 293 |
CREATE TABLE t4(x, y, z); |
| 294 |
CREATE INDEX t4xy ON t4(x, y); |
| 295 |
CREATE INDEX t4xz ON t4(x, z); |
| 296 |
|
| 297 |
WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000) |
| 298 |
INSERT INTO t4 SELECT i/10, i, i FROM s; |
| 299 |
|
| 300 |
ANALYZE; |
| 301 |
UPDATE sqlite_stat1 SET stat='1000000 10 1' WHERE idx='t3x'; |
| 302 |
ANALYZE sqlite_schema; |
| 303 |
} |
| 304 |
|
| 305 |
# If both sides of the OR reference the right-hand side of the LEFT JOIN |
| 306 |
# then simplify the LEFT JOIN. |
| 307 |
# |
| 308 |
do_eqp_test 7.4 { |
| 309 |
SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t4.y = ? OR t4.z = ?); |
| 310 |
} { |
| 311 |
QUERY PLAN |
| 312 |
|--SCAN t4 |
| 313 |
`--SEARCH t3 USING COVERING INDEX t3x (x=?) |
| 314 |
} |
| 315 |
# If only one side of the OR references the right-hand side of the LEFT JOIN |
| 316 |
# then do not do the simplification |
| 317 |
# |
| 318 |
do_eqp_test 7.4b { |
| 319 |
SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t4.y = ? OR t3.x = ?); |
| 320 |
} { |
| 321 |
QUERY PLAN |
| 322 |
|--SCAN t3 |
| 323 |
`--SEARCH t4 USING INDEX t4xz (x=?) LEFT-JOIN |
| 324 |
} |
| 325 |
do_eqp_test 7.4c { |
| 326 |
SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t3.x = ? OR t4.z = ?); |
| 327 |
} { |
| 328 |
QUERY PLAN |
| 329 |
|--SCAN t3 |
| 330 |
`--SEARCH t4 USING INDEX t4xz (x=?) LEFT-JOIN |
| 331 |
} |
| 332 |
do_eqp_test 7.4d { |
| 333 |
SELECT * FROM t3 CROSS JOIN t4 ON (t4.x = t3.x) WHERE (+t4.y = ? OR t4.z = ?); |
| 334 |
} { |
| 335 |
QUERY PLAN |
| 336 |
|--SCAN t3 |
| 337 |
|--BLOOM FILTER ON t4 (x=?) |
| 338 |
`--SEARCH t4 USING INDEX t4xz (x=?) |
| 339 |
} |
| 340 |
|
| 341 |
reset_db |
| 342 |
do_execsql_test 8.0 { |
| 343 |
CREATE TABLE t0 (c0, c1, PRIMARY KEY (c0, c1)); |
| 344 |
CREATE TABLE t1 (c0); |
| 345 |
|
| 346 |
INSERT INTO t1 VALUES (2); |
| 347 |
|
| 348 |
INSERT INTO t0 VALUES(0, 10); |
| 349 |
INSERT INTO t0 VALUES(1, 10); |
| 350 |
INSERT INTO t0 VALUES(2, 10); |
| 351 |
INSERT INTO t0 VALUES(3, 10); |
| 352 |
} |
| 353 |
|
| 354 |
do_execsql_test 8.1 { |
| 355 |
SELECT * FROM t0, t1 |
| 356 |
WHERE (t0.c1 >= 1 OR t0.c1 < 1) AND t0.c0 IN (1, t1.c0) ORDER BY 1; |
| 357 |
} { |
| 358 |
1 10 2 |
| 359 |
2 10 2 |
| 360 |
} |
| 361 |
|
| 362 |
|
| 363 |
# 2022-01-31 dbsqlfuzz 787d9bd73164c6f0c85469e2e48b2aff19af6938 |
| 364 |
# |
| 365 |
reset_db |
| 366 |
do_execsql_test 9.1 { |
| 367 |
CREATE TABLE t1(a ,b FLOAT); |
| 368 |
INSERT INTO t1 VALUES(1,1); |
| 369 |
CREATE INDEX t1x1 ON t1(a,b,a,a,a,a,a,a,a,a,a,b); |
| 370 |
ANALYZE sqlite_schema; |
| 371 |
INSERT INTO sqlite_stat1 VALUES('t1','t1x1','648 324 81 81 81 81 81 81 81081 81 81 81'); |
| 372 |
ANALYZE sqlite_schema; |
| 373 |
} |
| 374 |
do_catchsql_test 9.2 { |
| 375 |
SELECT a FROM |
| 376 |
(SELECT a FROM t1 NATURAL LEFT JOIN t1) NATURAL LEFT JOIN t1 |
| 377 |
WHERE (rowid,1)<=(5,0); |
| 378 |
} {0 1} |
| 379 |
|
| 380 |
# 2022-03-02 https://sqlite.org/forum/info/50a1bbe08ce4c29c |
| 381 |
# Bloom-filter pulldown is incompatible with skip-scan. |
| 382 |
# |
| 383 |
reset_db |
| 384 |
do_execsql_test 10.1 { |
| 385 |
CREATE TABLE t1(x INT); |
| 386 |
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20) |
| 387 |
INSERT INTO t1(x) SELECT 0 FROM c; |
| 388 |
CREATE INDEX t1x1 ON t1(x BETWEEN 0 AND 10, x); |
| 389 |
ANALYZE; |
| 390 |
DELETE FROM t1; |
| 391 |
INSERT INTO t1 VALUES(0),(0); |
| 392 |
CREATE VIEW v1 AS SELECT * FROM t1 NATURAL JOIN t1 WHERE (x BETWEEN 0 AND 10) OR true; |
| 393 |
CREATE VIEW v2 AS SELECT * FROM v1 NATURAL JOIN v1; |
| 394 |
CREATE VIEW v3 AS SELECT * FROM v2, v1 USING (x) GROUP BY x; |
| 395 |
SELECT x FROM v3; |
| 396 |
} {0} |
| 397 |
|
| 398 |
# 2022-03-24 https://sqlite.org/forum/forumpost/031e262a89b6a9d2 |
| 399 |
# Bloom-filter on a LEFT JOIN with NULL-based WHERE constraints. |
| 400 |
# |
| 401 |
reset_db |
| 402 |
do_execsql_test 11.1 { |
| 403 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT); |
| 404 |
CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT); |
| 405 |
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<8) |
| 406 |
INSERT INTO t1(a,b) SELECT x, 10*x FROM c; |
| 407 |
INSERT INTO t2(c,d) SELECT b*2, 100*a FROM t1; |
| 408 |
ANALYZE; |
| 409 |
DELETE FROM sqlite_stat1; |
| 410 |
INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES |
| 411 |
('t1',NULL,150105),('t2',NULL,98747); |
| 412 |
ANALYZE sqlite_schema; |
| 413 |
} {} |
| 414 |
do_execsql_test 11.2 { |
| 415 |
SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d IS NULL; |
| 416 |
} {4} |
| 417 |
do_execsql_test 11.3 { |
| 418 |
SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d=100; |
| 419 |
} {1} |
| 420 |
do_execsql_test 11.4 { |
| 421 |
SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d>=300; |
| 422 |
} {2} |
| 423 |
|
| 424 |
# 2022-05-03 https://sqlite.org/forum/forumpost/2482b32700384a0f |
| 425 |
# Bloom-filter pull-down does not handle NOT NULL constraints correctly. |
| 426 |
# |
| 427 |
reset_db |
| 428 |
do_execsql_test 12.1 { |
| 429 |
CREATE TABLE t1(a INT, b INT, c INT); |
| 430 |
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100) |
| 431 |
INSERT INTO t1(a,b,c) SELECT x, x*1000, x*1000000 FROM c; |
| 432 |
CREATE TABLE t2(b INT, x INT); |
| 433 |
INSERT INTO t2(b,x) SELECT b, a FROM t1 WHERE a%3==0; |
| 434 |
CREATE INDEX t2b ON t2(b); |
| 435 |
CREATE TABLE t3(c INT, y INT); |
| 436 |
INSERT INTO t3(c,y) SELECT c, a FROM t1 WHERE a%4==0; |
| 437 |
CREATE INDEX t3c ON t3(c); |
| 438 |
INSERT INTO t1(a,b,c) VALUES(200, 200000, NULL); |
| 439 |
ANALYZE; |
| 440 |
} {} |
| 441 |
do_execsql_test 12.2 { |
| 442 |
SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 WHERE x>0 AND y>0 |
| 443 |
ORDER BY +a; |
| 444 |
} { |
| 445 |
12 12000 12000000 12 12 |
| 446 |
24 24000 24000000 24 24 |
| 447 |
36 36000 36000000 36 36 |
| 448 |
48 48000 48000000 48 48 |
| 449 |
60 60000 60000000 60 60 |
| 450 |
72 72000 72000000 72 72 |
| 451 |
84 84000 84000000 84 84 |
| 452 |
96 96000 96000000 96 96 |
| 453 |
} |
| 454 |
|
| 455 |
|
| 456 |
|
| 457 |
|
| 458 |
finish_test |