| 1 |
# 2016-04-15 |
| 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. The |
| 12 |
# focus of this script is DISTINCT queries using the skip-ahead |
| 13 |
# optimization. |
| 14 |
# |
| 15 |
|
| 16 |
set testdir [file dirname $argv0] |
| 17 |
source $testdir/tester.tcl |
| 18 |
|
| 19 |
set testprefix distinct2 |
| 20 |
|
| 21 |
do_execsql_test 100 { |
| 22 |
CREATE TABLE t1(x INTEGER PRIMARY KEY); |
| 23 |
INSERT INTO t1 VALUES(0),(1),(2); |
| 24 |
CREATE TABLE t2 AS |
| 25 |
SELECT DISTINCT a.x AS aa, b.x AS bb |
| 26 |
FROM t1 a, t1 b; |
| 27 |
SELECT *, '|' FROM t2 ORDER BY aa, bb; |
| 28 |
} {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |} |
| 29 |
do_execsql_test 110 { |
| 30 |
DROP TABLE t2; |
| 31 |
CREATE TABLE t2 AS |
| 32 |
SELECT DISTINCT a.x AS aa, b.x AS bb |
| 33 |
FROM t1 a, t1 b |
| 34 |
WHERE a.x IN t1 AND b.x IN t1; |
| 35 |
SELECT *, '|' FROM t2 ORDER BY aa, bb; |
| 36 |
} {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |} |
| 37 |
do_execsql_test 120 { |
| 38 |
CREATE TABLE t102 (i0 TEXT UNIQUE NOT NULL); |
| 39 |
INSERT INTO t102 VALUES ('0'),('1'),('2'); |
| 40 |
DROP TABLE t2; |
| 41 |
CREATE TABLE t2 AS |
| 42 |
SELECT DISTINCT * |
| 43 |
FROM t102 AS t0 |
| 44 |
JOIN t102 AS t4 ON (t2.i0 IN t102) |
| 45 |
NATURAL JOIN t102 AS t3 |
| 46 |
JOIN t102 AS t1 ON (t0.i0 IN t102) |
| 47 |
JOIN t102 AS t2 ON (t2.i0=+t0.i0 OR (t0.i0<>500 AND t2.i0=t1.i0)); |
| 48 |
SELECT *, '|' FROM t2 ORDER BY 1, 2, 3, 4, 5; |
| 49 |
} {0 0 0 0 | 0 0 1 0 | 0 0 1 1 | 0 0 2 0 | 0 0 2 2 | 0 1 0 0 | 0 1 1 0 | 0 1 1 1 | 0 1 2 0 | 0 1 2 2 | 0 2 0 0 | 0 2 1 0 | 0 2 1 1 | 0 2 2 0 | 0 2 2 2 | 1 0 0 0 | 1 0 0 1 | 1 0 1 1 | 1 0 2 1 | 1 0 2 2 | 1 1 0 0 | 1 1 0 1 | 1 1 1 1 | 1 1 2 1 | 1 1 2 2 | 1 2 0 0 | 1 2 0 1 | 1 2 1 1 | 1 2 2 1 | 1 2 2 2 | 2 0 0 0 | 2 0 0 2 | 2 0 1 1 | 2 0 1 2 | 2 0 2 2 | 2 1 0 0 | 2 1 0 2 | 2 1 1 1 | 2 1 1 2 | 2 1 2 2 | 2 2 0 0 | 2 2 0 2 | 2 2 1 1 | 2 2 1 2 | 2 2 2 2 |} |
| 50 |
|
| 51 |
do_execsql_test 400 { |
| 52 |
CREATE TABLE t4(a,b,c,d,e,f,g,h,i,j); |
| 53 |
INSERT INTO t4 VALUES(0,1,2,3,4,5,6,7,8,9); |
| 54 |
INSERT INTO t4 SELECT * FROM t4; |
| 55 |
INSERT INTO t4 SELECT * FROM t4; |
| 56 |
CREATE INDEX t4x ON t4(c,d,e); |
| 57 |
SELECT DISTINCT a,b,c FROM t4 WHERE a=0 AND b=1; |
| 58 |
} {0 1 2} |
| 59 |
do_execsql_test 410 { |
| 60 |
SELECT DISTINCT a,b,c,d FROM t4 WHERE a=0 AND b=1; |
| 61 |
} {0 1 2 3} |
| 62 |
do_execsql_test 411 { |
| 63 |
SELECT DISTINCT d,a,b,c FROM t4 WHERE a=0 AND b=1; |
| 64 |
} {3 0 1 2} |
| 65 |
do_execsql_test 420 { |
| 66 |
SELECT DISTINCT a,b,c,d,e FROM t4 WHERE a=0 AND b=1; |
| 67 |
} {0 1 2 3 4} |
| 68 |
do_execsql_test 430 { |
| 69 |
SELECT DISTINCT a,b,c,d,e,f FROM t4 WHERE a=0 AND b=1; |
| 70 |
} {0 1 2 3 4 5} |
| 71 |
|
| 72 |
do_execsql_test 500 { |
| 73 |
CREATE TABLE t5(a INT, b INT); |
| 74 |
CREATE UNIQUE INDEX t5x ON t5(a+b); |
| 75 |
INSERT INTO t5(a,b) VALUES(0,0),(1,0),(1,1),(0,3); |
| 76 |
CREATE TEMP TABLE out AS SELECT DISTINCT a+b FROM t5; |
| 77 |
SELECT * FROM out ORDER BY 1; |
| 78 |
} {0 1 2 3} |
| 79 |
|
| 80 |
do_execsql_test 600 { |
| 81 |
CREATE TABLE t6a(x INTEGER PRIMARY KEY); |
| 82 |
INSERT INTO t6a VALUES(1); |
| 83 |
CREATE TABLE t6b(y INTEGER PRIMARY KEY); |
| 84 |
INSERT INTO t6b VALUES(2),(3); |
| 85 |
SELECT DISTINCT x, x FROM t6a, t6b; |
| 86 |
} {1 1} |
| 87 |
|
| 88 |
do_execsql_test 700 { |
| 89 |
CREATE TABLE t7(a, b, c); |
| 90 |
WITH s(i) AS ( |
| 91 |
SELECT 1 UNION ALL SELECT i+1 FROM s WHERE (i+1)<200 |
| 92 |
) |
| 93 |
INSERT INTO t7 SELECT i/100, i/50, i FROM s; |
| 94 |
} |
| 95 |
do_execsql_test 710 { |
| 96 |
SELECT DISTINCT a, b FROM t7; |
| 97 |
} { |
| 98 |
0 0 0 1 |
| 99 |
1 2 1 3 |
| 100 |
} |
| 101 |
do_execsql_test 720 { |
| 102 |
SELECT DISTINCT a, b+1 FROM t7; |
| 103 |
} { |
| 104 |
0 1 0 2 |
| 105 |
1 3 1 4 |
| 106 |
} |
| 107 |
do_execsql_test 730 { |
| 108 |
CREATE INDEX i7 ON t7(a, b+1); |
| 109 |
ANALYZE; |
| 110 |
SELECT DISTINCT a, b+1 FROM t7; |
| 111 |
} { |
| 112 |
0 1 0 2 |
| 113 |
1 3 1 4 |
| 114 |
} |
| 115 |
|
| 116 |
do_execsql_test 800 { |
| 117 |
CREATE TABLE t8(a, b, c); |
| 118 |
WITH s(i) AS ( |
| 119 |
SELECT 1 UNION ALL SELECT i+1 FROM s WHERE (i+1)<100 |
| 120 |
) |
| 121 |
INSERT INTO t8 SELECT i/40, i/20, i/40 FROM s; |
| 122 |
} |
| 123 |
|
| 124 |
do_execsql_test 820 { |
| 125 |
SELECT DISTINCT a, b, c FROM t8; |
| 126 |
} { |
| 127 |
0 0 0 0 1 0 |
| 128 |
1 2 1 1 3 1 |
| 129 |
2 4 2 |
| 130 |
} |
| 131 |
|
| 132 |
do_execsql_test 820 { |
| 133 |
SELECT DISTINCT a, b, c FROM t8 WHERE b=3; |
| 134 |
} {1 3 1} |
| 135 |
|
| 136 |
do_execsql_test 830 { |
| 137 |
CREATE INDEX i8 ON t8(a, c); |
| 138 |
ANALYZE; |
| 139 |
SELECT DISTINCT a, b, c FROM t8 WHERE b=3; |
| 140 |
} {1 3 1} |
| 141 |
|
| 142 |
do_execsql_test 900 { |
| 143 |
CREATE TABLE t9(v); |
| 144 |
INSERT INTO t9 VALUES |
| 145 |
('abcd'), ('Abcd'), ('aBcd'), ('ABcd'), ('abCd'), ('AbCd'), ('aBCd'), |
| 146 |
('ABCd'), ('abcD'), ('AbcD'), ('aBcD'), ('ABcD'), ('abCD'), ('AbCD'), |
| 147 |
('aBCD'), ('ABCD'), |
| 148 |
('wxyz'), ('Wxyz'), ('wXyz'), ('WXyz'), ('wxYz'), ('WxYz'), ('wXYz'), |
| 149 |
('WXYz'), ('wxyZ'), ('WxyZ'), ('wXyZ'), ('WXyZ'), ('wxYZ'), ('WxYZ'), |
| 150 |
('wXYZ'), ('WXYZ'); |
| 151 |
} |
| 152 |
|
| 153 |
do_execsql_test 910 { |
| 154 |
SELECT DISTINCT v COLLATE NOCASE, v FROM t9 ORDER BY +v; |
| 155 |
} { |
| 156 |
ABCD ABCD ABCd ABCd ABcD ABcD ABcd ABcd AbCD |
| 157 |
AbCD AbCd AbCd AbcD AbcD Abcd Abcd |
| 158 |
WXYZ WXYZ WXYz WXYz WXyZ WXyZ WXyz WXyz WxYZ |
| 159 |
WxYZ WxYz WxYz WxyZ WxyZ Wxyz Wxyz |
| 160 |
aBCD aBCD aBCd aBCd aBcD aBcD aBcd aBcd abCD |
| 161 |
abCD abCd abCd abcD abcD abcd abcd |
| 162 |
wXYZ wXYZ wXYz wXYz wXyZ wXyZ wXyz wXyz wxYZ |
| 163 |
wxYZ wxYz wxYz wxyZ wxyZ wxyz wxyz |
| 164 |
} |
| 165 |
|
| 166 |
do_execsql_test 920 { |
| 167 |
CREATE INDEX i9 ON t9(v COLLATE NOCASE, v); |
| 168 |
ANALYZE; |
| 169 |
|
| 170 |
SELECT DISTINCT v COLLATE NOCASE, v FROM t9 ORDER BY +v; |
| 171 |
} { |
| 172 |
ABCD ABCD ABCd ABCd ABcD ABcD ABcd ABcd AbCD |
| 173 |
AbCD AbCd AbCd AbcD AbcD Abcd Abcd |
| 174 |
WXYZ WXYZ WXYz WXYz WXyZ WXyZ WXyz WXyz WxYZ |
| 175 |
WxYZ WxYz WxYz WxyZ WxyZ Wxyz Wxyz |
| 176 |
aBCD aBCD aBCd aBCd aBcD aBcD aBcd aBcd abCD |
| 177 |
abCD abCd abCd abcD abcD abcd abcd |
| 178 |
wXYZ wXYZ wXYz wXYz wXyZ wXyZ wXyz wXyz wxYZ |
| 179 |
wxYZ wxYz wxYz wxyZ wxyZ wxyz wxyz |
| 180 |
} |
| 181 |
|
| 182 |
# Ticket https://sqlite.org/src/info/ef9318757b152e3a on 2017-11-21 |
| 183 |
# Incorrect result due to a skip-ahead-distinct optimization on a |
| 184 |
# join where no rows of the inner loop appear in the result set. |
| 185 |
# |
| 186 |
db close |
| 187 |
sqlite3 db :memory: |
| 188 |
do_execsql_test 1000 { |
| 189 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER); |
| 190 |
CREATE INDEX t1b ON t1(b); |
| 191 |
CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER); |
| 192 |
CREATE INDEX t2y ON t2(y); |
| 193 |
WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<49) |
| 194 |
INSERT INTO t1(b) SELECT x/10 - 1 FROM c; |
| 195 |
WITH RECURSIVE c(x) AS (VALUES(-1) UNION ALL SELECT x+1 FROM c WHERE x<19) |
| 196 |
INSERT INTO t2(x,y) SELECT x, 1 FROM c; |
| 197 |
SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1; |
| 198 |
ANALYZE; |
| 199 |
SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1; |
| 200 |
} {1 1} |
| 201 |
db close |
| 202 |
sqlite3 db :memory: |
| 203 |
do_execsql_test 1010 { |
| 204 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER); |
| 205 |
CREATE INDEX t1b ON t1(b); |
| 206 |
CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER); |
| 207 |
CREATE INDEX t2y ON t2(y); |
| 208 |
WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<49) |
| 209 |
INSERT INTO t1(b) SELECT -(x/10 - 1) FROM c; |
| 210 |
WITH RECURSIVE c(x) AS (VALUES(-1) UNION ALL SELECT x+1 FROM c WHERE x<19) |
| 211 |
INSERT INTO t2(x,y) SELECT -x, 1 FROM c; |
| 212 |
SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>1 ORDER BY y DESC; |
| 213 |
ANALYZE; |
| 214 |
SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>1 ORDER BY y DESC; |
| 215 |
} {1 1} |
| 216 |
db close |
| 217 |
sqlite3 db :memory: |
| 218 |
do_execsql_test 1020 { |
| 219 |
CREATE TABLE t1(a, b); |
| 220 |
CREATE INDEX t1a ON t1(a, b); |
| 221 |
-- Lots of rows of (1, 'no'), followed by a single (1, 'yes'). |
| 222 |
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100) |
| 223 |
INSERT INTO t1(a, b) SELECT 1, 'no' FROM c; |
| 224 |
INSERT INTO t1(a, b) VALUES(1, 'yes'); |
| 225 |
CREATE TABLE t2(x PRIMARY KEY); |
| 226 |
INSERT INTO t2 VALUES('yes'); |
| 227 |
SELECT DISTINCT a FROM t1, t2 WHERE x=b; |
| 228 |
ANALYZE; |
| 229 |
SELECT DISTINCT a FROM t1, t2 WHERE x=b; |
| 230 |
} {1 1} |
| 231 |
|
| 232 |
#------------------------------------------------------------------------- |
| 233 |
reset_db |
| 234 |
|
| 235 |
do_execsql_test 2000 { |
| 236 |
CREATE TABLE t0 (c0, c1, c2, PRIMARY KEY (c0, c1)); |
| 237 |
CREATE TABLE t1 (c2); |
| 238 |
INSERT INTO t0(c2) VALUES (0),(1),(3),(4),(5),(6),(7),(8),(9),(10),(11); |
| 239 |
INSERT INTO t0(c1) VALUES ('a'); |
| 240 |
INSERT INTO t1(c2) VALUES (0); |
| 241 |
} |
| 242 |
do_execsql_test 2010 { |
| 243 |
SELECT DISTINCT t0.c0, t1._rowid_, t0.c1 FROM t1 CROSS JOIN t0 ORDER BY t0.c0; |
| 244 |
} {{} 1 {} {} 1 a} |
| 245 |
do_execsql_test 1.2 { |
| 246 |
ANALYZE; |
| 247 |
} |
| 248 |
do_execsql_test 2020 { |
| 249 |
SELECT DISTINCT t0.c0, t1._rowid_, t0.c1 FROM t1 CROSS JOIN t0 ORDER BY t0.c0; |
| 250 |
} {{} 1 {} {} 1 a} |
| 251 |
|
| 252 |
|
| 253 |
do_execsql_test 2030 { |
| 254 |
CREATE TABLE t2(a, b, c); |
| 255 |
CREATE INDEX t2ab ON t2(a, b); |
| 256 |
|
| 257 |
WITH c(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<64) |
| 258 |
INSERT INTO t2 SELECT 'one', i%2, 'one' FROM c; |
| 259 |
|
| 260 |
WITH c(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<64) |
| 261 |
INSERT INTO t2 SELECT 'two', i%2, 'two' FROM c; |
| 262 |
|
| 263 |
CREATE TABLE t3(x INTEGER PRIMARY KEY); |
| 264 |
INSERT INTO t3 VALUES(1); |
| 265 |
|
| 266 |
ANALYZE; |
| 267 |
} |
| 268 |
do_execsql_test 2040 { |
| 269 |
SELECT DISTINCT a, b, x FROM t3 CROSS JOIN t2 ORDER BY a, +b; |
| 270 |
} { |
| 271 |
one 0 1 |
| 272 |
one 1 1 |
| 273 |
two 0 1 |
| 274 |
two 1 1 |
| 275 |
} |
| 276 |
|
| 277 |
#------------------------------------------------------------------------- |
| 278 |
# |
| 279 |
reset_db |
| 280 |
do_execsql_test 3000 { |
| 281 |
CREATE TABLE t0 (c0, c1 NOT NULL DEFAULT 1, c2, PRIMARY KEY (c0, c1)); |
| 282 |
INSERT INTO t0(c2) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL); |
| 283 |
INSERT INTO t0(c2) VALUES('a'); |
| 284 |
} |
| 285 |
|
| 286 |
do_execsql_test 3010 { |
| 287 |
SELECT DISTINCT * FROM t0 WHERE NULL IS t0.c0; |
| 288 |
} { |
| 289 |
{} 1 {} |
| 290 |
{} 1 a |
| 291 |
} |
| 292 |
|
| 293 |
do_execsql_test 3020 { |
| 294 |
ANALYZE; |
| 295 |
} |
| 296 |
|
| 297 |
do_execsql_test 3030 { |
| 298 |
SELECT DISTINCT * FROM t0 WHERE NULL IS c0; |
| 299 |
} { |
| 300 |
{} 1 {} |
| 301 |
{} 1 a |
| 302 |
} |
| 303 |
|
| 304 |
#------------------------------------------------------------------------- |
| 305 |
# |
| 306 |
reset_db |
| 307 |
|
| 308 |
do_execsql_test 4010 { |
| 309 |
CREATE TABLE t1(a, b COLLATE RTRIM); |
| 310 |
INSERT INTO t1 VALUES(1, ''), (2, ' '), (3, ' '); |
| 311 |
} |
| 312 |
do_execsql_test 4020 { |
| 313 |
SELECT b FROM t1 UNION SELECT 1; |
| 314 |
} {1 {}} |
| 315 |
|
| 316 |
#------------------------------------------------------------------------- |
| 317 |
# |
| 318 |
reset_db |
| 319 |
do_execsql_test 5010 { |
| 320 |
CREATE TABLE cnt(a); |
| 321 |
WITH RECURSIVE cnt2(x) AS ( |
| 322 |
VALUES(1) UNION ALL SELECT x+1 FROM cnt2 WHERE x<50 |
| 323 |
) |
| 324 |
INSERT INTO cnt SELECT x FROM cnt2; |
| 325 |
} |
| 326 |
|
| 327 |
do_execsql_test 5020 { |
| 328 |
SELECT DISTINCT abs(random())%5 AS r FROM cnt ORDER BY r; |
| 329 |
} {0 1 2 3 4} |
| 330 |
|
| 331 |
do_execsql_test 5030 { |
| 332 |
SELECT abs(random())%5 AS r FROM cnt GROUP BY 1 ORDER BY 1; |
| 333 |
} {0 1 2 3 4} |
| 334 |
|
| 335 |
do_execsql_test 5040 { |
| 336 |
SELECT a FROM cnt WHERE a>45 GROUP BY 1; |
| 337 |
} {46 47 48 49 50} |
| 338 |
|
| 339 |
|
| 340 |
# 2024-06-03 dbsqlfuzz 8a44f675401a8b1f68a43bf813c4f4f72ad8f0ea |
| 341 |
# Use of uninitialized bytecode register due to the call-function-once |
| 342 |
# optimization at check-in 663f5dd32d9db832 |
| 343 |
# |
| 344 |
db null NULL |
| 345 |
do_execsql_test 5050 { |
| 346 |
CREATE TABLE t0(a TEXT); INSERT INTO t0 VALUES('abcd'); |
| 347 |
CREATE TABLE t1(b TEXT); |
| 348 |
CREATE TABLE t2(c TEXT); |
| 349 |
CREATE TABLE t3(d TEXT); INSERT INTO t3 VALUES('wxyz'); |
| 350 |
CREATE VIEW v4(e) AS SELECT (SELECT t2.c FROM t0, t1 GROUP BY 1) FROM t2; |
| 351 |
SELECT v4.e FROM t3 LEFT JOIN v4 ON true GROUP BY 1; |
| 352 |
} NULL |
| 353 |
do_execsql_test 5060 { |
| 354 |
DROP VIEW v4; |
| 355 |
CREATE VIEW v4(e) AS SELECT (SELECT t2.c COLLATE nocase FROM t0, t1 GROUP BY 1) FROM t2; |
| 356 |
SELECT v4.e FROM t3 LEFT JOIN v4 ON true GROUP BY 1; |
| 357 |
} NULL |
| 358 |
|
| 359 |
do_execsql_test 5070 { |
| 360 |
DROP VIEW v4; |
| 361 |
CREATE VIEW v4(e) AS SELECT (SELECT unlikely(t2.c COLLATE nocase) FROM t0, t1 GROUP BY 1) FROM t2; |
| 362 |
SELECT v4.e FROM t3 LEFT JOIN v4 ON true GROUP BY 1; |
| 363 |
} NULL |
| 364 |
|
| 365 |
# 2024-06-28 dbsqlfuzz 46343912848a603e32c6072cae792eb056bac897 |
| 366 |
# Do not call sqlite3ExprToRegister() on an expression that is already |
| 367 |
# a register. |
| 368 |
# |
| 369 |
do_execsql_test 5080 { |
| 370 |
CREATE TABLE dual(dummy TEXT); |
| 371 |
INSERT INTO dual VALUES('X'); |
| 372 |
SELECT 11 = ( |
| 373 |
SELECT b |
| 374 |
FROM ( |
| 375 |
SELECT a AS b |
| 376 |
FROM dual |
| 377 |
LEFT JOIN (SELECT 22 AS a FROM dual) |
| 378 |
) |
| 379 |
GROUP BY b, b |
| 380 |
); |
| 381 |
} 0 |
| 382 |
|
| 383 |
#------------------------------------------------------------------------- |
| 384 |
# 2026-03-05 - do not "skip-ahead" of a null-row on the RHS of a |
| 385 |
# LEFT JOIN. |
| 386 |
# |
| 387 |
reset_db |
| 388 |
|
| 389 |
do_execsql_test 6000 { |
| 390 |
CREATE TABLE t1(c1 UNIQUE NOT NULL); |
| 391 |
INSERT INTO t1 VALUES(1); |
| 392 |
CREATE TABLE t0(c0 UNIQUE); |
| 393 |
INSERT INTO t0 VALUES(0); |
| 394 |
WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<1000) |
| 395 |
INSERT INTO t0(c0) SELECT NULL FROM c; |
| 396 |
} {} |
| 397 |
|
| 398 |
do_execsql_test 6010 { |
| 399 |
SELECT DISTINCT * FROM t1 LEFT OUTER JOIN t0 ON c0>c1; |
| 400 |
} {1 {}} |
| 401 |
|
| 402 |
do_execsql_test 6020 { |
| 403 |
SELECT DISTINCT * FROM t1 FULL OUTER JOIN t0 ON c0>c1; |
| 404 |
} {1 {} {} 0 {} {}} |
| 405 |
|
| 406 |
do_execsql_test 6040 { |
| 407 |
ANALYZE; |
| 408 |
SELECT DISTINCT * FROM t1 LEFT OUTER JOIN t0 ON c0>c1; |
| 409 |
} {1 {}} |
| 410 |
|
| 411 |
do_execsql_test 6050 { |
| 412 |
SELECT DISTINCT * FROM t1 FULL OUTER JOIN t0 ON c0>c1; |
| 413 |
} {1 {} {} 0 {} {}} |
| 414 |
|
| 415 |
finish_test |