|
1588.1.1
by Stewart Smith
add test (straight from bug report) for bug lp:588408 |
1 |
--echo #
|
2 |
--echo # BUG#46680 - Assertion failed in file item_subselect.cc,
|
|
3 |
--echo # line 305 crashing on HAVING subquery
|
|
4 |
--echo #
|
|
5 |
||
6 |
--echo # Create tables
|
|
7 |
--echo #
|
|
8 |
||
9 |
CREATE TABLE t1 ( |
|
10 |
pk INT, |
|
11 |
v VARCHAR(1) DEFAULT NULL, |
|
12 |
PRIMARY KEY(pk) |
|
13 |
);
|
|
14 |
CREATE TABLE t2 LIKE t1; |
|
15 |
CREATE TABLE t3 LIKE t1; |
|
16 |
CREATE TABLE empty1 (a int); |
|
17 |
||
18 |
INSERT INTO t1 VALUES (1,'c'),(2,NULL); |
|
19 |
INSERT INTO t2 VALUES (3,'m'),(4,NULL); |
|
20 |
INSERT INTO t3 VALUES (1,'n'); |
|
21 |
||
22 |
--echo
|
|
23 |
--echo #
|
|
24 |
--echo # 1) Test that subquery materialization is setup for query with
|
|
25 |
--echo # premature optimize() exit due to "Impossible WHERE"
|
|
26 |
--echo #
|
|
27 |
SELECT MIN(t2.pk) |
|
28 |
FROM t2 JOIN t1 ON t1.pk=t2.pk |
|
29 |
WHERE 'j' |
|
30 |
HAVING ('m') IN ( |
|
31 |
SELECT v |
|
32 |
FROM t2); |
|
33 |
||
34 |
--echo
|
|
35 |
EXPLAIN
|
|
36 |
SELECT MIN(t2.pk) |
|
37 |
FROM t2 JOIN t1 ON t1.pk=t2.pk |
|
38 |
WHERE 'j' |
|
39 |
HAVING ('m') IN ( |
|
40 |
SELECT v |
|
41 |
FROM t2); |
|
42 |
||
43 |
--echo
|
|
44 |
--echo #
|
|
45 |
--echo # 2) Test that subquery materialization is setup for query with
|
|
46 |
--echo # premature optimize() exit due to "No matching min/max row"
|
|
47 |
--echo #
|
|
48 |
SELECT MIN(t2.pk) |
|
49 |
FROM t2 |
|
50 |
WHERE t2.pk>10 |
|
51 |
HAVING ('m') IN ( |
|
52 |
SELECT v |
|
53 |
FROM t2); |
|
54 |
||
55 |
--echo
|
|
56 |
EXPLAIN
|
|
57 |
SELECT MIN(t2.pk) |
|
58 |
FROM t2 |
|
59 |
WHERE t2.pk>10 |
|
60 |
HAVING ('m') IN ( |
|
61 |
SELECT v |
|
62 |
FROM t2); |
|
63 |
||
64 |
--echo
|
|
65 |
--echo #
|
|
66 |
--echo # 3) Test that subquery materialization is setup for query with
|
|
67 |
--echo # premature optimize() exit due to "Select tables optimized away"
|
|
68 |
--echo #
|
|
69 |
--echo # NOTE: The result of this query is actually wrong; it should be NULL
|
|
70 |
--echo # See BUG#47762. Even so, the test case is still needed to test
|
|
71 |
--echo # that the HAVING subquery does not crash the server
|
|
72 |
--echo #
|
|
73 |
SELECT MIN(pk) |
|
74 |
FROM t1 |
|
75 |
WHERE pk=NULL |
|
76 |
HAVING ('m') IN ( |
|
77 |
SELECT v |
|
78 |
FROM t2); |
|
79 |
||
80 |
--echo
|
|
81 |
EXPLAIN
|
|
82 |
SELECT MIN(pk) |
|
83 |
FROM t1 |
|
84 |
WHERE pk=NULL |
|
85 |
HAVING ('m') IN ( |
|
86 |
SELECT v |
|
87 |
FROM t2); |
|
88 |
||
89 |
--echo
|
|
90 |
--echo #
|
|
91 |
--echo # 4) Test that subquery materialization is setup for query with
|
|
92 |
--echo # premature optimize() exit due to "No matching row in const table"
|
|
93 |
--echo #
|
|
94 |
--echo
|
|
95 |
SELECT MIN(a) |
|
96 |
FROM (SELECT a FROM empty1) tt |
|
97 |
HAVING ('m') IN ( |
|
98 |
SELECT v |
|
99 |
FROM t2); |
|
100 |
||
101 |
--echo
|
|
102 |
EXPLAIN
|
|
103 |
SELECT MIN(a) |
|
104 |
FROM (SELECT a FROM empty1) tt |
|
105 |
HAVING ('m') IN ( |
|
106 |
SELECT v |
|
107 |
FROM t2); |
|
108 |
||
109 |
--echo
|
|
110 |
--echo #
|
|
111 |
--echo # 5) Test that subquery materialization is setup for query with
|
|
112 |
--echo # premature optimize() exit due to "Impossible WHERE noticed
|
|
113 |
--echo # after reading const tables"
|
|
114 |
--echo #
|
|
115 |
SELECT min(t1.pk) |
|
116 |
FROM t1 |
|
117 |
WHERE t1.pk IN (SELECT 1 from t3 where pk>10) |
|
118 |
HAVING ('m') IN ( |
|
119 |
SELECT v |
|
120 |
FROM t2); |
|
121 |
||
122 |
--echo
|
|
123 |
EXPLAIN
|
|
124 |
SELECT min(t1.pk) |
|
125 |
FROM t1 |
|
126 |
WHERE t1.pk IN (SELECT 1 from t3 where pk>10) |
|
127 |
HAVING ('m') IN ( |
|
128 |
SELECT v |
|
129 |
FROM t2); |
|
130 |
||
131 |
--echo #
|
|
132 |
--echo # Cleanup for BUG#46680
|
|
133 |
--echo #
|
|
134 |
DROP TABLE IF EXISTS t1,t2,t3,empty1; |
|
135 |
||
136 |
###
|
|
137 |
--echo End of 6.0 tests
|