[Commits] 35294053b28: MDEV-26106: [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record
by psergey 07 Jul '21
by psergey 07 Jul '21
07 Jul '21
revision-id: 35294053b281294bb49a6217da07b93eb6c2595b (mariadb-10.5.11-33-g35294053b28)
parent(s): c262ccac027e71f22fc1329cf295a6bf687e4684
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-07-07 18:54:00 +0300
message:
MDEV-26106: [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record
Port the following patch from MySQL:
commit 1b2e8ea269c80cb93cc79d8be934c40b1c58e947
Author: Kailasnath Nagarkar <kailasnath.nagarkar(a)oracle.com>
Date: Fri Nov 30 16:43:13 2018 +0530
Bug #20939184: INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE
LOCK ON THE RECORD
Issue:
------
Consdier tables t1 and t2 such that t1 has multiple rows
and join condition for t1 left join t2 results in only
single row from t2.
In this case, access to table t2 is const since there
is a single row that qualifies the join condition.
However, while executing the query, attempt is made to
unlock t2's row multiple times.
The current algorithm to fetch rows approximates to:
1) Retrieve the row for t1.
2) Retrieve the row for t2.
3) Apply the join conditions.
a) If condition evaluates to true:
Project the row to the result.
b) If condition evaluates to false:
i) If t2's qep_tab->not_null_complement is true,
unlock t2's row.
ii) Null-complement the row by calling
"evaluate_null_complemented_join_record()". In
this function qep_tab->not_null_complement is
set to false.
The t2's only one row, that qualifies join condition,
is unlocked in Step i) when t1's row is evaluated to
false.
When t1's next row is also evaluated to false, another
attempt is made to unlock t2's already unlocked row.
This results in following error being logged in error.log:
"[ERROR] InnoDB: Unlock row could not find a 3 mode lock on
the record. Current statement:
select * from t1 left join t2 ......"
Solution:
---------
When a table's access method is "const", set record unlock
method for this table to do no operation.
---
mysql-test/main/join_outer_innodb.result | 19 +++++++++++++++++++
mysql-test/main/join_outer_innodb.test | 17 +++++++++++++++++
sql/sql_select.cc | 31 +++++++++++++++++++++++++++----
3 files changed, 63 insertions(+), 4 deletions(-)
diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result
index 0b34a399d77..09a37a29702 100644
--- a/mysql-test/main/join_outer_innodb.result
+++ b/mysql-test/main/join_outer_innodb.result
@@ -496,3 +496,22 @@ natural right outer join t3;
drop table t1,t2,t3;
set optimizer_prune_level=@mdev4270_opl;
set optimizer_search_depth=@mdev4270_osd;
+#
+# Bug #20939184:INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE
+# RECORD
+#
+CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1,c2) ) engine=innodb;
+CREATE TABLE t2 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1), KEY (c2)) engine=innodb;
+INSERT INTO t1 VALUES (1,2,3),(2,3,4),(3,4,5);
+INSERT INTO t2 SELECT * FROM t1;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.c2=t2.c2 AND t2.c1=1 FOR UPDATE;
+c1 c2 c3 c1 c2 c3
+1 2 3 1 2 3
+2 3 4 NULL NULL NULL
+3 4 5 NULL NULL NULL
+UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c2 AND t2.c1 = 3 SET t1.c3 = RAND()*10;
+COMMIT;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+DROP TABLE t1,t2;
diff --git a/mysql-test/main/join_outer_innodb.test b/mysql-test/main/join_outer_innodb.test
index c26cd62fbc7..6b332f3d155 100644
--- a/mysql-test/main/join_outer_innodb.test
+++ b/mysql-test/main/join_outer_innodb.test
@@ -374,3 +374,20 @@ drop table t1,t2,t3;
set optimizer_prune_level=@mdev4270_opl;
set optimizer_search_depth=@mdev4270_osd;
+--echo #
+--echo # Bug #20939184:INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE
+--echo # RECORD
+--echo #
+CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1,c2) ) engine=innodb;
+CREATE TABLE t2 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1), KEY (c2)) engine=innodb;
+INSERT INTO t1 VALUES (1,2,3),(2,3,4),(3,4,5);
+INSERT INTO t2 SELECT * FROM t1;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+#unlocks rows in table t2 where c1 = 1
+SELECT * FROM t1 LEFT JOIN t2 ON t1.c2=t2.c2 AND t2.c1=1 FOR UPDATE;
+UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c2 AND t2.c1 = 3 SET t1.c3 = RAND()*10;
+COMMIT;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+DROP TABLE t1,t2;
+
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 8800fbf5b6d..ab54bd3b94d 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -189,6 +189,7 @@ static int join_read_system(JOIN_TAB *tab);
static int join_read_const(JOIN_TAB *tab);
static int join_read_key(JOIN_TAB *tab);
static void join_read_key_unlock_row(st_join_table *tab);
+static void join_const_unlock_row(JOIN_TAB *tab);
static int join_read_always_key(JOIN_TAB *tab);
static int join_read_last_key(JOIN_TAB *tab);
static int join_no_more_records(READ_RECORD *info);
@@ -10968,8 +10969,12 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
else
j->type=JT_EQ_REF;
- j->read_record.unlock_row= (j->type == JT_EQ_REF)?
- join_read_key_unlock_row : rr_unlock_row;
+ if (j->type == JT_EQ_REF)
+ j->read_record.unlock_row= join_read_key_unlock_row;
+ else if (j->type == JT_CONST)
+ j->read_record.unlock_row= join_const_unlock_row;
+ else
+ j->read_record.unlock_row= rr_unlock_row;
DBUG_RETURN(0);
}
@@ -13232,6 +13237,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
/* Only happens with outer joins */
tab->read_first_record= tab->type == JT_SYSTEM ? join_read_system
: join_read_const;
+ tab->read_record.unlock_row= join_const_unlock_row;
if (!(table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) &&
(!jcl || jcl > 4) && !tab->ref.is_access_triggered())
push_index_cond(tab, tab->ref.key);
@@ -21532,6 +21538,19 @@ join_read_key_unlock_row(st_join_table *tab)
tab->ref.use_count--;
}
+/**
+ Rows from const tables are read once but potentially used
+ multiple times during execution of a query.
+ Ensure such rows are never unlocked during query execution.
+*/
+
+void
+join_const_unlock_row(JOIN_TAB *tab)
+{
+ DBUG_ASSERT(tab->type == JT_CONST);
+}
+
+
/*
ref access method implementation: "read_first" function
@@ -23938,8 +23957,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
else if (select && select->quick)
select->quick->need_sorted_output();
- tab->read_record.unlock_row= (tab->type == JT_EQ_REF) ?
- join_read_key_unlock_row : rr_unlock_row;
+ if (tab->type == JT_EQ_REF)
+ tab->read_record.unlock_row= join_read_key_unlock_row;
+ else if (tab->type == JT_CONST)
+ tab->read_record.unlock_row= join_const_unlock_row;
+ else
+ tab->read_record.unlock_row= rr_unlock_row;
} // QEP has been modified
1
0
[Commits] 8de6c5b2190: as MDEV-26106: [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record
by psergey 07 Jul '21
by psergey 07 Jul '21
07 Jul '21
revision-id: 8de6c5b21906a731c50a880bd5a87d5bedf9938a (mariadb-10.5.11-33-g8de6c5b2190)
parent(s): c262ccac027e71f22fc1329cf295a6bf687e4684
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-07-07 18:51:13 +0300
message:
as MDEV-26106: [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record
Port the following patch from MySQL:
commit 1b2e8ea269c80cb93cc79d8be934c40b1c58e947
Author: Kailasnath Nagarkar <kailasnath.nagarkar(a)oracle.com>
Date: Fri Nov 30 16:43:13 2018 +0530
Bug #20939184: INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE
LOCK ON THE RECORD
Issue:
------
Consdier tables t1 and t2 such that t1 has multiple rows
and join condition for t1 left join t2 results in only
single row from t2.
In this case, access to table t2 is const since there
is a single row that qualifies the join condition.
However, while executing the query, attempt is made to
unlock t2's row multiple times.
The current algorithm to fetch rows approximates to:
1) Retrieve the row for t1.
2) Retrieve the row for t2.
3) Apply the join conditions.
a) If condition evaluates to true:
Project the row to the result.
b) If condition evaluates to false:
i) If t2's qep_tab->not_null_complement is true,
unlock t2's row.
ii) Null-complement the row by calling
"evaluate_null_complemented_join_record()". In
this function qep_tab->not_null_complement is
set to false.
The t2's only one row, that qualifies join condition,
is unlocked in Step i) when t1's row is evaluated to
false.
When t1's next row is also evaluated to false, another
attempt is made to unlock t2's already unlocked row.
This results in following error being logged in error.log:
"[ERROR] InnoDB: Unlock row could not find a 3 mode lock on
the record. Current statement:
select * from t1 left join t2 ......"
Solution:
---------
When a table's access method is "const", set record unlock
method for this table to do no operation.
---
mysql-test/main/join_outer_innodb.result | 19 +++++++++++++++++++
mysql-test/main/join_outer_innodb.test | 17 +++++++++++++++++
sql/sql_select.cc | 31 +++++++++++++++++++++++++++----
3 files changed, 63 insertions(+), 4 deletions(-)
diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result
index 0b34a399d77..09a37a29702 100644
--- a/mysql-test/main/join_outer_innodb.result
+++ b/mysql-test/main/join_outer_innodb.result
@@ -496,3 +496,22 @@ natural right outer join t3;
drop table t1,t2,t3;
set optimizer_prune_level=@mdev4270_opl;
set optimizer_search_depth=@mdev4270_osd;
+#
+# Bug #20939184:INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE
+# RECORD
+#
+CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1,c2) ) engine=innodb;
+CREATE TABLE t2 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1), KEY (c2)) engine=innodb;
+INSERT INTO t1 VALUES (1,2,3),(2,3,4),(3,4,5);
+INSERT INTO t2 SELECT * FROM t1;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.c2=t2.c2 AND t2.c1=1 FOR UPDATE;
+c1 c2 c3 c1 c2 c3
+1 2 3 1 2 3
+2 3 4 NULL NULL NULL
+3 4 5 NULL NULL NULL
+UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c2 AND t2.c1 = 3 SET t1.c3 = RAND()*10;
+COMMIT;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+DROP TABLE t1,t2;
diff --git a/mysql-test/main/join_outer_innodb.test b/mysql-test/main/join_outer_innodb.test
index c26cd62fbc7..6b332f3d155 100644
--- a/mysql-test/main/join_outer_innodb.test
+++ b/mysql-test/main/join_outer_innodb.test
@@ -374,3 +374,20 @@ drop table t1,t2,t3;
set optimizer_prune_level=@mdev4270_opl;
set optimizer_search_depth=@mdev4270_osd;
+--echo #
+--echo # Bug #20939184:INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE
+--echo # RECORD
+--echo #
+CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1,c2) ) engine=innodb;
+CREATE TABLE t2 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1), KEY (c2)) engine=innodb;
+INSERT INTO t1 VALUES (1,2,3),(2,3,4),(3,4,5);
+INSERT INTO t2 SELECT * FROM t1;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+#unlocks rows in table t2 where c1 = 1
+SELECT * FROM t1 LEFT JOIN t2 ON t1.c2=t2.c2 AND t2.c1=1 FOR UPDATE;
+UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c2 AND t2.c1 = 3 SET t1.c3 = RAND()*10;
+COMMIT;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+DROP TABLE t1,t2;
+
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 8800fbf5b6d..ab54bd3b94d 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -189,6 +189,7 @@ static int join_read_system(JOIN_TAB *tab);
static int join_read_const(JOIN_TAB *tab);
static int join_read_key(JOIN_TAB *tab);
static void join_read_key_unlock_row(st_join_table *tab);
+static void join_const_unlock_row(JOIN_TAB *tab);
static int join_read_always_key(JOIN_TAB *tab);
static int join_read_last_key(JOIN_TAB *tab);
static int join_no_more_records(READ_RECORD *info);
@@ -10968,8 +10969,12 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
else
j->type=JT_EQ_REF;
- j->read_record.unlock_row= (j->type == JT_EQ_REF)?
- join_read_key_unlock_row : rr_unlock_row;
+ if (j->type == JT_EQ_REF)
+ j->read_record.unlock_row= join_read_key_unlock_row;
+ else if (j->type == JT_CONST)
+ j->read_record.unlock_row= join_const_unlock_row;
+ else
+ j->read_record.unlock_row= rr_unlock_row;
DBUG_RETURN(0);
}
@@ -13232,6 +13237,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
/* Only happens with outer joins */
tab->read_first_record= tab->type == JT_SYSTEM ? join_read_system
: join_read_const;
+ tab->read_record.unlock_row= join_const_unlock_row;
if (!(table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) &&
(!jcl || jcl > 4) && !tab->ref.is_access_triggered())
push_index_cond(tab, tab->ref.key);
@@ -21532,6 +21538,19 @@ join_read_key_unlock_row(st_join_table *tab)
tab->ref.use_count--;
}
+/**
+ Rows from const tables are read once but potentially used
+ multiple times during execution of a query.
+ Ensure such rows are never unlocked during query execution.
+*/
+
+void
+join_const_unlock_row(JOIN_TAB *tab)
+{
+ DBUG_ASSERT(tab->type == JT_CONST);
+}
+
+
/*
ref access method implementation: "read_first" function
@@ -23938,8 +23957,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
else if (select && select->quick)
select->quick->need_sorted_output();
- tab->read_record.unlock_row= (tab->type == JT_EQ_REF) ?
- join_read_key_unlock_row : rr_unlock_row;
+ if (tab->type == JT_EQ_REF)
+ tab->read_record.unlock_row= join_read_key_unlock_row;
+ else if (tab->type == JT_CONST)
+ tab->read_record.unlock_row= join_const_unlock_row;
+ else
+ tab->read_record.unlock_row= rr_unlock_row;
} // QEP has been modified
1
0
07 Jul '21
revision-id: a2afe9cb1ed23ca00556858583f476220297fa33 (mariadb-10.5.2-1047-ga2afe9cb1ed)
parent(s): be9ab9dcff48462d053769927e21d60eb3e32b76
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-07-07 18:30:20 +0300
message:
Update Xpand to: Change Xpand Version to 6.0.1, part 2
---
storage/xpand | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/storage/xpand b/storage/xpand
index ea285dff95c..e578dbf7ed7 160000
--- a/storage/xpand
+++ b/storage/xpand
@@ -1 +1 @@
-Subproject commit ea285dff95c0c192223bea3a089d1e0a54228c1d
+Subproject commit e578dbf7ed7d18c62ca18c64ea4c6bedbf6b0a11
1
0
revision-id: e578dbf7ed7d18c62ca18c64ea4c6bedbf6b0a11 (10.5.8-5ES-8-ge578dbf)
parent(s): ea285dff95c0c192223bea3a089d1e0a54228c1d
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-07-07 18:28:32 +0300
message:
Change Xpand Version to 6.0.1, part 2
---
ha_xpand.cc | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/ha_xpand.cc b/ha_xpand.cc
index 505d74f..0f91022 100644
--- a/ha_xpand.cc
+++ b/ha_xpand.cc
@@ -2316,7 +2316,7 @@ maria_declare_plugin(xpand) {
PLUGIN_LICENSE_GPL, /* Plugin Licence */
xpand_init, /* Plugin Entry Point */
xpand_deinit, /* Plugin Deinitializer */
- 0x0503, /* Hex Version Number (9.3) */
+ 0x0600, /* Hex Version Number (9.3) */
xpand_status_vars, /* Status Variables */
xpand_system_variables, /* System Variables */
"6.0.1", /* String Version */
1
0
revision-id: e405ba6e2ef4b2de8063705fd9caa133e03aab1a (mariadb-10.5.4-862-ge405ba6e2ef)
parent(s): 0b743db642eaccdb56b607f3a68c6a4926ce6228
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-07-07 15:22:55 +0300
message:
Update Xpand Plugin to 6.0.1
---
storage/xpand | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/storage/xpand b/storage/xpand
index e5d45a713d7..e16f905fd7c 160000
--- a/storage/xpand
+++ b/storage/xpand
@@ -1 +1 @@
-Subproject commit e5d45a713d7369ad1091948d7b8fe3c854f94b6f
+Subproject commit e16f905fd7cb67e2ad82dd46c0a9895e70d86403
1
0
revision-id: e16f905fd7cb67e2ad82dd46c0a9895e70d86403 (10.5.8-5ES-6-ge16f905)
parent(s): e5d45a713d7369ad1091948d7b8fe3c854f94b6f
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-07-07 15:19:04 +0300
message:
Update Xpand Plugin vesion to 6.0.1
---
ha_xpand.cc | 4 ++--
mysql-test/xpand/xpand_plugin.result | 4 ++--
2 files changed, 4 insertions(+), 4 deletions(-)
diff --git a/ha_xpand.cc b/ha_xpand.cc
index e5f088a..354e694 100644
--- a/ha_xpand.cc
+++ b/ha_xpand.cc
@@ -2315,10 +2315,10 @@ maria_declare_plugin(xpand) {
PLUGIN_LICENSE_GPL, /* Plugin Licence */
xpand_init, /* Plugin Entry Point */
xpand_deinit, /* Plugin Deinitializer */
- 0x0503, /* Hex Version Number (9.3) */
+ 0x0600, /* Hex Version Number (9.3) */
xpand_status_vars, /* Status Variables */
xpand_system_variables, /* System Variables */
- "5.3.16", /* String Version */
+ "6.0.1", /* String Version */
MariaDB_PLUGIN_MATURITY_STABLE /* Maturity Level */
} maria_declare_plugin_end;
diff --git a/mysql-test/xpand/xpand_plugin.result b/mysql-test/xpand/xpand_plugin.result
index 9862c4c..018d7ff 100644
--- a/mysql-test/xpand/xpand_plugin.result
+++ b/mysql-test/xpand/xpand_plugin.result
@@ -6,8 +6,8 @@ from information_schema.plugins
WHERE PLUGIN_NAME='XPAND';
PLUGIN_NAME XPAND
PLUGIN_MATURITY Stable
-PLUGIN_VERSION 5.3
-PLUGIN_AUTH_VERSION 5.3.16
+PLUGIN_VERSION 6.0
+PLUGIN_AUTH_VERSION 6.0.1
#
# Xpand supports a replication mode where both master and slave MariaDB
# are connected to the same Clustrix instance. But it must NOT be
1
0
[Commits] 2f2bf3d: MDEV-26095 Infinite recursion when processing embedded recursive CTE
by IgorBabaev 06 Jul '21
by IgorBabaev 06 Jul '21
06 Jul '21
revision-id: 2f2bf3df7c4318a1e94a331b304c3c18fcafd7bb (mariadb-10.2.31-1040-g2f2bf3d)
parent(s): 99f700a820ef90b5b36ef765fb1532145ab3e907
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-07-06 14:38:32 -0700
message:
MDEV-26095 Infinite recursion when processing embedded recursive CTE
with missing RECURSIVE
If a table reference r used inthe specification of a CTE whose definition
is contained in the WITH clause where RECURSIVE is omitted then this table
reference cannot be considered as a recursive table reference even if it is
used in the query that specifies CTE whose name is r. It can be considered
only as a reference to an embedding CTE or to a temporary table or to
a base table/view. If there is no such object with name r then an error
message must be reported.
This patch fixes the code that actually in some cases resolved r as a
reference to the CTE whose specification contained r if its name was r
in spite of the fact that r was not considered as a recursive CTE.
This happened in the cases when the definition of r was used in the
specification of another CTE. Such wrong name resolution for r led to an
infinite recursive invocations of the parser that ultimately crashed the
server.
This bug is a result of the fix for mdev-13780 that was not quite correct.
Approved by Oleksandr Byelkin <sanja(a)mariadb.com>
---
mysql-test/r/cte_nonrecursive.result | 25 +++++++++++++++++++++++++
mysql-test/t/cte_nonrecursive.test | 23 +++++++++++++++++++++++
sql/sql_cte.cc | 4 +++-
3 files changed, 51 insertions(+), 1 deletion(-)
diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index c1d7fd0..5cc5a25 100644
--- a/mysql-test/r/cte_nonrecursive.result
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -2019,4 +2019,29 @@ drop procedure sp1;
drop procedure sp2;
drop procedure sp3;
drop table t1;
+#
+# MDEV-26095: missing RECURSIVE for the recursive definition of CTE
+# embedded into another CTE definition
+#
+create table t1 (a int);
+insert into t1 values (5), (7);
+with cte_e as (
+with recursive cte_r as (
+select a from t1 union select a+1 as a from cte_r r where a < 10
+) select * from cte_r
+) select * from cte_e;
+a
+5
+7
+6
+8
+9
+10
+with cte_e as (
+with cte_r as (
+select a from t1 union select a+1 as a from cte_r r where a < 10
+) select * from cte_r
+) select * from cte_e;
+ERROR 42S02: Table 'test.cte_r' doesn't exist
+drop table t1;
# End of 10.2 tests
diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
index cbe4f8b..68dbc0c 100644
--- a/mysql-test/t/cte_nonrecursive.test
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -1492,4 +1492,27 @@ drop procedure sp3;
drop table t1;
+--echo #
+--echo # MDEV-26095: missing RECURSIVE for the recursive definition of CTE
+--echo # embedded into another CTE definition
+--echo #
+
+create table t1 (a int);
+insert into t1 values (5), (7);
+
+with cte_e as (
+ with recursive cte_r as (
+ select a from t1 union select a+1 as a from cte_r r where a < 10
+ ) select * from cte_r
+) select * from cte_e;
+
+--ERROR ER_NO_SUCH_TABLE
+with cte_e as (
+ with cte_r as (
+ select a from t1 union select a+1 as a from cte_r r where a < 10
+ ) select * from cte_r
+) select * from cte_e;
+
+drop table t1;
+
--echo # End of 10.2 tests
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index 9dad33d..702db8f 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -1256,6 +1256,7 @@ bool With_element::is_anchor(st_select_lex *sel)
With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table)
{
With_element *found= NULL;
+ With_clause *containing_with_clause= NULL;
st_select_lex_unit *master_unit;
st_select_lex *outer_sl;
for (st_select_lex *sl= this; sl; sl= outer_sl)
@@ -1268,6 +1269,7 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table)
*/
With_clause *attached_with_clause= sl->get_with_clause();
if (attached_with_clause &&
+ attached_with_clause != containing_with_clause &&
(found= attached_with_clause->find_table_def(table, NULL)))
break;
master_unit= sl->master_unit();
@@ -1275,7 +1277,7 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table)
With_element *with_elem= sl->get_with_element();
if (with_elem)
{
- With_clause *containing_with_clause= with_elem->get_owner();
+ containing_with_clause= with_elem->get_owner();
With_element *barrier= containing_with_clause->with_recursive ?
NULL : with_elem;
if ((found= containing_with_clause->find_table_def(table, barrier)))
1
0
[Commits] c7443a0911a: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
by psergey 01 Jul '21
by psergey 01 Jul '21
01 Jul '21
revision-id: c7443a0911a98dccfc9c5bda4c2f4d9052516d8f (mariadb-10.4.20-22-gc7443a0911a)
parent(s): eebe2090c848b5cedc5235473d80dbd2c25d2943
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-07-01 01:08:28 +0300
message:
MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
Post-merge fix in 10.4: add a testcase for pushdown into IN subquery
---
mysql-test/main/derived_cond_pushdown.result | 57 +++++++++++++++++++++++++++-
mysql-test/main/derived_cond_pushdown.test | 19 +++++++++-
2 files changed, 74 insertions(+), 2 deletions(-)
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 76918d975cc..01a863ccb8b 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -10814,9 +10814,64 @@ EXPLAIN
}
}
}
+# Extra test for 10.4+: Check that this works for pushdown into IN
+# subqueries:
+create table t4 (a int, b int, c decimal);
+insert into t4 select a,a,a from t1;
+# The subquery must be materialized and must have
+# "attached_condition": "t1.a + 1 > 10",
+# "having_condition": "`f1(a)` > 1 and `sum(b)` > 123",
+explain format=json
+select *
+from t4
+where
+(a,b,c) in (select a, f1(a), sum(b) from t1 group by a, f1(a))
+and
+(a+1) > 10 AND b > 1 and c>123;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t4",
+ "access_type": "ALL",
+ "rows": 3,
+ "filtered": 100,
+ "attached_condition": "t4.a + 1 > 10 and t4.b > 1 and t4.c > 123 and t4.a is not null and t4.b is not null and t4.c is not null"
+ },
+ "table": {
+ "table_name": "<subquery2>",
+ "access_type": "eq_ref",
+ "possible_keys": ["distinct_key"],
+ "key": "distinct_key",
+ "key_length": "23",
+ "used_key_parts": ["a", "f1(a)", "sum(b)"],
+ "ref": ["test.t4.a", "test.t4.b", "test.t4.c"],
+ "rows": 1,
+ "filtered": 100,
+ "attached_condition": "t4.c = `<subquery2>`.`sum(b)`",
+ "materialized": {
+ "unique": 1,
+ "query_block": {
+ "select_id": 2,
+ "having_condition": "`f1(a)` > 1 and `sum(b)` > 123",
+ "temporary_table": {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 3,
+ "filtered": 100,
+ "attached_condition": "t1.a + 1 > 10"
+ }
+ }
+ }
+ }
+ }
+ }
+}
drop view v2;
drop function f1;
-drop table t1;
+drop table t1, t4;
# End of 10.2 tests
#
# MDEV-14579: pushdown conditions into materialized views/derived tables
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index bc5034621b4..9544ad34572 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -2305,9 +2305,26 @@ select a, f1(a), sum(b) from t1 group by a, f1(a);
explain format=json
select * from v2 where (s+1) > 10 AND a > 1 and a2>123;
+--echo # Extra test for 10.4+: Check that this works for pushdown into IN
+--echo # subqueries:
+
+create table t4 (a int, b int, c decimal);
+insert into t4 select a,a,a from t1;
+
+--echo # The subquery must be materialized and must have
+--echo # "attached_condition": "t1.a + 1 > 10",
+--echo # "having_condition": "`f1(a)` > 1 and `sum(b)` > 123",
+explain format=json
+select *
+from t4
+where
+ (a,b,c) in (select a, f1(a), sum(b) from t1 group by a, f1(a))
+ and
+ (a+1) > 10 AND b > 1 and c>123;
+
drop view v2;
drop function f1;
-drop table t1;
+drop table t1, t4;
--echo # End of 10.2 tests
--echo #
1
0
revision-id: eebe2090c848b5cedc5235473d80dbd2c25d2943 (mariadb-10.4.20-21-geebe2090c84)
parent(s): a1e2ca057dda4dc434f057ce9391aa7afd9b5583 4a6e2d343745c11086c05f0041a8267591bb073c
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-06-30 18:41:46 +0300
message:
Merge 10.3 -> 10.4
.gitignore | 3 +
cmake/cpack_rpm.cmake | 6 +-
mysql-test/main/cte_nonrecursive.result | 55 ++++++++
mysql-test/main/cte_nonrecursive.test | 29 ++++
mysql-test/main/derived_cond_pushdown.result | 147 +++++++++++++++++++++
mysql-test/main/derived_cond_pushdown.test | 70 ++++++++++
mysql-test/main/gis-json.result | 10 ++
mysql-test/main/gis-json.test | 7 +
mysql-test/main/information_schema.result | 2 +
.../main/information_schema_all_engines.result | 12 +-
mysql-test/suite/funcs_1/r/is_columns_is.result | 4 +
.../suite/funcs_1/r/is_columns_is_embedded.result | 4 +
mysql-test/suite/funcs_1/r/is_tables_is.result | 92 +++++++++++++
.../suite/funcs_1/r/is_tables_is_embedded.result | 92 +++++++++++++
.../galera/r/galera_sst_rsync_logbasename.result | 1 +
mysql-test/suite/galera/r/lp1376747-4.result | 2 +-
mysql-test/suite/galera/t/lp1376747-4.test | 2 +-
sql/handler.h | 2 +
sql/item_create.cc | 5 +-
sql/lex.h | 7 +-
sql/spatial.cc | 6 +
sql/sql_derived.cc | 63 ++++++++-
sql/sql_derived.h | 5 +
sql/sql_lex.cc | 13 +-
sql/sql_show.cc | 83 ++++++++++++
storage/innobase/os/os0file.cc | 38 +++++-
26 files changed, 741 insertions(+), 19 deletions(-)
diff --cc .gitignore
index c10f08b20d7,c26767717c6..b3cbd1d430d
--- a/.gitignore
+++ b/.gitignore
@@@ -554,73 -521,4 +554,76 @@@ compile_commands.jso
# Visual Studio Code workspace
.vscode/
+# Clion && other JetBrains ides
+.idea
+
+ .cache/clangd
++
++
+client/mariadb
+client/mariadb-admin
+client/mariadb-binlog
+client/mariadb-check
+client/mariadb-dump
+client/mariadb-import
+client/mariadb-plugin
+client/mariadb-show
+client/mariadb-slap
+client/mariadb-test
+client/mariadb-upgrade
+extra/mariabackup/mariadb-backup
+extra/mariadb-waitpid
+extra/mariadbd-safe-helper
+libmysqld/examples/mariadb-client-test-embedded
+libmysqld/examples/mariadb-embedded
+libmysqld/examples/mariadb-test-embedded
+man/mariadb-access.1
+man/mariadb-admin.1
+man/mariadb-backup.1
+man/mariadb-binlog.1
+man/mariadb-check.1
+man/mariadb-client-test-embedded.1
+man/mariadb-client-test.1
+man/mariadb-convert-table-format.1
+man/mariadb-dump.1
+man/mariadb-dumpslow.1
+man/mariadb-embedded.1
+man/mariadb-find-rows.1
+man/mariadb-fix-extensions.1
+man/mariadb-hotcopy.1
+man/mariadb-import.1
+man/mariadb-install-db.1
+man/mariadb-ldb.1
+man/mariadb-plugin.1
+man/mariadb-secure-installation.1
+man/mariadb-setpermission.1
+man/mariadb-show.1
+man/mariadb-slap.1
+man/mariadb-test-embedded.1
+man/mariadb-test.1
+man/mariadb-tzinfo-to-sql.1
+man/mariadb-upgrade.1
+man/mariadb-waitpid.1
+man/mariadb.1
+man/mariadbd-multi.1
+man/mariadbd-safe-helper.1
+man/mariadbd-safe.1
+man/mariadbd.8
+scripts/mariadb-access
+scripts/mariadb-convert-table-format
+scripts/mariadb-dumpslow
+scripts/mariadb-find-rows
+scripts/mariadb-fix-extensions
+scripts/mariadb-hotcopy
+scripts/mariadb-install-db
+scripts/mariadb-secure-installation
+scripts/mariadb-setpermission
+scripts/mariadbd-multi
+scripts/mariadbd-safe
+sql/mariadb-tzinfo-to-sql
+sql/mariadbd
+storage/rocksdb/mariadb-ldb
+tests/mariadb-client-test
+versioninfo_dll.rc
+versioninfo_exe.rc
+win/packaging/ca/symlinks.cc
diff --cc mysql-test/main/information_schema.result
index 3534c4bd337,0559c42350d..9ba19cca7e1
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@@ -65,9 -65,9 +65,10 @@@ GEOMETRY_COLUMN
GLOBAL_STATUS
GLOBAL_VARIABLES
INDEX_STATISTICS
+ KEYWORDS
KEY_CACHES
KEY_COLUMN_USAGE
+OPTIMIZER_TRACE
PARAMETERS
PARTITIONS
PLUGINS
diff --cc mysql-test/main/information_schema_all_engines.result
index 9ba4d20c76d,41d6ab3b2f4..1269972d127
--- a/mysql-test/main/information_schema_all_engines.result
+++ b/mysql-test/main/information_schema_all_engines.result
@@@ -41,9 -41,9 +41,10 @@@ INNODB_SYS_VIRTUA
INNODB_TABLESPACES_ENCRYPTION
INNODB_TABLESPACES_SCRUBBING
INNODB_TRX
+ KEYWORDS
KEY_CACHES
KEY_COLUMN_USAGE
+OPTIMIZER_TRACE
PARAMETERS
PARTITIONS
PLUGINS
@@@ -122,9 -123,9 +124,10 @@@ INNODB_SYS_VIRTUAL TABLE_I
INNODB_TABLESPACES_ENCRYPTION SPACE
INNODB_TABLESPACES_SCRUBBING SPACE
INNODB_TRX trx_id
+ KEYWORDS WORD
KEY_CACHES KEY_CACHE_NAME
KEY_COLUMN_USAGE CONSTRAINT_SCHEMA
+OPTIMIZER_TRACE QUERY
PARAMETERS SPECIFIC_SCHEMA
PARTITIONS TABLE_SCHEMA
PLUGINS PLUGIN_NAME
@@@ -203,9 -205,9 +207,10 @@@ INNODB_SYS_VIRTUAL TABLE_I
INNODB_TABLESPACES_ENCRYPTION SPACE
INNODB_TABLESPACES_SCRUBBING SPACE
INNODB_TRX trx_id
+ KEYWORDS WORD
KEY_CACHES KEY_CACHE_NAME
KEY_COLUMN_USAGE CONSTRAINT_SCHEMA
+OPTIMIZER_TRACE QUERY
PARAMETERS SPECIFIC_SCHEMA
PARTITIONS TABLE_SCHEMA
PLUGINS PLUGIN_NAME
@@@ -360,9 -362,9 +366,10 @@@ Database: information_schem
| INNODB_TABLESPACES_ENCRYPTION |
| INNODB_TABLESPACES_SCRUBBING |
| INNODB_TRX |
+ | KEYWORDS |
| KEY_CACHES |
| KEY_COLUMN_USAGE |
+| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
@@@ -431,9 -434,9 +439,10 @@@ Database: INFORMATION_SCHEM
| INNODB_TABLESPACES_ENCRYPTION |
| INNODB_TABLESPACES_SCRUBBING |
| INNODB_TRX |
+ | KEYWORDS |
| KEY_CACHES |
| KEY_COLUMN_USAGE |
+| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
@@@ -465,5 -469,5 +475,5 @@@ Wildcard: inf_rmation_schem
| information_schema |
SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') GROUP BY TABLE_SCHEMA;
table_schema count(*)
- information_schema 66
-information_schema 67
++information_schema 68
mysql 31
diff --cc sql/sql_derived.cc
index fc01dcdc750,93dc62828ac..a4e0fd6b683
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@@ -25,16 -25,13 +25,16 @@@
#include "mariadb.h" /* NO_EMBEDDED_ACCESS_CHECKS */
#include "sql_priv.h"
#include "unireg.h"
--#include "sql_derived.h"
#include "sql_select.h"
+#include "derived_handler.h"
#include "sql_base.h"
#include "sql_view.h" // check_duplicate_names
#include "sql_acl.h" // SELECT_ACL
#include "sql_class.h"
++#include "sql_derived.h"
#include "sql_cte.h"
+#include "my_json_writer.h"
+#include "opt_trace.h"
typedef bool (*dt_processor)(THD *thd, LEX *lex, TABLE_LIST *derived);
@@@ -1359,63 -1264,89 +1359,124 @@@ bool mysql_derived_reinit(THD *thd, LE
}
+ /*
+ @brief
+ Given condition cond and transformer+argument, try transforming as many
+ conjuncts as possible.
+
+ @detail
+ The motivation of this function is to convert the condition that's being
+ pushed into a WHERE clause with derived_field_transformer_for_where or
+ with derived_grouping_field_transformer_for_where.
+ The transformer may fail for some sub-condition, in this case we want to
+ convert the most restrictive part of the condition that can be pushed.
+
+ This function only does it for top-level AND: conjuncts that could not be
+ converted are dropped.
+
+ @return
+ Converted condition, or NULL if nothing could be converted
+ */
+
-static
+ Item *transform_condition_or_part(THD *thd,
+ Item *cond,
+ Item_transformer transformer,
+ uchar *arg)
+ {
+ if (cond->type() != Item::COND_ITEM ||
+ ((Item_cond*) cond)->functype() != Item_func::COND_AND_FUNC)
+ {
+ Item *new_item= cond->transform(thd, transformer, arg);
+ // Indicate that the condition is not pushable
+ if (!new_item)
+ cond->clear_extraction_flag();
+ return new_item;
+ }
+
+ List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+ Item *item;
+ while ((item=li++))
+ {
+ Item *new_item= item->transform(thd, transformer, arg);
+ if (!new_item)
+ {
+ // Indicate that the condition is not pushable
+ item->clear_extraction_flag();
+ li.remove();
+ }
+ else
+ li.replace(new_item);
+ }
+
+ switch (((Item_cond*) cond)->argument_list()->elements)
+ {
+ case 0:
+ return NULL;
+ case 1:
+ return ((Item_cond*) cond)->argument_list()->head();
+ default:
+ return cond;
+ }
+ }
+
+
/**
@brief
- Extract the condition depended on derived table/view and pushed it there
+ Extract condition that can be pushed into a derived table/view
- @param thd The thread handle
- @param cond The condition from which to extract the pushed condition
- @param derived The reference to the derived table/view
+ @param thd the thread handle
+ @param cond current condition
+ @param derived the reference to the derived table/view
@details
- This functiom builds the most restrictive condition depending only on
- the derived table/view that can be extracted from the condition cond.
- The built condition is pushed into the having clauses of the
- selects contained in the query specifying the derived table/view.
- The function also checks for each select whether any condition depending
- only on grouping fields can be extracted from the pushed condition.
- If so, it pushes the condition over grouping fields into the where
- clause of the select.
-
- @retval
- true if an error is reported
- false otherwise
+ This function builds the most restrictive condition depending only on
+ the derived table/view (directly or indirectly through equality) that
+ can be extracted from the given condition cond and pushes it into the
+ derived table/view.
+
+ Example of the transformation:
+
+ SELECT *
+ FROM t1,
+ (
+ SELECT x,MAX(y) AS max_y
+ FROM t2
+ GROUP BY x
+ ) AS d_tab
+ WHERE d_tab.x>1 AND d_tab.max_y<30;
+
+ =>
+
+ SELECT *
+ FROM t1,
+ (
+ SELECT x,z,MAX(y) AS max_y
+ FROM t2
+ WHERE x>1
+ HAVING max_y<30
+ GROUP BY x
+ ) AS d_tab
+ WHERE d_tab.x>1 AND d_tab.max_y<30;
+
+ In details:
+ 1. Check what pushable formula can be extracted from cond
+ 2. Build a clone PC of the formula that can be extracted
+ (the clone is built only if the extracted formula is a AND subformula
+ of cond or conjunction of such subformulas)
+ Do for every select specifying derived table/view:
+ 3. If there is no HAVING clause prepare PC to be conjuncted with
+ WHERE clause of the select. Otherwise do 4-7.
+ 4. Check what formula PC_where can be extracted from PC to be pushed
+ into the WHERE clause of the select
+ 5. Build PC_where and if PC_where is a conjunct(s) of PC remove it from PC
+ getting PC_having
+ 6. Prepare PC_where to be conjuncted with the WHERE clause of the select
+ 7. Prepare PC_having to be conjuncted with the HAVING clause of the select
+ @note
+ This method is similar to pushdown_cond_for_in_subquery()
+
+ @retval TRUE if an error occurs
+ @retval FALSE otherwise
*/
bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
diff --cc sql/sql_derived.h
index 403277d65c9,093191e62a7..6100b4b4d7e
--- a/sql/sql_derived.h
+++ b/sql/sql_derived.h
@@@ -22,7 -22,8 +22,12 @@@ struct LEX
bool mysql_handle_derived(LEX *lex, uint phases);
bool mysql_handle_single_derived(LEX *lex, TABLE_LIST *derived, uint phases);
-bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived);
+
++Item *transform_condition_or_part(THD *thd,
++ Item *cond,
++ Item_transformer transformer,
++ uchar *arg);
+
bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived);
#endif /* SQL_DERIVED_INCLUDED */
diff --cc sql/sql_lex.cc
index 93a4fcbe277,2a337b0f842..1ec5d0b0550
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@@ -31,11 -31,8 +31,12 @@@
#include "sql_select.h"
#include "sql_cte.h"
#include "sql_signal.h"
++#include "sql_derived.h"
+#include "sql_truncate.h" // Sql_cmd_truncate_table
+#include "sql_admin.h" // Sql_cmd_analyze/Check..._table
#include "sql_partition.h"
-
+#include "sql_partition_admin.h" // Sql_cmd_alter_table_*_part
+#include "event_parse_data.h"
void LEX::parse_error(uint err_number)
{
@@@ -9000,1522 -8388,16 +9001,1524 @@@ void st_select_lex::register_unit(SELEC
}
-bool LEX::tvc_finalize_derived()
+void st_select_lex::add_statistics(SELECT_LEX_UNIT *unit)
{
- derived_tables|= DERIVED_SUBQUERY;
- if (unlikely(!expr_allows_subselect || sql_command == (int)SQLCOM_PURGE))
+ for (;
+ unit;
+ unit= unit->next_unit())
+ for(SELECT_LEX *child= unit->first_select();
+ child;
+ child= child->next_select())
+ {
+ /*
+ A subselect can add fields to an outer select.
+ Reserve space for them.
+ */
+ select_n_where_fields+= child->select_n_where_fields;
+ /*
+ Aggregate functions in having clause may add fields
+ to an outer select. Count them also.
+ */
+ select_n_having_items+= child->select_n_having_items;
+ }
+}
+
+
+bool LEX::main_select_push(bool service)
+{
+ DBUG_ENTER("LEX::main_select_push");
+ current_select_number= ++thd->lex->stmt_lex->current_select_number;
+ builtin_select.select_number= current_select_number;
+ builtin_select.is_service_select= service;
+ if (push_select(&builtin_select))
+ DBUG_RETURN(TRUE);
+ DBUG_RETURN(FALSE);
+}
+
+void Lex_select_lock::set_to(SELECT_LEX *sel)
+{
+ if (defined_lock)
{
- thd->parse_error();
+ if (sel->master_unit() &&
+ sel == sel->master_unit()->fake_select_lex)
+ sel->master_unit()->set_lock_to_the_last_select(*this);
+ else
+ {
+ sel->parent_lex->safe_to_cache_query= 0;
+ if (update_lock)
+ {
+ sel->lock_type= TL_WRITE;
+ sel->set_lock_for_tables(TL_WRITE, false);
+ }
+ else
+ {
+ sel->lock_type= TL_READ_WITH_SHARED_LOCKS;
+ sel->set_lock_for_tables(TL_READ_WITH_SHARED_LOCKS, false);
+ }
+ }
+ }
+}
+
+bool Lex_order_limit_lock::set_to(SELECT_LEX *sel)
+{
+ /*TODO: lock */
+ //if (lock.defined_lock && sel == sel->master_unit()->fake_select_lex)
+ // return TRUE;
+ if (lock.defined_timeout)
+ {
+ THD *thd= sel->parent_lex->thd;
+ if (set_statement_var_if_exists(thd,
+ C_STRING_WITH_LEN("lock_wait_timeout"),
+ lock.timeout) ||
+ set_statement_var_if_exists(thd,
+ C_STRING_WITH_LEN("innodb_lock_wait_timeout"),
+ lock.timeout))
+ return TRUE;
+ }
+ lock.set_to(sel);
+ sel->explicit_limit= limit.explicit_limit;
+ sel->select_limit= limit.select_limit;
+ sel->offset_limit= limit.offset_limit;
+ if (order_list)
+ {
+ if (sel->get_linkage() != GLOBAL_OPTIONS_TYPE &&
+ sel->olap != UNSPECIFIED_OLAP_TYPE &&
+ (sel->get_linkage() != UNION_TYPE || sel->braces))
+ {
+ my_error(ER_WRONG_USAGE, MYF(0),
+ "CUBE/ROLLUP", "ORDER BY");
+ return TRUE;
+ }
+ sel->order_list= *(order_list);
+ }
+ sel->is_set_query_expr_tail= true;
+ return FALSE;
+}
+
+
+static void change_item_list_context(List<Item> *list,
+ Name_resolution_context *context)
+{
+ List_iterator_fast<Item> it (*list);
+ Item *item;
+ while((item= it++))
+ {
+ item->walk(&Item::change_context_processor, FALSE, (void *)context);
+ }
+}
+
+
+bool LEX::insert_select_hack(SELECT_LEX *sel)
+{
+ DBUG_ENTER("LEX::insert_select_hack");
+
+ DBUG_ASSERT(first_select_lex() == &builtin_select);
+ DBUG_ASSERT(sel != NULL);
+
+ DBUG_ASSERT(builtin_select.first_inner_unit() == NULL);
+
+ if (builtin_select.link_prev)
+ {
+ if ((*builtin_select.link_prev= builtin_select.link_next))
+ ((st_select_lex *)builtin_select.link_next)->link_prev=
+ builtin_select.link_prev;
+ builtin_select.link_prev= NULL; // indicator of removal
+ }
+
+ if (set_main_unit(sel->master_unit()))
return true;
+
+ DBUG_ASSERT(builtin_select.table_list.elements == 1);
+ TABLE_LIST *insert_table= builtin_select.table_list.first;
+
+ if (!(insert_table->next_local= sel->table_list.first))
+ {
+ sel->table_list.next= &insert_table->next_local;
+ }
+ sel->table_list.first= insert_table;
+ sel->table_list.elements++;
+ insert_table->select_lex= sel;
+
+ sel->context.first_name_resolution_table= insert_table;
+ builtin_select.context= sel->context;
+ change_item_list_context(&field_list, &sel->context);
+
+ if (sel->tvc && !sel->next_select() &&
+ (sql_command == SQLCOM_INSERT_SELECT ||
+ sql_command == SQLCOM_REPLACE_SELECT))
+ {
+ DBUG_PRINT("info", ("'Usual' INSERT detected"));
+ many_values= sel->tvc->lists_of_values;
+ sel->options= sel->tvc->select_options;
+ sel->tvc= NULL;
+ if (sql_command == SQLCOM_INSERT_SELECT)
+ sql_command= SQLCOM_INSERT;
+ else
+ sql_command= SQLCOM_REPLACE;
+ }
+
+
+ for (SELECT_LEX *sel= all_selects_list;
+ sel;
+ sel= sel->next_select_in_list())
+ {
+ if (sel->select_number != 1)
+ sel->select_number--;
+ };
+
+ DBUG_RETURN(FALSE);
+}
+
+
+/**
+ Create an Item_singlerow_subselect for a query expression.
+*/
+
+Item *LEX::create_item_query_expression(THD *thd,
+ st_select_lex_unit *unit)
+{
+ if (clause_that_disallows_subselect)
+ {
+ my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0),
+ clause_that_disallows_subselect);
+ return NULL;
+ }
+
+ // Add the subtree of subquery to the current SELECT_LEX
+ SELECT_LEX *curr_sel= select_stack_head();
+ DBUG_ASSERT(current_select == curr_sel ||
+ (curr_sel == NULL && current_select == &builtin_select));
+ if (!curr_sel)
+ {
+ curr_sel= &builtin_select;
+ curr_sel->register_unit(unit, &curr_sel->context);
+ curr_sel->add_statistics(unit);
+ }
+
+ return new (thd->mem_root)
+ Item_singlerow_subselect(thd, unit->first_select());
+}
+
+
+SELECT_LEX_UNIT *LEX::parsed_select_expr_start(SELECT_LEX *s1, SELECT_LEX *s2,
+ enum sub_select_type unit_type,
+ bool distinct)
+{
+ SELECT_LEX_UNIT *res;
+ SELECT_LEX *sel1;
+ SELECT_LEX *sel2;
+ if (!s1->next_select())
+ sel1= s1;
+ else
+ {
+ sel1= wrap_unit_into_derived(s1->master_unit());
+ if (!sel1)
+ return NULL;
+ }
+ if (!s2->next_select())
+ sel2= s2;
+ else
+ {
+ sel2= wrap_unit_into_derived(s2->master_unit());
+ if (!sel2)
+ return NULL;
+ }
+ sel1->link_neighbour(sel2);
+ sel2->set_linkage_and_distinct(unit_type, distinct);
+ sel2->first_nested= sel1->first_nested= sel1;
+ res= create_unit(sel1);
+ if (res == NULL)
+ return NULL;
+ res->pre_last_parse= sel1;
+ push_select(res->fake_select_lex);
+ return res;
+}
+
+
+SELECT_LEX_UNIT *LEX::parsed_select_expr_cont(SELECT_LEX_UNIT *unit,
+ SELECT_LEX *s2,
+ enum sub_select_type unit_type,
+ bool distinct, bool oracle)
+{
+ DBUG_ASSERT(!s2->next_select());
+ SELECT_LEX *sel1= s2;
+ SELECT_LEX *last= unit->pre_last_parse->next_select();
+
+ int cmp= oracle? 0 : cmp_unit_op(unit_type, last->get_linkage());
+ if (cmp == 0)
+ {
+ sel1->first_nested= last->first_nested;
+ }
+ else if (cmp > 0)
+ {
+ last->first_nested= unit->pre_last_parse;
+ sel1->first_nested= last;
+ }
+ else /* cmp < 0 */
+ {
+ SELECT_LEX *first_in_nest= last->first_nested;
+ if (first_in_nest->first_nested != first_in_nest)
+ {
+ /* There is a priority jump starting from first_in_nest */
+ if ((last= create_priority_nest(first_in_nest)) == NULL)
+ return NULL;
+ unit->fix_distinct();
+ }
+ sel1->first_nested= last->first_nested;
+ }
+ last->link_neighbour(sel1);
+ sel1->set_master_unit(unit);
+ sel1->set_linkage_and_distinct(unit_type, distinct);
+ unit->pre_last_parse= last;
+ return unit;
+}
+
+
+/**
+ Add primary expression as the next term in a given query expression body
+ pruducing a new query expression body
+*/
+
+SELECT_LEX_UNIT *
+LEX::add_primary_to_query_expression_body(SELECT_LEX_UNIT *unit,
+ SELECT_LEX *sel,
+ enum sub_select_type unit_type,
+ bool distinct,
+ bool oracle)
+{
+ SELECT_LEX *sel2= sel;
+ if (sel->master_unit() && sel->master_unit()->first_select()->next_select())
+ {
+ sel2= wrap_unit_into_derived(sel->master_unit());
+ if (!sel2)
+ return NULL;
+ }
+ SELECT_LEX *sel1= unit->first_select();
+ if (!sel1->next_select())
+ unit= parsed_select_expr_start(sel1, sel2, unit_type, distinct);
+ else
+ unit= parsed_select_expr_cont(unit, sel2, unit_type, distinct, oracle);
+ return unit;
+}
+
+
+/**
+ Add query primary to a parenthesized query primary
+ pruducing a new query expression body
+*/
+
+SELECT_LEX_UNIT *
+LEX::add_primary_to_query_expression_body_ext_parens(
+ SELECT_LEX_UNIT *unit,
+ SELECT_LEX *sel,
+ enum sub_select_type unit_type,
+ bool distinct)
+{
+ SELECT_LEX *sel1= unit->first_select();
+ if (unit->first_select()->next_select())
+ {
+ sel1= wrap_unit_into_derived(unit);
+ if (!sel1)
+ return NULL;
+ if (!create_unit(sel1))
+ return NULL;
+ }
+ SELECT_LEX *sel2= sel;
+ if (sel->master_unit() && sel->master_unit()->first_select()->next_select())
+ {
+ sel2= wrap_unit_into_derived(sel->master_unit());
+ if (!sel2)
+ return NULL;
+ }
+ unit= parsed_select_expr_start(sel1, sel2, unit_type, distinct);
+ return unit;
+}
+
+
+/**
+ Process multi-operand query expression body
+*/
+
+bool LEX::parsed_multi_operand_query_expression_body(SELECT_LEX_UNIT *unit)
+{
+ SELECT_LEX *first_in_nest=
+ unit->pre_last_parse->next_select()->first_nested;
+ if (first_in_nest->first_nested != first_in_nest)
+ {
+ /* There is a priority jump starting from first_in_nest */
+ if (create_priority_nest(first_in_nest) == NULL)
+ return true;
+ unit->fix_distinct();
}
- current_select->linkage= DERIVED_TABLE_TYPE;
- return tvc_finalize();
+ return false;
+}
+
+
+/**
+ Add non-empty tail to a query expression body
+*/
+
+SELECT_LEX_UNIT *LEX::add_tail_to_query_expression_body(SELECT_LEX_UNIT *unit,
+ Lex_order_limit_lock *l)
+{
+ DBUG_ASSERT(l != NULL);
+ pop_select();
+ SELECT_LEX *sel= unit->first_select()->next_select() ? unit->fake_select_lex :
+ unit->first_select();
+ l->set_to(sel);
+ return unit;
+}
+
+
+/**
+ Add non-empty tail to a parenthesized query primary
+*/
+
+SELECT_LEX_UNIT *
+LEX::add_tail_to_query_expression_body_ext_parens(SELECT_LEX_UNIT *unit,
+ Lex_order_limit_lock *l)
+{
+ SELECT_LEX *sel= unit->first_select()->next_select() ? unit->fake_select_lex :
+ unit->first_select();
+
+ DBUG_ASSERT(l != NULL);
+
+ pop_select();
+ if (sel->is_set_query_expr_tail)
+ {
+ if (!l->order_list && !sel->explicit_limit)
+ l->order_list= &sel->order_list;
+ else
+ {
+ if (!unit)
+ return NULL;
+ sel= wrap_unit_into_derived(unit);
+ if (!sel)
+ return NULL;
+ if (!create_unit(sel))
+ return NULL;
+ }
+ }
+ l->set_to(sel);
+ return sel->master_unit();
+}
+
+
+/**
+ Process subselect parsing
+*/
+
+SELECT_LEX *LEX::parsed_subselect(SELECT_LEX_UNIT *unit)
+{
+ if (clause_that_disallows_subselect)
+ {
+ my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0),
+ clause_that_disallows_subselect);
+ return NULL;
+ }
+
+ // Add the subtree of subquery to the current SELECT_LEX
+ SELECT_LEX *curr_sel= select_stack_head();
+ DBUG_ASSERT(current_select == curr_sel ||
+ (curr_sel == NULL && current_select == &builtin_select));
+ if (curr_sel)
+ {
+ curr_sel->register_unit(unit, context_stack.head());
+ curr_sel->add_statistics(unit);
+ }
+
+ return unit->first_select();
+}
+
+
+/**
+ Process INSERT-like select
+*/
+
+bool LEX::parsed_insert_select(SELECT_LEX *first_select)
+{
+ if (sql_command == SQLCOM_INSERT ||
+ sql_command == SQLCOM_REPLACE)
+ {
+ if (sql_command == SQLCOM_INSERT)
+ sql_command= SQLCOM_INSERT_SELECT;
+ else
+ sql_command= SQLCOM_REPLACE_SELECT;
+ }
+ insert_select_hack(first_select);
+ if (check_main_unit_semantics())
+ return true;
+
+ // fix "main" select
+ SELECT_LEX *blt __attribute__((unused))= pop_select();
+ DBUG_ASSERT(blt == &builtin_select);
+ push_select(first_select);
+ return false;
+}
+
+
+bool LEX::parsed_TVC_start()
+{
+ SELECT_LEX *sel;
+ save_values_list_state();
+ many_values.empty();
+ insert_list= 0;
+ if (!(sel= alloc_select(TRUE)) ||
+ push_select(sel))
+ return true;
+ sel->init_select();
+ sel->braces= FALSE; // just initialisation
+ return false;
+}
+
+
+SELECT_LEX *LEX::parsed_TVC_end()
+{
+ SELECT_LEX *res= pop_select(); // above TVC select
+ if (!(res->tvc=
+ new (thd->mem_root) table_value_constr(many_values,
+ res,
+ res->options)))
+ return NULL;
+ restore_values_list_state();
+ return res;
+}
+
+
+
+TABLE_LIST *LEX::parsed_derived_table(SELECT_LEX_UNIT *unit,
+ int for_system_time,
+ LEX_CSTRING *alias)
+{
+ TABLE_LIST *res;
+ derived_tables|= DERIVED_SUBQUERY;
+ unit->first_select()->set_linkage(DERIVED_TABLE_TYPE);
+
+ // Add the subtree of subquery to the current SELECT_LEX
+ SELECT_LEX *curr_sel= select_stack_head();
+ DBUG_ASSERT(current_select == curr_sel ||
+ (curr_sel == NULL && current_select == &builtin_select));
+
+ Table_ident *ti= new (thd->mem_root) Table_ident(unit);
+ if (ti == NULL)
+ return NULL;
+ if (!(res= curr_sel->add_table_to_list(thd, ti, alias, 0,
+ TL_READ, MDL_SHARED_READ)))
+ return NULL;
+ if (for_system_time)
+ {
+ res->vers_conditions= vers_conditions;
+ }
+ return res;
+}
+
+bool LEX::parsed_create_view(SELECT_LEX_UNIT *unit, int check)
+{
+ SQL_I_List<TABLE_LIST> *save= &first_select_lex()->table_list;
+ if (set_main_unit(unit))
+ return true;
+ if (check_main_unit_semantics())
+ return true;
+ first_select_lex()->table_list.push_front(save);
+ current_select= first_select_lex();
+ size_t len= thd->m_parser_state->m_lip.get_cpp_ptr() -
+ create_view->select.str;
+ void *create_view_select= thd->memdup(create_view->select.str, len);
+ create_view->select.length= len;
+ create_view->select.str= (char *) create_view_select;
+ size_t not_used;
+ trim_whitespace(thd->charset(),
+ &create_view->select, ¬_used);
+ create_view->check= check;
+ parsing_options.allows_variable= TRUE;
+ return false;
+}
+
+bool LEX::select_finalize(st_select_lex_unit *expr)
+{
+ sql_command= SQLCOM_SELECT;
+ selects_allow_into= TRUE;
+ selects_allow_procedure= TRUE;
+ if (set_main_unit(expr))
+ return true;
+ return check_main_unit_semantics();
+}
+
+
+bool LEX::select_finalize(st_select_lex_unit *expr, Lex_select_lock l)
+{
+ return expr->set_lock_to_the_last_select(l) ||
+ select_finalize(expr);
+}
+
+
+/*
+ "IN" and "EXISTS" subselect can appear in two statement types:
+
+ 1. Statements that can have table columns, such as SELECT, DELETE, UPDATE
+ 2. Statements that cannot have table columns, e.g:
+ RETURN ((1) IN (SELECT * FROM t1))
+ IF ((1) IN (SELECT * FROM t1))
+
+ Statements of the first type call master_select_push() in the beginning.
+ In such case everything is properly linked.
+
+ Statements of the second type do not call mastr_select_push().
+ Here we catch the second case and relink thd->lex->builtin_select and
+ select_lex to properly point to each other.
+
+ QQ: Shouldn't subselects of other type also call relink_hack()?
+ QQ: Can we do it at constructor time instead?
+*/
+
+void LEX::relink_hack(st_select_lex *select_lex)
+{
+ if (!select_stack_top) // Statements of the second type
+ {
+ if (!select_lex->get_master()->get_master())
+ ((st_select_lex *) select_lex->get_master())->
+ set_master(&builtin_select);
+ if (!builtin_select.get_slave())
+ builtin_select.set_slave(select_lex->get_master());
+ }
+}
+
+
+bool SELECT_LEX_UNIT::set_lock_to_the_last_select(Lex_select_lock l)
+{
+ if (l.defined_lock)
+ {
+ SELECT_LEX *sel= first_select();
+ while (sel->next_select())
+ sel= sel->next_select();
+ if (sel->braces)
+ {
+ my_error(ER_WRONG_USAGE, MYF(0), "lock options",
+ "SELECT in brackets");
+ return TRUE;
+ }
+ l.set_to(sel);
+ }
+ return FALSE;
+}
+
+/**
+ Generate unique name for generated derived table for this SELECT
+*/
+
+bool SELECT_LEX::make_unique_derived_name(THD *thd, LEX_CSTRING *alias)
+{
+ // uint32 digits + two underscores + trailing '\0'
+ char buff[MAX_INT_WIDTH + 2 + 1];
+ alias->length= my_snprintf(buff, sizeof(buff), "__%u", select_number);
+ alias->str= thd->strmake(buff, alias->length);
+ return !alias->str;
+}
+
+
+/*
+ Make a new sp_instr_stmt and set its m_query to a concatenation
+ of two strings.
+*/
+bool LEX::new_sp_instr_stmt(THD *thd,
+ const LEX_CSTRING &prefix,
+ const LEX_CSTRING &suffix)
+{
+ LEX_STRING qbuff;
+ sp_instr_stmt *i;
+
+ if (!(i= new (thd->mem_root) sp_instr_stmt(sphead->instructions(),
+ spcont, this)))
+ return true;
+
+ qbuff.length= prefix.length + suffix.length;
+ if (!(qbuff.str= (char*) alloc_root(thd->mem_root, qbuff.length + 1)))
+ return true;
+ if (prefix.length)
+ memcpy(qbuff.str, prefix.str, prefix.length);
+ strmake(qbuff.str + prefix.length, suffix.str, suffix.length);
+ i->m_query= qbuff;
+ return sphead->add_instr(i);
+}
+
+
+bool LEX::sp_proc_stmt_statement_finalize_buf(THD *thd, const LEX_CSTRING &qbuf)
+{
+ sphead->m_flags|= sp_get_flags_for_command(this);
+ /* "USE db" doesn't work in a procedure */
+ if (unlikely(sql_command == SQLCOM_CHANGE_DB))
+ {
+ my_error(ER_SP_BADSTATEMENT, MYF(0), "USE");
+ return true;
+ }
+ /*
+ Don't add an instruction for SET statements, since all
+ instructions for them were already added during processing
+ of "set" rule.
+ */
+ DBUG_ASSERT(sql_command != SQLCOM_SET_OPTION || var_list.is_empty());
+ if (sql_command != SQLCOM_SET_OPTION)
+ return new_sp_instr_stmt(thd, empty_clex_str, qbuf);
+ return false;
+}
+
+
+bool LEX::sp_proc_stmt_statement_finalize(THD *thd, bool no_lookahead)
+{
+ // Extract the query statement from the tokenizer
+ Lex_input_stream *lip= &thd->m_parser_state->m_lip;
+ Lex_cstring qbuf(sphead->m_tmp_query, no_lookahead ? lip->get_ptr() :
+ lip->get_tok_start());
+ return LEX::sp_proc_stmt_statement_finalize_buf(thd, qbuf);
+}
+
+
+/**
+ @brief
+ Extract the condition that can be pushed into WHERE clause
+
+ @param thd the thread handle
+ @param cond the condition from which to extract a pushed condition
+ @param remaining_cond IN/OUT the condition that will remain of cond after
+ the extraction
+ @param transformer the transformer callback function to be
+ applied to the fields of the condition so it
+ can be pushed`
+ @param arg parameter to be passed to the transformer
+
+ @details
+ This function builds the most restrictive condition depending only on
+ the fields used in the GROUP BY of this SELECT. These fields were
+ collected before in grouping_tmp_fields list of this SELECT.
+
+ First this method checks if this SELECT doesn't have any aggregation
+ functions and has no GROUP BY clause. If so cond can be entirely pushed
+ into WHERE.
+
+ Otherwise the method checks if there is a condition depending only on
+ grouping fields that can be extracted from cond.
+
+ The condition that can be pushed into WHERE should be transformed.
+ It is done by transformer.
+
+ The extracted condition is saved in cond_pushed_into_where of this select.
+ cond can remain un empty after the extraction of the condition that can be
+ pushed into WHERE. It is saved in remaining_cond.
+
+ @note
+ This method is called for pushdown conditions into materialized
+ derived tables/views optimization.
+ Item::derived_field_transformer_for_where is passed as the actual
+ callback function.
+ Also it is called for pushdown into materialized IN subqueries.
+ Item::in_subq_field_transformer_for_where is passed as the actual
+ callback function.
+*/
+
+void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond,
+ Item **remaining_cond,
+ Item_transformer transformer,
+ uchar *arg)
+{
+ if (!cond_pushdown_is_allowed())
+ return;
+ thd->lex->current_select= this;
+ if (have_window_funcs())
+ {
+ Item *cond_over_partition_fields;
+ check_cond_extraction_for_grouping_fields(thd, cond);
+ cond_over_partition_fields=
+ build_cond_for_grouping_fields(thd, cond, true);
+ if (cond_over_partition_fields)
+ cond_over_partition_fields= cond_over_partition_fields->transform(thd,
+ &Item::grouping_field_transformer_for_where,
+ (uchar*) this);
+ if (cond_over_partition_fields)
+ {
+ cond_over_partition_fields->walk(
+ &Item::cleanup_excluding_const_fields_processor, 0, 0);
+ cond_pushed_into_where= cond_over_partition_fields;
+ }
+
+ return;
+ }
+
+ if (!join->group_list && !with_sum_func)
+ {
- cond=
- cond->transform(thd, transformer, arg);
++ cond= transform_condition_or_part(thd, cond, transformer, arg);
+ if (cond)
+ {
+ cond->walk(
+ &Item::cleanup_excluding_const_fields_processor, 0, 0);
+ cond_pushed_into_where= cond;
+ }
+
+ return;
+ }
+
+ /*
+ Figure out what can be extracted from cond and pushed into
+ the WHERE clause of this select.
+ */
+ Item *cond_over_grouping_fields;
+ check_cond_extraction_for_grouping_fields(thd, cond);
+ cond_over_grouping_fields=
+ build_cond_for_grouping_fields(thd, cond, true);
+
+ /*
+ Transform references to the columns of condition that can be pushed
+ into WHERE so it can be pushed.
+ */
+ if (cond_over_grouping_fields)
- cond_over_grouping_fields= cond_over_grouping_fields->transform(thd,
- &Item::grouping_field_transformer_for_where,
- (uchar*) this);
++ {
++ cond_over_grouping_fields=
++ transform_condition_or_part(thd, cond_over_grouping_fields,
++ &Item::grouping_field_transformer_for_where,
++ (uchar*) this);
++ }
+
+ if (cond_over_grouping_fields)
+ {
+
+ /*
+ Remove top conjuncts in cond that has been pushed into the WHERE
+ clause of this select
+ */
+ cond= remove_pushed_top_conjuncts(thd, cond);
+
+ cond_over_grouping_fields->walk(
+ &Item::cleanup_excluding_const_fields_processor, 0, 0);
+ cond_pushed_into_where= cond_over_grouping_fields;
+ }
+
+ *remaining_cond= cond;
+}
+
+
+/**
+ @brief
+ Mark OR-conditions as non-pushable to avoid repeatable pushdown
+
+ @param cond the processed condition
+
+ @details
+ Consider pushdown into the materialized derived table/view.
+ Consider OR condition that can be pushed into HAVING and some
+ parts of this OR condition that can be pushed into WHERE.
+
+ On example:
+
+ SELECT *
+ FROM t1,
+ (
+ SELECT a,MAX(c) AS m_c
+ GROUP BY a
+ ) AS dt
+ WHERE ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) AND
+ (t1.a=v1.a);
+
+
+ Here ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) or1
+ can be pushed down into the HAVING of the materialized
+ derived table dt.
+
+ (dt.a>2) OR (dt.a<3) part of or1 depends only on grouping fields
+ of dt and can be pushed into WHERE.
+
+ As a result:
+
+ SELECT *
+ FROM t1,
+ (
+ SELECT a,MAX(c) AS m_c
+ WHERE (dt.a>2) OR (dt.a<3)
+ GROUP BY a
+ HAVING ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3))
+ ) AS dt
+ WHERE ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) AND
+ (t1.a=v1.a);
+
+
+ Here (dt.a>2) OR (dt.a<3) also remains in HAVING of dt.
+ When SELECT that defines df is processed HAVING pushdown optimization
+ is made. In HAVING pushdown optimization it will extract
+ (dt.a>2) OR (dt.a<3) condition from or1 again and push it into WHERE.
+ This will cause duplicate conditions in WHERE of dt.
+
+ To avoid repeatable pushdown such OR conditions as or1 describen
+ above are marked with NO_EXTRACTION_FL.
+
+ @note
+ This method is called for pushdown into materialized
+ derived tables/views/IN subqueries optimization.
+*/
+
+void mark_or_conds_to_avoid_pushdown(Item *cond)
+{
+ if (cond->type() == Item::COND_ITEM &&
+ ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
+ {
+ List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+ Item *item;
+ while ((item=li++))
+ {
+ if (item->type() == Item::COND_ITEM &&
+ ((Item_cond*) item)->functype() == Item_func::COND_OR_FUNC)
+ item->set_extraction_flag(NO_EXTRACTION_FL);
+ }
+ }
+ else if (cond->type() == Item::COND_ITEM &&
+ ((Item_cond*) cond)->functype() == Item_func::COND_OR_FUNC)
+ cond->set_extraction_flag(NO_EXTRACTION_FL);
+}
+
+/**
+ @brief
+ Get condition that can be pushed from HAVING into WHERE
+
+ @param thd the thread handle
+ @param cond the condition from which to extract the condition
+
+ @details
+ The method collects in attach_to_conds list conditions from cond
+ that can be pushed from HAVING into WHERE.
+
+ Conditions that can be pushed were marked with FULL_EXTRACTION_FL in
+ check_cond_extraction_for_grouping_fields() method.
+ Conditions that can't be pushed were marked with NO_EXTRACTION_FL.
+ Conditions which parts can be pushed weren't marked.
+
+ There are two types of conditions that can be pushed:
+ 1. Condition that can be simply moved from HAVING
+ (if cond is marked with FULL_EXTRACTION_FL or
+ cond is an AND condition and some of its parts are marked with
+ FULL_EXTRACTION_FL)
+ In this case condition is transformed and pushed into attach_to_conds
+ list.
+ 2. Part of some other condition c1 that can't be entirely pushed
+ (if с1 isn't marked with any flag).
+
+ For example:
+
+ SELECT t1.a,MAX(t1.b),t1.c
+ FROM t1
+ GROUP BY t1.a
+ HAVING ((t1.a > 5) AND (t1.c < 3)) OR (t1.a = 3);
+
+ Here (t1.a > 5) OR (t1.a = 3) from HAVING can be pushed into WHERE.
+
+ In this case build_pushable_cond() is called for c1.
+ This method builds a clone of the c1 part that can be pushed.
+
+ Transformation mentioned above is made with multiple_equality_transformer
+ transformer. It transforms all multiple equalities in the extracted
+ condition into the set of equalities.
+
+ @note
+ Conditions that can be pushed are collected in attach_to_conds in this way:
+ 1. if cond is an AND condition its parts that can be pushed into WHERE
+ are added to attach_to_conds list separately.
+ 2. in all other cases conditions are pushed into the list entirely.
+
+ @retval
+ true - if an error occurs
+ false - otherwise
+*/
+
+bool
+st_select_lex::build_pushable_cond_for_having_pushdown(THD *thd, Item *cond)
+{
+ List<Item> equalities;
+
+ /* Condition can't be pushed */
+ if (cond->get_extraction_flag() == NO_EXTRACTION_FL)
+ return false;
+
+ /**
+ Condition can be pushed entirely.
+ Transform its multiple equalities and add to attach_to_conds list.
+ */
+ if (cond->get_extraction_flag() == FULL_EXTRACTION_FL)
+ {
+ Item *result= cond->transform(thd,
+ &Item::multiple_equality_transformer,
+ (uchar *)this);
+ if (!result)
+ return true;
+ if (result->type() == Item::COND_ITEM &&
+ ((Item_cond*) result)->functype() == Item_func::COND_AND_FUNC)
+ {
+ List_iterator<Item> li(*((Item_cond*) result)->argument_list());
+ Item *item;
+ while ((item= li++))
+ {
+ if (attach_to_conds.push_back(item, thd->mem_root))
+ return true;
+ }
+ }
+ else
+ {
+ if (attach_to_conds.push_back(result, thd->mem_root))
+ return true;
+ }
+ return false;
+ }
+
+ /**
+ There is no flag set for this condition. It means that some
+ part of this condition can be pushed.
+ */
+ if (cond->type() != Item::COND_ITEM)
+ return false;
+
+ if (((Item_cond *)cond)->functype() != Item_cond::COND_AND_FUNC)
+ {
+ /*
+ cond is not a conjunctive formula and it cannot be pushed into WHERE.
+ Try to extract a formula that can be pushed.
+ */
+ Item *fix= cond->build_pushable_cond(thd, 0, 0);
+ if (!fix)
+ return false;
+ if (attach_to_conds.push_back(fix, thd->mem_root))
+ return true;
+ }
+ else
+ {
+ List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+ Item *item;
+ while ((item=li++))
+ {
+ if (item->get_extraction_flag() == NO_EXTRACTION_FL)
+ continue;
+ else if (item->get_extraction_flag() == FULL_EXTRACTION_FL)
+ {
+ Item *result= item->transform(thd,
+ &Item::multiple_equality_transformer,
+ (uchar *)item);
+ if (!result)
+ return true;
+ if (result->type() == Item::COND_ITEM &&
+ ((Item_cond*) result)->functype() == Item_func::COND_AND_FUNC)
+ {
+ List_iterator<Item> li(*((Item_cond*) result)->argument_list());
+ Item *item;
+ while ((item=li++))
+ {
+ if (attach_to_conds.push_back(item, thd->mem_root))
+ return true;
+ }
+ }
+ else
+ {
+ if (attach_to_conds.push_back(result, thd->mem_root))
+ return true;
+ }
+ }
+ else
+ {
+ Item *fix= item->build_pushable_cond(thd, 0, 0);
+ if (!fix)
+ continue;
+ if (attach_to_conds.push_back(fix, thd->mem_root))
+ return true;
+ }
+ }
+ }
+ return false;
+}
+
+
+/**
+ Check if item is equal to some field in Field_pair 'field_pair'
+ from 'pair_list' and return found 'field_pair' if it exists.
+*/
+
+Field_pair *get_corresponding_field_pair(Item *item,
+ List<Field_pair> pair_list)
+{
+ DBUG_ASSERT(item->type() == Item::FIELD_ITEM ||
+ (item->type() == Item::REF_ITEM &&
+ ((((Item_ref *) item)->ref_type() == Item_ref::VIEW_REF) ||
+ (((Item_ref *) item)->ref_type() == Item_ref::REF))));
+
+ List_iterator<Field_pair> it(pair_list);
+ Field_pair *field_pair;
+ Item_field *field_item= (Item_field *) (item->real_item());
+ while ((field_pair= it++))
+ {
+ if (field_item->field == field_pair->field)
+ return field_pair;
+ }
+ return NULL;
+}
+
+
+/**
+ @brief
+ Collect fields from multiple equalities which are equal to grouping
+
+ @param thd the thread handle
+
+ @details
+ This method checks if multiple equalities of the WHERE clause contain
+ fields from GROUP BY of this SELECT. If so all fields of such multiple
+ equalities are collected in grouping_tmp_fields list without repetitions.
+
+ @retval
+ true - if an error occurs
+ false - otherwise
+*/
+
+bool st_select_lex::collect_fields_equal_to_grouping(THD *thd)
+{
+ if (!join->cond_equal || join->cond_equal->is_empty())
+ return false;
+
+ List_iterator_fast<Item_equal> li(join->cond_equal->current_level);
+ Item_equal *item_equal;
+
+ while ((item_equal= li++))
+ {
+ Item_equal_fields_iterator it(*item_equal);
+ Item *item;
+ while ((item= it++))
+ {
+ if (get_corresponding_field_pair(item, grouping_tmp_fields))
+ break;
+ }
+ if (!item)
+ break;
+
+ it.rewind();
+ while ((item= it++))
+ {
+ if (get_corresponding_field_pair(item, grouping_tmp_fields))
+ continue;
+ Field_pair *grouping_tmp_field=
+ new Field_pair(((Item_field *)item->real_item())->field, item);
+ if (grouping_tmp_fields.push_back(grouping_tmp_field, thd->mem_root))
+ return true;
+ }
+ }
+ return false;
+}
+
+
+/**
+ @brief
+ Remove marked top conjuncts of HAVING for having pushdown
+
+ @param thd the thread handle
+ @param cond the condition which subformulas are to be removed
+
+ @details
+ This method removes from cond all subformulas that can be moved from HAVING
+ into WHERE.
+
+ @retval
+ condition without removed subformulas
+ 0 if the whole 'cond' is removed
+*/
+
+Item *remove_pushed_top_conjuncts_for_having(THD *thd, Item *cond)
+{
+ /* Nothing to extract */
+ if (cond->get_extraction_flag() == NO_EXTRACTION_FL)
+ {
+ cond->clear_extraction_flag();
+ return cond;
+ }
+ /* cond can be pushed in WHERE entirely */
+ if (cond->get_extraction_flag() == FULL_EXTRACTION_FL)
+ {
+ cond->clear_extraction_flag();
+ return 0;
+ }
+
+ /* Some parts of cond can be pushed */
+ if (cond->type() == Item::COND_ITEM &&
+ ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
+ {
+ List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+ Item *item;
+ while ((item=li++))
+ {
+ if (item->get_extraction_flag() == NO_EXTRACTION_FL)
+ item->clear_extraction_flag();
+ else if (item->get_extraction_flag() == FULL_EXTRACTION_FL)
+ {
+ if (item->type() == Item::FUNC_ITEM &&
+ ((Item_func*) item)->functype() == Item_func::MULT_EQUAL_FUNC)
+ item->set_extraction_flag(DELETION_FL);
+ else
+ {
+ item->clear_extraction_flag();
+ li.remove();
+ }
+ }
+ }
+ switch (((Item_cond*) cond)->argument_list()->elements)
+ {
+ case 0:
+ return 0;
+ case 1:
+ return (((Item_cond*) cond)->argument_list()->head());
+ default:
+ return cond;
+ }
+ }
+ return cond;
+}
+
+
+/**
+ @brief
+ Extract condition that can be pushed from HAVING into WHERE
+
+ @param thd the thread handle
+ @param having the HAVING clause of this select
+ @param having_equal multiple equalities of HAVING
+
+ @details
+ This method builds a set of conditions dependent only on
+ fields used in the GROUP BY of this select (directly or indirectly
+ through equalities). These conditions are extracted from the HAVING
+ clause of this select.
+ The method saves these conditions into attach_to_conds list and removes
+ from HAVING conditions that can be entirely pushed into WHERE.
+
+ Example of the HAVING pushdown transformation:
+
+ SELECT t1.a,MAX(t1.b)
+ FROM t1
+ GROUP BY t1.a
+ HAVING (t1.a>2) AND (MAX(c)>12);
+
+ =>
+
+ SELECT t1.a,MAX(t1.b)
+ FROM t1
+ WHERE (t1.a>2)
+ GROUP BY t1.a
+ HAVING (MAX(c)>12);
+
+ In this method (t1.a>2) is not attached to the WHERE clause.
+ It is pushed into the attach_to_conds list to be attached to
+ the WHERE clause later.
+
+ In details:
+ 1. Collect fields used in the GROUP BY grouping_fields of this SELECT
+ 2. Collect fields equal to grouping_fields from the WHERE clause
+ of this SELECT and add them to the grouping_fields list.
+ 3. Extract the most restrictive condition from the HAVING clause of this
+ select that depends only on the grouping fields (directly or indirectly
+ through equality).
+ If the extracted condition is an AND condition it is transformed into a
+ list of all its conjuncts saved in attach_to_conds. Otherwise,
+ the condition is put into attach_to_conds as the only its element.
+ 4. Remove conditions from HAVING clause that can be entirely pushed
+ into WHERE.
+ Multiple equalities are not removed but marked with DELETION_FL flag.
+ They will be deleted later in substitite_for_best_equal_field() called
+ for the HAVING condition.
+ 5. Unwrap fields wrapped in Item_ref wrappers contained in the condition
+ of attach_to_conds so the condition could be pushed into WHERE.
+
+ @note
+ This method is similar to st_select_lex::pushdown_cond_into_where_clause().
+
+ @retval TRUE if an error occurs
+ @retval FALSE otherwise
+*/
+
+Item *st_select_lex::pushdown_from_having_into_where(THD *thd, Item *having)
+{
+ if (!having || !group_list.first)
+ return having;
+ if (!cond_pushdown_is_allowed())
+ return having;
+
+ st_select_lex *save_curr_select= thd->lex->current_select;
+ thd->lex->current_select= this;
+
+ /*
+ 1. Collect fields used in the GROUP BY grouping fields of this SELECT
+ 2. Collect fields equal to grouping_fields from the WHERE clause
+ of this SELECT and add them to the grouping fields list.
+ */
+ if (collect_grouping_fields(thd) ||
+ collect_fields_equal_to_grouping(thd))
+ return having;
+
+ /*
+ 3. Extract the most restrictive condition from the HAVING clause of this
+ select that depends only on the grouping fields (directly or indirectly
+ through equality).
+ If the extracted condition is an AND condition it is transformed into a
+ list of all its conjuncts saved in attach_to_conds. Otherwise,
+ the condition is put into attach_to_conds as the only its element.
+ */
+ List_iterator_fast<Item> it(attach_to_conds);
+ Item *item;
+ check_cond_extraction_for_grouping_fields(thd, having);
+ if (build_pushable_cond_for_having_pushdown(thd, having))
+ {
+ attach_to_conds.empty();
+ goto exit;
+ }
+ if (!attach_to_conds.elements)
+ goto exit;
+
+ /*
+ 4. Remove conditions from HAVING clause that can be entirely pushed
+ into WHERE.
+ Multiple equalities are not removed but marked with DELETION_FL flag.
+ They will be deleted later in substitite_for_best_equal_field() called
+ for the HAVING condition.
+ */
+ having= remove_pushed_top_conjuncts_for_having(thd, having);
+
+ /*
+ Change join->cond_equal which points to the multiple equalities of
+ the top level of HAVING.
+ Removal of AND conditions may leave only one conjunct in HAVING.
+
+ Example 1:
+ SELECT *
+ FROM t1
+ GROUP BY t1.a
+ (t1.a < 2) AND (t1.b = 2)
+
+ (t1.a < 2) is pushed into WHERE.
+ join->cond_equal should point on (t1.b = 2) multiple equality now.
+
+ Example 2:
+ SELECT *
+ FROM t1
+ GROUP BY t1.a
+ (t1.a = 2) AND (t1.b < 2)
+
+ (t1.a = 2) is pushed into WHERE.
+ join->cond_equal should be NULL now.
+ */
+ if (having &&
+ having->type() == Item::FUNC_ITEM &&
+ ((Item_func*) having)->functype() == Item_func::MULT_EQUAL_FUNC)
+ join->having_equal= new (thd->mem_root) COND_EQUAL((Item_equal *)having,
+ thd->mem_root);
+ else if (!having ||
+ having->type() != Item::COND_ITEM ||
+ ((Item_cond *)having)->functype() != Item_cond::COND_AND_FUNC)
+ join->having_equal= 0;
+
+ /*
+ 5. Unwrap fields wrapped in Item_ref wrappers contained in the condition
+ of attach_to_conds so the condition could be pushed into WHERE.
+ */
+ it.rewind();
+ while ((item=it++))
+ {
+ item= item->transform(thd,
+ &Item::field_transformer_for_having_pushdown,
+ (uchar *)this);
+
+ if (item->walk(&Item::cleanup_excluding_immutables_processor, 0, STOP_PTR)
+ || item->fix_fields(thd, NULL))
+ {
+ attach_to_conds.empty();
+ goto exit;
+ }
+ }
+exit:
+ thd->lex->current_select= save_curr_select;
+ return having;
+}
+
+
+bool LEX::stmt_install_plugin(const DDL_options_st &opt,
+ const Lex_ident_sys_st &name,
+ const LEX_CSTRING &soname)
+{
+ create_info.init();
+ if (add_create_options_with_check(opt))
+ return true;
+ sql_command= SQLCOM_INSTALL_PLUGIN;
+ comment= name;
+ ident= soname;
+ return false;
+}
+
+
+void LEX::stmt_install_plugin(const LEX_CSTRING &soname)
+{
+ sql_command= SQLCOM_INSTALL_PLUGIN;
+ comment= null_clex_str;
+ ident= soname;
+}
+
+
+bool LEX::stmt_uninstall_plugin_by_name(const DDL_options_st &opt,
+ const Lex_ident_sys_st &name)
+{
+ check_opt.init();
+ if (add_create_options_with_check(opt))
+ return true;
+ sql_command= SQLCOM_UNINSTALL_PLUGIN;
+ comment= name;
+ ident= null_clex_str;
+ return false;
+}
+
+
+bool LEX::stmt_uninstall_plugin_by_soname(const DDL_options_st &opt,
+ const LEX_CSTRING &soname)
+{
+ check_opt.init();
+ if (add_create_options_with_check(opt))
+ return true;
+ sql_command= SQLCOM_UNINSTALL_PLUGIN;
+ comment= null_clex_str;
+ ident= soname;
+ return false;
+}
+
+
+bool LEX::stmt_prepare_validate(const char *stmt_type)
+{
+ if (unlikely(table_or_sp_used()))
+ {
+ my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0), stmt_type);
+ return true;
+ }
+ return check_main_unit_semantics();
+}
+
+
+bool LEX::stmt_prepare(const Lex_ident_sys_st &ident, Item *code)
+{
+ sql_command= SQLCOM_PREPARE;
+ if (stmt_prepare_validate("PREPARE..FROM"))
+ return true;
+ prepared_stmt.set(ident, code, NULL);
+ return false;
+}
+
+
+bool LEX::stmt_execute_immediate(Item *code, List<Item> *params)
+{
+ sql_command= SQLCOM_EXECUTE_IMMEDIATE;
+ if (stmt_prepare_validate("EXECUTE IMMEDIATE"))
+ return true;
+ static const Lex_ident_sys immediate(STRING_WITH_LEN("IMMEDIATE"));
+ prepared_stmt.set(immediate, code, params);
+ return false;
+}
+
+
+bool LEX::stmt_execute(const Lex_ident_sys_st &ident, List<Item> *params)
+{
+ sql_command= SQLCOM_EXECUTE;
+ prepared_stmt.set(ident, NULL, params);
+ return stmt_prepare_validate("EXECUTE..USING");
+}
+
+
+void LEX::stmt_deallocate_prepare(const Lex_ident_sys_st &ident)
+{
+ sql_command= SQLCOM_DEALLOCATE_PREPARE;
+ prepared_stmt.set(ident, NULL, NULL);
+}
+
+
+bool LEX::stmt_alter_table_exchange_partition(Table_ident *table)
+{
+ DBUG_ASSERT(sql_command == SQLCOM_ALTER_TABLE);
+ first_select_lex()->db= table->db;
+ if (first_select_lex()->db.str == NULL &&
+ copy_db_to(&first_select_lex()->db))
+ return true;
+ name= table->table;
+ alter_info.partition_flags|= ALTER_PARTITION_EXCHANGE;
+ if (!first_select_lex()->add_table_to_list(thd, table, NULL,
+ TL_OPTION_UPDATING,
+ TL_READ_NO_INSERT,
+ MDL_SHARED_NO_WRITE))
+ return true;
+ DBUG_ASSERT(!m_sql_cmd);
+ m_sql_cmd= new (thd->mem_root) Sql_cmd_alter_table_exchange_partition();
+ return m_sql_cmd == NULL;
+}
+
+
+void LEX::stmt_purge_to(const LEX_CSTRING &to)
+{
+ type= 0;
+ sql_command= SQLCOM_PURGE;
+ to_log= to.str;
+}
+
+
+bool LEX::stmt_purge_before(Item *item)
+{
+ type= 0;
+ sql_command= SQLCOM_PURGE_BEFORE;
+ value_list.empty();
+ value_list.push_front(item, thd->mem_root);
+ return check_main_unit_semantics();
+}
+
+
+bool LEX::stmt_create_udf_function(const DDL_options_st &options,
+ enum_sp_aggregate_type agg_type,
+ const Lex_ident_sys_st &name,
+ Item_result return_type,
+ const LEX_CSTRING &soname)
+{
+ if (stmt_create_function_start(options))
+ return true;
+
+ if (unlikely(is_native_function(thd, &name)))
+ {
+ my_error(ER_NATIVE_FCT_NAME_COLLISION, MYF(0), name.str);
+ return true;
+ }
+ sql_command= SQLCOM_CREATE_FUNCTION;
+ udf.name= name;
+ udf.returns= return_type;
+ udf.dl= soname.str;
+ udf.type= agg_type == GROUP_AGGREGATE ? UDFTYPE_AGGREGATE :
+ UDFTYPE_FUNCTION;
+ stmt_create_routine_finalize();
+ return false;
+}
+
+
+bool LEX::stmt_create_stored_function_start(const DDL_options_st &options,
+ enum_sp_aggregate_type agg_type,
+ const sp_name *spname)
+{
+ if (stmt_create_function_start(options) ||
+ unlikely(!make_sp_head_no_recursive(thd, spname,
+ &sp_handler_function, agg_type)))
+ return true;
+ return false;
+}
+
+
+Spvar_definition *LEX::row_field_name(THD *thd, const Lex_ident_sys_st &name)
+{
+ Spvar_definition *res;
+ if (unlikely(check_string_char_length(&name, 0, NAME_CHAR_LEN,
+ system_charset_info, 1)))
+ {
+ my_error(ER_TOO_LONG_IDENT, MYF(0), name.str);
+ return NULL;
+ }
+ if (unlikely(!(res= new (thd->mem_root) Spvar_definition())))
+ return NULL;
+ init_last_field(res, &name, thd->variables.collation_database);
+ return res;
}
diff --cc sql/sql_show.cc
index 7b5b2c8bf89,e7c42cbc8e4..1f8278528cf
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@@ -63,9 -63,20 +63,22 @@@
#include "ha_partition.h"
#endif
#include "transaction.h"
+#include "opt_trace.h"
+#include "my_cpu.h"
+
+ #include "lex_symbol.h"
+ #define KEYWORD_SIZE 64
+
+ extern SYMBOL symbols[];
+ extern size_t symbols_length;
+
+ extern SYMBOL sql_functions[];
+ extern size_t sql_functions_length;
+
+ extern Native_func_registry func_array[];
+ extern size_t func_array_length;
+
enum enum_i_s_events_fields
{
ISE_EVENT_CATALOG= 0,
1
0
revision-id: 65d8d3ba93ec41d271e3cdc5e2bc2c6b035358d6 (mariadb-10.4.20-21-g65d8d3ba93e)
parent(s): a1e2ca057dda4dc434f057ce9391aa7afd9b5583 4a6e2d343745c11086c05f0041a8267591bb073c
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-06-30 18:13:08 +0300
message:
Merge 10.3 -> 10.4
.gitignore | 3 +
cmake/cpack_rpm.cmake | 6 +-
mysql-test/main/cte_nonrecursive.result | 55 ++++++++
mysql-test/main/cte_nonrecursive.test | 29 ++++
mysql-test/main/derived_cond_pushdown.result | 147 +++++++++++++++++++++
mysql-test/main/derived_cond_pushdown.test | 70 ++++++++++
mysql-test/main/gis-json.result | 10 ++
mysql-test/main/gis-json.test | 7 +
mysql-test/main/information_schema.result | 2 +
.../main/information_schema_all_engines.result | 12 +-
mysql-test/suite/funcs_1/r/is_columns_is.result | 4 +
.../suite/funcs_1/r/is_columns_is_embedded.result | 4 +
mysql-test/suite/funcs_1/r/is_tables_is.result | 92 +++++++++++++
.../suite/funcs_1/r/is_tables_is_embedded.result | 92 +++++++++++++
.../galera/r/galera_sst_rsync_logbasename.result | 1 +
mysql-test/suite/galera/r/lp1376747-4.result | 2 +-
mysql-test/suite/galera/t/lp1376747-4.test | 2 +-
sql/handler.h | 2 +
sql/item_create.cc | 5 +-
sql/lex.h | 7 +-
sql/spatial.cc | 6 +
sql/sql_derived.cc | 63 ++++++++-
sql/sql_derived.h | 5 +
sql/sql_lex.cc | 13 +-
sql/sql_show.cc | 83 ++++++++++++
storage/innobase/os/os0file.cc | 38 +++++-
26 files changed, 741 insertions(+), 19 deletions(-)
diff --cc .gitignore
index c10f08b20d7,c26767717c6..b3cbd1d430d
--- a/.gitignore
+++ b/.gitignore
@@@ -554,73 -521,4 +554,76 @@@ compile_commands.jso
# Visual Studio Code workspace
.vscode/
+# Clion && other JetBrains ides
+.idea
+
+ .cache/clangd
++
++
+client/mariadb
+client/mariadb-admin
+client/mariadb-binlog
+client/mariadb-check
+client/mariadb-dump
+client/mariadb-import
+client/mariadb-plugin
+client/mariadb-show
+client/mariadb-slap
+client/mariadb-test
+client/mariadb-upgrade
+extra/mariabackup/mariadb-backup
+extra/mariadb-waitpid
+extra/mariadbd-safe-helper
+libmysqld/examples/mariadb-client-test-embedded
+libmysqld/examples/mariadb-embedded
+libmysqld/examples/mariadb-test-embedded
+man/mariadb-access.1
+man/mariadb-admin.1
+man/mariadb-backup.1
+man/mariadb-binlog.1
+man/mariadb-check.1
+man/mariadb-client-test-embedded.1
+man/mariadb-client-test.1
+man/mariadb-convert-table-format.1
+man/mariadb-dump.1
+man/mariadb-dumpslow.1
+man/mariadb-embedded.1
+man/mariadb-find-rows.1
+man/mariadb-fix-extensions.1
+man/mariadb-hotcopy.1
+man/mariadb-import.1
+man/mariadb-install-db.1
+man/mariadb-ldb.1
+man/mariadb-plugin.1
+man/mariadb-secure-installation.1
+man/mariadb-setpermission.1
+man/mariadb-show.1
+man/mariadb-slap.1
+man/mariadb-test-embedded.1
+man/mariadb-test.1
+man/mariadb-tzinfo-to-sql.1
+man/mariadb-upgrade.1
+man/mariadb-waitpid.1
+man/mariadb.1
+man/mariadbd-multi.1
+man/mariadbd-safe-helper.1
+man/mariadbd-safe.1
+man/mariadbd.8
+scripts/mariadb-access
+scripts/mariadb-convert-table-format
+scripts/mariadb-dumpslow
+scripts/mariadb-find-rows
+scripts/mariadb-fix-extensions
+scripts/mariadb-hotcopy
+scripts/mariadb-install-db
+scripts/mariadb-secure-installation
+scripts/mariadb-setpermission
+scripts/mariadbd-multi
+scripts/mariadbd-safe
+sql/mariadb-tzinfo-to-sql
+sql/mariadbd
+storage/rocksdb/mariadb-ldb
+tests/mariadb-client-test
+versioninfo_dll.rc
+versioninfo_exe.rc
+win/packaging/ca/symlinks.cc
diff --cc mysql-test/main/information_schema.result
index 3534c4bd337,0559c42350d..9ba19cca7e1
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@@ -65,9 -65,9 +65,10 @@@ GEOMETRY_COLUMN
GLOBAL_STATUS
GLOBAL_VARIABLES
INDEX_STATISTICS
+ KEYWORDS
KEY_CACHES
KEY_COLUMN_USAGE
+OPTIMIZER_TRACE
PARAMETERS
PARTITIONS
PLUGINS
diff --cc mysql-test/main/information_schema_all_engines.result
index 9ba4d20c76d,41d6ab3b2f4..de524f9a669
--- a/mysql-test/main/information_schema_all_engines.result
+++ b/mysql-test/main/information_schema_all_engines.result
@@@ -41,9 -41,9 +41,10 @@@ INNODB_SYS_VIRTUA
INNODB_TABLESPACES_ENCRYPTION
INNODB_TABLESPACES_SCRUBBING
INNODB_TRX
+ KEYWORDS
KEY_CACHES
KEY_COLUMN_USAGE
+OPTIMIZER_TRACE
PARAMETERS
PARTITIONS
PLUGINS
@@@ -122,9 -123,9 +124,10 @@@ INNODB_SYS_VIRTUAL TABLE_I
INNODB_TABLESPACES_ENCRYPTION SPACE
INNODB_TABLESPACES_SCRUBBING SPACE
INNODB_TRX trx_id
+ KEYWORDS WORD
KEY_CACHES KEY_CACHE_NAME
KEY_COLUMN_USAGE CONSTRAINT_SCHEMA
+OPTIMIZER_TRACE QUERY
PARAMETERS SPECIFIC_SCHEMA
PARTITIONS TABLE_SCHEMA
PLUGINS PLUGIN_NAME
@@@ -203,9 -205,9 +207,10 @@@ INNODB_SYS_VIRTUAL TABLE_I
INNODB_TABLESPACES_ENCRYPTION SPACE
INNODB_TABLESPACES_SCRUBBING SPACE
INNODB_TRX trx_id
+ KEYWORDS WORD
KEY_CACHES KEY_CACHE_NAME
KEY_COLUMN_USAGE CONSTRAINT_SCHEMA
+OPTIMIZER_TRACE QUERY
PARAMETERS SPECIFIC_SCHEMA
PARTITIONS TABLE_SCHEMA
PLUGINS PLUGIN_NAME
@@@ -360,9 -362,9 +366,10 @@@ Database: information_schem
| INNODB_TABLESPACES_ENCRYPTION |
| INNODB_TABLESPACES_SCRUBBING |
| INNODB_TRX |
+ | KEYWORDS |
| KEY_CACHES |
| KEY_COLUMN_USAGE |
+| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
@@@ -431,9 -434,9 +439,10 @@@ Database: INFORMATION_SCHEM
| INNODB_TABLESPACES_ENCRYPTION |
| INNODB_TABLESPACES_SCRUBBING |
| INNODB_TRX |
+ | KEYWORDS |
| KEY_CACHES |
| KEY_COLUMN_USAGE |
+| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
diff --cc sql/sql_derived.cc
index fc01dcdc750,93dc62828ac..a4e0fd6b683
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@@ -25,16 -25,13 +25,16 @@@
#include "mariadb.h" /* NO_EMBEDDED_ACCESS_CHECKS */
#include "sql_priv.h"
#include "unireg.h"
--#include "sql_derived.h"
#include "sql_select.h"
+#include "derived_handler.h"
#include "sql_base.h"
#include "sql_view.h" // check_duplicate_names
#include "sql_acl.h" // SELECT_ACL
#include "sql_class.h"
++#include "sql_derived.h"
#include "sql_cte.h"
+#include "my_json_writer.h"
+#include "opt_trace.h"
typedef bool (*dt_processor)(THD *thd, LEX *lex, TABLE_LIST *derived);
@@@ -1359,63 -1264,89 +1359,124 @@@ bool mysql_derived_reinit(THD *thd, LE
}
+ /*
+ @brief
+ Given condition cond and transformer+argument, try transforming as many
+ conjuncts as possible.
+
+ @detail
+ The motivation of this function is to convert the condition that's being
+ pushed into a WHERE clause with derived_field_transformer_for_where or
+ with derived_grouping_field_transformer_for_where.
+ The transformer may fail for some sub-condition, in this case we want to
+ convert the most restrictive part of the condition that can be pushed.
+
+ This function only does it for top-level AND: conjuncts that could not be
+ converted are dropped.
+
+ @return
+ Converted condition, or NULL if nothing could be converted
+ */
+
-static
+ Item *transform_condition_or_part(THD *thd,
+ Item *cond,
+ Item_transformer transformer,
+ uchar *arg)
+ {
+ if (cond->type() != Item::COND_ITEM ||
+ ((Item_cond*) cond)->functype() != Item_func::COND_AND_FUNC)
+ {
+ Item *new_item= cond->transform(thd, transformer, arg);
+ // Indicate that the condition is not pushable
+ if (!new_item)
+ cond->clear_extraction_flag();
+ return new_item;
+ }
+
+ List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+ Item *item;
+ while ((item=li++))
+ {
+ Item *new_item= item->transform(thd, transformer, arg);
+ if (!new_item)
+ {
+ // Indicate that the condition is not pushable
+ item->clear_extraction_flag();
+ li.remove();
+ }
+ else
+ li.replace(new_item);
+ }
+
+ switch (((Item_cond*) cond)->argument_list()->elements)
+ {
+ case 0:
+ return NULL;
+ case 1:
+ return ((Item_cond*) cond)->argument_list()->head();
+ default:
+ return cond;
+ }
+ }
+
+
/**
@brief
- Extract the condition depended on derived table/view and pushed it there
+ Extract condition that can be pushed into a derived table/view
- @param thd The thread handle
- @param cond The condition from which to extract the pushed condition
- @param derived The reference to the derived table/view
+ @param thd the thread handle
+ @param cond current condition
+ @param derived the reference to the derived table/view
@details
- This functiom builds the most restrictive condition depending only on
- the derived table/view that can be extracted from the condition cond.
- The built condition is pushed into the having clauses of the
- selects contained in the query specifying the derived table/view.
- The function also checks for each select whether any condition depending
- only on grouping fields can be extracted from the pushed condition.
- If so, it pushes the condition over grouping fields into the where
- clause of the select.
-
- @retval
- true if an error is reported
- false otherwise
+ This function builds the most restrictive condition depending only on
+ the derived table/view (directly or indirectly through equality) that
+ can be extracted from the given condition cond and pushes it into the
+ derived table/view.
+
+ Example of the transformation:
+
+ SELECT *
+ FROM t1,
+ (
+ SELECT x,MAX(y) AS max_y
+ FROM t2
+ GROUP BY x
+ ) AS d_tab
+ WHERE d_tab.x>1 AND d_tab.max_y<30;
+
+ =>
+
+ SELECT *
+ FROM t1,
+ (
+ SELECT x,z,MAX(y) AS max_y
+ FROM t2
+ WHERE x>1
+ HAVING max_y<30
+ GROUP BY x
+ ) AS d_tab
+ WHERE d_tab.x>1 AND d_tab.max_y<30;
+
+ In details:
+ 1. Check what pushable formula can be extracted from cond
+ 2. Build a clone PC of the formula that can be extracted
+ (the clone is built only if the extracted formula is a AND subformula
+ of cond or conjunction of such subformulas)
+ Do for every select specifying derived table/view:
+ 3. If there is no HAVING clause prepare PC to be conjuncted with
+ WHERE clause of the select. Otherwise do 4-7.
+ 4. Check what formula PC_where can be extracted from PC to be pushed
+ into the WHERE clause of the select
+ 5. Build PC_where and if PC_where is a conjunct(s) of PC remove it from PC
+ getting PC_having
+ 6. Prepare PC_where to be conjuncted with the WHERE clause of the select
+ 7. Prepare PC_having to be conjuncted with the HAVING clause of the select
+ @note
+ This method is similar to pushdown_cond_for_in_subquery()
+
+ @retval TRUE if an error occurs
+ @retval FALSE otherwise
*/
bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
diff --cc sql/sql_derived.h
index 403277d65c9,093191e62a7..6100b4b4d7e
--- a/sql/sql_derived.h
+++ b/sql/sql_derived.h
@@@ -22,7 -22,8 +22,12 @@@ struct LEX
bool mysql_handle_derived(LEX *lex, uint phases);
bool mysql_handle_single_derived(LEX *lex, TABLE_LIST *derived, uint phases);
-bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived);
+
++Item *transform_condition_or_part(THD *thd,
++ Item *cond,
++ Item_transformer transformer,
++ uchar *arg);
+
bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived);
#endif /* SQL_DERIVED_INCLUDED */
diff --cc sql/sql_lex.cc
index 93a4fcbe277,2a337b0f842..1ec5d0b0550
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@@ -31,11 -31,8 +31,12 @@@
#include "sql_select.h"
#include "sql_cte.h"
#include "sql_signal.h"
++#include "sql_derived.h"
+#include "sql_truncate.h" // Sql_cmd_truncate_table
+#include "sql_admin.h" // Sql_cmd_analyze/Check..._table
#include "sql_partition.h"
-
+#include "sql_partition_admin.h" // Sql_cmd_alter_table_*_part
+#include "event_parse_data.h"
void LEX::parse_error(uint err_number)
{
@@@ -9000,1522 -8388,16 +9001,1524 @@@ void st_select_lex::register_unit(SELEC
}
-bool LEX::tvc_finalize_derived()
+void st_select_lex::add_statistics(SELECT_LEX_UNIT *unit)
{
- derived_tables|= DERIVED_SUBQUERY;
- if (unlikely(!expr_allows_subselect || sql_command == (int)SQLCOM_PURGE))
+ for (;
+ unit;
+ unit= unit->next_unit())
+ for(SELECT_LEX *child= unit->first_select();
+ child;
+ child= child->next_select())
+ {
+ /*
+ A subselect can add fields to an outer select.
+ Reserve space for them.
+ */
+ select_n_where_fields+= child->select_n_where_fields;
+ /*
+ Aggregate functions in having clause may add fields
+ to an outer select. Count them also.
+ */
+ select_n_having_items+= child->select_n_having_items;
+ }
+}
+
+
+bool LEX::main_select_push(bool service)
+{
+ DBUG_ENTER("LEX::main_select_push");
+ current_select_number= ++thd->lex->stmt_lex->current_select_number;
+ builtin_select.select_number= current_select_number;
+ builtin_select.is_service_select= service;
+ if (push_select(&builtin_select))
+ DBUG_RETURN(TRUE);
+ DBUG_RETURN(FALSE);
+}
+
+void Lex_select_lock::set_to(SELECT_LEX *sel)
+{
+ if (defined_lock)
{
- thd->parse_error();
+ if (sel->master_unit() &&
+ sel == sel->master_unit()->fake_select_lex)
+ sel->master_unit()->set_lock_to_the_last_select(*this);
+ else
+ {
+ sel->parent_lex->safe_to_cache_query= 0;
+ if (update_lock)
+ {
+ sel->lock_type= TL_WRITE;
+ sel->set_lock_for_tables(TL_WRITE, false);
+ }
+ else
+ {
+ sel->lock_type= TL_READ_WITH_SHARED_LOCKS;
+ sel->set_lock_for_tables(TL_READ_WITH_SHARED_LOCKS, false);
+ }
+ }
+ }
+}
+
+bool Lex_order_limit_lock::set_to(SELECT_LEX *sel)
+{
+ /*TODO: lock */
+ //if (lock.defined_lock && sel == sel->master_unit()->fake_select_lex)
+ // return TRUE;
+ if (lock.defined_timeout)
+ {
+ THD *thd= sel->parent_lex->thd;
+ if (set_statement_var_if_exists(thd,
+ C_STRING_WITH_LEN("lock_wait_timeout"),
+ lock.timeout) ||
+ set_statement_var_if_exists(thd,
+ C_STRING_WITH_LEN("innodb_lock_wait_timeout"),
+ lock.timeout))
+ return TRUE;
+ }
+ lock.set_to(sel);
+ sel->explicit_limit= limit.explicit_limit;
+ sel->select_limit= limit.select_limit;
+ sel->offset_limit= limit.offset_limit;
+ if (order_list)
+ {
+ if (sel->get_linkage() != GLOBAL_OPTIONS_TYPE &&
+ sel->olap != UNSPECIFIED_OLAP_TYPE &&
+ (sel->get_linkage() != UNION_TYPE || sel->braces))
+ {
+ my_error(ER_WRONG_USAGE, MYF(0),
+ "CUBE/ROLLUP", "ORDER BY");
+ return TRUE;
+ }
+ sel->order_list= *(order_list);
+ }
+ sel->is_set_query_expr_tail= true;
+ return FALSE;
+}
+
+
+static void change_item_list_context(List<Item> *list,
+ Name_resolution_context *context)
+{
+ List_iterator_fast<Item> it (*list);
+ Item *item;
+ while((item= it++))
+ {
+ item->walk(&Item::change_context_processor, FALSE, (void *)context);
+ }
+}
+
+
+bool LEX::insert_select_hack(SELECT_LEX *sel)
+{
+ DBUG_ENTER("LEX::insert_select_hack");
+
+ DBUG_ASSERT(first_select_lex() == &builtin_select);
+ DBUG_ASSERT(sel != NULL);
+
+ DBUG_ASSERT(builtin_select.first_inner_unit() == NULL);
+
+ if (builtin_select.link_prev)
+ {
+ if ((*builtin_select.link_prev= builtin_select.link_next))
+ ((st_select_lex *)builtin_select.link_next)->link_prev=
+ builtin_select.link_prev;
+ builtin_select.link_prev= NULL; // indicator of removal
+ }
+
+ if (set_main_unit(sel->master_unit()))
return true;
+
+ DBUG_ASSERT(builtin_select.table_list.elements == 1);
+ TABLE_LIST *insert_table= builtin_select.table_list.first;
+
+ if (!(insert_table->next_local= sel->table_list.first))
+ {
+ sel->table_list.next= &insert_table->next_local;
+ }
+ sel->table_list.first= insert_table;
+ sel->table_list.elements++;
+ insert_table->select_lex= sel;
+
+ sel->context.first_name_resolution_table= insert_table;
+ builtin_select.context= sel->context;
+ change_item_list_context(&field_list, &sel->context);
+
+ if (sel->tvc && !sel->next_select() &&
+ (sql_command == SQLCOM_INSERT_SELECT ||
+ sql_command == SQLCOM_REPLACE_SELECT))
+ {
+ DBUG_PRINT("info", ("'Usual' INSERT detected"));
+ many_values= sel->tvc->lists_of_values;
+ sel->options= sel->tvc->select_options;
+ sel->tvc= NULL;
+ if (sql_command == SQLCOM_INSERT_SELECT)
+ sql_command= SQLCOM_INSERT;
+ else
+ sql_command= SQLCOM_REPLACE;
+ }
+
+
+ for (SELECT_LEX *sel= all_selects_list;
+ sel;
+ sel= sel->next_select_in_list())
+ {
+ if (sel->select_number != 1)
+ sel->select_number--;
+ };
+
+ DBUG_RETURN(FALSE);
+}
+
+
+/**
+ Create an Item_singlerow_subselect for a query expression.
+*/
+
+Item *LEX::create_item_query_expression(THD *thd,
+ st_select_lex_unit *unit)
+{
+ if (clause_that_disallows_subselect)
+ {
+ my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0),
+ clause_that_disallows_subselect);
+ return NULL;
+ }
+
+ // Add the subtree of subquery to the current SELECT_LEX
+ SELECT_LEX *curr_sel= select_stack_head();
+ DBUG_ASSERT(current_select == curr_sel ||
+ (curr_sel == NULL && current_select == &builtin_select));
+ if (!curr_sel)
+ {
+ curr_sel= &builtin_select;
+ curr_sel->register_unit(unit, &curr_sel->context);
+ curr_sel->add_statistics(unit);
+ }
+
+ return new (thd->mem_root)
+ Item_singlerow_subselect(thd, unit->first_select());
+}
+
+
+SELECT_LEX_UNIT *LEX::parsed_select_expr_start(SELECT_LEX *s1, SELECT_LEX *s2,
+ enum sub_select_type unit_type,
+ bool distinct)
+{
+ SELECT_LEX_UNIT *res;
+ SELECT_LEX *sel1;
+ SELECT_LEX *sel2;
+ if (!s1->next_select())
+ sel1= s1;
+ else
+ {
+ sel1= wrap_unit_into_derived(s1->master_unit());
+ if (!sel1)
+ return NULL;
+ }
+ if (!s2->next_select())
+ sel2= s2;
+ else
+ {
+ sel2= wrap_unit_into_derived(s2->master_unit());
+ if (!sel2)
+ return NULL;
+ }
+ sel1->link_neighbour(sel2);
+ sel2->set_linkage_and_distinct(unit_type, distinct);
+ sel2->first_nested= sel1->first_nested= sel1;
+ res= create_unit(sel1);
+ if (res == NULL)
+ return NULL;
+ res->pre_last_parse= sel1;
+ push_select(res->fake_select_lex);
+ return res;
+}
+
+
+SELECT_LEX_UNIT *LEX::parsed_select_expr_cont(SELECT_LEX_UNIT *unit,
+ SELECT_LEX *s2,
+ enum sub_select_type unit_type,
+ bool distinct, bool oracle)
+{
+ DBUG_ASSERT(!s2->next_select());
+ SELECT_LEX *sel1= s2;
+ SELECT_LEX *last= unit->pre_last_parse->next_select();
+
+ int cmp= oracle? 0 : cmp_unit_op(unit_type, last->get_linkage());
+ if (cmp == 0)
+ {
+ sel1->first_nested= last->first_nested;
+ }
+ else if (cmp > 0)
+ {
+ last->first_nested= unit->pre_last_parse;
+ sel1->first_nested= last;
+ }
+ else /* cmp < 0 */
+ {
+ SELECT_LEX *first_in_nest= last->first_nested;
+ if (first_in_nest->first_nested != first_in_nest)
+ {
+ /* There is a priority jump starting from first_in_nest */
+ if ((last= create_priority_nest(first_in_nest)) == NULL)
+ return NULL;
+ unit->fix_distinct();
+ }
+ sel1->first_nested= last->first_nested;
+ }
+ last->link_neighbour(sel1);
+ sel1->set_master_unit(unit);
+ sel1->set_linkage_and_distinct(unit_type, distinct);
+ unit->pre_last_parse= last;
+ return unit;
+}
+
+
+/**
+ Add primary expression as the next term in a given query expression body
+ pruducing a new query expression body
+*/
+
+SELECT_LEX_UNIT *
+LEX::add_primary_to_query_expression_body(SELECT_LEX_UNIT *unit,
+ SELECT_LEX *sel,
+ enum sub_select_type unit_type,
+ bool distinct,
+ bool oracle)
+{
+ SELECT_LEX *sel2= sel;
+ if (sel->master_unit() && sel->master_unit()->first_select()->next_select())
+ {
+ sel2= wrap_unit_into_derived(sel->master_unit());
+ if (!sel2)
+ return NULL;
+ }
+ SELECT_LEX *sel1= unit->first_select();
+ if (!sel1->next_select())
+ unit= parsed_select_expr_start(sel1, sel2, unit_type, distinct);
+ else
+ unit= parsed_select_expr_cont(unit, sel2, unit_type, distinct, oracle);
+ return unit;
+}
+
+
+/**
+ Add query primary to a parenthesized query primary
+ pruducing a new query expression body
+*/
+
+SELECT_LEX_UNIT *
+LEX::add_primary_to_query_expression_body_ext_parens(
+ SELECT_LEX_UNIT *unit,
+ SELECT_LEX *sel,
+ enum sub_select_type unit_type,
+ bool distinct)
+{
+ SELECT_LEX *sel1= unit->first_select();
+ if (unit->first_select()->next_select())
+ {
+ sel1= wrap_unit_into_derived(unit);
+ if (!sel1)
+ return NULL;
+ if (!create_unit(sel1))
+ return NULL;
+ }
+ SELECT_LEX *sel2= sel;
+ if (sel->master_unit() && sel->master_unit()->first_select()->next_select())
+ {
+ sel2= wrap_unit_into_derived(sel->master_unit());
+ if (!sel2)
+ return NULL;
+ }
+ unit= parsed_select_expr_start(sel1, sel2, unit_type, distinct);
+ return unit;
+}
+
+
+/**
+ Process multi-operand query expression body
+*/
+
+bool LEX::parsed_multi_operand_query_expression_body(SELECT_LEX_UNIT *unit)
+{
+ SELECT_LEX *first_in_nest=
+ unit->pre_last_parse->next_select()->first_nested;
+ if (first_in_nest->first_nested != first_in_nest)
+ {
+ /* There is a priority jump starting from first_in_nest */
+ if (create_priority_nest(first_in_nest) == NULL)
+ return true;
+ unit->fix_distinct();
}
- current_select->linkage= DERIVED_TABLE_TYPE;
- return tvc_finalize();
+ return false;
+}
+
+
+/**
+ Add non-empty tail to a query expression body
+*/
+
+SELECT_LEX_UNIT *LEX::add_tail_to_query_expression_body(SELECT_LEX_UNIT *unit,
+ Lex_order_limit_lock *l)
+{
+ DBUG_ASSERT(l != NULL);
+ pop_select();
+ SELECT_LEX *sel= unit->first_select()->next_select() ? unit->fake_select_lex :
+ unit->first_select();
+ l->set_to(sel);
+ return unit;
+}
+
+
+/**
+ Add non-empty tail to a parenthesized query primary
+*/
+
+SELECT_LEX_UNIT *
+LEX::add_tail_to_query_expression_body_ext_parens(SELECT_LEX_UNIT *unit,
+ Lex_order_limit_lock *l)
+{
+ SELECT_LEX *sel= unit->first_select()->next_select() ? unit->fake_select_lex :
+ unit->first_select();
+
+ DBUG_ASSERT(l != NULL);
+
+ pop_select();
+ if (sel->is_set_query_expr_tail)
+ {
+ if (!l->order_list && !sel->explicit_limit)
+ l->order_list= &sel->order_list;
+ else
+ {
+ if (!unit)
+ return NULL;
+ sel= wrap_unit_into_derived(unit);
+ if (!sel)
+ return NULL;
+ if (!create_unit(sel))
+ return NULL;
+ }
+ }
+ l->set_to(sel);
+ return sel->master_unit();
+}
+
+
+/**
+ Process subselect parsing
+*/
+
+SELECT_LEX *LEX::parsed_subselect(SELECT_LEX_UNIT *unit)
+{
+ if (clause_that_disallows_subselect)
+ {
+ my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0),
+ clause_that_disallows_subselect);
+ return NULL;
+ }
+
+ // Add the subtree of subquery to the current SELECT_LEX
+ SELECT_LEX *curr_sel= select_stack_head();
+ DBUG_ASSERT(current_select == curr_sel ||
+ (curr_sel == NULL && current_select == &builtin_select));
+ if (curr_sel)
+ {
+ curr_sel->register_unit(unit, context_stack.head());
+ curr_sel->add_statistics(unit);
+ }
+
+ return unit->first_select();
+}
+
+
+/**
+ Process INSERT-like select
+*/
+
+bool LEX::parsed_insert_select(SELECT_LEX *first_select)
+{
+ if (sql_command == SQLCOM_INSERT ||
+ sql_command == SQLCOM_REPLACE)
+ {
+ if (sql_command == SQLCOM_INSERT)
+ sql_command= SQLCOM_INSERT_SELECT;
+ else
+ sql_command= SQLCOM_REPLACE_SELECT;
+ }
+ insert_select_hack(first_select);
+ if (check_main_unit_semantics())
+ return true;
+
+ // fix "main" select
+ SELECT_LEX *blt __attribute__((unused))= pop_select();
+ DBUG_ASSERT(blt == &builtin_select);
+ push_select(first_select);
+ return false;
+}
+
+
+bool LEX::parsed_TVC_start()
+{
+ SELECT_LEX *sel;
+ save_values_list_state();
+ many_values.empty();
+ insert_list= 0;
+ if (!(sel= alloc_select(TRUE)) ||
+ push_select(sel))
+ return true;
+ sel->init_select();
+ sel->braces= FALSE; // just initialisation
+ return false;
+}
+
+
+SELECT_LEX *LEX::parsed_TVC_end()
+{
+ SELECT_LEX *res= pop_select(); // above TVC select
+ if (!(res->tvc=
+ new (thd->mem_root) table_value_constr(many_values,
+ res,
+ res->options)))
+ return NULL;
+ restore_values_list_state();
+ return res;
+}
+
+
+
+TABLE_LIST *LEX::parsed_derived_table(SELECT_LEX_UNIT *unit,
+ int for_system_time,
+ LEX_CSTRING *alias)
+{
+ TABLE_LIST *res;
+ derived_tables|= DERIVED_SUBQUERY;
+ unit->first_select()->set_linkage(DERIVED_TABLE_TYPE);
+
+ // Add the subtree of subquery to the current SELECT_LEX
+ SELECT_LEX *curr_sel= select_stack_head();
+ DBUG_ASSERT(current_select == curr_sel ||
+ (curr_sel == NULL && current_select == &builtin_select));
+
+ Table_ident *ti= new (thd->mem_root) Table_ident(unit);
+ if (ti == NULL)
+ return NULL;
+ if (!(res= curr_sel->add_table_to_list(thd, ti, alias, 0,
+ TL_READ, MDL_SHARED_READ)))
+ return NULL;
+ if (for_system_time)
+ {
+ res->vers_conditions= vers_conditions;
+ }
+ return res;
+}
+
+bool LEX::parsed_create_view(SELECT_LEX_UNIT *unit, int check)
+{
+ SQL_I_List<TABLE_LIST> *save= &first_select_lex()->table_list;
+ if (set_main_unit(unit))
+ return true;
+ if (check_main_unit_semantics())
+ return true;
+ first_select_lex()->table_list.push_front(save);
+ current_select= first_select_lex();
+ size_t len= thd->m_parser_state->m_lip.get_cpp_ptr() -
+ create_view->select.str;
+ void *create_view_select= thd->memdup(create_view->select.str, len);
+ create_view->select.length= len;
+ create_view->select.str= (char *) create_view_select;
+ size_t not_used;
+ trim_whitespace(thd->charset(),
+ &create_view->select, ¬_used);
+ create_view->check= check;
+ parsing_options.allows_variable= TRUE;
+ return false;
+}
+
+bool LEX::select_finalize(st_select_lex_unit *expr)
+{
+ sql_command= SQLCOM_SELECT;
+ selects_allow_into= TRUE;
+ selects_allow_procedure= TRUE;
+ if (set_main_unit(expr))
+ return true;
+ return check_main_unit_semantics();
+}
+
+
+bool LEX::select_finalize(st_select_lex_unit *expr, Lex_select_lock l)
+{
+ return expr->set_lock_to_the_last_select(l) ||
+ select_finalize(expr);
+}
+
+
+/*
+ "IN" and "EXISTS" subselect can appear in two statement types:
+
+ 1. Statements that can have table columns, such as SELECT, DELETE, UPDATE
+ 2. Statements that cannot have table columns, e.g:
+ RETURN ((1) IN (SELECT * FROM t1))
+ IF ((1) IN (SELECT * FROM t1))
+
+ Statements of the first type call master_select_push() in the beginning.
+ In such case everything is properly linked.
+
+ Statements of the second type do not call mastr_select_push().
+ Here we catch the second case and relink thd->lex->builtin_select and
+ select_lex to properly point to each other.
+
+ QQ: Shouldn't subselects of other type also call relink_hack()?
+ QQ: Can we do it at constructor time instead?
+*/
+
+void LEX::relink_hack(st_select_lex *select_lex)
+{
+ if (!select_stack_top) // Statements of the second type
+ {
+ if (!select_lex->get_master()->get_master())
+ ((st_select_lex *) select_lex->get_master())->
+ set_master(&builtin_select);
+ if (!builtin_select.get_slave())
+ builtin_select.set_slave(select_lex->get_master());
+ }
+}
+
+
+bool SELECT_LEX_UNIT::set_lock_to_the_last_select(Lex_select_lock l)
+{
+ if (l.defined_lock)
+ {
+ SELECT_LEX *sel= first_select();
+ while (sel->next_select())
+ sel= sel->next_select();
+ if (sel->braces)
+ {
+ my_error(ER_WRONG_USAGE, MYF(0), "lock options",
+ "SELECT in brackets");
+ return TRUE;
+ }
+ l.set_to(sel);
+ }
+ return FALSE;
+}
+
+/**
+ Generate unique name for generated derived table for this SELECT
+*/
+
+bool SELECT_LEX::make_unique_derived_name(THD *thd, LEX_CSTRING *alias)
+{
+ // uint32 digits + two underscores + trailing '\0'
+ char buff[MAX_INT_WIDTH + 2 + 1];
+ alias->length= my_snprintf(buff, sizeof(buff), "__%u", select_number);
+ alias->str= thd->strmake(buff, alias->length);
+ return !alias->str;
+}
+
+
+/*
+ Make a new sp_instr_stmt and set its m_query to a concatenation
+ of two strings.
+*/
+bool LEX::new_sp_instr_stmt(THD *thd,
+ const LEX_CSTRING &prefix,
+ const LEX_CSTRING &suffix)
+{
+ LEX_STRING qbuff;
+ sp_instr_stmt *i;
+
+ if (!(i= new (thd->mem_root) sp_instr_stmt(sphead->instructions(),
+ spcont, this)))
+ return true;
+
+ qbuff.length= prefix.length + suffix.length;
+ if (!(qbuff.str= (char*) alloc_root(thd->mem_root, qbuff.length + 1)))
+ return true;
+ if (prefix.length)
+ memcpy(qbuff.str, prefix.str, prefix.length);
+ strmake(qbuff.str + prefix.length, suffix.str, suffix.length);
+ i->m_query= qbuff;
+ return sphead->add_instr(i);
+}
+
+
+bool LEX::sp_proc_stmt_statement_finalize_buf(THD *thd, const LEX_CSTRING &qbuf)
+{
+ sphead->m_flags|= sp_get_flags_for_command(this);
+ /* "USE db" doesn't work in a procedure */
+ if (unlikely(sql_command == SQLCOM_CHANGE_DB))
+ {
+ my_error(ER_SP_BADSTATEMENT, MYF(0), "USE");
+ return true;
+ }
+ /*
+ Don't add an instruction for SET statements, since all
+ instructions for them were already added during processing
+ of "set" rule.
+ */
+ DBUG_ASSERT(sql_command != SQLCOM_SET_OPTION || var_list.is_empty());
+ if (sql_command != SQLCOM_SET_OPTION)
+ return new_sp_instr_stmt(thd, empty_clex_str, qbuf);
+ return false;
+}
+
+
+bool LEX::sp_proc_stmt_statement_finalize(THD *thd, bool no_lookahead)
+{
+ // Extract the query statement from the tokenizer
+ Lex_input_stream *lip= &thd->m_parser_state->m_lip;
+ Lex_cstring qbuf(sphead->m_tmp_query, no_lookahead ? lip->get_ptr() :
+ lip->get_tok_start());
+ return LEX::sp_proc_stmt_statement_finalize_buf(thd, qbuf);
+}
+
+
+/**
+ @brief
+ Extract the condition that can be pushed into WHERE clause
+
+ @param thd the thread handle
+ @param cond the condition from which to extract a pushed condition
+ @param remaining_cond IN/OUT the condition that will remain of cond after
+ the extraction
+ @param transformer the transformer callback function to be
+ applied to the fields of the condition so it
+ can be pushed`
+ @param arg parameter to be passed to the transformer
+
+ @details
+ This function builds the most restrictive condition depending only on
+ the fields used in the GROUP BY of this SELECT. These fields were
+ collected before in grouping_tmp_fields list of this SELECT.
+
+ First this method checks if this SELECT doesn't have any aggregation
+ functions and has no GROUP BY clause. If so cond can be entirely pushed
+ into WHERE.
+
+ Otherwise the method checks if there is a condition depending only on
+ grouping fields that can be extracted from cond.
+
+ The condition that can be pushed into WHERE should be transformed.
+ It is done by transformer.
+
+ The extracted condition is saved in cond_pushed_into_where of this select.
+ cond can remain un empty after the extraction of the condition that can be
+ pushed into WHERE. It is saved in remaining_cond.
+
+ @note
+ This method is called for pushdown conditions into materialized
+ derived tables/views optimization.
+ Item::derived_field_transformer_for_where is passed as the actual
+ callback function.
+ Also it is called for pushdown into materialized IN subqueries.
+ Item::in_subq_field_transformer_for_where is passed as the actual
+ callback function.
+*/
+
+void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond,
+ Item **remaining_cond,
+ Item_transformer transformer,
+ uchar *arg)
+{
+ if (!cond_pushdown_is_allowed())
+ return;
+ thd->lex->current_select= this;
+ if (have_window_funcs())
+ {
+ Item *cond_over_partition_fields;
+ check_cond_extraction_for_grouping_fields(thd, cond);
+ cond_over_partition_fields=
+ build_cond_for_grouping_fields(thd, cond, true);
+ if (cond_over_partition_fields)
+ cond_over_partition_fields= cond_over_partition_fields->transform(thd,
+ &Item::grouping_field_transformer_for_where,
+ (uchar*) this);
+ if (cond_over_partition_fields)
+ {
+ cond_over_partition_fields->walk(
+ &Item::cleanup_excluding_const_fields_processor, 0, 0);
+ cond_pushed_into_where= cond_over_partition_fields;
+ }
+
+ return;
+ }
+
+ if (!join->group_list && !with_sum_func)
+ {
- cond=
- cond->transform(thd, transformer, arg);
++ cond= transform_condition_or_part(thd, cond, transformer, arg);
+ if (cond)
+ {
+ cond->walk(
+ &Item::cleanup_excluding_const_fields_processor, 0, 0);
+ cond_pushed_into_where= cond;
+ }
+
+ return;
+ }
+
+ /*
+ Figure out what can be extracted from cond and pushed into
+ the WHERE clause of this select.
+ */
+ Item *cond_over_grouping_fields;
+ check_cond_extraction_for_grouping_fields(thd, cond);
+ cond_over_grouping_fields=
+ build_cond_for_grouping_fields(thd, cond, true);
+
+ /*
+ Transform references to the columns of condition that can be pushed
+ into WHERE so it can be pushed.
+ */
+ if (cond_over_grouping_fields)
- cond_over_grouping_fields= cond_over_grouping_fields->transform(thd,
- &Item::grouping_field_transformer_for_where,
- (uchar*) this);
++ {
++ cond_over_grouping_fields=
++ transform_condition_or_part(thd, cond_over_grouping_fields,
++ &Item::grouping_field_transformer_for_where,
++ (uchar*) this);
++ }
+
+ if (cond_over_grouping_fields)
+ {
+
+ /*
+ Remove top conjuncts in cond that has been pushed into the WHERE
+ clause of this select
+ */
+ cond= remove_pushed_top_conjuncts(thd, cond);
+
+ cond_over_grouping_fields->walk(
+ &Item::cleanup_excluding_const_fields_processor, 0, 0);
+ cond_pushed_into_where= cond_over_grouping_fields;
+ }
+
+ *remaining_cond= cond;
+}
+
+
+/**
+ @brief
+ Mark OR-conditions as non-pushable to avoid repeatable pushdown
+
+ @param cond the processed condition
+
+ @details
+ Consider pushdown into the materialized derived table/view.
+ Consider OR condition that can be pushed into HAVING and some
+ parts of this OR condition that can be pushed into WHERE.
+
+ On example:
+
+ SELECT *
+ FROM t1,
+ (
+ SELECT a,MAX(c) AS m_c
+ GROUP BY a
+ ) AS dt
+ WHERE ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) AND
+ (t1.a=v1.a);
+
+
+ Here ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) or1
+ can be pushed down into the HAVING of the materialized
+ derived table dt.
+
+ (dt.a>2) OR (dt.a<3) part of or1 depends only on grouping fields
+ of dt and can be pushed into WHERE.
+
+ As a result:
+
+ SELECT *
+ FROM t1,
+ (
+ SELECT a,MAX(c) AS m_c
+ WHERE (dt.a>2) OR (dt.a<3)
+ GROUP BY a
+ HAVING ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3))
+ ) AS dt
+ WHERE ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) AND
+ (t1.a=v1.a);
+
+
+ Here (dt.a>2) OR (dt.a<3) also remains in HAVING of dt.
+ When SELECT that defines df is processed HAVING pushdown optimization
+ is made. In HAVING pushdown optimization it will extract
+ (dt.a>2) OR (dt.a<3) condition from or1 again and push it into WHERE.
+ This will cause duplicate conditions in WHERE of dt.
+
+ To avoid repeatable pushdown such OR conditions as or1 describen
+ above are marked with NO_EXTRACTION_FL.
+
+ @note
+ This method is called for pushdown into materialized
+ derived tables/views/IN subqueries optimization.
+*/
+
+void mark_or_conds_to_avoid_pushdown(Item *cond)
+{
+ if (cond->type() == Item::COND_ITEM &&
+ ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
+ {
+ List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+ Item *item;
+ while ((item=li++))
+ {
+ if (item->type() == Item::COND_ITEM &&
+ ((Item_cond*) item)->functype() == Item_func::COND_OR_FUNC)
+ item->set_extraction_flag(NO_EXTRACTION_FL);
+ }
+ }
+ else if (cond->type() == Item::COND_ITEM &&
+ ((Item_cond*) cond)->functype() == Item_func::COND_OR_FUNC)
+ cond->set_extraction_flag(NO_EXTRACTION_FL);
+}
+
+/**
+ @brief
+ Get condition that can be pushed from HAVING into WHERE
+
+ @param thd the thread handle
+ @param cond the condition from which to extract the condition
+
+ @details
+ The method collects in attach_to_conds list conditions from cond
+ that can be pushed from HAVING into WHERE.
+
+ Conditions that can be pushed were marked with FULL_EXTRACTION_FL in
+ check_cond_extraction_for_grouping_fields() method.
+ Conditions that can't be pushed were marked with NO_EXTRACTION_FL.
+ Conditions which parts can be pushed weren't marked.
+
+ There are two types of conditions that can be pushed:
+ 1. Condition that can be simply moved from HAVING
+ (if cond is marked with FULL_EXTRACTION_FL or
+ cond is an AND condition and some of its parts are marked with
+ FULL_EXTRACTION_FL)
+ In this case condition is transformed and pushed into attach_to_conds
+ list.
+ 2. Part of some other condition c1 that can't be entirely pushed
+ (if с1 isn't marked with any flag).
+
+ For example:
+
+ SELECT t1.a,MAX(t1.b),t1.c
+ FROM t1
+ GROUP BY t1.a
+ HAVING ((t1.a > 5) AND (t1.c < 3)) OR (t1.a = 3);
+
+ Here (t1.a > 5) OR (t1.a = 3) from HAVING can be pushed into WHERE.
+
+ In this case build_pushable_cond() is called for c1.
+ This method builds a clone of the c1 part that can be pushed.
+
+ Transformation mentioned above is made with multiple_equality_transformer
+ transformer. It transforms all multiple equalities in the extracted
+ condition into the set of equalities.
+
+ @note
+ Conditions that can be pushed are collected in attach_to_conds in this way:
+ 1. if cond is an AND condition its parts that can be pushed into WHERE
+ are added to attach_to_conds list separately.
+ 2. in all other cases conditions are pushed into the list entirely.
+
+ @retval
+ true - if an error occurs
+ false - otherwise
+*/
+
+bool
+st_select_lex::build_pushable_cond_for_having_pushdown(THD *thd, Item *cond)
+{
+ List<Item> equalities;
+
+ /* Condition can't be pushed */
+ if (cond->get_extraction_flag() == NO_EXTRACTION_FL)
+ return false;
+
+ /**
+ Condition can be pushed entirely.
+ Transform its multiple equalities and add to attach_to_conds list.
+ */
+ if (cond->get_extraction_flag() == FULL_EXTRACTION_FL)
+ {
+ Item *result= cond->transform(thd,
+ &Item::multiple_equality_transformer,
+ (uchar *)this);
+ if (!result)
+ return true;
+ if (result->type() == Item::COND_ITEM &&
+ ((Item_cond*) result)->functype() == Item_func::COND_AND_FUNC)
+ {
+ List_iterator<Item> li(*((Item_cond*) result)->argument_list());
+ Item *item;
+ while ((item= li++))
+ {
+ if (attach_to_conds.push_back(item, thd->mem_root))
+ return true;
+ }
+ }
+ else
+ {
+ if (attach_to_conds.push_back(result, thd->mem_root))
+ return true;
+ }
+ return false;
+ }
+
+ /**
+ There is no flag set for this condition. It means that some
+ part of this condition can be pushed.
+ */
+ if (cond->type() != Item::COND_ITEM)
+ return false;
+
+ if (((Item_cond *)cond)->functype() != Item_cond::COND_AND_FUNC)
+ {
+ /*
+ cond is not a conjunctive formula and it cannot be pushed into WHERE.
+ Try to extract a formula that can be pushed.
+ */
+ Item *fix= cond->build_pushable_cond(thd, 0, 0);
+ if (!fix)
+ return false;
+ if (attach_to_conds.push_back(fix, thd->mem_root))
+ return true;
+ }
+ else
+ {
+ List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+ Item *item;
+ while ((item=li++))
+ {
+ if (item->get_extraction_flag() == NO_EXTRACTION_FL)
+ continue;
+ else if (item->get_extraction_flag() == FULL_EXTRACTION_FL)
+ {
+ Item *result= item->transform(thd,
+ &Item::multiple_equality_transformer,
+ (uchar *)item);
+ if (!result)
+ return true;
+ if (result->type() == Item::COND_ITEM &&
+ ((Item_cond*) result)->functype() == Item_func::COND_AND_FUNC)
+ {
+ List_iterator<Item> li(*((Item_cond*) result)->argument_list());
+ Item *item;
+ while ((item=li++))
+ {
+ if (attach_to_conds.push_back(item, thd->mem_root))
+ return true;
+ }
+ }
+ else
+ {
+ if (attach_to_conds.push_back(result, thd->mem_root))
+ return true;
+ }
+ }
+ else
+ {
+ Item *fix= item->build_pushable_cond(thd, 0, 0);
+ if (!fix)
+ continue;
+ if (attach_to_conds.push_back(fix, thd->mem_root))
+ return true;
+ }
+ }
+ }
+ return false;
+}
+
+
+/**
+ Check if item is equal to some field in Field_pair 'field_pair'
+ from 'pair_list' and return found 'field_pair' if it exists.
+*/
+
+Field_pair *get_corresponding_field_pair(Item *item,
+ List<Field_pair> pair_list)
+{
+ DBUG_ASSERT(item->type() == Item::FIELD_ITEM ||
+ (item->type() == Item::REF_ITEM &&
+ ((((Item_ref *) item)->ref_type() == Item_ref::VIEW_REF) ||
+ (((Item_ref *) item)->ref_type() == Item_ref::REF))));
+
+ List_iterator<Field_pair> it(pair_list);
+ Field_pair *field_pair;
+ Item_field *field_item= (Item_field *) (item->real_item());
+ while ((field_pair= it++))
+ {
+ if (field_item->field == field_pair->field)
+ return field_pair;
+ }
+ return NULL;
+}
+
+
+/**
+ @brief
+ Collect fields from multiple equalities which are equal to grouping
+
+ @param thd the thread handle
+
+ @details
+ This method checks if multiple equalities of the WHERE clause contain
+ fields from GROUP BY of this SELECT. If so all fields of such multiple
+ equalities are collected in grouping_tmp_fields list without repetitions.
+
+ @retval
+ true - if an error occurs
+ false - otherwise
+*/
+
+bool st_select_lex::collect_fields_equal_to_grouping(THD *thd)
+{
+ if (!join->cond_equal || join->cond_equal->is_empty())
+ return false;
+
+ List_iterator_fast<Item_equal> li(join->cond_equal->current_level);
+ Item_equal *item_equal;
+
+ while ((item_equal= li++))
+ {
+ Item_equal_fields_iterator it(*item_equal);
+ Item *item;
+ while ((item= it++))
+ {
+ if (get_corresponding_field_pair(item, grouping_tmp_fields))
+ break;
+ }
+ if (!item)
+ break;
+
+ it.rewind();
+ while ((item= it++))
+ {
+ if (get_corresponding_field_pair(item, grouping_tmp_fields))
+ continue;
+ Field_pair *grouping_tmp_field=
+ new Field_pair(((Item_field *)item->real_item())->field, item);
+ if (grouping_tmp_fields.push_back(grouping_tmp_field, thd->mem_root))
+ return true;
+ }
+ }
+ return false;
+}
+
+
+/**
+ @brief
+ Remove marked top conjuncts of HAVING for having pushdown
+
+ @param thd the thread handle
+ @param cond the condition which subformulas are to be removed
+
+ @details
+ This method removes from cond all subformulas that can be moved from HAVING
+ into WHERE.
+
+ @retval
+ condition without removed subformulas
+ 0 if the whole 'cond' is removed
+*/
+
+Item *remove_pushed_top_conjuncts_for_having(THD *thd, Item *cond)
+{
+ /* Nothing to extract */
+ if (cond->get_extraction_flag() == NO_EXTRACTION_FL)
+ {
+ cond->clear_extraction_flag();
+ return cond;
+ }
+ /* cond can be pushed in WHERE entirely */
+ if (cond->get_extraction_flag() == FULL_EXTRACTION_FL)
+ {
+ cond->clear_extraction_flag();
+ return 0;
+ }
+
+ /* Some parts of cond can be pushed */
+ if (cond->type() == Item::COND_ITEM &&
+ ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
+ {
+ List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+ Item *item;
+ while ((item=li++))
+ {
+ if (item->get_extraction_flag() == NO_EXTRACTION_FL)
+ item->clear_extraction_flag();
+ else if (item->get_extraction_flag() == FULL_EXTRACTION_FL)
+ {
+ if (item->type() == Item::FUNC_ITEM &&
+ ((Item_func*) item)->functype() == Item_func::MULT_EQUAL_FUNC)
+ item->set_extraction_flag(DELETION_FL);
+ else
+ {
+ item->clear_extraction_flag();
+ li.remove();
+ }
+ }
+ }
+ switch (((Item_cond*) cond)->argument_list()->elements)
+ {
+ case 0:
+ return 0;
+ case 1:
+ return (((Item_cond*) cond)->argument_list()->head());
+ default:
+ return cond;
+ }
+ }
+ return cond;
+}
+
+
+/**
+ @brief
+ Extract condition that can be pushed from HAVING into WHERE
+
+ @param thd the thread handle
+ @param having the HAVING clause of this select
+ @param having_equal multiple equalities of HAVING
+
+ @details
+ This method builds a set of conditions dependent only on
+ fields used in the GROUP BY of this select (directly or indirectly
+ through equalities). These conditions are extracted from the HAVING
+ clause of this select.
+ The method saves these conditions into attach_to_conds list and removes
+ from HAVING conditions that can be entirely pushed into WHERE.
+
+ Example of the HAVING pushdown transformation:
+
+ SELECT t1.a,MAX(t1.b)
+ FROM t1
+ GROUP BY t1.a
+ HAVING (t1.a>2) AND (MAX(c)>12);
+
+ =>
+
+ SELECT t1.a,MAX(t1.b)
+ FROM t1
+ WHERE (t1.a>2)
+ GROUP BY t1.a
+ HAVING (MAX(c)>12);
+
+ In this method (t1.a>2) is not attached to the WHERE clause.
+ It is pushed into the attach_to_conds list to be attached to
+ the WHERE clause later.
+
+ In details:
+ 1. Collect fields used in the GROUP BY grouping_fields of this SELECT
+ 2. Collect fields equal to grouping_fields from the WHERE clause
+ of this SELECT and add them to the grouping_fields list.
+ 3. Extract the most restrictive condition from the HAVING clause of this
+ select that depends only on the grouping fields (directly or indirectly
+ through equality).
+ If the extracted condition is an AND condition it is transformed into a
+ list of all its conjuncts saved in attach_to_conds. Otherwise,
+ the condition is put into attach_to_conds as the only its element.
+ 4. Remove conditions from HAVING clause that can be entirely pushed
+ into WHERE.
+ Multiple equalities are not removed but marked with DELETION_FL flag.
+ They will be deleted later in substitite_for_best_equal_field() called
+ for the HAVING condition.
+ 5. Unwrap fields wrapped in Item_ref wrappers contained in the condition
+ of attach_to_conds so the condition could be pushed into WHERE.
+
+ @note
+ This method is similar to st_select_lex::pushdown_cond_into_where_clause().
+
+ @retval TRUE if an error occurs
+ @retval FALSE otherwise
+*/
+
+Item *st_select_lex::pushdown_from_having_into_where(THD *thd, Item *having)
+{
+ if (!having || !group_list.first)
+ return having;
+ if (!cond_pushdown_is_allowed())
+ return having;
+
+ st_select_lex *save_curr_select= thd->lex->current_select;
+ thd->lex->current_select= this;
+
+ /*
+ 1. Collect fields used in the GROUP BY grouping fields of this SELECT
+ 2. Collect fields equal to grouping_fields from the WHERE clause
+ of this SELECT and add them to the grouping fields list.
+ */
+ if (collect_grouping_fields(thd) ||
+ collect_fields_equal_to_grouping(thd))
+ return having;
+
+ /*
+ 3. Extract the most restrictive condition from the HAVING clause of this
+ select that depends only on the grouping fields (directly or indirectly
+ through equality).
+ If the extracted condition is an AND condition it is transformed into a
+ list of all its conjuncts saved in attach_to_conds. Otherwise,
+ the condition is put into attach_to_conds as the only its element.
+ */
+ List_iterator_fast<Item> it(attach_to_conds);
+ Item *item;
+ check_cond_extraction_for_grouping_fields(thd, having);
+ if (build_pushable_cond_for_having_pushdown(thd, having))
+ {
+ attach_to_conds.empty();
+ goto exit;
+ }
+ if (!attach_to_conds.elements)
+ goto exit;
+
+ /*
+ 4. Remove conditions from HAVING clause that can be entirely pushed
+ into WHERE.
+ Multiple equalities are not removed but marked with DELETION_FL flag.
+ They will be deleted later in substitite_for_best_equal_field() called
+ for the HAVING condition.
+ */
+ having= remove_pushed_top_conjuncts_for_having(thd, having);
+
+ /*
+ Change join->cond_equal which points to the multiple equalities of
+ the top level of HAVING.
+ Removal of AND conditions may leave only one conjunct in HAVING.
+
+ Example 1:
+ SELECT *
+ FROM t1
+ GROUP BY t1.a
+ (t1.a < 2) AND (t1.b = 2)
+
+ (t1.a < 2) is pushed into WHERE.
+ join->cond_equal should point on (t1.b = 2) multiple equality now.
+
+ Example 2:
+ SELECT *
+ FROM t1
+ GROUP BY t1.a
+ (t1.a = 2) AND (t1.b < 2)
+
+ (t1.a = 2) is pushed into WHERE.
+ join->cond_equal should be NULL now.
+ */
+ if (having &&
+ having->type() == Item::FUNC_ITEM &&
+ ((Item_func*) having)->functype() == Item_func::MULT_EQUAL_FUNC)
+ join->having_equal= new (thd->mem_root) COND_EQUAL((Item_equal *)having,
+ thd->mem_root);
+ else if (!having ||
+ having->type() != Item::COND_ITEM ||
+ ((Item_cond *)having)->functype() != Item_cond::COND_AND_FUNC)
+ join->having_equal= 0;
+
+ /*
+ 5. Unwrap fields wrapped in Item_ref wrappers contained in the condition
+ of attach_to_conds so the condition could be pushed into WHERE.
+ */
+ it.rewind();
+ while ((item=it++))
+ {
+ item= item->transform(thd,
+ &Item::field_transformer_for_having_pushdown,
+ (uchar *)this);
+
+ if (item->walk(&Item::cleanup_excluding_immutables_processor, 0, STOP_PTR)
+ || item->fix_fields(thd, NULL))
+ {
+ attach_to_conds.empty();
+ goto exit;
+ }
+ }
+exit:
+ thd->lex->current_select= save_curr_select;
+ return having;
+}
+
+
+bool LEX::stmt_install_plugin(const DDL_options_st &opt,
+ const Lex_ident_sys_st &name,
+ const LEX_CSTRING &soname)
+{
+ create_info.init();
+ if (add_create_options_with_check(opt))
+ return true;
+ sql_command= SQLCOM_INSTALL_PLUGIN;
+ comment= name;
+ ident= soname;
+ return false;
+}
+
+
+void LEX::stmt_install_plugin(const LEX_CSTRING &soname)
+{
+ sql_command= SQLCOM_INSTALL_PLUGIN;
+ comment= null_clex_str;
+ ident= soname;
+}
+
+
+bool LEX::stmt_uninstall_plugin_by_name(const DDL_options_st &opt,
+ const Lex_ident_sys_st &name)
+{
+ check_opt.init();
+ if (add_create_options_with_check(opt))
+ return true;
+ sql_command= SQLCOM_UNINSTALL_PLUGIN;
+ comment= name;
+ ident= null_clex_str;
+ return false;
+}
+
+
+bool LEX::stmt_uninstall_plugin_by_soname(const DDL_options_st &opt,
+ const LEX_CSTRING &soname)
+{
+ check_opt.init();
+ if (add_create_options_with_check(opt))
+ return true;
+ sql_command= SQLCOM_UNINSTALL_PLUGIN;
+ comment= null_clex_str;
+ ident= soname;
+ return false;
+}
+
+
+bool LEX::stmt_prepare_validate(const char *stmt_type)
+{
+ if (unlikely(table_or_sp_used()))
+ {
+ my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0), stmt_type);
+ return true;
+ }
+ return check_main_unit_semantics();
+}
+
+
+bool LEX::stmt_prepare(const Lex_ident_sys_st &ident, Item *code)
+{
+ sql_command= SQLCOM_PREPARE;
+ if (stmt_prepare_validate("PREPARE..FROM"))
+ return true;
+ prepared_stmt.set(ident, code, NULL);
+ return false;
+}
+
+
+bool LEX::stmt_execute_immediate(Item *code, List<Item> *params)
+{
+ sql_command= SQLCOM_EXECUTE_IMMEDIATE;
+ if (stmt_prepare_validate("EXECUTE IMMEDIATE"))
+ return true;
+ static const Lex_ident_sys immediate(STRING_WITH_LEN("IMMEDIATE"));
+ prepared_stmt.set(immediate, code, params);
+ return false;
+}
+
+
+bool LEX::stmt_execute(const Lex_ident_sys_st &ident, List<Item> *params)
+{
+ sql_command= SQLCOM_EXECUTE;
+ prepared_stmt.set(ident, NULL, params);
+ return stmt_prepare_validate("EXECUTE..USING");
+}
+
+
+void LEX::stmt_deallocate_prepare(const Lex_ident_sys_st &ident)
+{
+ sql_command= SQLCOM_DEALLOCATE_PREPARE;
+ prepared_stmt.set(ident, NULL, NULL);
+}
+
+
+bool LEX::stmt_alter_table_exchange_partition(Table_ident *table)
+{
+ DBUG_ASSERT(sql_command == SQLCOM_ALTER_TABLE);
+ first_select_lex()->db= table->db;
+ if (first_select_lex()->db.str == NULL &&
+ copy_db_to(&first_select_lex()->db))
+ return true;
+ name= table->table;
+ alter_info.partition_flags|= ALTER_PARTITION_EXCHANGE;
+ if (!first_select_lex()->add_table_to_list(thd, table, NULL,
+ TL_OPTION_UPDATING,
+ TL_READ_NO_INSERT,
+ MDL_SHARED_NO_WRITE))
+ return true;
+ DBUG_ASSERT(!m_sql_cmd);
+ m_sql_cmd= new (thd->mem_root) Sql_cmd_alter_table_exchange_partition();
+ return m_sql_cmd == NULL;
+}
+
+
+void LEX::stmt_purge_to(const LEX_CSTRING &to)
+{
+ type= 0;
+ sql_command= SQLCOM_PURGE;
+ to_log= to.str;
+}
+
+
+bool LEX::stmt_purge_before(Item *item)
+{
+ type= 0;
+ sql_command= SQLCOM_PURGE_BEFORE;
+ value_list.empty();
+ value_list.push_front(item, thd->mem_root);
+ return check_main_unit_semantics();
+}
+
+
+bool LEX::stmt_create_udf_function(const DDL_options_st &options,
+ enum_sp_aggregate_type agg_type,
+ const Lex_ident_sys_st &name,
+ Item_result return_type,
+ const LEX_CSTRING &soname)
+{
+ if (stmt_create_function_start(options))
+ return true;
+
+ if (unlikely(is_native_function(thd, &name)))
+ {
+ my_error(ER_NATIVE_FCT_NAME_COLLISION, MYF(0), name.str);
+ return true;
+ }
+ sql_command= SQLCOM_CREATE_FUNCTION;
+ udf.name= name;
+ udf.returns= return_type;
+ udf.dl= soname.str;
+ udf.type= agg_type == GROUP_AGGREGATE ? UDFTYPE_AGGREGATE :
+ UDFTYPE_FUNCTION;
+ stmt_create_routine_finalize();
+ return false;
+}
+
+
+bool LEX::stmt_create_stored_function_start(const DDL_options_st &options,
+ enum_sp_aggregate_type agg_type,
+ const sp_name *spname)
+{
+ if (stmt_create_function_start(options) ||
+ unlikely(!make_sp_head_no_recursive(thd, spname,
+ &sp_handler_function, agg_type)))
+ return true;
+ return false;
+}
+
+
+Spvar_definition *LEX::row_field_name(THD *thd, const Lex_ident_sys_st &name)
+{
+ Spvar_definition *res;
+ if (unlikely(check_string_char_length(&name, 0, NAME_CHAR_LEN,
+ system_charset_info, 1)))
+ {
+ my_error(ER_TOO_LONG_IDENT, MYF(0), name.str);
+ return NULL;
+ }
+ if (unlikely(!(res= new (thd->mem_root) Spvar_definition())))
+ return NULL;
+ init_last_field(res, &name, thd->variables.collation_database);
+ return res;
}
diff --cc sql/sql_show.cc
index 7b5b2c8bf89,e7c42cbc8e4..1f8278528cf
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@@ -63,9 -63,20 +63,22 @@@
#include "ha_partition.h"
#endif
#include "transaction.h"
+#include "opt_trace.h"
+#include "my_cpu.h"
+
+ #include "lex_symbol.h"
+ #define KEYWORD_SIZE 64
+
+ extern SYMBOL symbols[];
+ extern size_t symbols_length;
+
+ extern SYMBOL sql_functions[];
+ extern size_t sql_functions_length;
+
+ extern Native_func_registry func_array[];
+ extern size_t func_array_length;
+
enum enum_i_s_events_fields
{
ISE_EVENT_CATALOG= 0,
1
0