2
# BUG#46680 - Assertion failed in file item_subselect.cc,
3
# line 305 crashing on HAVING subquery
9
v VARCHAR(1) DEFAULT NULL,
12
CREATE TABLE t2 LIKE t1;
13
CREATE TABLE t3 LIKE t1;
14
CREATE TABLE empty1 (a int);
15
INSERT INTO t1 VALUES (1,'c'),(2,NULL);
16
INSERT INTO t2 VALUES (3,'m'),(4,NULL);
17
INSERT INTO t3 VALUES (1,'n');
20
# 1) Test that subquery materialization is setup for query with
21
# premature optimize() exit due to "Impossible WHERE"
24
FROM t2 JOIN t1 ON t1.pk=t2.pk
32
Warning 1292 Truncated incorrect INTEGER value: 'j'
36
FROM t2 JOIN t1 ON t1.pk=t2.pk
41
id select_type table type possible_keys key key_len ref rows Extra
42
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
43
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
45
Warning 1292 Truncated incorrect INTEGER value: 'j'
48
# 2) Test that subquery materialization is setup for query with
49
# premature optimize() exit due to "No matching min/max row"
67
id select_type table type possible_keys key key_len ref rows Extra
68
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No matching min/max row
69
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
72
# 3) Test that subquery materialization is setup for query with
73
# premature optimize() exit due to "Select tables optimized away"
75
# NOTE: The result of this query is actually wrong; it should be NULL
76
# See BUG#47762. Even so, the test case is still needed to test
77
# that the HAVING subquery does not crash the server
95
id select_type table type possible_keys key key_len ref rows Extra
96
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
97
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
100
# 4) Test that subquery materialization is setup for query with
101
# premature optimize() exit due to "No matching row in const table"
105
FROM (SELECT a FROM empty1) tt
114
FROM (SELECT a FROM empty1) tt
118
id select_type table type possible_keys key key_len ref rows Extra
119
1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
120
3 SUBQUERY t2 ALL NULL NULL NULL NULL 2
121
2 DERIVED empty1 ALL NULL NULL NULL NULL 1
124
# 5) Test that subquery materialization is setup for query with
125
# premature optimize() exit due to "Impossible WHERE noticed
126
# after reading const tables"
130
WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
140
WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
144
id select_type table type possible_keys key key_len ref rows Extra
145
1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using where; Using index
146
3 SUBQUERY t2 ALL NULL NULL NULL NULL 2
147
2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 1 Using where; Using index
149
# Cleanup for BUG#46680
151
DROP TABLE IF EXISTS t1,t2,t3,empty1;