maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #04603
BUG#939009: Making sj-materialized fields non-nullable doesn't remove the crash
Hello Igor,
I've made a patch that makes temporary table's fields non-nullable. It still
has one part missing, but I have discovered that it will not remove the crash.
I was able to construct an example with inner join and a non-nullable key
field for which best_access_path() will still access rec_per_key[...].
<testcase>
create table t21 (a int NOT NULL, b int, key(a));
# note the NOT NULL key column...
# we can make the key unique:
create table t21a (a int NOT NULL, b int, unique key(a));
insert into t21 select a,a from test.one_k;
explain select * from t21a where a=b and (b=10 or b is null);
</testcase>
When I debug the above EXPLAIN, I get this execution in best_access_path():
if (found_part == PREV_BITS(uint,keyinfo->key_parts) &&
!ref_or_null_part)
//(branch not taken)
if ((found_part & 1) &&
(!(table->file->index_flags(key, 0, 0) & HA_ONLY_WHOLE_INDEX) ||
found_part == PREV_BITS(uint,keyinfo->key_parts)))
{
//(branch taken)
if (table->quick_keys.is_set(key) && !found_ref && //(C1)
table->quick_key_parts[key] == max_key_part && //(C2)
table->quick_n_ranges[key] == 1+test(ref_or_null_part)) //(C3)
{
//(branch not taken)
tmp= records= (double) table->quick_rows[key];
}
else
{
//(we end up here, where the crash is)
/* Check if we have statistic about the distribution */
if ((records= keyinfo->rec_per_key[max_key_part-1]))
Conclusion:
marking temporary table's field as NOT NULL will not cause best_access_path
to avoid accessing the " rec_per_key[max_key_part-1] " part.
BR
Sergei
--
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
=== modified file 'mysql-test/r/subselect.result'
--- mysql-test/r/subselect.result 2012-02-09 21:35:26 +0000
+++ mysql-test/r/subselect.result 2012-02-23 21:23:29 +0000
@@ -3563,7 +3563,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 21 test.t1.a,test.t1.b 1
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 9 Using temporary
ALTER TABLE t1 ADD INDEX(a);
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
@@ -3575,7 +3575,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL a NULL NULL NULL 9 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 21 test.t1.a,test.t1.b 1
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 9 Using temporary
DROP TABLE t1;
create table t1( f1 int,f2 int);
@@ -4471,14 +4471,14 @@ SET @save_join_cache_level=@@join_cache_
SET join_cache_level=0;
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 100.00
+1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary
Warnings:
Note 1003 select 1 AS `1` from <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`min(a)` = 1)
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 100.00
+1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary
Warnings:
=== modified file 'mysql-test/r/subselect_no_scache.result'
--- mysql-test/r/subselect_no_scache.result 2012-02-09 21:35:26 +0000
+++ mysql-test/r/subselect_no_scache.result 2012-02-23 21:24:50 +0000
@@ -3569,7 +3569,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 21 test.t1.a,test.t1.b 1
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 9 Using temporary
ALTER TABLE t1 ADD INDEX(a);
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
@@ -3581,7 +3581,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL a NULL NULL NULL 9 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 21 test.t1.a,test.t1.b 1
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 9 Using temporary
DROP TABLE t1;
create table t1( f1 int,f2 int);
@@ -4477,14 +4477,14 @@ SET @save_join_cache_level=@@join_cache_
SET join_cache_level=0;
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 100.00
+1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary
Warnings:
Note 1003 select 1 AS `1` from <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`min(a)` = 1)
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 100.00
+1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary
Warnings:
=== modified file 'mysql-test/r/subselect_sj2_jcl6.result'
--- mysql-test/r/subselect_sj2_jcl6.result 2012-01-19 19:44:43 +0000
+++ mysql-test/r/subselect_sj2_jcl6.result 2012-02-23 21:21:53 +0000
@@ -999,7 +999,7 @@ EXPLAIN
SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where
-1 PRIMARY <subquery2> hash_ALL distinct_key #hash#distinct_key 5 test.t1.b 1 Using join buffer (flat, BNLH join)
+1 PRIMARY <subquery2> hash_ALL distinct_key #hash#distinct_key 4 test.t1.b 1 Using join buffer (flat, BNLH join)
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 Using temporary
SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a);
a b
=== modified file 'mysql-test/r/subselect_sj_mat.result'
--- mysql-test/r/subselect_sj_mat.result 2012-02-20 11:30:54 +0000
+++ mysql-test/r/subselect_sj_mat.result 2012-02-23 21:25:26 +0000
@@ -83,7 +83,7 @@ explain extended
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1`,min(`test`.`t2`.`b2`) from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where ((`<subquery2>`.`min(b2)` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`))
@@ -106,7 +106,7 @@ explain extended
select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1i index it1i1,it1i3 # 18 # 3 100.00 #
-1 PRIMARY <subquery2> eq_ref distinct_key # 9 # 1 100.00 #
+1 PRIMARY <subquery2> eq_ref distinct_key # 8 # 1 100.00 #
2 MATERIALIZED t2i index it2i1,it2i3 # 9 # 5 100.00 #
Warnings:
Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`<subquery2>`.`max(b1)` = `test`.`t1i`.`a1`)
@@ -153,7 +153,7 @@ explain extended
select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00
2 MATERIALIZED t2i range NULL it2i3 9 NULL 3 100.00 Using index for group-by
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`<subquery2>`.`max(b2)` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`))
@@ -165,12 +165,12 @@ prepare st1 from "explain select * from
execute st1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1
2 MATERIALIZED t2i range NULL it2i3 9 NULL 3 Using index for group-by
execute st1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1
2 MATERIALIZED t2i range NULL it2i3 9 NULL 3 Using index for group-by
prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
execute st2;
@@ -185,7 +185,7 @@ explain extended
select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00
2 MATERIALIZED t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`<subquery2>`.`min(b2)` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`))
@@ -233,7 +233,7 @@ explain extended
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`<subquery2>`.`b2` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`))
@@ -245,7 +245,7 @@ explain extended
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1i index it1i1,it1i2,it1i3 it1i3 18 NULL 3 100.00 Using where; Using index
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1i.a1,test.t1i.a2 1 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1i.a1,test.t1i.a2 1 100.00
2 MATERIALIZED t2i index NULL it2i3 18 NULL 5 100.00 Using index
Warnings:
Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`<subquery2>`.`b2` = `test`.`t1i`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1i`.`a1`))
@@ -675,7 +675,7 @@ from t1_16
where a1 in (select group_concat(b1) from t2_16 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_16.a1 1 100.00 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_16.a1 1 100.00 Using where
2 MATERIALIZED t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_16`.`b1` separator ',') from `test`.`t2_16` group by `test`.`t2_16`.`b2`) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -775,7 +775,7 @@ from t1_512
where a1 in (select group_concat(b1) from t2_512 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_512.a1 1 100.00 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_512.a1 1 100.00 Using where
2 MATERIALIZED t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -789,7 +789,7 @@ from t1_512
where a1 in (select group_concat(b1) from t2_512 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_512.a1 1 100.00 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_512.a1 1 100.00 Using where
2 MATERIALIZED t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -870,7 +870,7 @@ from t1_1024
where a1 in (select group_concat(b1) from t2_1024 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_1024.a1 1 100.00 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1024.a1 1 100.00 Using where
2 MATERIALIZED t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -884,7 +884,7 @@ from t1_1024
where a1 in (select group_concat(b1) from t2_1024 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_1024.a1 1 100.00 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1024.a1 1 100.00 Using where
2 MATERIALIZED t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -965,7 +965,7 @@ from t1_1025
where a1 in (select group_concat(b1) from t2_1025 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_1025.a1 1 100.00 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1025.a1 1 100.00 Using where
2 MATERIALIZED t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -979,7 +979,7 @@ from t1_1025
where a1 in (select group_concat(b1) from t2_1025 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_1025.a1 1 100.00 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1025.a1 1 100.00 Using where
2 MATERIALIZED t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -1187,7 +1187,7 @@ insert into t1 values (5);
explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1
+1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1
2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
min(a1)
@@ -1273,7 +1273,7 @@ GROUP BY t3i
);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 5 const 1
+1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 const 1
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 Using temporary
=== modified file 'sql/item_subselect.cc'
--- sql/item_subselect.cc 2012-02-01 23:48:02 +0000
+++ sql/item_subselect.cc 2012-02-23 19:35:27 +0000
@@ -4180,6 +4180,11 @@ bool subselect_hash_sj_engine::init(List
memcpy(name, buf, len+1);
result_sink->get_tmp_table_param()->materialized_subquery= true;
+ if (item->substype() == Item_subselect::IN_SUBS &&
+ ((Item_in_subselect*)item)->is_jtbm_merged)
+ {
+ result_sink->get_tmp_table_param()->force_not_null_cols= true;
+ }
if (result_sink->create_result_table(thd, tmp_columns, TRUE,
tmp_create_options,
name, TRUE, TRUE))
=== modified file 'sql/sql_class.cc'
--- sql/sql_class.cc 2011-12-11 16:39:33 +0000
+++ sql/sql_class.cc 2012-02-23 15:47:29 +0000
@@ -3236,6 +3236,7 @@ void TMP_TABLE_PARAM::init()
precomputed_group_by= 0;
bit_fields_as_long= 0;
materialized_subquery= 0;
+ force_not_null_cols= FALSE;
skip_create_table= 0;
DBUG_VOID_RETURN;
}
=== modified file 'sql/sql_class.h'
--- sql/sql_class.h 2012-02-22 12:48:29 +0000
+++ sql/sql_class.h 2012-02-23 15:47:13 +0000
@@ -3081,6 +3081,8 @@ public:
bool schema_table;
/* TRUE if the temp table is created for subquery materialization. */
bool materialized_subquery;
+
+ bool force_not_null_cols;
/*
True if GROUP BY and its aggregate functions are already computed
by a table access method (e.g. by loose index scan). In this case
@@ -3104,7 +3106,8 @@ public:
TMP_TABLE_PARAM()
:copy_field(0), group_parts(0),
group_length(0), group_null_parts(0), convert_blob_length(0),
- schema_table(0), materialized_subquery(0), precomputed_group_by(0),
+ schema_table(0), materialized_subquery(0), force_not_null_cols(FALSE),
+ precomputed_group_by(0),
force_copy_fields(0), bit_fields_as_long(0), skip_create_table(0)
{}
~TMP_TABLE_PARAM()
=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc 2012-02-20 11:34:50 +0000
+++ sql/sql_select.cc 2012-02-23 19:28:53 +0000
@@ -13656,6 +13656,11 @@ create_tmp_table(THD *thd, TMP_TABLE_PAR
thd->mem_root= mem_root_save;
arg= sum_item->set_arg(i, thd, new Item_field(new_field));
thd->mem_root= &table->mem_root;
+
+ // psergey:
+ if (param->force_not_null_cols)
+ new_field->flags |= NOT_NULL_FLAG;
+
if (!(new_field->flags & NOT_NULL_FLAG))
{
null_count++;
@@ -13731,6 +13736,11 @@ create_tmp_table(THD *thd, TMP_TABLE_PAR
agg_item->result_field= new_field;
}
tmp_from_field++;
+
+ // psergey:
+ if (param->force_not_null_cols)
+ new_field->flags |= NOT_NULL_FLAG;
+
reclength+=new_field->pack_length();
if (!(new_field->flags & NOT_NULL_FLAG))
null_count++;
