|
1 # 2007 Sep 12 |
|
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 is to test that ticket #2640 has been fixed. |
|
13 # |
|
14 # $Id: tkt2640.test,v 1.3 2008/08/04 03:51:24 danielk1977 Exp $ |
|
15 # |
|
16 |
|
17 # The problem in ticket #2640 was that the query optimizer was |
|
18 # not recognizing all uses of tables within subqueries in the |
|
19 # WHERE clause. If the subquery contained a compound SELECT, |
|
20 # then tables that were used by terms of the compound other than |
|
21 # the last term would not be recognized as dependencies. |
|
22 # So if one of the SELECT statements within a compound made |
|
23 # use of a table that occurs later in a join, the query |
|
24 # optimizer would not recognize this and would try to evaluate |
|
25 # the subquery too early, before that tables value had been |
|
26 # established. |
|
27 |
|
28 set testdir [file dirname $argv0] |
|
29 source $testdir/tester.tcl |
|
30 |
|
31 ifcapable !subquery||!compound { |
|
32 finish_test |
|
33 return |
|
34 } |
|
35 |
|
36 do_test tkt2640-1.1 { |
|
37 execsql { |
|
38 CREATE TABLE persons(person_id, name); |
|
39 INSERT INTO persons VALUES(1,'fred'); |
|
40 INSERT INTO persons VALUES(2,'barney'); |
|
41 INSERT INTO persons VALUES(3,'wilma'); |
|
42 INSERT INTO persons VALUES(4,'pebbles'); |
|
43 INSERT INTO persons VALUES(5,'bambam'); |
|
44 CREATE TABLE directors(person_id); |
|
45 INSERT INTO directors VALUES(5); |
|
46 INSERT INTO directors VALUES(3); |
|
47 CREATE TABLE writers(person_id); |
|
48 INSERT INTO writers VALUES(2); |
|
49 INSERT INTO writers VALUES(3); |
|
50 INSERT INTO writers VALUES(4); |
|
51 SELECT DISTINCT p.name |
|
52 FROM persons p, directors d |
|
53 WHERE d.person_id=p.person_id |
|
54 AND NOT EXISTS ( |
|
55 SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id |
|
56 EXCEPT |
|
57 SELECT person_id FROM writers w |
|
58 ); |
|
59 } |
|
60 } {wilma} |
|
61 do_test tkt2640-1.2 { |
|
62 execsql { |
|
63 SELECT DISTINCT p.name |
|
64 FROM persons p CROSS JOIN directors d |
|
65 WHERE d.person_id=p.person_id |
|
66 AND NOT EXISTS ( |
|
67 SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id |
|
68 EXCEPT |
|
69 SELECT person_id FROM writers w |
|
70 ); |
|
71 } |
|
72 } {wilma} |
|
73 do_test tkt2640-1.3 { |
|
74 execsql { |
|
75 SELECT DISTINCT p.name |
|
76 FROM directors d CROSS JOIN persons p |
|
77 WHERE d.person_id=p.person_id |
|
78 AND NOT EXISTS ( |
|
79 SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id |
|
80 EXCEPT |
|
81 SELECT person_id FROM writers w |
|
82 ); |
|
83 } |
|
84 } {wilma} |
|
85 do_test tkt2640-1.4 { |
|
86 execsql { |
|
87 SELECT DISTINCT p.name |
|
88 FROM persons p, directors d |
|
89 WHERE d.person_id=p.person_id |
|
90 AND NOT EXISTS ( |
|
91 SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id |
|
92 EXCEPT |
|
93 SELECT person_id FROM writers w |
|
94 ); |
|
95 } |
|
96 } {wilma} |
|
97 do_test tkt2640-1.5 { |
|
98 execsql { |
|
99 SELECT DISTINCT p.name |
|
100 FROM persons p CROSS JOIN directors d |
|
101 WHERE d.person_id=p.person_id |
|
102 AND NOT EXISTS ( |
|
103 SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id |
|
104 EXCEPT |
|
105 SELECT person_id FROM writers w |
|
106 ); |
|
107 } |
|
108 } {wilma} |
|
109 do_test tkt2640-1.6 { |
|
110 execsql { |
|
111 SELECT DISTINCT p.name |
|
112 FROM directors d CROSS JOIN persons p |
|
113 WHERE d.person_id=p.person_id |
|
114 AND NOT EXISTS ( |
|
115 SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id |
|
116 EXCEPT |
|
117 SELECT person_id FROM writers w |
|
118 ); |
|
119 } |
|
120 } {wilma} |
|
121 |
|
122 |
|
123 |
|
124 finish_test |