[Commits] 2b4479b: MDEV-16930 Crash when VALUES in derived table contains expressions
by IgorBabaev 13 Aug '18
by IgorBabaev 13 Aug '18
13 Aug '18
revision-id: 2b4479bca822827c6c8cc85f45c11b36ae9aff4a (mariadb-10.3.7-109-g2b4479b)
parent(s): d453374fc480112266996a1026b97654cc174c09
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-08-12 23:11:14 -0700
message:
MDEV-16930 Crash when VALUES in derived table contains expressions
This patch provides columns of the temporary table used for
materialization of a table value constructor with comprehensive names.
Before this patch these names were always borrowed from the items
of the first row of the table value constructor. When this row
contained expressions and expressions were not named then it could cause
different kinds of problems. In particular if the TVC is used as the
specification of a derived table this could cause a crash.
---
mysql-test/main/table_value_constr.result | 17 ++++++++++++++++
mysql-test/main/table_value_constr.test | 11 ++++++++++
sql/sql_tvc.cc | 34 ++++++++++++++++++++++++++++---
3 files changed, 59 insertions(+), 3 deletions(-)
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index 9e0a096..6eacc57 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -2097,3 +2097,20 @@ v
#
with t as (values (),()) select 1 from t;
ERROR HY000: Row with no elements is not allowed in table value constructor in this context
+#
+# MDEV-16930: expression in the first row of TVC specifying derived table
+#
+VALUES(1+1,2);
+exp_1 2
+2 2
+SELECT * FROM (VALUES(1+1,2)) t;
+exp_1 2
+2 2
+PREPARE stmt FROM "SELECT * FROM (VALUES(1+1,2)) t";
+EXECUTE stmt;
+exp_1 2
+2 2
+EXECUTE stmt;
+exp_1 2
+2 2
+DEALLOCATE PREPARE stmt;
diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index eb5ea59..12bae99 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1075,3 +1075,14 @@ DELIMITER ;|
--error ER_EMPTY_ROW_IN_TVC
with t as (values (),()) select 1 from t;
+
+--echo #
+--echo # MDEV-16930: expression in the first row of TVC specifying derived table
+--echo #
+
+VALUES(1+1,2);
+SELECT * FROM (VALUES(1+1,2)) t;
+PREPARE stmt FROM "SELECT * FROM (VALUES(1+1,2)) t";
+EXECUTE stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index 188ba8c..a184487 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -237,10 +237,27 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl,
get_type_attributes_for_tvc(thd, li, holders,
lists_of_values.elements, cnt))
DBUG_RETURN(true);
-
+
List_iterator_fast<Item> it(*first_elem);
Item *item;
-
+
+ /*
+ Temporarily set comprehensive names for those items in the first_elem list
+ that have no name. Each item from the first_elem list must have a name as
+ this name will be used as the corresponding column name of the temporary table
+ that is created for the table value constructor.
+ */
+ size_t name_len;
+ char buff[NAME_LEN];
+ for (uint column_no= 1; (item= it++); column_no++)
+ {
+ if ((item->orig_name= item->name.str))
+ continue;
+ name_len= my_snprintf(buff, NAME_LEN, "exp_%u", column_no);
+ item->set_name(thd, buff, name_len, system_charset_info);
+ }
+
+ it.rewind();
sl->item_list.empty();
for (uint pos= 0; (item= it++); pos++)
{
@@ -254,7 +271,18 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl,
new_holder->fix_fields(thd, 0);
sl->item_list.push_back(new_holder);
}
-
+
+ /*
+ Restore the original names of the items in the first_elem list.
+ This is needed for execution of prepared statements.
+ */
+ it.rewind();
+ while((item= it++))
+ {
+ if (!item->orig_name)
+ item->name= null_clex_str;
+ }
+
if (unlikely(thd->is_fatal_error))
DBUG_RETURN(true); // out of memory
1
0
[Commits] ba10ffe: MDEV-16203: autoinc_debug of rocksdb test suite fails
by psergeyļ¼ askmonty.org 12 Aug '18
by psergeyļ¼ askmonty.org 12 Aug '18
12 Aug '18
revision-id: ba10ffe0f4c94c0fd2b1e42615a0c8d1a7f88ab9
parent(s): 9dd3e5ea3c7392562b75a40c7fb90b6750308b3a
committer: Sergei Petrunia
branch nick: 10.2-r11
timestamp: 2018-08-12 22:10:32 +0300
message:
MDEV-16203: autoinc_debug of rocksdb test suite fails
The test causes simulated server crashes with DBUG_SUICIDE();.
It also relies on transactions that were committed right before the
crash to be visible after the crash (that is, it requires durability).
Run the test with transaction durability enabled: set
rocksdb-flush-log-at-trx-commit=1.
---
storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug-master.opt | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug-master.opt b/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug-master.opt
index 83ed852..0691718 100644
--- a/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug-master.opt
+++ b/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug-master.opt
@@ -1 +1 @@
---binlog-format=row
+--binlog-format=row --rocksdb-flush-log-at-trx-commit=1
1
0
[Commits] 2261387: MDEV-16930 Crash when VALUES in derived table contains expressions
by IgorBabaev 11 Aug '18
by IgorBabaev 11 Aug '18
11 Aug '18
revision-id: 226138774a1befa622e5e1dcaf9749b5cda43db4 (mariadb-10.3.7-109-g2261387)
parent(s): d453374fc480112266996a1026b97654cc174c09
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-08-11 16:21:08 -0700
message:
MDEV-16930 Crash when VALUES in derived table contains expressions
This patch provides columns of the temporary table used for
materialization of a table value constructors with comprehensive names.
Before this patch these names were borrowed from the items of
the first row of the table value constructor. It caused different
problems when a TVC was used as the specification of a derived table:
some columns of the derived table has the same name, some columns did not
get any name and this triggered a crash.
---
mysql-test/main/opt_tvc.result | 46 +++----
mysql-test/main/sp-bugs.result | 2 +-
mysql-test/main/table_value_constr.result | 216 ++++++++++++++++--------------
mysql-test/main/table_value_constr.test | 8 ++
sql/sql_tvc.cc | 33 ++++-
5 files changed, 175 insertions(+), 130 deletions(-)
diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result
index 0ecae5b..37c76d1 100644
--- a/mysql-test/main/opt_tvc.result
+++ b/mysql-test/main/opt_tvc.result
@@ -51,7 +51,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1`
explain extended select * from t1
where a in
(
@@ -64,7 +64,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1`
# AND-condition with IN-predicates in WHERE-part
select * from t1
where a in (1,2) and
@@ -98,7 +98,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`col_1`
explain extended select * from t1
where a in
(
@@ -119,7 +119,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`col_1`
# subquery with IN-predicate
select * from t1
where a in
@@ -154,7 +154,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join)
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`col_1`
explain extended select * from t1
where a in
(
@@ -172,7 +172,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join)
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`col_1`
# derived table with IN-predicate
select * from
(
@@ -211,7 +211,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1`
explain extended select * from
(
select *
@@ -229,7 +229,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1`
# non-recursive CTE with IN-predicate
with tvc_0 as
(
@@ -270,7 +270,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1`
explain extended select * from
(
select *
@@ -288,7 +288,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1`
# VIEW with IN-predicate
create view v1 as
select *
@@ -321,7 +321,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1`
explain extended select * from v2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00
@@ -329,7 +329,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1`
drop view v1,v2;
# subselect defined by derived table with IN-predicate
select * from t1
@@ -386,7 +386,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join)
5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`col_1`
explain extended select * from t1
where a in
(
@@ -411,7 +411,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join)
5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`col_1`
# derived table with IN-predicate and group by
select * from
(
@@ -509,11 +509,11 @@ a b
explain extended select * from t3 where a in (1,4,10);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00
-1 PRIMARY t3 ref idx idx 5 tvc_0.1 3 100.00
+1 PRIMARY t3 ref idx idx 5 tvc_0.col_1 3 100.00
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4),(10)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1`
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4),(10)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`col_1`
# use vectors in IN predeicate
set @@in_predicate_conversion_threshold= 4;
select * from t1 where (a,b) in ((1,2),(3,4));
@@ -526,7 +526,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` and `test`.`t1`.`b` = `tvc_0`.`2`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` and `test`.`t1`.`b` = `tvc_0`.`col_2`
set @@in_predicate_conversion_threshold= 2;
# trasformation works for the one IN predicate and doesn't work for the other
set @@in_predicate_conversion_threshold= 5;
@@ -545,7 +545,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`1` and `test`.`t2`.`c` = `tvc_0`.`3` and (`tvc_0`.`1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9)))
+Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`col_1` and `test`.`t2`.`c` = `tvc_0`.`col_2` and (`tvc_0`.`col_1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9)))
set @@in_predicate_conversion_threshold= 2;
#
# mdev-14281: conversion of NOT IN predicate into subquery predicate
@@ -573,7 +573,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`col_1`,`tvc_0`.`col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`col_1` and `test`.`t1`.`b` = `<subquery2>`.`col_2`))))
explain extended select * from t1
where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0);
id select_type table type possible_keys key key_len ref rows filtered Extra
@@ -581,7 +581,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`col_1`,`tvc_0`.`col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`col_1` and `test`.`t1`.`b` = `<subquery2>`.`col_2`))))
select * from t1
where b < 7 and (a,b) not in ((1,2),(8,9), (5,1));
a b
@@ -595,7 +595,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`col_1`,`tvc_0`.`col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`col_1` and `test`.`t1`.`b` = `<subquery2>`.`col_2`))))
select * from t2
where (a,c) not in ((1,2),(8,9), (5,1));
a b c
@@ -611,7 +611,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),(`test`.`t2`.`a`,`test`.`t2`.`c`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`1` and `test`.`t2`.`c` = `<subquery2>`.`2`))))
+Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),(`test`.`t2`.`a`,`test`.`t2`.`c`) in ( <materialize> (/* select#2 */ select `tvc_0`.`col_1`,`tvc_0`.`col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`col_1` and `test`.`t2`.`c` = `<subquery2>`.`col_2`))))
drop table t1, t2, t3;
set @@in_predicate_conversion_threshold= default;
#
@@ -635,7 +635,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`NULL`
+Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`col_1`
SET in_predicate_conversion_threshold= default;
DROP TABLE t1;
#
diff --git a/mysql-test/main/sp-bugs.result b/mysql-test/main/sp-bugs.result
index a699cd1..aaaf4f6 100644
--- a/mysql-test/main/sp-bugs.result
+++ b/mysql-test/main/sp-bugs.result
@@ -322,7 +322,7 @@ DROP PROCEDURE p1;
CREATE PROCEDURE p1() VALUES (1);
$$
CALL p1;
-1
+col_1
1
SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1';
body
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index 9e0a096..4023c27 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -3,15 +3,15 @@ insert into t1 values (1,2),(4,6),(9,7),
(1,1),(2,5),(7,8);
# just VALUES
values (1,2);
-1 2
+col_1 col_2
1 2
values (1,2), (3,4), (5.6,0);
-1 2
+col_1 col_2
1.0 2
3.0 4
5.6 0
values ("abc", "def");
-abc def
+col_1 col_2
abc def
# UNION that uses VALUES structure(s)
select 1,2
@@ -22,7 +22,7 @@ values (1,2);
values (1,2)
union
select 1,2;
-1 2
+col_1 col_2
1 2
select 1,2
union
@@ -70,14 +70,14 @@ values (1,2),(3,6);
values (1,2.4),(3,6)
union
select 2.8,9;
-1 2.4
+col_1 col_2
1.0 2.4
3.0 6.0
2.8 9.0
values (1,2),(3,4),(5,6),(7,8)
union
select 5,6;
-1 2
+col_1 col_2
1 2
3 4
5 6
@@ -92,18 +92,18 @@ we q
values ("ab", "cdf")
union
select "ab","cdf";
-ab cdf
+col_1 col_2
ab cdf
values (1,2)
union
values (1,2),(5,6);
-1 2
+col_1 col_2
1 2
5 6
values (1,2)
union
values (3,4),(5,6);
-1 2
+col_1 col_2
1 2
3 4
5 6
@@ -111,21 +111,21 @@ values (1,2)
union
values (1,2)
union values (4,5);
-1 2
+col_1 col_2
1 2
4 5
# UNION ALL that uses VALUES structure
values (1,2),(3,4)
union all
select 5,6;
-1 2
+col_1 col_2
1 2
3 4
5 6
values (1,2),(3,4)
union all
select 1,2;
-1 2
+col_1 col_2
1 2
3 4
1 2
@@ -146,14 +146,14 @@ values (1,2),(3,4);
values (1,2)
union all
values (1,2),(5,6);
-1 2
+col_1 col_2
1 2
1 2
5 6
values (1,2)
union all
values (3,4),(5,6);
-1 2
+col_1 col_2
1 2
3 4
5 6
@@ -162,7 +162,7 @@ union all
values (1,2)
union all
values (4,5);
-1 2
+col_1 col_2
1 2
1 2
4 5
@@ -170,14 +170,14 @@ values (1,2)
union all
values (1,2)
union values (1,2);
-1 2
+col_1 col_2
1 2
values (1,2)
union
values (1,2)
union all
values (1,2);
-1 2
+col_1 col_2
1 2
1 2
# EXCEPT that uses VALUES structure(s)
@@ -193,24 +193,24 @@ values (1,2),(3,4);
values (1,2),(3,4)
except
select 5,6;
-1 2
+col_1 col_2
1 2
3 4
values (1,2),(3,4)
except
select 1,2;
-1 2
+col_1 col_2
3 4
values (1,2),(3,4)
except
values (5,6);
-1 2
+col_1 col_2
1 2
3 4
values (1,2),(3,4)
except
values (1,2);
-1 2
+col_1 col_2
3 4
# INTERSECT that uses VALUES structure(s)
select 1,2
@@ -225,27 +225,27 @@ values (1,2),(3,4);
values (1,2),(3,4)
intersect
select 5,6;
-1 2
+col_1 col_2
values (1,2),(3,4)
intersect
select 1,2;
-1 2
+col_1 col_2
1 2
values (1,2),(3,4)
intersect
values (5,6);
-1 2
+col_1 col_2
values (1,2),(3,4)
intersect
values (1,2);
-1 2
+col_1 col_2
1 2
# combination of different structures that uses VALUES structures : UNION + EXCEPT
values (1,2),(3,4)
except
select 1,2
union values (1,2);
-1 2
+col_1 col_2
1 2
3 4
values (1,2),(3,4)
@@ -253,7 +253,7 @@ except
values (1,2)
union
values (1,2);
-1 2
+col_1 col_2
1 2
3 4
values (1,2),(3,4)
@@ -261,14 +261,14 @@ except
values (1,2)
union
values (3,4);
-1 2
+col_1 col_2
3 4
values (1,2),(3,4)
union
values (1,2)
except
values (1,2);
-1 2
+col_1 col_2
3 4
# combination of different structures that uses VALUES structures : UNION ALL + EXCEPT
values (1,2),(3,4)
@@ -276,7 +276,7 @@ except
select 1,2
union all
values (1,2);
-1 2
+col_1 col_2
1 2
3 4
values (1,2),(3,4)
@@ -284,7 +284,7 @@ except
values (1,2)
union all
values (1,2);
-1 2
+col_1 col_2
1 2
3 4
values (1,2),(3,4)
@@ -292,7 +292,7 @@ except
values (1,2)
union all
values (3,4);
-1 2
+col_1 col_2
3 4
3 4
values (1,2),(3,4)
@@ -300,7 +300,7 @@ union all
values (1,2)
except
values (1,2);
-1 2
+col_1 col_2
3 4
# combination of different structures that uses VALUES structures : UNION + INTERSECT
values (1,2),(3,4)
@@ -308,21 +308,21 @@ intersect
select 1,2
union
values (1,2);
-1 2
+col_1 col_2
1 2
values (1,2),(3,4)
intersect
values (1,2)
union
values (1,2);
-1 2
+col_1 col_2
1 2
values (1,2),(3,4)
intersect
values (1,2)
union
values (3,4);
-1 2
+col_1 col_2
1 2
3 4
values (1,2),(3,4)
@@ -330,7 +330,7 @@ union
values (1,2)
intersect
values (1,2);
-1 2
+col_1 col_2
1 2
3 4
# combination of different structures that uses VALUES structures : UNION ALL + INTERSECT
@@ -339,7 +339,7 @@ intersect
select 1,2
union all
values (1,2);
-1 2
+col_1 col_2
1 2
1 2
values (1,2),(3,4)
@@ -347,7 +347,7 @@ intersect
values (1,2)
union all
values (1,2);
-1 2
+col_1 col_2
1 2
1 2
values (1,2),(3,4)
@@ -355,7 +355,7 @@ intersect
values (1,2)
union all
values (3,4);
-1 2
+col_1 col_2
1 2
3 4
values (1,2),(3,4)
@@ -363,7 +363,7 @@ union all
values (1,2)
intersect
values (1,2);
-1 2
+col_1 col_2
1 2
3 4
1 2
@@ -373,7 +373,7 @@ union all
select 1,2
union
values (1,2);
-1 2
+col_1 col_2
1 2
3 4
values (1,2),(3,4)
@@ -381,7 +381,7 @@ union all
values (1,2)
union
values (1,2);
-1 2
+col_1 col_2
1 2
3 4
values (1,2),(3,4)
@@ -389,7 +389,7 @@ union all
values (1,2)
union
values (3,4);
-1 2
+col_1 col_2
1 2
3 4
values (1,2),(3,4)
@@ -397,7 +397,7 @@ union
values (1,2)
union all
values (1,2);
-1 2
+col_1 col_2
1 2
3 4
1 2
@@ -406,7 +406,7 @@ union
values (1,2)
union all
values (1,2);
-1 2
+col_1 col_2
1 2
1 2
# CTE that uses VALUES structure(s) : non-recursive CTE
@@ -415,7 +415,7 @@ with t2 as
values (1,2),(3,4)
)
select * from t2;
-1 2
+col_1 col_2
1 2
3 4
with t2 as
@@ -444,7 +444,7 @@ union
select 1,2
)
select * from t2;
-1 2
+col_1 col_2
1 2
with t2 as
(
@@ -453,7 +453,7 @@ union
select 1,2
)
select * from t2;
-1 2
+col_1 col_2
1 2
3 4
with t2 as
@@ -463,7 +463,7 @@ union
values (1,2),(3,4)
)
select * from t2;
-5 6
+col_1 col_2
5 6
1 2
3 4
@@ -474,7 +474,7 @@ union
values (1,2),(3,4)
)
select * from t2;
-1 2
+col_1 col_2
1 2
3 4
with t2 as
@@ -495,7 +495,7 @@ union all
select 1,2
)
select * from t2;
-1 2
+col_1 col_2
1 2
3 4
1 2
@@ -506,7 +506,7 @@ union all
values (1,2),(3,4)
)
select * from t2;
-1 2
+col_1 col_2
1 2
1 2
3 4
@@ -594,7 +594,7 @@ n f
10 362880
# Derived table that uses VALUES structure(s) : singe VALUES structure
select * from (values (1,2),(3,4)) as t2;
-1 2
+col_1 col_2
1 2
3 4
# Derived table that uses VALUES structure(s) : UNION with VALUES structure(s)
@@ -606,19 +606,19 @@ select * from (select 1,2 union values (1,2),(3,4)) as t2;
1 2
3 4
select * from (values (1,2) union select 1,2) as t2;
-1 2
+col_1 col_2
1 2
select * from (values (1,2),(3,4) union select 1,2) as t2;
-1 2
+col_1 col_2
1 2
3 4
select * from (values (5,6) union values (1,2),(3,4)) as t2;
-5 6
+col_1 col_2
5 6
1 2
3 4
select * from (values (1,2) union values (1,2),(3,4)) as t2;
-1 2
+col_1 col_2
1 2
3 4
# Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s)
@@ -628,19 +628,19 @@ select * from (select 1,2 union all values (1,2),(3,4)) as t2;
1 2
3 4
select * from (values (1,2),(3,4) union all select 1,2) as t2;
-1 2
+col_1 col_2
1 2
3 4
1 2
select * from (values (1,2) union all values (1,2),(3,4)) as t2;
-1 2
+col_1 col_2
1 2
1 2
3 4
# CREATE VIEW that uses VALUES structure(s) : singe VALUES structure
create view v1 as values (1,2),(3,4);
select * from v1;
-1 2
+col_1 col_2
1 2
3 4
drop view v1;
@@ -667,7 +667,7 @@ values (1,2)
union
select 1,2;
select * from v1;
-1 2
+col_1 col_2
1 2
drop view v1;
create view v1 as
@@ -675,7 +675,7 @@ values (1,2),(3,4)
union
select 1,2;
select * from v1;
-1 2
+col_1 col_2
1 2
3 4
drop view v1;
@@ -684,7 +684,7 @@ values (5,6)
union
values (1,2),(3,4);
select * from v1;
-5 6
+col_1 col_2
5 6
1 2
3 4
@@ -695,7 +695,7 @@ values (1,2)
union
values (1,2),(3,4);
select * from v1;
-1 2
+col_1 col_2
1 2
3 4
drop view v1;
@@ -714,7 +714,7 @@ values (1,2),(3,4)
union all
select 1,2;
select * from v1;
-1 2
+col_1 col_2
1 2
3 4
1 2
@@ -724,7 +724,7 @@ values (1,2)
union all
values (1,2),(3,4);
select * from v1;
-1 2
+col_1 col_2
1 2
1 2
3 4
@@ -748,7 +748,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1`
explain extended select * from t1
where a in (select * from (values (1)) as tvc_0);
id select_type table type possible_keys key key_len ref rows filtered Extra
@@ -757,7 +757,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1`
# IN-subquery with VALUES structure(s) : UNION with VALUES on the first place
select * from t1
where a in (values (1) union select 2);
@@ -781,7 +781,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))
explain extended select * from t1
where a in (select * from (values (1)) as tvc_0 union
select 2);
@@ -792,7 +792,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))
# IN-subquery with VALUES structure(s) : UNION with VALUES on the second place
select * from t1
where a in (select 2 union values (1));
@@ -816,7 +816,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`)))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`)))
explain extended select * from t1
where a in (select 2 union
select * from (values (1)) tvc_0);
@@ -827,7 +827,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`)))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`)))
# IN-subquery with VALUES structure(s) : UNION ALL
select * from t1
where a in (values (1) union all select b from t1);
@@ -852,7 +852,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
3 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 6 100.00 Using where
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union all /* select#3 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`)))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union all /* select#3 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`)))
explain extended select * from t1
where a in (select * from (values (1)) as tvc_0 union all
select b from t1);
@@ -862,7 +862,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
4 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 6 100.00 Using where
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union all /* select#4 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`)))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union all /* select#4 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`)))
# NOT IN subquery with VALUES structure(s) : simple case
select * from t1
where a not in (values (1),(2));
@@ -883,7 +883,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`col_1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`col_1`))))
explain extended select * from t1
where a not in (select * from (values (1),(2)) as tvc_0);
id select_type table type possible_keys key key_len ref rows filtered Extra
@@ -891,7 +891,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1`))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `tvc_0`.`col_1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`col_1`))))
# NOT IN subquery with VALUES structure(s) : UNION with VALUES on the first place
select * from t1
where a not in (values (1) union select 2);
@@ -915,7 +915,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) union /* select#3 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`) union /* select#3 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))))
explain extended select * from t1
where a not in (select * from (values (1)) as tvc_0 union
select 2);
@@ -926,7 +926,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) union /* select#4 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`) union /* select#4 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))))
# NOT IN subquery with VALUES structure(s) : UNION with VALUES on the second place
select * from t1
where a not in (select 2 union values (1));
@@ -950,7 +950,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))))
explain extended select * from t1
where a not in (select 2 union
select * from (values (1)) as tvc_0);
@@ -961,7 +961,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))))
# ANY-subquery with VALUES structure(s) : simple case
select * from t1
where a = any (values (1),(2));
@@ -983,7 +983,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1`
explain extended select * from t1
where a = any (select * from (values (1),(2)) as tvc_0);
id select_type table type possible_keys key key_len ref rows filtered Extra
@@ -992,7 +992,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1`
# ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place
select * from t1
where a = any (values (1) union select 2);
@@ -1016,7 +1016,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))
explain extended select * from t1
where a = any (select * from (values (1)) as tvc_0 union
select 2);
@@ -1027,7 +1027,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))
# ANY-subquery with VALUES structure(s) : UNION with VALUES on the second place
select * from t1
where a = any (select 2 union values (1));
@@ -1051,7 +1051,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`)))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`)))
explain extended select * from t1
where a = any (select 2 union
select * from (values (1)) as tvc_0);
@@ -1062,7 +1062,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`)))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`)))
# ALL-subquery with VALUES structure(s) : simple case
select * from t1
where a = all (values (1));
@@ -1081,7 +1081,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`)))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`col_1`)))))
explain extended select * from t1
where a = all (select * from (values (1)) as tvc_0);
id select_type table type possible_keys key key_len ref rows filtered Extra
@@ -1089,7 +1089,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`)))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`col_1`)))))
# ALL-subquery with VALUES structure(s) : UNION with VALUES on the first place
select * from t1
where a = all (values (1) union select 1);
@@ -1111,7 +1111,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`) union /* select#3 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1))))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`col_1`) union /* select#3 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1))))))
explain extended select * from t1
where a = all (select * from (values (1)) as tvc_0 union
select 1);
@@ -1122,7 +1122,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`) union /* select#4 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1))))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`col_1`) union /* select#4 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1))))))
# ALL-subquery with VALUES structure(s) : UNION with VALUES on the second place
select * from t1
where a = any (select 1 union values (1));
@@ -1144,7 +1144,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`)))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`)))
explain extended select * from t1
where a = any (select 1 union
select * from (values (1)) as tvc_0);
@@ -1155,16 +1155,16 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`)))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`)))
# prepare statement that uses VALUES structure(s): single VALUES structure
prepare stmt1 from "
values (1,2);
";
execute stmt1;
-1 2
+col_1 col_2
1 2
execute stmt1;
-1 2
+col_1 col_2
1 2
deallocate prepare stmt1;
# prepare statement that uses VALUES structure(s): UNION with VALUES structure(s)
@@ -1188,11 +1188,11 @@ prepare stmt1 from "
select 1,2;
";
execute stmt1;
-1 2
+col_1 col_2
1 2
3 4
execute stmt1;
-1 2
+col_1 col_2
1 2
3 4
deallocate prepare stmt1;
@@ -1218,12 +1218,12 @@ prepare stmt1 from "
values (1,2),(3,4);
";
execute stmt1;
-5 6
+col_1 col_2
5 6
1 2
3 4
execute stmt1;
-5 6
+col_1 col_2
5 6
1 2
3 4
@@ -1249,12 +1249,12 @@ prepare stmt1 from "
select 1,2;
";
execute stmt1;
-1 2
+col_1 col_2
1 2
3 4
1 2
execute stmt1;
-1 2
+col_1 col_2
1 2
3 4
1 2
@@ -1283,12 +1283,12 @@ prepare stmt1 from "
values (1,2),(3,4);
";
execute stmt1;
-1 2
+col_1 col_2
1 2
1 2
3 4
execute stmt1;
-1 2
+col_1 col_2
1 2
1 2
3 4
@@ -2097,3 +2097,13 @@ v
#
with t as (values (),()) select 1 from t;
ERROR HY000: Row with no elements is not allowed in table value constructor in this context
+#
+# MDEV-16930: expression in the first row of TVC specifying derived table
+# the same constant/expressions in the first row
+#
+SELECT * FROM (VALUES(1+1,2)) t;
+col_1 col_2
+2 2
+SELECT * FROM (VALUES(2,2)) t;
+col_1 col_2
+2 2
diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index eb5ea59..73d0195 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1075,3 +1075,11 @@ DELIMITER ;|
--error ER_EMPTY_ROW_IN_TVC
with t as (values (),()) select 1 from t;
+
+--echo #
+--echo # MDEV-16930: expression in the first row of TVC specifying derived table
+--echo # the same constant/expressions in the first row
+--echo #
+
+SELECT * FROM (VALUES(1+1,2)) t;
+SELECT * FROM (VALUES(2,2)) t;
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index 188ba8c..5c81228 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -237,10 +237,25 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl,
get_type_attributes_for_tvc(thd, li, holders,
lists_of_values.elements, cnt))
DBUG_RETURN(true);
-
+
List_iterator_fast<Item> it(*first_elem);
Item *item;
-
+
+ /*
+ Temporarily set comprehensive names for the items in the first_elem list
+ These names will be used as the column names of the temporary table
+ that is created for the table value constructor.
+ */
+ size_t name_len;
+ char buff[NAME_LEN];
+ for (uint column_no= 1; (item= it++); column_no++)
+ {
+ name_len= my_snprintf(buff, NAME_LEN, "col_%u", column_no);
+ item->orig_name= item->name.str;
+ item->set_name(thd, buff, name_len, system_charset_info);
+ }
+
+ it.rewind();
sl->item_list.empty();
for (uint pos= 0; (item= it++); pos++)
{
@@ -254,7 +269,19 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl,
new_holder->fix_fields(thd, 0);
sl->item_list.push_back(new_holder);
}
-
+
+ /*
+ Restore the original names of the items in the first_elem list. This is
+ needed for the proper print of the table value constructor.
+ */
+ it.rewind();
+ while((item= it++))
+ {
+ item->set_name(thd, item->orig_name,
+ item->orig_name ? strlen(item->orig_name) : 0,
+ system_charset_info);
+ }
+
if (unlikely(thd->is_fatal_error))
DBUG_RETURN(true); // out of memory
1
0
[Commits] 0d99049dbcd: MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions
by Varun 10 Aug '18
by Varun 10 Aug '18
10 Aug '18
revision-id: 0d99049dbcd7cc5e3dd309dd7e3df627863fcb81 (mariadb-10.3.7-115-g0d99049dbcd)
parent(s): 340c8a2a32dcbe0bb9bc88bd0a6bda5d5e76ed02
author: Varun Gupta
committer: Varun Gupta
timestamp: 2018-08-11 02:31:28 +0530
message:
MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions
Aggregate function has:
- initialization code
- code that is run for every row
- code that generates a result
For an empty table we don't run the middle part but for custom aggregate we are running
the middle part also.
Fixed this by only allowing the generation of result. As soon as we encounter the first FETCH GROUP NEXT ROW
instruction with empty tables, we should exit and tell the handler that there is nothing to fetch and return
the result.
---
mysql-test/main/custom_aggregate_functions.result | 44 ++++++++++++++++++++---
mysql-test/main/custom_aggregate_functions.test | 43 ++++++++++++++++++++++
sql/sp_head.cc | 41 ++++++++++++++++-----
3 files changed, 115 insertions(+), 13 deletions(-)
diff --git a/mysql-test/main/custom_aggregate_functions.result b/mysql-test/main/custom_aggregate_functions.result
index 4060d6665f6..e85145c040c 100644
--- a/mysql-test/main/custom_aggregate_functions.result
+++ b/mysql-test/main/custom_aggregate_functions.result
@@ -84,8 +84,7 @@ return (select count(*) + f2( i - 1) from t1 where id = i);
end if;
end|
select f2(1)|
-f2(1)
-3
+ERROR 02000: No data - zero rows fetched, selected, or processed
select f2(2)|
ERROR HY000: Recursive stored functions and triggers are not allowed
select f2(3)|
@@ -109,7 +108,7 @@ f1(sal)
6000
select f1(sal) from t1 where 1=0;
f1(sal)
-NULL
+0
drop function f1;
create aggregate function f1(x int) returns int
begin
@@ -464,7 +463,7 @@ f1(sal)
set @param= 5;
execute test using @param;
f1(sal)
-NULL
+0
deallocate prepare test;
drop function f2;
prepare test from "select f1(sal) from t1 where id>= ?";
@@ -1153,3 +1152,40 @@ i sum(i)
NULL 8
drop function agg_sum;
drop table t1;
+#
+# MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions
+#
+CREATE AGGREGATE FUNCTION test1(p_value TEXT)
+RETURNS BOOL
+BEGIN
+DECLARE CONTINUE HANDLER
+FOR NOT FOUND
+BEGIN
+RETURN FALSE;
+END;
+FETCH GROUP NEXT ROW;
+RETURN TRUE;
+END|
+CREATE OR REPLACE TABLE t1 (n TEXT);
+SELECT test1(n) FROM t1;
+test1(n)
+0
+CREATE OR REPLACE AGGREGATE FUNCTION test2(p_value TEXT)
+RETURNS BOOL
+BEGIN
+DECLARE CONTINUE HANDLER
+FOR NOT FOUND
+BEGIN
+RETURN FALSE;
+END;
+FETCH GROUP NEXT ROW;
+FETCH GROUP NEXT ROW;
+FETCH GROUP NEXT ROW;
+RETURN TRUE;
+END|
+SELECT test2(n) FROM t1;
+test2(n)
+0
+drop function test1;
+drop function test2;
+drop table t1;
diff --git a/mysql-test/main/custom_aggregate_functions.test b/mysql-test/main/custom_aggregate_functions.test
index ab799b48bdb..2c981ac518d 100644
--- a/mysql-test/main/custom_aggregate_functions.test
+++ b/mysql-test/main/custom_aggregate_functions.test
@@ -69,6 +69,7 @@ begin
return (select count(*) + f2( i - 1) from t1 where id = i);
end if;
end|
+--error 1329
select f2(1)|
# Since currently recursive functions are disallowed ER_SP_NO_RECURSION
# error will be returned, once we will allow them error about
@@ -965,3 +966,45 @@ select i, sum(i) from t1 group by i with rollup;
# Cleanup
drop function agg_sum;
drop table t1;
+
+--echo #
+--echo # MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions
+--echo #
+
+delimiter |;
+CREATE AGGREGATE FUNCTION test1(p_value TEXT)
+ RETURNS BOOL
+BEGIN
+ DECLARE CONTINUE HANDLER
+ FOR NOT FOUND
+ BEGIN
+ RETURN FALSE;
+ END;
+ FETCH GROUP NEXT ROW;
+ RETURN TRUE;
+END|
+
+delimiter ;|
+CREATE OR REPLACE TABLE t1 (n TEXT);
+SELECT test1(n) FROM t1;
+
+delimiter |;
+CREATE OR REPLACE AGGREGATE FUNCTION test2(p_value TEXT)
+ RETURNS BOOL
+ BEGIN
+ DECLARE CONTINUE HANDLER
+ FOR NOT FOUND
+ BEGIN
+ RETURN FALSE;
+ END;
+ FETCH GROUP NEXT ROW;
+ FETCH GROUP NEXT ROW;
+ FETCH GROUP NEXT ROW;
+ RETURN TRUE;
+ END|
+
+delimiter ;|
+SELECT test2(n) FROM t1;
+drop function test1;
+drop function test2;
+drop table t1;
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index c1c938dd9e7..3828cb5237b 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -4406,6 +4406,37 @@ sp_instr_agg_cfetch::execute(THD *thd, uint *nextp)
{
DBUG_ENTER("sp_instr_agg_cfetch::execute");
int res= 0;
+ if (thd->server_status == SERVER_STATUS_LAST_ROW_SENT)
+ {
+ if (!thd->spcont->quit_func)
+ {
+ my_message(ER_SP_FETCH_NO_DATA,
+ ER_THD(thd, ER_SP_FETCH_NO_DATA), MYF(0));
+ thd->spcont->quit_func= TRUE;
+ thd->spcont->pause_state= FALSE;
+ }
+ else
+ {
+ /*
+ required when we don't come across the return statement.
+ An example would be
+ create aggregate function f1(x int) returns int
+ begin
+ declare mini int default 0;
+ declare continue handler for not found set mini=-1;
+ LOOP
+ FETCH GROUP NEXT ROW;
+ set mini = mini + x;
+ END LOOP;
+ return 0;
+ end|
+ So here we would never execute the RETURN statement, so here
+ we force to quit the function execution.
+ */
+ thd->spcont->pause_state= TRUE;
+ }
+ DBUG_RETURN(res);
+ }
if (!thd->spcont->instr_ptr)
{
*nextp= m_ip+1;
@@ -4416,15 +4447,7 @@ sp_instr_agg_cfetch::execute(THD *thd, uint *nextp)
else
{
thd->spcont->pause_state= FALSE;
- if (thd->server_status == SERVER_STATUS_LAST_ROW_SENT)
- {
- my_message(ER_SP_FETCH_NO_DATA,
- ER_THD(thd, ER_SP_FETCH_NO_DATA), MYF(0));
- res= -1;
- thd->spcont->quit_func= TRUE;
- }
- else
- *nextp= m_ip + 1;
+ *nextp= m_ip + 1;
}
DBUG_RETURN(res);
}
1
0
10 Aug '18
revision-id: 470b99d4cbc93daad8fed568bea6b6eed1920d58 (mariadb-10.3.6-84-g470b99d4cbc)
parent(s): 522cd3c7aa9b466d5e0a4d010d4acb13c76a014c
author: Varun Gupta
committer: Varun Gupta
timestamp: 2018-08-10 14:48:51 +0530
message:
MDEV-16722: Assertion `type() != NULL_ITEM' failed
We hit this assert during the create of a temporary table field
because the current code does not handle the case when the value
of the NAME_CONST function is NULL.
Fixed this by allowing creation of temporary table fields even
for the case when NAME_CONST returns NULL value.
Introduced tmp_table_field_from_field_type_maybe_null() function
in Item class so both Item_basic_value and Item_name_const can use it.
Introduced a virtual method get_func_item() in the Item class.
---
mysql-test/main/win.result | 11 +++++++++++
mysql-test/main/win.test | 9 +++++++++
sql/item.cc | 22 ++++++----------------
sql/item.h | 24 +++++++++++++++++++++---
sql/item_cmpfunc.h | 14 ++++----------
sql/item_func.h | 1 +
sql/sql_select.cc | 41 ++++++++++++++++-------------------------
7 files changed, 68 insertions(+), 54 deletions(-)
diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result
index 7607cebc3a5..fd0fbefdcc5 100644
--- a/mysql-test/main/win.result
+++ b/mysql-test/main/win.result
@@ -3334,3 +3334,14 @@ d x
00:00:01 00:00:02
00:00:02 NULL
DROP TABLE t1;
+#
+# MDEV-16722: Assertion `type() != NULL_ITEM' failed
+#
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+SELECT row_number() OVER (order by a) FROM t1 order by NAME_CONST('myname',NULL);
+row_number() OVER (order by a)
+1
+2
+3
+drop table t1;
diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test
index 4b73f70d737..cc16595fcd4 100644
--- a/mysql-test/main/win.test
+++ b/mysql-test/main/win.test
@@ -2100,3 +2100,12 @@ CREATE TABLE t1 (d time);
INSERT INTO t1 VALUES ('00:00:01'),('00:00:02');
SELECT *, LEAD(d) OVER (ORDER BY d) AS x FROM t1;
DROP TABLE t1;
+
+--echo #
+--echo # MDEV-16722: Assertion `type() != NULL_ITEM' failed
+--echo #
+
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+SELECT row_number() OVER (order by a) FROM t1 order by NAME_CONST('myname',NULL);
+drop table t1;
diff --git a/sql/item.cc b/sql/item.cc
index 8b962d1706d..d9c66d3cfc5 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -2004,7 +2004,6 @@ Item_name_const::Item_name_const(THD *thd, Item *name_arg, Item *val):
Item_fixed_hybrid(thd), value_item(val), name_item(name_arg)
{
Item::maybe_null= TRUE;
- valid_args= true;
if (!name_item->basic_const_item())
goto err;
@@ -2023,7 +2022,6 @@ Item_name_const::Item_name_const(THD *thd, Item *name_arg, Item *val):
}
err:
- valid_args= false;
my_error(ER_WRONG_ARGUMENTS, MYF(0), "NAME_CONST");
}
@@ -2031,24 +2029,16 @@ Item_name_const::Item_name_const(THD *thd, Item *name_arg, Item *val):
Item::Type Item_name_const::type() const
{
/*
- As
- 1. one can try to create the Item_name_const passing non-constant
- arguments, although it's incorrect and
- 2. the type() method can be called before the fix_fields() to get
- type information for a further type cast, e.g.
- if (item->type() == FIELD_ITEM)
- ((Item_field *) item)->...
- we return NULL_ITEM in the case to avoid wrong casting.
-
- valid_args guarantees value_item->basic_const_item(); if type is
- FUNC_ITEM, then we have a fudged item_func_neg() on our hands
- and return the underlying type.
+
+ We are guarenteed that value_item->basic_const_item(), if not
+ an error is thrown that WRONG ARGUMENTS are supplied to
+ NAME_CONST function.
+ If type is FUNC_ITEM, then we have a fudged item_func_neg()
+ on our hands and return the underlying type.
For Item_func_set_collation()
e.g. NAME_CONST('name', 'value' COLLATE collation) we return its
'value' argument type.
*/
- if (!valid_args)
- return NULL_ITEM;
Item::Type value_type= value_item->type();
if (value_type == FUNC_ITEM)
{
diff --git a/sql/item.h b/sql/item.h
index a96406fa0cd..d6ad088e60a 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -820,6 +820,10 @@ class Item: public Value_source,
return tmp_table_field_from_field_type(table);
}
Field *create_tmp_field_int(TABLE *table, uint convert_int_length);
+ Field *tmp_table_field_from_field_type_maybe_null(TABLE *table,
+ Tmp_field_src *src,
+ const Tmp_field_param *param,
+ bool is_explicit_null);
void push_note_converted_to_negative_complement(THD *thd);
void push_note_converted_to_positive_complement(THD *thd);
@@ -876,6 +880,7 @@ class Item: public Value_source,
expressions with subqueries in the ORDER/GROUP clauses.
*/
String *val_str() { return val_str(&str_value); }
+ virtual Item_func *get_item_func() { return NULL; }
const MY_LOCALE *locale_from_val_str();
@@ -2608,7 +2613,20 @@ class Item_basic_value :public Item,
Item_basic_value(THD *thd): Item(thd) {}
public:
Field *create_tmp_field_ex(TABLE *table, Tmp_field_src *src,
- const Tmp_field_param *param);
+ const Tmp_field_param *param)
+ {
+
+ /*
+ create_tmp_field_ex() for this type of Items is called for:
+ - CREATE TABLE ... SELECT
+ - In ORDER BY: SELECT max(a) FROM t1 GROUP BY a ORDER BY 'const';
+ - In CURSORS:
+ DECLARE c CURSOR FOR SELECT 'test';
+ OPEN c;
+ */
+ return tmp_table_field_from_field_type_maybe_null(table, src, param,
+ type() == Item::NULL_ITEM);
+ }
bool eq(const Item *item, bool binary_cmp) const;
const Type_all_attributes *get_type_all_attributes_from_const() const
{ return this; }
@@ -2949,7 +2967,6 @@ class Item_name_const : public Item_fixed_hybrid
{
Item *value_item;
Item *name_item;
- bool valid_args;
public:
Item_name_const(THD *thd, Item *name_arg, Item *val);
@@ -2982,7 +2999,8 @@ class Item_name_const : public Item_fixed_hybrid
DECLARE c CURSOR FOR SELECT NAME_CONST('x','y') FROM t1;
OPEN c;
*/
- return create_tmp_field_ex_simple(table, src, param);
+ return tmp_table_field_from_field_type_maybe_null(table, src, param,
+ type() == Item::NULL_ITEM);
}
int save_in_field(Field *field, bool no_conversions)
{
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 3336885c036..2d917389e32 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -3313,11 +3313,8 @@ class Item_cond_and :public Item_cond
inline bool is_cond_and(Item *item)
{
- if (item->type() != Item::COND_ITEM)
- return FALSE;
-
- Item_cond *cond_item= (Item_cond*) item;
- return (cond_item->functype() == Item_func::COND_AND_FUNC);
+ Item_func *func_item= item->get_item_func();
+ return func_item && func_item->type() == Item::COND_ITEM && func_item->functype() == Item_func::COND_AND_FUNC;
}
class Item_cond_or :public Item_cond
@@ -3418,11 +3415,8 @@ class Item_func_cursor_notfound: public Item_func_cursor_bool_attr
inline bool is_cond_or(Item *item)
{
- if (item->type() != Item::COND_ITEM)
- return FALSE;
-
- Item_cond *cond_item= (Item_cond*) item;
- return (cond_item->functype() == Item_func::COND_OR_FUNC);
+ Item_func *func_item= item->get_item_func();
+ return func_item && func_item->type() == Item::COND_ITEM && func_item->functype() == Item_func::COND_OR_FUNC;
}
Item *and_expressions(Item *a, Item *b, Item **org_item);
diff --git a/sql/item_func.h b/sql/item_func.h
index f44986a9111..48b2ad2afeb 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -393,6 +393,7 @@ class Item_func :public Item_func_or_sum,
bool with_sum_func() const { return m_with_sum_func; }
With_sum_func_cache* get_with_sum_func_cache() { return this; }
+ Item_func *get_item_func() { return this; }
};
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 80ca1d7da62..bfd1c7580fc 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -16616,6 +16616,22 @@ Field *Item::create_tmp_field_int(TABLE *table, uint convert_int_length)
*this, table);
}
+Field *Item::tmp_table_field_from_field_type_maybe_null(TABLE *table,
+ Tmp_field_src *src,
+ const Tmp_field_param *param,
+ bool is_explicit_null)
+{
+ DBUG_ASSERT(!param->make_copy_field());
+ DBUG_ASSERT(!is_result_field());
+ Field *result;
+ if ((result= tmp_table_field_from_field_type(table)))
+ {
+ if (result && is_explicit_null)
+ result->is_created_from_null_item= true;
+ }
+ return result;
+}
+
Field *Item_sum::create_tmp_field(bool group, TABLE *table)
{
@@ -16847,31 +16863,6 @@ Field *Item_func_sp::create_tmp_field_ex(TABLE *table,
return result;
}
-
-Field *Item_basic_value::create_tmp_field_ex(TABLE *table,
- Tmp_field_src *src,
- const Tmp_field_param *param)
-{
- /*
- create_tmp_field_ex() for this type of Items is called for:
- - CREATE TABLE ... SELECT
- - In ORDER BY: SELECT max(a) FROM t1 GROUP BY a ORDER BY 'const';
- - In CURSORS:
- DECLARE c CURSOR FOR SELECT 'test';
- OPEN c;
- */
- DBUG_ASSERT(!param->make_copy_field());
- DBUG_ASSERT(!is_result_field());
- Field *result;
- if ((result= tmp_table_field_from_field_type(table)))
- {
- if (type() == Item::NULL_ITEM) // Item_null or Item_param
- result->is_created_from_null_item= true;
- }
- return result;
-}
-
-
/**
Create field for temporary table.
1
0
[Commits] 017adbeb394: MDEV-15475: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed on EXPLAIN EXTENDED with constant table and view
by Oleksandr Byelkin 08 Aug '18
by Oleksandr Byelkin 08 Aug '18
08 Aug '18
revision-id: 017adbeb3940ac162dcb8dd99478375a37edec02 (mariadb-5.5.61-3-g017adbeb394)
parent(s): 68ebfb31f215247d2fa08c8ed97a320191afc179
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-08-08 19:44:04 +0200
message:
MDEV-15475: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed on EXPLAIN EXTENDED with constant table and view
Print constant ISNULL fireld independent.
Fix of printing of view FRM and CREATE VIEW output
---
mysql-test/r/derived_view.result | 6 +++---
mysql-test/r/func_isnull.result | 20 ++++++++++++++++++++
mysql-test/r/subselect_mat.result | 6 +++---
mysql-test/r/subselect_sj_mat.result | 6 +++---
mysql-test/t/func_isnull.test | 16 ++++++++++++++++
sql/item.cc | 2 +-
sql/item_cmpfunc.cc | 13 +++++++++++++
sql/item_cmpfunc.h | 1 +
sql/sql_lex.cc | 2 +-
sql/sql_show.cc | 2 +-
10 files changed, 62 insertions(+), 12 deletions(-)
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index f7062473a3f..12811ebc6b3 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1101,7 +1101,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0
WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
a b
@@ -1115,7 +1115,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0
WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
a b
@@ -1129,7 +1129,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
DROP VIEW v1;
DROP TABLE t1,t2,t3;
#
diff --git a/mysql-test/r/func_isnull.result b/mysql-test/r/func_isnull.result
index 88c5bfd5468..a97d4a67939 100644
--- a/mysql-test/r/func_isnull.result
+++ b/mysql-test/r/func_isnull.result
@@ -106,5 +106,25 @@ Note 1003 select `test`.`t2`.`d1` AS `d1`,`test`.`t1`.`d1` AS `d1` from `test`.`
DROP VIEW v1;
DROP TABLE t1,t2;
#
+# MDEV-15475: Assertion `!table || (!table->read_set ||
+# bitmap_is_set(table->read_set, field_index))'
+# failed on EXPLAIN EXTENDED with constant table and view
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM;
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1);
+EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 select isnull(/*always not null*/ 1) AS `ISNULL(pk)` from dual
+EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 select ifnull(1,0) AS `IFNULL(pk,0)` from dual
+DROP VIEW v1;
+DROP TABLE t1;
+#
# End of 5.5 tests
#
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index eca3b760b65..efc348a26ce 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -499,7 +499,7 @@ 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
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
a1 a2
1 - 01 2 - 01
@@ -509,7 +509,7 @@ 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
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
a1 a2
1 - 01 2 - 01
@@ -1896,7 +1896,7 @@ 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; Using join buffer (flat, BNL join)
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7)))
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7)))
SELECT * FROM t1
WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
a b
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 180c182a51a..fd9435e8a39 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -520,7 +520,7 @@ 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
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
a1 a2
1 - 01 2 - 01
@@ -530,7 +530,7 @@ 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
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
a1 a2
1 - 01 2 - 01
@@ -1934,7 +1934,7 @@ 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; Using join buffer (flat, BNL join)
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7)))
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7)))
SELECT * FROM t1
WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
a b
diff --git a/mysql-test/t/func_isnull.test b/mysql-test/t/func_isnull.test
index 4c59fa3cbe8..7d1a7e83a1a 100644
--- a/mysql-test/t/func_isnull.test
+++ b/mysql-test/t/func_isnull.test
@@ -83,6 +83,22 @@ SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL;
DROP VIEW v1;
DROP TABLE t1,t2;
+
+--echo #
+--echo # MDEV-15475: Assertion `!table || (!table->read_set ||
+--echo # bitmap_is_set(table->read_set, field_index))'
+--echo # failed on EXPLAIN EXTENDED with constant table and view
+--echo #
+
+CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM;
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1);
+EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1;
+EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1;
+# Cleanup
+DROP VIEW v1;
+DROP TABLE t1;
+
--echo #
--echo # End of 5.5 tests
--echo #
diff --git a/sql/item.cc b/sql/item.cc
index 33c35f8c3e0..0cf4864326f 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -6845,7 +6845,7 @@ Item *Item_field::update_value_transformer(uchar *select_arg)
void Item_field::print(String *str, enum_query_type query_type)
{
if (field && field->table->const_table &&
- !(query_type & QT_NO_DATA_EXPANSION))
+ !(query_type & (QT_NO_DATA_EXPANSION | QT_VIEW_INTERNAL)))
{
print_value(str);
return;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 6fb650b975b..d4a2c767b15 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -4850,6 +4850,19 @@ Item *and_expressions(Item *a, Item *b, Item **org_item)
}
+void Item_func_isnull::print(String *str, enum_query_type query_type)
+{
+ str->append(func_name());
+ str->append('(');
+ if (const_item() && !args[0]->maybe_null &&
+ !(query_type & (QT_NO_DATA_EXPANSION | QT_VIEW_INTERNAL)))
+ str->append("/*always not null*/ 1");
+ else
+ args[0]->print(str, query_type);
+ str->append(')');
+}
+
+
longlong Item_func_isnull::val_int()
{
DBUG_ASSERT(fixed == 1);
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 3c8cc71370d..c4e6a53dd6b 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1389,6 +1389,7 @@ class Item_func_isnull :public Item_bool_func
const_item_cache= args[0]->const_item();
}
}
+ virtual void print(String *str, enum_query_type query_type);
table_map not_null_tables() const { return 0; }
optimize_type select_optimize() const { return OPTIMIZE_NULL; }
Item *neg_transformer(THD *thd);
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 2bf1216ec34..cfbde25314b 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2414,7 +2414,7 @@ void st_select_lex::print_order(String *str,
{
if (order->counter_used)
{
- if (query_type != QT_VIEW_INTERNAL)
+ if (!(query_type & QT_VIEW_INTERNAL))
{
char buffer[20];
size_t length= my_snprintf(buffer, 20, "%d", order->counter);
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 06d5a6f570a..db33a9de781 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -2085,7 +2085,7 @@ view_store_create_info(THD *thd, TABLE_LIST *table, String *buff)
We can't just use table->query, because our SQL_MODE may trigger
a different syntax, like when ANSI_QUOTES is defined.
*/
- table->view->unit.print(buff, QT_ORDINARY);
+ table->view->unit.print(buff, QT_VIEW_INTERNAL);
if (table->with_check != VIEW_CHECK_NONE)
{
1
0
revision-id: d964b91272caaf799eb53dfd075a961376e66e66 (mariadb-5.5.61-4-gd964b91272c)
parent(s): d1c90870ed114da62de8b12a71e2fba62724baa9
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-08-08 19:43:30 +0200
message:
Other fix (opst review)
---
mysql-test/r/derived_view.result | 6 +++---
mysql-test/r/func_isnull.result | 7 ++++++-
mysql-test/r/subselect_mat.result | 6 +++---
mysql-test/r/subselect_sj_mat.result | 6 +++---
mysql-test/t/func_isnull.test | 1 +
sql/item.cc | 5 +----
sql/item_cmpfunc.cc | 13 +++++++++++++
sql/item_cmpfunc.h | 1 +
sql/sql_lex.cc | 2 +-
sql/sql_show.cc | 2 +-
10 files changed, 33 insertions(+), 16 deletions(-)
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index f7062473a3f..12811ebc6b3 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1101,7 +1101,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0
WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
a b
@@ -1115,7 +1115,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0
WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
a b
@@ -1129,7 +1129,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
DROP VIEW v1;
DROP TABLE t1,t2,t3;
#
diff --git a/mysql-test/r/func_isnull.result b/mysql-test/r/func_isnull.result
index fd55b7be26c..a97d4a67939 100644
--- a/mysql-test/r/func_isnull.result
+++ b/mysql-test/r/func_isnull.result
@@ -117,7 +117,12 @@ EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select isnull(`test`.`t1`.`pk`) AS `ISNULL(pk)` from dual
+Note 1003 select isnull(/*always not null*/ 1) AS `ISNULL(pk)` from dual
+EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 select ifnull(1,0) AS `IFNULL(pk,0)` from dual
DROP VIEW v1;
DROP TABLE t1;
#
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index eca3b760b65..efc348a26ce 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -499,7 +499,7 @@ 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
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
a1 a2
1 - 01 2 - 01
@@ -509,7 +509,7 @@ 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
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
a1 a2
1 - 01 2 - 01
@@ -1896,7 +1896,7 @@ 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; Using join buffer (flat, BNL join)
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7)))
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7)))
SELECT * FROM t1
WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
a b
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 180c182a51a..fd9435e8a39 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -520,7 +520,7 @@ 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
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
a1 a2
1 - 01 2 - 01
@@ -530,7 +530,7 @@ 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
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
a1 a2
1 - 01 2 - 01
@@ -1934,7 +1934,7 @@ 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; Using join buffer (flat, BNL join)
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7)))
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7)))
SELECT * FROM t1
WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
a b
diff --git a/mysql-test/t/func_isnull.test b/mysql-test/t/func_isnull.test
index 2111b8f16bc..7d1a7e83a1a 100644
--- a/mysql-test/t/func_isnull.test
+++ b/mysql-test/t/func_isnull.test
@@ -94,6 +94,7 @@ CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM;
CREATE VIEW v1 AS SELECT * FROM t1;
INSERT INTO t1 VALUES (1);
EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1;
+EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1;
# Cleanup
DROP VIEW v1;
DROP TABLE t1;
diff --git a/sql/item.cc b/sql/item.cc
index 77be702bac5..0cf4864326f 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -6845,10 +6845,7 @@ Item *Item_field::update_value_transformer(uchar *select_arg)
void Item_field::print(String *str, enum_query_type query_type)
{
if (field && field->table->const_table &&
- !(query_type & QT_NO_DATA_EXPANSION) &&
- // and we was gping to read field value (it is not optimised out)
- field->table->read_set &&
- bitmap_is_set(field->table->read_set, field->field_index))
+ !(query_type & (QT_NO_DATA_EXPANSION | QT_VIEW_INTERNAL)))
{
print_value(str);
return;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 6fb650b975b..d4a2c767b15 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -4850,6 +4850,19 @@ Item *and_expressions(Item *a, Item *b, Item **org_item)
}
+void Item_func_isnull::print(String *str, enum_query_type query_type)
+{
+ str->append(func_name());
+ str->append('(');
+ if (const_item() && !args[0]->maybe_null &&
+ !(query_type & (QT_NO_DATA_EXPANSION | QT_VIEW_INTERNAL)))
+ str->append("/*always not null*/ 1");
+ else
+ args[0]->print(str, query_type);
+ str->append(')');
+}
+
+
longlong Item_func_isnull::val_int()
{
DBUG_ASSERT(fixed == 1);
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 3c8cc71370d..c4e6a53dd6b 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1389,6 +1389,7 @@ class Item_func_isnull :public Item_bool_func
const_item_cache= args[0]->const_item();
}
}
+ virtual void print(String *str, enum_query_type query_type);
table_map not_null_tables() const { return 0; }
optimize_type select_optimize() const { return OPTIMIZE_NULL; }
Item *neg_transformer(THD *thd);
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 2bf1216ec34..cfbde25314b 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2414,7 +2414,7 @@ void st_select_lex::print_order(String *str,
{
if (order->counter_used)
{
- if (query_type != QT_VIEW_INTERNAL)
+ if (!(query_type & QT_VIEW_INTERNAL))
{
char buffer[20];
size_t length= my_snprintf(buffer, 20, "%d", order->counter);
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 06d5a6f570a..db33a9de781 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -2085,7 +2085,7 @@ view_store_create_info(THD *thd, TABLE_LIST *table, String *buff)
We can't just use table->query, because our SQL_MODE may trigger
a different syntax, like when ANSI_QUOTES is defined.
*/
- table->view->unit.print(buff, QT_ORDINARY);
+ table->view->unit.print(buff, QT_VIEW_INTERNAL);
if (table->with_check != VIEW_CHECK_NONE)
{
1
0
[Commits] 6df4e4a855f: MDEV-16217: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in Field_num::get_date
by Oleksandr Byelkin 08 Aug '18
by Oleksandr Byelkin 08 Aug '18
08 Aug '18
revision-id: 6df4e4a855fe223988c12681f8caec6c49b2f629 (mariadb-10.2.16-66-g6df4e4a855f)
parent(s): 4ddcb4eb46c62cf459936554d43351db740ba14d
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-08-08 12:28:15 +0200
message:
MDEV-16217: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in Field_num::get_date
- clean up DEFAULT() to work only with default value and correctly print
itself.
- fix of DBUG_ASSERT about fields read/write
- fix of firld marking for write based really on the thd->mark_used_columns flag
---
mysql-test/r/func_default.result | 2 +-
mysql-test/r/func_time.result | 28 ++++++++++++++++++++++++++++
mysql-test/t/func_time.test | 21 +++++++++++++++++++++
sql/field.cc | 21 +++++++++++++++++++--
sql/field.h | 1 +
sql/item.cc | 25 ++++++++++++++++++++++++-
sql/item.h | 5 +++++
sql/sql_base.cc | 2 +-
8 files changed, 100 insertions(+), 5 deletions(-)
diff --git a/mysql-test/r/func_default.result b/mysql-test/r/func_default.result
index 535be10da86..1f331af287c 100644
--- a/mysql-test/r/func_default.result
+++ b/mysql-test/r/func_default.result
@@ -8,7 +8,7 @@ explain extended select default(str), default(strnull), default(intg), default(r
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select default('') AS `default(str)`,default('') AS `default(strnull)`,default(0) AS `default(intg)`,default(0) AS `default(rel)` from dual
+Note 1003 select default(`str`) AS `default(str)`,default(`strnull`) AS `default(strnull)`,default(`intg`) AS `default(intg)`,default(`rel`) AS `default(rel)` from dual
select * from t1 where str <> default(str);
str strnull intg rel
0 0
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 55c2c93eb56..51f51820efc 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -3260,3 +3260,31 @@ DROP TABLE t1,t2;
#
# End of 10.1 tests
#
+#
+# MDEV-16217: Assertion `!table || (!table->read_set ||
+# bitmap_is_set(table->read_set, field_index))'
+# failed in Field_num::get_date
+#
+CREATE TABLE t1 (pk int default 0, a1 date);
+INSERT INTO t1 VALUES (1,'1900-01-01'),(2,NULL),(3,NULL),(4,NULL);
+CREATE VIEW v1 AS
+SELECT t1.pk AS pk, t1.a1 AS a1 FROM t1;
+SELECT a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk)) FROM v1;
+a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk))
+0
+NULL
+NULL
+NULL
+SELECT a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk)) FROM v1;
+a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk))
+0
+NULL
+NULL
+NULL
+Warnings:
+Warning 1292 Incorrect datetime value: '18446744073709551615'
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# End of 10.2 tests
+#
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 3503b6d5bc6..c8859feee93 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -1857,3 +1857,24 @@ DROP TABLE t1,t2;
--echo #
--echo # End of 10.1 tests
--echo #
+
+--echo #
+--echo # MDEV-16217: Assertion `!table || (!table->read_set ||
+--echo # bitmap_is_set(table->read_set, field_index))'
+--echo # failed in Field_num::get_date
+--echo #
+CREATE TABLE t1 (pk int default 0, a1 date);
+INSERT INTO t1 VALUES (1,'1900-01-01'),(2,NULL),(3,NULL),(4,NULL);
+
+CREATE VIEW v1 AS
+SELECT t1.pk AS pk, t1.a1 AS a1 FROM t1;
+
+SELECT a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk)) FROM v1;
+SELECT a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk)) FROM v1;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
diff --git a/sql/field.cc b/sql/field.cc
index 9ca9663f066..e12adbf47b3 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -70,8 +70,25 @@ const char field_separator=',';
#define BLOB_PACK_LENGTH_TO_MAX_LENGH(arg) \
((ulong) ((1LL << MY_MIN(arg, 4) * 8) - 1))
-#define ASSERT_COLUMN_MARKED_FOR_READ DBUG_ASSERT(!table || (!table->read_set || bitmap_is_set(table->read_set, field_index)))
-#define ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED DBUG_ASSERT(is_stat_field || !table || (!table->write_set || bitmap_is_set(table->write_set, field_index) || (table->vcol_set && bitmap_is_set(table->vcol_set, field_index))))
+// Column marked for read or the field set to read out or record[0] or [1]
+#define ASSERT_COLUMN_MARKED_FOR_READ \
+ DBUG_ASSERT(!table || \
+ (!table->read_set || \
+ bitmap_is_set(table->read_set, field_index) || \
+ (!(ptr >= table->record[0] && \
+ ptr < table->record[0] + table->s->reclength) && \
+ !(ptr >= table->record[1] && \
+ ptr < table->record[1] + table->s->reclength))))
+
+#define ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED \
+ DBUG_ASSERT(is_stat_field || !table || \
+ (!table->write_set || \
+ bitmap_is_set(table->write_set, field_index) || \
+ (!(ptr >= table->record[0] && \
+ ptr < table->record[0] + table->s->reclength) && \
+ !(ptr >= table->record[1] && \
+ ptr < table->record[1] + table->s->reclength))) || \
+ (table->vcol_set && bitmap_is_set(table->vcol_set, field_index)))
#define FLAGSTR(S,F) ((S) & (F) ? #F " " : "")
diff --git a/sql/field.h b/sql/field.h
index 22c276478b6..55c3ed4c4bd 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -630,6 +630,7 @@ class Virtual_column_info: public Sql_alloc
bool utf8; /* Already in utf8 */
Item *expr;
LEX_STRING name; /* Name of constraint */
+ /* see VCOL_* (VCOL_FIELD_REF, ...) */
uint flags;
Virtual_column_info()
diff --git a/sql/item.cc b/sql/item.cc
index 58d2b7dbfc0..91fe7ff03aa 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -8917,8 +8917,19 @@ bool Item_default_value::fix_fields(THD *thd, Item **items)
fixed= 1;
return FALSE;
}
+
+ /*
+ DEFAULT() do not need table field so should not ask handler to bring
+ field value (mark column for read)
+ */
+ enum_mark_columns save_mark_used_columns= thd->mark_used_columns;
+ thd->mark_used_columns= MARK_COLUMNS_NONE;
if (!arg->fixed && arg->fix_fields(thd, &arg))
+ {
+ thd->mark_used_columns= save_mark_used_columns;
goto error;
+ }
+ thd->mark_used_columns= save_mark_used_columns;
real_arg= arg->real_item();
@@ -8938,12 +8949,16 @@ bool Item_default_value::fix_fields(THD *thd, Item **items)
goto error;
memcpy((void *)def_field, (void *)field_arg->field,
field_arg->field->size_of());
- IF_DBUG(def_field->is_stat_field=1,); // a hack to fool ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED
+ // If non-constant default value expression
if (def_field->default_value && def_field->default_value->flags)
{
uchar *newptr= (uchar*) thd->alloc(1+def_field->pack_length());
if (!newptr)
goto error;
+ /*
+ Even if DEFAULT() do not read tables fields, the default value
+ expression can do it.
+ */
fix_session_vcol_expr_for_read(thd, def_field, def_field->default_value);
if (thd->mark_used_columns != MARK_COLUMNS_NONE)
def_field->default_value->expr->walk(&Item::register_field_in_read_map, 1, 0);
@@ -8970,6 +8985,14 @@ void Item_default_value::print(String *str, enum_query_type query_type)
return;
}
str->append(STRING_WITH_LEN("default("));
+ /*
+ We take DEFAULT from a field so do not need it value in case of const
+ tables but its name so we set QT_NO_DATA_EXPANSION (as we print for
+ table definition, also we do not need table and database name)
+ */
+ query_type= (enum_query_type) (query_type | QT_NO_DATA_EXPANSION |
+ QT_ITEM_IDENT_SKIP_DB_NAMES |
+ QT_ITEM_IDENT_SKIP_TABLE_NAMES);
arg->print(str, query_type);
str->append(')');
}
diff --git a/sql/item.h b/sql/item.h
index 8fad8dadf22..d252a256f76 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -5316,6 +5316,11 @@ class Item_default_value : public Item_field
return false;
}
table_map used_tables() const;
+ virtual void update_used_tables()
+ {
+ if (field && field->default_value)
+ field->default_value->expr->walk(&Item::register_field_in_read_map, 1, 0);
+ }
Field *get_tmp_table_field() { return 0; }
Item *get_tmp_table_item(THD *thd) { return this; }
Item_field *field_for_view_update() { return 0; }
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 36bf39ea8f3..2c5bdf01468 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5688,7 +5688,7 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list,
TABLE *table= field_to_set->table;
if (thd->mark_used_columns == MARK_COLUMNS_READ)
bitmap_set_bit(table->read_set, field_to_set->field_index);
- else
+ else if (thd->mark_used_columns == MARK_COLUMNS_WRITE)
bitmap_set_bit(table->write_set, field_to_set->field_index);
}
}
1
0
08 Aug '18
revision-id: 637c2d76a0332058b8771be37c17d077f297f0b1 (mariadb-10.2.16-67-g637c2d76a03)
parent(s): 1e8b79d966fca2ae541477972ac759be5ee60c4e
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-08-08 12:24:37 +0200
message:
fix default() itself after talking to serg
---
mysql-test/r/func_default.result | 2 +-
mysql-test/r/func_time.result | 2 +-
mysql-test/t/func_time.test | 6 +++---
sql/field.cc | 21 +++++++++++++++++++--
sql/field.h | 1 +
sql/item.cc | 25 ++++++++++++++++++++++++-
sql/item.h | 14 +++++---------
sql/sql_base.cc | 2 +-
8 files changed, 55 insertions(+), 18 deletions(-)
diff --git a/mysql-test/r/func_default.result b/mysql-test/r/func_default.result
index 535be10da86..1f331af287c 100644
--- a/mysql-test/r/func_default.result
+++ b/mysql-test/r/func_default.result
@@ -8,7 +8,7 @@ explain extended select default(str), default(strnull), default(intg), default(r
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select default('') AS `default(str)`,default('') AS `default(strnull)`,default(0) AS `default(intg)`,default(0) AS `default(rel)` from dual
+Note 1003 select default(`str`) AS `default(str)`,default(`strnull`) AS `default(strnull)`,default(`intg`) AS `default(intg)`,default(`rel`) AS `default(rel)` from dual
select * from t1 where str <> default(str);
str strnull intg rel
0 0
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index d3e81a8f2ee..51f51820efc 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -3267,7 +3267,7 @@ DROP TABLE t1,t2;
#
CREATE TABLE t1 (pk int default 0, a1 date);
INSERT INTO t1 VALUES (1,'1900-01-01'),(2,NULL),(3,NULL),(4,NULL);
-CREATE VIEW v1 AS
+CREATE VIEW v1 AS
SELECT t1.pk AS pk, t1.a1 AS a1 FROM t1;
SELECT a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk)) FROM v1;
a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk))
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 98b5f968fe0..c8859feee93 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -1865,10 +1865,10 @@ DROP TABLE t1,t2;
--echo #
CREATE TABLE t1 (pk int default 0, a1 date);
INSERT INTO t1 VALUES (1,'1900-01-01'),(2,NULL),(3,NULL),(4,NULL);
-
-CREATE VIEW v1 AS
+
+CREATE VIEW v1 AS
SELECT t1.pk AS pk, t1.a1 AS a1 FROM t1;
-
+
SELECT a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk)) FROM v1;
SELECT a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk)) FROM v1;
diff --git a/sql/field.cc b/sql/field.cc
index 9ca9663f066..e12adbf47b3 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -70,8 +70,25 @@ const char field_separator=',';
#define BLOB_PACK_LENGTH_TO_MAX_LENGH(arg) \
((ulong) ((1LL << MY_MIN(arg, 4) * 8) - 1))
-#define ASSERT_COLUMN_MARKED_FOR_READ DBUG_ASSERT(!table || (!table->read_set || bitmap_is_set(table->read_set, field_index)))
-#define ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED DBUG_ASSERT(is_stat_field || !table || (!table->write_set || bitmap_is_set(table->write_set, field_index) || (table->vcol_set && bitmap_is_set(table->vcol_set, field_index))))
+// Column marked for read or the field set to read out or record[0] or [1]
+#define ASSERT_COLUMN_MARKED_FOR_READ \
+ DBUG_ASSERT(!table || \
+ (!table->read_set || \
+ bitmap_is_set(table->read_set, field_index) || \
+ (!(ptr >= table->record[0] && \
+ ptr < table->record[0] + table->s->reclength) && \
+ !(ptr >= table->record[1] && \
+ ptr < table->record[1] + table->s->reclength))))
+
+#define ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED \
+ DBUG_ASSERT(is_stat_field || !table || \
+ (!table->write_set || \
+ bitmap_is_set(table->write_set, field_index) || \
+ (!(ptr >= table->record[0] && \
+ ptr < table->record[0] + table->s->reclength) && \
+ !(ptr >= table->record[1] && \
+ ptr < table->record[1] + table->s->reclength))) || \
+ (table->vcol_set && bitmap_is_set(table->vcol_set, field_index)))
#define FLAGSTR(S,F) ((S) & (F) ? #F " " : "")
diff --git a/sql/field.h b/sql/field.h
index 22c276478b6..55c3ed4c4bd 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -630,6 +630,7 @@ class Virtual_column_info: public Sql_alloc
bool utf8; /* Already in utf8 */
Item *expr;
LEX_STRING name; /* Name of constraint */
+ /* see VCOL_* (VCOL_FIELD_REF, ...) */
uint flags;
Virtual_column_info()
diff --git a/sql/item.cc b/sql/item.cc
index 58d2b7dbfc0..91fe7ff03aa 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -8917,8 +8917,19 @@ bool Item_default_value::fix_fields(THD *thd, Item **items)
fixed= 1;
return FALSE;
}
+
+ /*
+ DEFAULT() do not need table field so should not ask handler to bring
+ field value (mark column for read)
+ */
+ enum_mark_columns save_mark_used_columns= thd->mark_used_columns;
+ thd->mark_used_columns= MARK_COLUMNS_NONE;
if (!arg->fixed && arg->fix_fields(thd, &arg))
+ {
+ thd->mark_used_columns= save_mark_used_columns;
goto error;
+ }
+ thd->mark_used_columns= save_mark_used_columns;
real_arg= arg->real_item();
@@ -8938,12 +8949,16 @@ bool Item_default_value::fix_fields(THD *thd, Item **items)
goto error;
memcpy((void *)def_field, (void *)field_arg->field,
field_arg->field->size_of());
- IF_DBUG(def_field->is_stat_field=1,); // a hack to fool ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED
+ // If non-constant default value expression
if (def_field->default_value && def_field->default_value->flags)
{
uchar *newptr= (uchar*) thd->alloc(1+def_field->pack_length());
if (!newptr)
goto error;
+ /*
+ Even if DEFAULT() do not read tables fields, the default value
+ expression can do it.
+ */
fix_session_vcol_expr_for_read(thd, def_field, def_field->default_value);
if (thd->mark_used_columns != MARK_COLUMNS_NONE)
def_field->default_value->expr->walk(&Item::register_field_in_read_map, 1, 0);
@@ -8970,6 +8985,14 @@ void Item_default_value::print(String *str, enum_query_type query_type)
return;
}
str->append(STRING_WITH_LEN("default("));
+ /*
+ We take DEFAULT from a field so do not need it value in case of const
+ tables but its name so we set QT_NO_DATA_EXPANSION (as we print for
+ table definition, also we do not need table and database name)
+ */
+ query_type= (enum_query_type) (query_type | QT_NO_DATA_EXPANSION |
+ QT_ITEM_IDENT_SKIP_DB_NAMES |
+ QT_ITEM_IDENT_SKIP_TABLE_NAMES);
arg->print(str, query_type);
str->append(')');
}
diff --git a/sql/item.h b/sql/item.h
index 54cd358112d..d252a256f76 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -5316,6 +5316,11 @@ class Item_default_value : public Item_field
return false;
}
table_map used_tables() const;
+ virtual void update_used_tables()
+ {
+ if (field && field->default_value)
+ field->default_value->expr->walk(&Item::register_field_in_read_map, 1, 0);
+ }
Field *get_tmp_table_field() { return 0; }
Item *get_tmp_table_item(THD *thd) { return this; }
Item_field *field_for_view_update() { return 0; }
@@ -5649,15 +5654,6 @@ class Item_cache: public Item_basic_constant,
example->split_sum_func2(thd, ref_pointer_array, fields, &example, flags);
}
Item *get_example() const { return example; }
- /*
- Functiuons like DEFAULT() are constant but still read from tables and
- update them, so we should gave that chance to them in case of caching.
- */
- virtual void update_used_tables()
- {
- if (example)
- return example->update_used_tables();
- }
virtual Item *convert_to_basic_const_item(THD *thd) { return 0; };
Item *derived_field_transformer_for_having(THD *thd, uchar *arg)
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 36bf39ea8f3..2c5bdf01468 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5688,7 +5688,7 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list,
TABLE *table= field_to_set->table;
if (thd->mark_used_columns == MARK_COLUMNS_READ)
bitmap_set_bit(table->read_set, field_to_set->field_index);
- else
+ else if (thd->mark_used_columns == MARK_COLUMNS_WRITE)
bitmap_set_bit(table->write_set, field_to_set->field_index);
}
}
1
0
08 Aug '18
revision-id: 44bcd544b785840d09b856540080866eeb9144b4 (mariadb-10.3.6-33-g44bcd544b78)
parent(s): 4b0cedf82d8d8ba582648dcb4a2620c146862a43
author: Varun Gupta
committer: Varun Gupta
timestamp: 2018-08-08 11:54:11 +0530
message:
MDEV-16722: Assertion `type() != NULL_ITEM' failed
We hit this assert during the create of a temporary table field
because the current code does not handle the case when the value
of the NAME_CONST function is NULL.
Fixed this by allowing creation of temporary table fields even
for the case when NAME_CONST returns NULL value.
Introduced tmp_table_field_from_field_type_maybe_null() function
in Item class so both Item_basic_value and Item_name_const can use it.
---
mysql-test/main/win.result | 11 +++++++++++
mysql-test/main/win.test | 9 +++++++++
sql/item.cc | 22 ++++++----------------
sql/item.h | 23 ++++++++++++++++++++---
sql/sql_select.cc | 41 ++++++++++++++++-------------------------
5 files changed, 62 insertions(+), 44 deletions(-)
diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result
index 3d56cd8e435..1a3467bba7a 100644
--- a/mysql-test/main/win.result
+++ b/mysql-test/main/win.result
@@ -3315,5 +3315,16 @@ COUNT(DISTINCT t2.a2) rank() OVER (ORDER BY t2.b1)
1 3
DROP TABLE t1,t2;
#
+# MDEV-16722: Assertion `type() != NULL_ITEM' failed
+#
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+SELECT row_number() OVER (order by a) FROM t1 order by NAME_CONST('myname',NULL);
+row_number() OVER (order by a)
+1
+2
+3
+drop table t1;
+#
# Start of 10.3 tests
#
diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test
index d483cdbaa83..2efa0d562c8 100644
--- a/mysql-test/main/win.test
+++ b/mysql-test/main/win.test
@@ -2083,6 +2083,15 @@ SELECT COUNT(DISTINCT t2.a2),
FROM t2 ,t1 GROUP BY t2.b1 ORDER BY t1.a1;
DROP TABLE t1,t2;
+--echo #
+--echo # MDEV-16722: Assertion `type() != NULL_ITEM' failed
+--echo #
+
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+SELECT row_number() OVER (order by a) FROM t1 order by NAME_CONST('myname',NULL);
+drop table t1;
+
--echo #
--echo # Start of 10.3 tests
--echo #
diff --git a/sql/item.cc b/sql/item.cc
index 68aed25a580..68f7f6334ab 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -2164,7 +2164,6 @@ Item_name_const::Item_name_const(THD *thd, Item *name_arg, Item *val):
Item_fixed_hybrid(thd), value_item(val), name_item(name_arg)
{
Item::maybe_null= TRUE;
- valid_args= true;
if (!name_item->basic_const_item())
goto err;
@@ -2183,7 +2182,6 @@ Item_name_const::Item_name_const(THD *thd, Item *name_arg, Item *val):
}
err:
- valid_args= false;
my_error(ER_WRONG_ARGUMENTS, MYF(0), "NAME_CONST");
}
@@ -2191,24 +2189,16 @@ Item_name_const::Item_name_const(THD *thd, Item *name_arg, Item *val):
Item::Type Item_name_const::type() const
{
/*
- As
- 1. one can try to create the Item_name_const passing non-constant
- arguments, although it's incorrect and
- 2. the type() method can be called before the fix_fields() to get
- type information for a further type cast, e.g.
- if (item->type() == FIELD_ITEM)
- ((Item_field *) item)->...
- we return NULL_ITEM in the case to avoid wrong casting.
-
- valid_args guarantees value_item->basic_const_item(); if type is
- FUNC_ITEM, then we have a fudged item_func_neg() on our hands
- and return the underlying type.
+
+ We are guarenteed that value_item->basic_const_item(), if not
+ an error is thrown that WRONG ARGUMENTS are supplied to
+ NAME_CONST function.
+ If type is FUNC_ITEM, then we have a fudged item_func_neg()
+ on our hands and return the underlying type.
For Item_func_set_collation()
e.g. NAME_CONST('name', 'value' COLLATE collation) we return its
'value' argument type.
*/
- if (!valid_args)
- return NULL_ITEM;
Item::Type value_type= value_item->type();
if (value_type == FUNC_ITEM)
{
diff --git a/sql/item.h b/sql/item.h
index f6f4684f643..a5648cdd92d 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -820,6 +820,10 @@ class Item: public Value_source,
return tmp_table_field_from_field_type(table);
}
Field *create_tmp_field_int(TABLE *table, uint convert_int_length);
+ Field *tmp_table_field_from_field_type_maybe_null(TABLE *table,
+ Tmp_field_src *src,
+ const Tmp_field_param *param,
+ bool is_explicit_null);
void push_note_converted_to_negative_complement(THD *thd);
void push_note_converted_to_positive_complement(THD *thd);
@@ -2649,7 +2653,20 @@ class Item_basic_value :public Item,
Item_basic_value(THD *thd): Item(thd) {}
public:
Field *create_tmp_field_ex(TABLE *table, Tmp_field_src *src,
- const Tmp_field_param *param);
+ const Tmp_field_param *param)
+ {
+
+ /*
+ create_tmp_field_ex() for this type of Items is called for:
+ - CREATE TABLE ... SELECT
+ - In ORDER BY: SELECT max(a) FROM t1 GROUP BY a ORDER BY 'const';
+ - In CURSORS:
+ DECLARE c CURSOR FOR SELECT 'test';
+ OPEN c;
+ */
+ return tmp_table_field_from_field_type_maybe_null(table, src, param,
+ type() == Item::NULL_ITEM);
+ }
bool eq(const Item *item, bool binary_cmp) const;
const Type_all_attributes *get_type_all_attributes_from_const() const
{ return this; }
@@ -2990,7 +3007,6 @@ class Item_name_const : public Item_fixed_hybrid
{
Item *value_item;
Item *name_item;
- bool valid_args;
public:
Item_name_const(THD *thd, Item *name_arg, Item *val);
@@ -3023,7 +3039,8 @@ class Item_name_const : public Item_fixed_hybrid
DECLARE c CURSOR FOR SELECT NAME_CONST('x','y') FROM t1;
OPEN c;
*/
- return create_tmp_field_ex_simple(table, src, param);
+ return tmp_table_field_from_field_type_maybe_null(table, src, param,
+ type() == Item::NULL_ITEM);
}
int save_in_field(Field *field, bool no_conversions)
{
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 25b01e49a2f..c0f3e14f82e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -16591,6 +16591,22 @@ Field *Item::create_tmp_field_int(TABLE *table, uint convert_int_length)
*this, table);
}
+Field *Item::tmp_table_field_from_field_type_maybe_null(TABLE *table,
+ Tmp_field_src *src,
+ const Tmp_field_param *param,
+ bool is_explicit_null)
+{
+ DBUG_ASSERT(!param->make_copy_field());
+ DBUG_ASSERT(!is_result_field());
+ Field *result;
+ if ((result= tmp_table_field_from_field_type(table)))
+ {
+ if (result && is_explicit_null)
+ result->is_created_from_null_item= true;
+ }
+ return result;
+}
+
Field *Item_sum::create_tmp_field(bool group, TABLE *table)
{
@@ -16821,31 +16837,6 @@ Field *Item_func_sp::create_tmp_field_ex(TABLE *table,
return result;
}
-
-Field *Item_basic_value::create_tmp_field_ex(TABLE *table,
- Tmp_field_src *src,
- const Tmp_field_param *param)
-{
- /*
- create_tmp_field_ex() for this type of Items is called for:
- - CREATE TABLE ... SELECT
- - In ORDER BY: SELECT max(a) FROM t1 GROUP BY a ORDER BY 'const';
- - In CURSORS:
- DECLARE c CURSOR FOR SELECT 'test';
- OPEN c;
- */
- DBUG_ASSERT(!param->make_copy_field());
- DBUG_ASSERT(!is_result_field());
- Field *result;
- if ((result= tmp_table_field_from_field_type(table)))
- {
- if (type() == Item::NULL_ITEM) // Item_null or Item_param
- result->is_created_from_null_item= true;
- }
- return result;
-}
-
-
/**
Create field for temporary table.
1
0