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