Lines Matching refs:id
18 foreach {id schema} {
20 CREATE TABLE t3(id INTEGER PRIMARY KEY, w TEXT);
21 CREATE TABLE t4(id INTEGER PRIMARY KEY, x TEXT);
22 CREATE TABLE t5(id INTEGER PRIMARY KEY, y TEXT);
23 CREATE TABLE t6(id INTEGER PRIMARY KEY, z INT);
25 INSERT INTO t3(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven');
26 INSERT INTO t4(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave');
27 INSERT INTO t5(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'),
29 INSERT INTO t6(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999);
32 CREATE TABLE t3(id INT PRIMARY KEY, w TEXT) WITHOUT ROWID;
33 CREATE TABLE t4(id INT PRIMARY KEY, x TEXT) WITHOUT ROWID;
34 CREATE TABLE t5(id INT PRIMARY KEY, y TEXT) WITHOUT ROWID;
35 CREATE TABLE t6(id INT PRIMARY KEY, z INT) WITHOUT ROWID;
38 INSERT INTO t3(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven');
39 INSERT INTO t4(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave');
40 INSERT INTO t5(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'),
42 INSERT INTO t6(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999);
45 CREATE TABLE t3x(id INTEGER PRIMARY KEY, w TEXT);
46 CREATE TABLE t4x(id INTEGER PRIMARY KEY, x TEXT);
47 CREATE TABLE t5x(id INTEGER PRIMARY KEY, y TEXT);
48 CREATE TABLE t6x(id INTEGER PRIMARY KEY, z INT);
50 INSERT INTO t3x(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven');
51 INSERT INTO t4x(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave');
52 INSERT INTO t5x(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'),
54 INSERT INTO t6x(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999);
61 CREATE TABLE t3a(id INTEGER PRIMARY KEY, w TEXT);
62 CREATE TABLE t3b(id INTEGER PRIMARY KEY, w TEXT);
63 CREATE TABLE t4a(id INTEGER PRIMARY KEY, x TEXT);
64 CREATE TABLE t4b(id INTEGER PRIMARY KEY, x TEXT);
65 CREATE TABLE t5a(id INTEGER PRIMARY KEY, y TEXT);
66 CREATE TABLE t5b(id INTEGER PRIMARY KEY, y TEXT);
67 CREATE TABLE t6a(id INTEGER PRIMARY KEY, z INT);
68 CREATE TABLE t6b(id INTEGER PRIMARY KEY, z INT);
70 INSERT INTO t3a(id,w) VALUES(2,'two'),(3,'three');
71 INSERT INTO t3b(id,w) VALUES(6,'six'),(7,'seven');
72 INSERT INTO t4a(id,x) VALUES(2,'alice'),(4,'bob');
73 INSERT INTO t4b(id,x) VALUES(6,'cindy'),(8,'dave');
74 INSERT INTO t5a(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow');
75 INSERT INTO t5b(id,y) VALUES(4,'green'),(5,'blue');
76 INSERT INTO t6a(id,z) VALUES(3,333),(4,444);
77 INSERT INTO t6b(id,z) VALUES(5,555),(0,1000),(9,999);
84 CREATE TABLE t3a(id INTEGER PRIMARY KEY, w TEXT) WITHOUT ROWID;
85 CREATE TABLE t3b(id INTEGER PRIMARY KEY, w TEXT);
86 CREATE TABLE t4a(id INTEGER PRIMARY KEY, x TEXT) WITHOUT ROWID;
87 CREATE TABLE t4b(id INTEGER PRIMARY KEY, x TEXT) WITHOUT ROWID;
88 CREATE TABLE t5a(id INTEGER PRIMARY KEY, y TEXT);
89 CREATE TABLE t5b(id INTEGER PRIMARY KEY, y TEXT) WITHOUT ROWID;
90 CREATE TABLE t6a(id INTEGER PRIMARY KEY, z INT);
91 CREATE TABLE t6b(id INTEGER PRIMARY KEY, z INT);
93 INSERT INTO t3a(id,w) VALUES(2,'two'),(3,'three');
94 INSERT INTO t3b(id,w) VALUES(6,'six'),(7,'seven');
95 INSERT INTO t4a(id,x) VALUES(2,'alice'),(4,'bob');
96 INSERT INTO t4b(id,x) VALUES(6,'cindy'),(8,'dave');
97 INSERT INTO t5a(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow');
98 INSERT INTO t5b(id,y) VALUES(4,'green'),(5,'blue');
99 INSERT INTO t6a(id,z) VALUES(3,333),(4,444);
100 INSERT INTO t6b(id,z) VALUES(5,555),(0,1000),(9,999);
109 do_execsql_test join9-$id.setup $schema {}
112 do_execsql_test join9-$id.100 {
113 SELECT *, t4.id, t5.id, t6.id
123 do_execsql_test join9-$id.101 {
124 SELECT *, t4.id, t5.id, t6.id
126 ORDER BY id;
133 do_execsql_test join9-$id.102 {
134 SELECT *, t4.id, t5.id, t6.id
135 FROM t4 LEFT JOIN t5 USING(id) LEFT JOIN t6 USING(id)
136 ORDER BY id;
145 do_execsql_test join9-$id.200 {
146 SELECT id, x, y, z, t4.id, t5.id, t6.id
156 do_execsql_test join9-$id.201 {
157 SELECT id, x, y, z, t4.id, t5.id, t6.id
159 ORDER BY id;
168 do_execsql_test join9-$id.300 {
169 SELECT *, t4.id, t5.id, t6.id
180 do_execsql_test join9-$id.301 {
181 SELECT *, t4.id, t5.id, t6.id
183 ORDER BY id;
193 do_execsql_test join9-$id.400 {
194 SELECT *, t4.id, t5.id, t6.id
209 do_execsql_test join9-$id.401 {
210 SELECT *, t4.id, t5.id, t6.id
212 ORDER BY id;
224 do_execsql_test join9-$id.402 {
225 SELECT id, x, y, z, t4.id, t5.id, t6.id
227 ORDER BY id;
239 do_execsql_test join9-$id.403 {
240 SELECT id, x, y, z, t4.id, t5.id, t6.id
242 ORDER BY id;
254 do_execsql_test join9-$id.404 {
255 SELECT id, x, y, z, t4.id, t5.id, t6.id
257 ORDER BY id;
269 do_execsql_test join9-$id.405 {
270 SELECT id, x, y, z, t4.id, t5.id, t6.id
272 ORDER BY id;
284 do_execsql_test join9-$id.406 {
285 SELECT id, x, y, z, t4.id, t5.id, t6.id
287 ORDER BY id;
301 do_execsql_test join9-$id.500 {
302 SELECT id, w, x, y, z
303 FROM t3 FULL JOIN t4 USING(id)
305 FULL JOIN t6 USING(id)
321 do_execsql_test join9-$id.600 {
322 SELECT id, w, x, y, z
324 FULL JOIN t4 USING(id)
328 FULL JOIN t6 USING(id)
345 do_execsql_test join9-$id.700 {
346 SELECT id, w, x, y, z
348 FULL JOIN t4 USING(id)
352 FULL JOIN t6 USING(id)
369 do_execsql_test join9-$id.800 {
370 WITH t7(id,a) AS MATERIALIZED (SELECT * FROM t4 WHERE false)
373 JOIN t7 AS t7b USING(id)
374 FULL JOIN t3 USING(id);
383 do_execsql_test join9-$id.900 {
401 do_execsql_test join9-$id.910 {
419 do_execsql_test join9-$id.920 {
423 t5 FULL JOIN t6 USING (id)
424 ) USING(id)
425 ) USING(id)
439 do_execsql_test join9-$id.920 {
443 t5 FULL JOIN t6 USING (id)
444 ) USING(id)
445 ) USING(id)
461 do_execsql_test join9-$id.930 {
465 t5 FULL JOIN t6 USING(id)
466 ) USING(id)
467 ) AS j1 ON j1.id=t3.id
468 ORDER BY coalesce(t3.id,j1.id);
483 do_execsql_test join9-$id.940 {
487 t5 FULL JOIN t6 USING(id)
488 ) USING(id)
489 ) AS j1 ON j1.id=t3.id
490 ORDER BY coalesce(t3.id,j1.id);
504 do_execsql_test join9-$id.950 {
508 t5 FULL JOIN t6 USING(id)
509 ) USING(id)
510 ) AS j1 ON j1.id=t3.id
511 ORDER BY coalesce(t3.id,j1.id);
523 do_execsql_test join9-$id.1000 {
524 WITH t56(id,y,z) AS (SELECT * FROM t5 FULL JOIN t6 USING(id) LIMIT 50)
525 SELECT id,x,y,z FROM t4 JOIN t56 USING(id)
533 do_execsql_test join9-$id.1010 {
534 SELECT id,x,y,z
535 FROM t4 INNER JOIN (t5 FULL JOIN t6 USING(id)) USING(id)
543 do_execsql_test join9-$id.1020 {
544 SELECT id,x,y,z
545 FROM t4 FULL JOIN t5 USING(id) INNER JOIN t6 USING(id)
554 do_execsql_test join9-$id.1030 {
555 WITH t45(id,x,y) AS (SELECT * FROM t4 FULL JOIN t5 USING(id) LIMIT 50)
556 SELECT id,x,y,z FROM t45 JOIN t6 USING(id)