OLD | NEW |
1 # 2005 September 19 | 1 # 2005 September 19 |
2 # | 2 # |
3 # The author disclaims copyright to this source code. In place of | 3 # The author disclaims copyright to this source code. In place of |
4 # a legal notice, here is a blessing: | 4 # a legal notice, here is a blessing: |
5 # | 5 # |
6 # May you do good and not evil. | 6 # May you do good and not evil. |
7 # May you find forgiveness for yourself and forgive others. | 7 # May you find forgiveness for yourself and forgive others. |
8 # May you share freely, never taking more than you give. | 8 # May you share freely, never taking more than you give. |
9 # | 9 # |
10 #*********************************************************************** | 10 #*********************************************************************** |
11 # This file implements regression tests for SQLite library. | 11 # This file implements regression tests for SQLite library. |
12 # | 12 # |
13 # This file implements tests for left outer joins containing ON | 13 # This file implements tests for left outer joins containing ON |
14 # clauses that restrict the scope of the left term of the join. | 14 # clauses that restrict the scope of the left term of the join. |
15 # | 15 # |
16 # $Id: join5.test,v 1.2 2007/06/08 00:20:48 drh Exp $ | |
17 | 16 |
18 set testdir [file dirname $argv0] | 17 set testdir [file dirname $argv0] |
19 source $testdir/tester.tcl | 18 source $testdir/tester.tcl |
| 19 set testprefix join5 |
20 | 20 |
21 | 21 |
22 do_test join5-1.1 { | 22 do_test join5-1.1 { |
23 execsql { | 23 execsql { |
24 BEGIN; | 24 BEGIN; |
25 CREATE TABLE t1(a integer primary key, b integer, c integer); | 25 CREATE TABLE t1(a integer primary key, b integer, c integer); |
26 CREATE TABLE t2(x integer primary key, y); | 26 CREATE TABLE t2(x integer primary key, y); |
27 CREATE TABLE t3(p integer primary key, q); | 27 CREATE TABLE t3(p integer primary key, q); |
28 INSERT INTO t3 VALUES(11,'t3-11'); | 28 INSERT INTO t3 VALUES(11,'t3-11'); |
29 INSERT INTO t3 VALUES(12,'t3-12'); | 29 INSERT INTO t3 VALUES(12,'t3-12'); |
(...skipping 147 matching lines...) Expand 10 before | Expand all | Expand 10 after Loading... |
177 LEFT JOIN ( | 177 LEFT JOIN ( |
178 SELECT 1 isyellow | 178 SELECT 1 isyellow |
179 ) c ON b.fruit='banana'; | 179 ) c ON b.fruit='banana'; |
180 } {apple apple {} banana banana 1} | 180 } {apple apple {} banana banana 1} |
181 do_execsql_test join6-4.2 { | 181 do_execsql_test join6-4.2 { |
182 SELECT * | 182 SELECT * |
183 FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana') | 183 FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana') |
184 LEFT JOIN (SELECT 1) ON fruit='banana'; | 184 LEFT JOIN (SELECT 1) ON fruit='banana'; |
185 } {apple {} banana 1} | 185 } {apple {} banana 1} |
186 | 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 |
187 finish_test | 215 finish_test |
OLD | NEW |