AryaWu/sqlite
0
1# 2022 May 172#3# The author disclaims copyright to this source code. In place of4# 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 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix joinH17 18do_execsql_test 1.0 {19 CREATE TABLE t1(a INT);20 CREATE TABLE t2(b INT);21 INSERT INTO t2(b) VALUES(NULL);22}23 24db nullvalue NULL25 26do_execsql_test 1.1 {27 SELECT DISTINCT a FROM t1 FULL JOIN t2 ON true WHERE (b ISNULL);28} {NULL}29do_execsql_test 1.2 {30 SELECT a FROM t1 FULL JOIN t2 ON true;31} {NULL}32do_execsql_test 1.3 {33 SELECT a FROM t1 FULL JOIN t2 ON true WHERE (b ISNULL);34} {NULL}35do_execsql_test 1.4 {36 SELECT DISTINCT a FROM t1 FULL JOIN t2 ON true;37} {NULL}38 39#-----------------------------------------------------------40 41reset_db42do_execsql_test 2.0 {43 CREATE TABLE r3(x);44 CREATE TABLE r4(y INTEGER PRIMARY KEY);45 INSERT INTO r4 VALUES(55);46}47 48do_execsql_test 2.1 {49 SELECT 'value!' FROM r3 FULL JOIN r4 ON (y=x);50} {value!}51 52do_execsql_test 2.2 {53 SELECT 'value!' FROM r3 FULL JOIN r4 ON (y=x) WHERE +y=55;54} {value!}55 56#-----------------------------------------------------------57reset_db58do_execsql_test 3.1 {59 CREATE TABLE t0 (c0);60 CREATE TABLE t1 (c0);61 CREATE TABLE t2 (c0 , c1 , c2 , UNIQUE (c0), UNIQUE (c2 DESC));62 INSERT INTO t2 VALUES ('x', 'y', 'z');63 ANALYZE;64 CREATE VIEW v0(c0) AS SELECT FALSE;65}66 67do_catchsql_test 3.2 {68 SELECT * FROM t0 LEFT OUTER JOIN t1 ON v0.c0 INNER JOIN v0 INNER JOIN t2 ON (t2.c2 NOT NULL); 69} {1 {ON clause references tables to its right}}70 71#-------------------------------------------------------------72 73reset_db74do_execsql_test 4.1 {75 CREATE TABLE t1(a,b,c,d,e,f,g,h,PRIMARY KEY(a,b,c)) WITHOUT ROWID;76 CREATE TABLE t2(i, j);77 INSERT INTO t2 VALUES(10, 20);78}79 80do_execsql_test 4.2 {81 SELECT (d IS NULL) FROM t1 RIGHT JOIN t2 ON (j=33);82} {1}83 84do_execsql_test 4.3 {85 CREATE INDEX i1 ON t1( (d IS NULL), d );86}87 88do_execsql_test 4.4 {89 SELECT (d IS NULL) FROM t1 RIGHT JOIN t2 ON (j=33);90} {1}91 92#-------------------------------------------------------------------------93#94reset_db95do_execsql_test 5.0 {96 CREATE TABLE t0(w);97 CREATE TABLE t1(x);98 CREATE TABLE t2(y);99 CREATE TABLE t3(z);100 INSERT INTO t3 VALUES('t3val');101}102 103do_execsql_test 5.1 {104 SELECT * FROM t1 INNER JOIN t2 ON (0) RIGHT OUTER JOIN t3;105} {{} {} t3val}106 107do_execsql_test 5.2 {108 SELECT * FROM t1 INNER JOIN t2 ON (0) FULL OUTER JOIN t3;109} {{} {} t3val}110 111do_execsql_test 5.3 {112 SELECT * FROM t3 LEFT JOIN t2 ON (0);113} {t3val {}}114 115do_execsql_test 5.4 {116 SELECT * FROM t0 RIGHT JOIN t1 INNER JOIN t2 ON (0) RIGHT JOIN t3117} {{} {} {} t3val}118 119do_execsql_test 5.5 {120 SELECT * FROM t0 RIGHT JOIN t1 INNER JOIN t2 ON (0)121} {}122 123 124reset_db125db null NULL126do_execsql_test 6.0 {127 CREATE TABLE t1(a INT);128 CREATE TABLE t2(b INT);129 INSERT INTO t1 VALUES(3);130 SELECT CASE WHEN t2.b THEN 0 ELSE 1 END FROM t1 LEFT JOIN t2 ON true;131} {1}132do_execsql_test 6.1 {133 SELECT * FROM t1 LEFT JOIN t2 ON true WHERE CASE WHEN t2.b THEN 0 ELSE 1 END;134} {3 NULL}135 136#-------------------------------------------------------------------------137reset_db138do_execsql_test 7.0 {139 CREATE TABLE t1(a, b);140 CREATE TABLE t2(c);141 CREATE TABLE t3(d);142 143 INSERT INTO t1 VALUES ('a', 'a');144 INSERT INTO t2 VALUES ('ddd');145 INSERT INTO t3 VALUES(1234);146}147 148do_execsql_test 7.1 {149 SELECT t2.rowid FROM t1 JOIN (t2 JOIN t3);150} {1}151 152do_execsql_test 7.1 {153 UPDATE t1 SET b = t2.rowid FROM t2, t3;154}155 156do_execsql_test 7.2 { 157 SELECT * FROM t1158} {a 1}159 160#-------------------------------------------------------------------------161reset_db162do_execsql_test 8.0 {163 CREATE TABLE x1(a INTEGER PRIMARY KEY, b);164 CREATE TABLE x2(c, d);165 CREATE TABLE x3(rowid, _rowid_);166 167 CREATE TABLE x4(rowid, _rowid_, oid);168 169 INSERT INTO x1 VALUES(1000, 'thousand');170 INSERT INTO x2 VALUES('c', 'd');171 INSERT INTO x3(oid, rowid, _rowid_) VALUES(43, 'hello', 'world');172 INSERT INTO x4(oid, rowid, _rowid_) VALUES('forty three', 'hello', 'world');173}174 175do_execsql_test 8.1 {176 SELECT x3.oid FROM x1 JOIN (x2 JOIN x3 ON c='c')177} 43178 179breakpoint180do_execsql_test 8.2 {181 SELECT x3.rowid FROM x1 JOIN (x2 JOIN x3 ON c='c')182} {hello}183 184do_execsql_test 8.3 {185 SELECT x4.oid FROM x1 JOIN (x2 JOIN x4 ON c='c')186} {{forty three}}187 188 189#---------------------------------------------------------------------190#191reset_db192do_execsql_test 9.0 {193 CREATE TABLE x1(a);194 CREATE TABLE x2(b);195 CREATE TABLE x3(c);196 197 CREATE TABLE wo1(a PRIMARY KEY, b) WITHOUT ROWID;198 CREATE TABLE wo2(a PRIMARY KEY, rowid) WITHOUT ROWID;199 CREATE TABLE wo3(a PRIMARY KEY, b) WITHOUT ROWID;200}201 202do_catchsql_test 9.1 {203 SELECT rowid FROM wo1, x1, x2;204} {1 {ambiguous column name: rowid}}205do_catchsql_test 9.2 {206 SELECT rowid FROM wo1, (x1, x2);207} {1 {ambiguous column name: rowid}}208do_catchsql_test 9.3 {209 SELECT rowid FROM wo1 JOIN (x1 JOIN x2);210} {1 {ambiguous column name: rowid}}211do_catchsql_test 9.4 {212 SELECT a FROM wo1, x1, x2;213} {1 {ambiguous column name: a}}214 215 216# It is not possible to use "rowid" in a USING clause.217#218do_catchsql_test 9.5 {219 SELECT * FROM x1 JOIN x2 USING (rowid);220} {1 {cannot join using column rowid - column not present in both tables}}221do_catchsql_test 9.6 {222 SELECT * FROM wo2 JOIN x2 USING (rowid);223} {1 {cannot join using column rowid - column not present in both tables}}224 225# "rowid" columns are not matched by NATURAL JOIN. If they were, then226# the SELECT below would return zero rows.227do_execsql_test 9.7 {228 INSERT INTO x1(rowid, a) VALUES(101, 'A');229 INSERT INTO x2(rowid, b) VALUES(55, 'B');230 SELECT * FROM x1 NATURAL JOIN x2;231} {A B}232 233do_execsql_test 9.8 {234 INSERT INTO wo1(a, b) VALUES('mya', 'myb');235 INSERT INTO wo2(a, rowid) VALUES('mypk', 'myrowid');236 INSERT INTO wo3(a, b) VALUES('MYA', 'MYB');237 INSERT INTO x3(rowid, c) VALUES(99, 'x3B');238}239 240do_catchsql_test 9.8 {241 SELECT rowid FROM x1 JOIN (x2 JOIN wo2);242} {0 myrowid}243do_catchsql_test 9.9 {244 SELECT _rowid_ FROM wo1 JOIN (wo3 JOIN x3)245} {0 99}246do_catchsql_test 9.10 {247 SELECT oid FROM wo1 JOIN (wo3 JOIN x3)248} {0 99}249do_catchsql_test 9.11 {250 SELECT oid FROM wo2 JOIN (wo3 JOIN x3)251} {0 99}252 253reset_db254do_execsql_test 10.0 {255 CREATE TABLE rt0 (c0 INTEGER, c1 INTEGER, c2 INTEGER, c3 INTEGER, c4 INTEGER);256 CREATE TABLE rt3 (c3 INTEGER);257 258 INSERT INTO rt0(c3, c1) VALUES (x'', '1');259 INSERT INTO rt0(c3, c1) VALUES ('-1', -1e500);260 INSERT INTO rt0(c3, c1) VALUES (1, x'');261 262 CREATE VIEW v6(c0, c1, c2) AS SELECT 0, 0, 0;263}264 265do_execsql_test 10.1 {266 SELECT COUNT(*) FROM rt0 LEFT JOIN rt3 JOIN v6 ON ((CASE v6.c0 WHEN rt0.c4 THEN rt3.c3 END) NOT BETWEEN (rt0.c4) AND (NULL)) WHERE (rt0.c1); -- 2267} {0}268 269do_execsql_test 10.2 {270 SELECT COUNT(*) FROM rt0 LEFT JOIN rt3 RIGHT OUTER JOIN v6 ON ((CASE v6.c0 WHEN rt0.c4 THEN rt3.c3 END) NOT BETWEEN (rt0.c4) AND (NULL)) WHERE (rt0.c1); -- 2271} {0}272 273#-------------------------------------------------------------------------274 275do_execsql_test 11.1 {276 CREATE TABLE t1(a, b);277 CREATE TABLE t2(c, d);278 CREATE TABLE t3(e, f);279 280 INSERT INTO t1 VALUES(1, 1);281 INSERT INTO t2 VALUES(2, 2);282 INSERT INTO t3 VALUES(3, 3);283}284 285do_execsql_test 11.2 {286 SELECT * FROM t1 LEFT JOIN t2 RIGHT JOIN t3 ON (t2.c=10)287} {{} {} {} {} 3 3}288 289do_execsql_test 11.3 {290 SELECT * FROM t1 LEFT JOIN t2 RIGHT JOIN t3 ON (t2.c=10) WHERE t1.a=1291} {}292 293#-------------------------------------------------------------------------294reset_db295 296do_execsql_test 12.1 {297 CREATE TABLE t1(a1 INT, b1 TEXT);298 INSERT INTO t1 VALUES(88,'');299 CREATE TABLE t2(c2 INT, d2 TEXT);300 INSERT INTO t2 VALUES(88,'');301 CREATE TABLE t3(e3 TEXT PRIMARY KEY);302 INSERT INTO t3 VALUES('');303}304 305do_execsql_test 12.2 {306 SELECT * FROM t1 LEFT JOIN t2 ON true RIGHT JOIN t3 ON d2=e3 WHERE c2 BETWEEN NULL AND a1;307}308do_execsql_test 12.3 {309 SELECT * FROM t1 LEFT JOIN t2 ON true RIGHT JOIN t3 ON d2=e3 WHERE c2 BETWEEN NULL AND a1;310}311 312#-------------------------------------------------------------------------313# 2024-04-05 dbsqlfuzz b9e65e2f110df998f1306571fae7af6c01e4d92b314reset_db315do_execsql_test 13.1 {316 CREATE TABLE t1(a INT AS (b), b INT);317 INSERT INTO t1(b) VALUES(123);318 CREATE TABLE t2(a INT, c INT);319 SELECT a FROM t2 NATURAL RIGHT JOIN t1;320} {123}321do_execsql_test 13.2 {322 CREATE INDEX t1a ON t1(a);323 SELECT a FROM t2 NATURAL RIGHT JOIN t1;324} {123}325# Further tests of the same logic (indexes on expressions326# used by RIGHT JOIN) from check-in ffe23af73fcb324d and327# forum post https://sqlite.org/forum/forumpost/9b491e1debf0b67a.328db null NULL329do_execsql_test 13.3 {330 CREATE TABLE t3(a INT, b INT);331 CREATE UNIQUE INDEX t3x ON t3(a, a+b);332 INSERT INTO t3(a,b) VALUES(1,2),(4,8),(16,32),(4,80),(1,-300);333 CREATE TABLE t4(x INT, y INT);334 INSERT INTO t4(x,y) SELECT a, b FROM t3;335 INSERT INTO t4(x,y) VALUES(99,99);336 SELECT a1.a, sum( a1.a+a1.b ) FROM t3 AS a1 RIGHT JOIN t4 ON a=x337 GROUP BY a1.a ORDER BY 1;338} {NULL NULL 1 -592 4 192 16 48}339do_execsql_test 13.4 {340 SELECT sum( a1.a+a1.b ) FROM t3 AS a1 RIGHT JOIN t3 ON true341 GROUP BY a1.a ORDER BY 1;342} {-1480 240 480}343 344#-------------------------------------------------------------------------345# 2025-05-30346# https://sqlite.org/forum/forumpost/5028c785b6347#348reset_db349 350do_execsql_test 14.0 {351 CREATE TABLE t1(c0 INT);352 CREATE TABLE t2(c0 BLOB);353 CREATE TABLE t3(c0 BLOB);354 CREATE TABLE t4(c4 BLOB);355 INSERT INTO t1(c0) VALUES(0);356 INSERT INTO t3(c0) VALUES('0');357}358 359do_execsql_test 14.1.1 {360 SELECT * FROM t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3;361} {0}362 363do_execsql_test 14.1.2 {364 SELECT * FROM t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3 FULL JOIN t4 ON true;365} {0 {}}366 367do_execsql_test 14.1.3 {368 SELECT * FROM (t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3) FULL JOIN t4 ON true;369} {0 {}}370 371do_execsql_test 14.1.4 {372 SELECT * 373 FROM (t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3) AS qq FULL JOIN t4 ON true;374} {0 {}}375 376do_execsql_test 14.2.1 {377 SELECT * FROM t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1;378} {0}379 380do_execsql_test 14.2.2 {381 SELECT * FROM t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1 FULL JOIN t4 ON true;382} {0 {}}383 384do_execsql_test 14.2.3 {385 SELECT * FROM (t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1) FULL JOIN t4 ON true;386} {0 {}}387 388do_execsql_test 14.2.4 {389 SELECT * 390 FROM (t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1) AS qq FULL JOIN t4 ON true;391} {0 {}}392 393# 2025-06-01 394#395reset_db396do_execsql_test 15.1 {397 CREATE TABLE t0(c0);398 CREATE TABLE t1(c0);399 CREATE TABLE t2(c0);400 INSERT INTO t0 VALUES ('1.0');401 INSERT INTO t2(c0) VALUES (9);402 SELECT t0.c0,t2.c0 FROM (SELECT CAST(t0.c0 as REAL) AS c0 FROM t0) as subquery NATURAL LEFT JOIN t1 NATURAL JOIN t0 RIGHT JOIN t2 ON 1;403} {1.0 9}404do_execsql_test 15.2 {405 CREATE TABLE x1(x COLLATE nocase);406 CREATE TABLE x2(x);407 CREATE TABLE x3(x);408 CREATE TABLE t4(y);409 INSERT INTO x1 VALUES('ABC');410 INSERT INTO x3 VALUES('abc');411 SELECT lower(x), quote(y) FROM x1 LEFT JOIN x2 USING (x) JOIN x3 USING (x) FULL JOIN t4;412} {abc NULL}413 414#-------------------------------------------------------------------------415reset_db416do_execsql_test 16.0 {417 CREATE TABLE t0_a (c0 INT);418 CREATE TABLE t0_b (c0 INT);419 420 CREATE TABLE t2 (c0 INT);421 INSERT INTO t2 VALUES (1);422}423 424do_execsql_test 16.1 {425 SELECT * FROM t2 LEFT JOIN t0_b426} {1 {}}427 428do_execsql_test 16.2.1 {429 SELECT * FROM (t0_a RIGHT JOIN ( t2 LEFT JOIN t0_b))430} {{} 1 {}}431do_execsql_test 16.2.2 {432 SELECT * FROM (t0_a RIGHT JOIN (SELECT * FROM t2 LEFT JOIN t0_b))433} {{} 1 {}}434 435 436do_catchsql_test 16.3.1 {437 SELECT * FROM (t0_a RIGHT JOIN ( t2 LEFT JOIN t0_b) USING (c0));438} {1 {ambiguous column name: c0}}439 440do_execsql_test 16.3.2 {441 SELECT * FROM (t0_a RIGHT JOIN (SELECT * FROM t2 LEFT JOIN t0_b) USING (c0));442} {1 {}}443 444do_execsql_test 16.4.0 {445 CREATE TABLE x0(a TEXT);446 CREATE TABLE x1(a TEXT);447 CREATE TABLE x2(a TEXT);448 449 INSERT INTO x1 VALUES('blue');450 INSERT INTO x2 VALUES('red');451}452 453do_catchsql_test 16.4.1 {454 SELECT * FROM x0 RIGHT JOIN ( x1, x2) USING (a)455} {1 {ambiguous column name: a}}456 457do_execsql_test 16.4.2 {458 SELECT * FROM x0 RIGHT JOIN (SELECT * FROM x1, x2) USING (a)459} {blue red}460 461reset_db462do_execsql_test 16.5.0 {463 CREATE TABLE t0 (c0 INT);464 CREATE TABLE t1 (c0 INT);465 CREATE TABLE t2 (c0 INT);466 467 INSERT INTO t1(c0) VALUES (NULL);468 INSERT INTO t2 VALUES (1);469 470 CREATE VIEW v0(c0) AS SELECT 1 AS col_0 FROM t0;471}472 473do_catchsql_test 16.5.2 {474 SELECT * FROM (t0 NATURAL RIGHT JOIN (t0 FULL JOIN (v0 NATURAL FULL JOIN t2) ON TRUE)) NATURAL FULL JOIN t1;475} {1 {ambiguous column name: c0}}476 477finish_test478