1: # 2008 September 1
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: #
12: # This file implements regression tests for SQLite library. The
13: # focus of this file is testing the fix for ticket #3346
14: #
15: # $Id: tkt3346.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
16:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: do_test tkt3346-1.1 {
21: db eval {
22: CREATE TABLE t1(a,b);
23: INSERT INTO t1 VALUES(2,'bob');
24: INSERT INTO t1 VALUES(1,'alice');
25: INSERT INTO t1 VALUES(3,'claire');
26: SELECT *, ( SELECT y FROM (SELECT x.b='alice' AS y) )
27: FROM ( SELECT * FROM t1 ) AS x;
28: }
29: } {2 bob 0 1 alice 1 3 claire 0}
30: do_test tkt3346-1.2 {
31: db eval {
32: SELECT b FROM (SELECT * FROM t1) AS x
33: WHERE (SELECT y FROM (SELECT x.b='alice' AS y))=0
34: }
35: } {bob claire}
36: do_test tkt3346-1.3 {
37: db eval {
38: SELECT b FROM (SELECT * FROM t1 ORDER BY a) AS x
39: WHERE (SELECT y FROM (SELECT a||b y FROM t1 WHERE t1.b=x.b))=(x.a||x.b)
40: }
41: } {alice bob claire}
42: do_test tkt3346-1.4 {
43: db eval {
44: SELECT b FROM (SELECT * FROM t1 ORDER BY a) AS x
45: WHERE (SELECT y FROM (SELECT a||b y FROM t1 WHERE t1.b=x.b))=('2'||x.b)
46: }
47: } {bob}
48:
49: # Ticket #3530
50: #
51: # As shown by ticket #3346 above (see also ticket #3298) it is important
52: # that a subquery in the result-set be able to look up through multiple
53: # FROM levels in order to view tables in the FROM clause at the top level.
54: #
55: # But ticket #3530 shows us that a subquery in the FROM clause should not
56: # be able to look up to higher levels:
57: #
58: do_test tkt3346-2.1 {
59: catchsql {
60: CREATE TABLE t2(a);
61: INSERT INTO t2 VALUES(1);
62:
63: SELECT * FROM (SELECT * FROM t1 WHERE 1=x.a) AS x;
64: }
65: } {1 {no such column: x.a}}
66:
67: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>