[Commits] 58b9507: MDEV-17096 Pushdown of simple derived tables to storage engines
by IgorBabaev 12 Feb '19
by IgorBabaev 12 Feb '19
12 Feb '19
revision-id: 58b950737c547df42c82744029cbcad235e0efd9 (mariadb-10.3.6-133-g58b9507)
parent(s): d11be23933def394585fae83a6f1ab561e3736f2
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-02-12 13:11:32 -0800
message:
MDEV-17096 Pushdown of simple derived tables to storage engines
Added plugin system variable federated_pushdown.
---
mysql-test/suite/federated/federatedx_create_handlers.test | 4 ++++
storage/federatedx/federatedx_pushdown.cc | 6 ++++++
storage/federatedx/ha_federatedx.cc | 10 ++++++++--
3 files changed, 18 insertions(+), 2 deletions(-)
diff --git a/mysql-test/suite/federated/federatedx_create_handlers.test b/mysql-test/suite/federated/federatedx_create_handlers.test
index 21539b6..558b846 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.test
+++ b/mysql-test/suite/federated/federatedx_create_handlers.test
@@ -1,6 +1,8 @@
--source have_federatedx.inc
--source include/federated.inc
+set global federated_pushdown=1;
+
connection slave;
DROP TABLE IF EXISTS federated.t1;
@@ -152,3 +154,5 @@ DROP TABLE federated.t1, federated.t2;
connection default;
source include/federated_cleanup.inc;
+
+set global federated_pushdown=0;
diff --git a/storage/federatedx/federatedx_pushdown.cc b/storage/federatedx/federatedx_pushdown.cc
index c53b359..906d87c 100644
--- a/storage/federatedx/federatedx_pushdown.cc
+++ b/storage/federatedx/federatedx_pushdown.cc
@@ -19,6 +19,9 @@
static derived_handler*
create_federatedx_derived_handler(THD* thd, TABLE_LIST *derived)
{
+ if (!use_pushdown)
+ return 0;
+
ha_federatedx_derived_handler* handler = NULL;
handlerton *ht= 0;
@@ -144,6 +147,9 @@ void ha_federatedx_derived_handler::print_error(int, unsigned long)
static select_handler*
create_federatedx_select_handler(THD* thd, SELECT_LEX *sel)
{
+ if (!use_pushdown)
+ return 0;
+
ha_federatedx_select_handler* handler = NULL;
handlerton *ht= 0;
diff --git a/storage/federatedx/ha_federatedx.cc b/storage/federatedx/ha_federatedx.cc
index 918fbef..f55b0bc 100644
--- a/storage/federatedx/ha_federatedx.cc
+++ b/storage/federatedx/ha_federatedx.cc
@@ -3677,11 +3677,17 @@ int ha_federatedx::discover_assisted(handlerton *hton, THD* thd,
return error;
}
-#include "federatedx_pushdown.cc"
struct st_mysql_storage_engine federatedx_storage_engine=
{ MYSQL_HANDLERTON_INTERFACE_VERSION };
+my_bool use_pushdown;
+static MYSQL_SYSVAR_BOOL(pushdown, use_pushdown, 0,
+ "Use query fragments pushdown capabilities", NULL, NULL, FALSE);
+static struct st_mysql_sys_var* sysvars[]= { MYSQL_SYSVAR(pushdown) };
+
+#include "federatedx_pushdown.cc"
+
maria_declare_plugin(federatedx)
{
MYSQL_STORAGE_ENGINE_PLUGIN,
@@ -3694,7 +3700,7 @@ maria_declare_plugin(federatedx)
federatedx_done, /* Plugin Deinit */
0x0201 /* 2.1 */,
NULL, /* status variables */
- NULL, /* system variables */
+ sysvars, /* system variables */
"2.1", /* string version */
MariaDB_PLUGIN_MATURITY_STABLE /* maturity */
}
1
0
revision-id: 1b6b99be24b1757fc7ee06f5dc4c3af0218aedc8 (mariadb-10.3.12-52-g1b6b99be24b)
parent(s): dcc838168fc711386367af65cc0a62e1f18edef1
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2019-02-12 16:17:47 +0100
message:
tempesta fixes
---
mysql-test/suite/galera_3nodes/include/have_mariabackup.inc | 4 ----
mysql-test/suite/galera_3nodes/r/galera_pc_weight.result | 5 -----
mysql-test/suite/galera_3nodes/suite.pm | 1 -
mysql-test/suite/galera_3nodes/t/galera_garbd.test | 1 +
mysql-test/suite/galera_3nodes/t/galera_ipv6_mariabackup.test | 2 +-
mysql-test/suite/galera_3nodes/t/galera_ipv6_xtrabackup-v2.opt | 1 -
6 files changed, 2 insertions(+), 12 deletions(-)
diff --git a/mysql-test/suite/galera_3nodes/include/have_mariabackup.inc b/mysql-test/suite/galera_3nodes/include/have_mariabackup.inc
deleted file mode 100644
index 0dd693f2c63..00000000000
--- a/mysql-test/suite/galera_3nodes/include/have_mariabackup.inc
+++ /dev/null
@@ -1,4 +0,0 @@
-#
-# suite.pm will make sure that all tests including this file
-# will be skipped as needed
-#
diff --git a/mysql-test/suite/galera_3nodes/r/galera_pc_weight.result b/mysql-test/suite/galera_3nodes/r/galera_pc_weight.result
index aca9a1d2059..5fb9c1b9d66 100644
--- a/mysql-test/suite/galera_3nodes/r/galera_pc_weight.result
+++ b/mysql-test/suite/galera_3nodes/r/galera_pc_weight.result
@@ -79,13 +79,8 @@ VARIABLE_VALUE = 'Synced'
SET GLOBAL wsrep_provider_options = 'pc.weight=1';
SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight';
VARIABLE_VALUE = 1
-<<<<<<< HEAD
-connection node_1;
-||||||| merged common ancestors
-=======
1
connection node_1;
->>>>>>> 10.2
SET GLOBAL wsrep_provider_options = 'gmcast.isolate=0';
connection node_2;
connection node_3;
diff --git a/mysql-test/suite/galera_3nodes/suite.pm b/mysql-test/suite/galera_3nodes/suite.pm
index 3c2074320f3..a7c1bf79c06 100644
--- a/mysql-test/suite/galera_3nodes/suite.pm
+++ b/mysql-test/suite/galera_3nodes/suite.pm
@@ -50,7 +50,6 @@ push @::global_suppressions,
qr(WSREP: There are no nodes in the same segment that will ever be able to become donors, yet there is a suitable donor outside. Will use that one.),
qr(WSREP: evs::proto.*),
qr|WSREP: Ignoring possible split-brain \(allowed by configuration\) from view:.*|,
- qr|WSREP: Ignoring possible split-brain \(allowed by configuration\) from view:.*|,
qr(WSREP: no nodes coming from prim view, prim not possible),
qr(WSREP: Member .* requested state transfer from .* but it is impossible to select State Transfer donor: Resource temporarily unavailable),
qr(WSREP: user message in state LEAVING),
diff --git a/mysql-test/suite/galera_3nodes/t/galera_garbd.test b/mysql-test/suite/galera_3nodes/t/galera_garbd.test
index 869bad8894f..2d03e8897b9 100644
--- a/mysql-test/suite/galera_3nodes/t/galera_garbd.test
+++ b/mysql-test/suite/galera_3nodes/t/galera_garbd.test
@@ -10,6 +10,7 @@
--let $galera_connection_name = node_3
--let $galera_server_number = 3
--source include/galera_connect.inc
+
# Save original auto_increment_offset values.
--let $node_1=node_1
--let $node_2=node_2
diff --git a/mysql-test/suite/galera_3nodes/t/galera_ipv6_mariabackup.test b/mysql-test/suite/galera_3nodes/t/galera_ipv6_mariabackup.test
index 8cbd8cf2454..84c33251c98 100644
--- a/mysql-test/suite/galera_3nodes/t/galera_ipv6_mariabackup.test
+++ b/mysql-test/suite/galera_3nodes/t/galera_ipv6_mariabackup.test
@@ -1,6 +1,6 @@
--source include/galera_cluster.inc
--source include/check_ipv6.inc
---source include/have_mariabackup.inc
+--source suite/galera/include/have_mariabackup.inc
# Confirm that initial handshake happened over ipv6
diff --git a/mysql-test/suite/galera_3nodes/t/galera_ipv6_xtrabackup-v2.opt b/mysql-test/suite/galera_3nodes/t/galera_ipv6_xtrabackup-v2.opt
deleted file mode 100644
index c195dd0f35b..00000000000
--- a/mysql-test/suite/galera_3nodes/t/galera_ipv6_xtrabackup-v2.opt
+++ /dev/null
@@ -1 +0,0 @@
---bind-address=:: --skip-innodb-safe-truncate
1
0
revision-id: b953d70d15d3a723e33aaa268a321b51d2b028ca (mariadb-10.3.12-50-gb953d70d15d)
parent(s): 31d6e9c3c83c5248789df14f13e6f14599170f9c 8a9cdc5f44b14466e79630778c5e76d941d28435
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2019-02-12 12:04:10 +0100
message:
Merge branch '10.2' into 10.3
libmariadb | 2 +-
mysql-test/main/mysql.result | 13 +++-
mysql-test/main/mysql.test | 17 ++---
mysql-test/suite/galera_3nodes/galera_2x3nodes.cnf | 1 +
mysql-test/suite/galera_3nodes/galera_3nodes.cnf | 1 +
.../suite/galera_3nodes/r/galera_garbd.result | 4 +
.../r/galera_safe_to_bootstrap.result | 20 -----
.../galera_3nodes/r/galera_var_dirty_reads2.result | 4 +
mysql-test/suite/galera_3nodes/suite.pm | 36 ---------
mysql-test/suite/galera_3nodes/t/galera_garbd.test | 11 ++-
.../galera_3nodes/t/galera_var_dirty_reads2.test | 14 ++++
mysql-test/suite/innodb/r/foreign_key.result | 10 +++
mysql-test/suite/innodb/t/foreign_key.test | 9 +++
storage/connect/ha_connect.cc | 2 +-
storage/connect/mysql-test/connect/r/xml.result | 2 +-
storage/innobase/handler/i_s.cc | 89 +++++++++++++---------
16 files changed, 129 insertions(+), 106 deletions(-)
diff --cc mysql-test/main/mysql.test
index 66ccef18327,00000000000..cd335973288
mode 100644,000000..100644
--- a/mysql-test/main/mysql.test
+++ b/mysql-test/main/mysql.test
@@@ -1,705 -1,0 +1,704 @@@
+-- source include/have_working_dns.inc
+# This test should work in embedded server after we fix mysqltest
+-- source include/not_embedded.inc
+#
+# Testing the MySQL command line client(mysql)
+#
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
+
+--disable_warnings
+drop table if exists t1,t2,t3;
+--enable_warnings
+
+#
+# Test the "delimiter" functionality
+# Bug#9879
+#
+create table t1(a int);
+insert into t1 values(1);
+
+# Test delimiters
+--exec $MYSQL test 2>&1 < "./main/mysql_delimiter.sql"
+
+--disable_query_log
+# Test delimiter : supplied on the command line
+select "Test delimiter : from command line" as "_";
+--exec $MYSQL test --delimiter=":" -e "select * from t1:"
+# Test delimiter :; supplied on the command line
+select "Test delimiter :; from command line" as "_";
+--exec $MYSQL test --delimiter=":;" -e "select * from t1:;"
+# Test 'go' command (vertical output) \G
+select "Test 'go' command(vertical output) \G" as "_";
+--exec $MYSQL test -e "select * from t1\G"
+# Test 'go' command \g
+select "Test 'go' command \g" as "_";
+--exec $MYSQL test -e "select * from t1\g"
+--enable_query_log
+drop table t1;
+
+#
+# BUG9998 - MySQL client hangs on USE "database"
+#
+create table t1(a int);
+lock tables t1 write;
+--exec $MYSQL -e "use test; select database();"
+unlock tables;
+drop table t1;
+
+#
+# Bug#16859 -- NULLs in columns must not truncate data as if a C-language "string".
+#
+--exec $MYSQL -t test -e "create table t1 (col1 binary(4), col2 varchar(10), col3 int); insert into t1 values ('a', 'b', 123421),('a ', '0123456789', 4), ('abcd', '', 4); select concat('>',col1,'<'), col2, col3 from t1; drop table t1;" 2>&1
+
+#
+# Bug#17939 Wrong table format when using UTF8 strings
+write_file $MYSQL_TMP_DIR/mysql_in;
+SELECT 'John Doe' as '__tañgè Ñãmé';
+SELECT '__tañgè Ñãmé' as 'John Doe';
+EOF
+--exec $MYSQL --default-character-set=utf8 --table < $MYSQL_TMP_DIR/mysql_in 2>&1
+remove_file $MYSQL_TMP_DIR/mysql_in;
+
+#
+# Bug#18265 -- mysql client: No longer right-justifies numeric columns
+#
+write_file $MYSQL_TMP_DIR/mysql_in;
+create table t1 (i int, j int, k char(25) charset utf8); insert into t1 (i) values (1); insert into t1 (k) values ('<----------------------->'); insert into t1 (k) values ('<-----'); insert into t1 (k) values ('Τη γλώσσα'); insert into t1 (k) values ('ᛖᚴ ᚷᛖᛏ'); select * from t1; DROP TABLE t1;
+EOF
+--exec $MYSQL -t --default-character-set utf8 test < $MYSQL_TMP_DIR/mysql_in
+remove_file $MYSQL_TMP_DIR/mysql_in;
+
+
+#
+# "DESCRIBE" commands may return strange NULLness flags.
+#
+--exec $MYSQL --default-character-set utf8 test -e "create table t1 (i int, j int not null, k int); insert into t1 values (null, 1, null); select * from t1; describe t1; drop table t1;"
+--exec $MYSQL -t --default-character-set utf8 test -e "create table t1 (i int, j int not null, k int); insert into t1 values (null, 1, null); select * from t1; describe t1; drop table t1;"
+
+#
+# Bug#19564: mysql displays NULL instead of space
+#
+--exec $MYSQL test -e "create table b19564 (i int, s1 char(1)); insert into b19564 values (1, 'x'); insert into b19564 values (2, NULL); insert into b19564 values (3, ' '); select * from b19564 order by i; drop table b19564;"
+--exec $MYSQL -t test -e "create table b19564 (i int, s1 char(1)); insert into b19564 values (1, 'x'); insert into b19564 values (2, NULL); insert into b19564 values (3, ' '); select * from b19564 order by i; drop table b19564;"
+
+#
+# Bug#21618: NULL shown as empty string in client
+#
+--exec $MYSQL test -e "select unhex('zz');"
+--exec $MYSQL -t test -e "select unhex('zz');"
+
+# Bug#19265 describe command does not work from mysql prompt
+#
+
+create table t1(a int, b varchar(255), c int);
+--exec $MYSQL test -e "desc t1"
+--exec $MYSQL test -e "desc t1\g"
+drop table t1;
+
+--disable_parsing
+#
+# Bug#21042 mysql client segfaults on importing a mysqldump export
+#
+--error 1
+--exec $MYSQL test -e "connect verylongdatabasenamethatshouldblowthe256byteslongbufferincom_connectfunctionxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxendcccccccdxxxxxxxxxxxxxxxxxkskskskskkskskskskskskskskskskkskskskskkskskskskskskskskskend" 2>&1
+--enable_parsing
+
+
+#
+# Bug #20432: mysql client interprets commands in comments
+#
+
+--let $file = $MYSQLTEST_VARDIR/tmp/bug20432.sql
+
+# if the client sees the 'use' within the comment, we haven't fixed
+--exec echo "/*" > $file
+--exec echo "use" >> $file
+--exec echo "*/" >> $file
+--exec $MYSQL < $file 2>&1
+
+# SQL can have embedded comments => workie
+--exec echo "select /*" > $file
+--exec echo "use" >> $file
+--exec echo "*/ 1" >> $file
+--exec $MYSQL < $file 2>&1
+
+# client commands on the other hand must be at BOL => error
+--exec echo "/*" > $file
+--exec echo "xxx" >> $file
+--exec echo "*/ use" >> $file
+--error 1
+--exec $MYSQL < $file 2>&1
+
+# client comment recognized, but parameter missing => error
+--exec echo "use" > $file
+--exec $MYSQL < $file 2>&1
+
+# Test exceutable comments
+--exec echo "SELECT 1 /*! +1 */;" > $file
+--exec echo "SELECT 1 /*M! +1 */;" >> $file
+--exec echo "SELECT 1 /*!00000 +1 */;" >> $file
+--exec echo "SELECT 1 /*M!00000 +1 */" >> $file
+--exec $MYSQL < $file 2>&1
+
+--remove_file $file
+
+#
+# Bug #20328: mysql client interprets commands in comments
+#
+--let $file1 = $MYSQLTEST_VARDIR/tmp/bug20328_1.result
+--let $file2 = $MYSQLTEST_VARDIR/tmp/bug20328_2.result
+--exec $MYSQL -e "help" > $file1
+--exec $MYSQL -e "help " > $file2
+--diff_files $file1 $file2
+--remove_file $file1
+--remove_file $file2
+
+#
+# Bug #19216: Client crashes on long SELECT
+#
+# Create large SELECT
+# - 3400 * 20 makes 68000 columns that is more than the
+# max number that can fit in a 16 bit number.
+
+--perl
+open(FILE,">","$ENV{'MYSQLTEST_VARDIR'}/tmp/b19216.tmp") or die;
+print FILE "select\n";
+print FILE "'a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a',\n" x 3400;
+print FILE "'b';\n";
+close FILE;
+EOF
+
+--disable_query_log
+--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/b19216.tmp >/dev/null
+--enable_query_log
+
+--remove_file $MYSQLTEST_VARDIR/tmp/b19216.tmp
+
+#
+# Bug #20103: Escaping with backslash does not work
+#
+--let $file = $MYSQLTEST_VARDIR/tmp/bug20103.sql
+--exec echo "SET SQL_MODE = 'NO_BACKSLASH_ESCAPES';" > $file
+--exec echo "SELECT '\';" >> $file
+--exec $MYSQL < $file 2>&1
+
+--exec echo "SET SQL_MODE = '';" > $file
+--exec echo "SELECT '\';';" >> $file
+--exec $MYSQL < $file 2>&1
+--remove_file $file
+
+#
+# Bug#17583: mysql drops connection when stdout is not writable
+#
+create table t17583 (a int);
+insert into t17583 (a) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t17583 select a from t17583;
+insert into t17583 select a from t17583;
+insert into t17583 select a from t17583;
+insert into t17583 select a from t17583;
+insert into t17583 select a from t17583;
+insert into t17583 select a from t17583;
+insert into t17583 select a from t17583;
+# Close to the minimal data needed to exercise bug.
+select count(*) from t17583;
+--exec echo "select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) fr
om t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from
t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; select count(*) from t17583; " |$MYSQL test >&-
+drop table t17583;
+
+#
+# Bug#20984: Reproducible MySQL client segmentation fault
+# + additional tests for the "com_connect" function in mysql
+#
+#
+--echo Test connect without db- or host-name => reconnect
+--exec $MYSQL test -e "\r" 2>&1
+--exec $MYSQL test -e "connect" 2>&1
+
+--echo Test connect with dbname only => new dbname, old hostname
+--exec $MYSQL test -e "\r test" 2>&1
+--exec $MYSQL test -e "connect test" 2>&1
+--exec $MYSQL test -e "\rtest" 2>&1
+--error 1
+--exec $MYSQL test -e "connecttest" 2>&1
+
+--echo Test connect with _invalid_ dbname only => new invalid dbname, old hostname
+--error 1
+--exec $MYSQL test -e "\r invalid" 2>&1
+--error 1
+--exec $MYSQL test -e "connect invalid" 2>&1
+
+--echo Test connect with dbname + hostname
+--exec $MYSQL test -e "\r test localhost" 2>&1
+--exec $MYSQL test -e "connect test localhost" 2>&1
+
+--echo Test connect with dbname + _invalid_ hostname
+# Mask the errno of the error message
+--replace_regex /\([0-9|-]*\)/(errno)/
+--error 1
+--exec $MYSQL test -e "\r test invalid_hostname" 2>&1
+--replace_regex /\([0-9|-]*\)/(errno)/
+--error 1
+--exec $MYSQL test -e "connect test invalid_hostname" 2>&1
+
+--echo The commands reported in the bug report
+--replace_regex /\([0-9|-]*\)/(errno)/
+--error 1
+--exec $MYSQL test -e "\r\r\n\r\n cyril\ has\ found\ a\ bug\ :)XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" 2>&1
+
+#--replace_regex /\([0-9|-]*\)/(errno)/
+#--error 1
+#--exec echo '\r\r\n\r\n cyril\ has\ found\ a\ bug\ :)XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' | $MYSQL 2>&1
+
+--echo Too long dbname
+--error 1
+--exec $MYSQL test -e "\r test_really_long_dbnamexxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx localhost" 2>&1
+
+--echo Too long hostname
+--replace_regex /\([0-9|-]*\)/(errno)/
+--error 1
+--exec $MYSQL test -e "\r test cyrils_superlonghostnameXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" 2>&1
+
+
+#
+# Bug #21412: mysql cmdline client allows backslash(es)
+# as delimiter but can't recognize them
+#
+
+# This should work just fine...
+--write_file $MYSQLTEST_VARDIR/tmp/bug21412.sql
+DELIMITER /
+SELECT 1/
+EOF
+--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug21412.sql 2>&1
+remove_file $MYSQLTEST_VARDIR/tmp/bug21412.sql;
+
+# This should give an error...
+--write_file $MYSQLTEST_VARDIR/tmp/bug21412.sql
+DELIMITER \
+EOF
+--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug21412.sql 2>&1
+remove_file $MYSQLTEST_VARDIR/tmp/bug21412.sql;
+
+# As should this...
+--write_file $MYSQLTEST_VARDIR/tmp/bug21412.sql
+DELIMITER \\
+EOF
+--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug21412.sql 2>&1
+remove_file $MYSQLTEST_VARDIR/tmp/bug21412.sql;
+
+#
+# Some coverage of not normally used parts
+#
+
+--disable_query_log
+--exec $MYSQL test -e "show status" 2>&1 > /dev/null
+--exec $MYSQL --help 2>&1 > /dev/null
+--exec $MYSQL --version 2>&1 > /dev/null
+--enable_query_log
+
+#
+# bug #26851: Mysql Client --pager Buffer Overflow
+#
+
+# allow error 7(invalid argument) since --pager does not always exist in mysql
+--error 0,7
+--exec $MYSQL --pager="540bytelengthstringxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" -e "select 1" > /dev/null 2>&1
+--exec $MYSQL --character-sets-dir="540bytelengthstringxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" -e "select 1" 2>&1
+
+#
+# bug #30164: Using client side macro inside server side comments generates broken queries
+#
+--exec $MYSQL test -e "/*! \C latin1 */ select 1;"
+
+#
+# Bug#29323 mysql client only accetps ANSI encoded files
+#
+--write_file $MYSQLTEST_VARDIR/tmp/bug29323.sql
+select "This is a file starting with UTF8 BOM 0xEFBBBF";
+EOF
+--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug29323.sql 2>&1
+remove_file $MYSQLTEST_VARDIR/tmp/bug29323.sql;
+
+#
+# Bug #33812: mysql client incorrectly parsing DELIMITER
+#
+# The space and ; after delimiter are important
+--exec $MYSQL -e "select 1 delimiter ;"
+
+#
+# Bug #38158: mysql client regression, can't read dump files
+#
+--write_file $MYSQLTEST_VARDIR/tmp/bug38158.sql
+-- Testing
+--
+delimiter ||
+select 2 ||
+EOF
+--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug38158.sql 2>&1
+--exec $MYSQL -c < $MYSQLTEST_VARDIR/tmp/bug38158.sql 2>&1
+remove_file $MYSQLTEST_VARDIR/tmp/bug38158.sql;
+
+#
+# Bug #41437: Value stored in 'case' lacks charset, causees segfault
+#
+--exec $MYSQL -e "select @z:='1',@z=database()"
+
+
+#
+# Bug #31060: MySQL CLI parser bug 2
+#
+
+--write_file $MYSQLTEST_VARDIR/tmp/bug31060.sql
+;DELIMITER DELIMITER
+;
+SELECT 1DELIMITER
+DELIMITER ;
+SELECT 1;
+EOF
+
+--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug31060.sql 2>&1
+
+remove_file $MYSQLTEST_VARDIR/tmp/bug31060.sql;
+
+#
+# Bug #39101: client -i (--ignore-spaces) option does not seem to work
+#
+--exec $MYSQL -i -e "SELECT COUNT (*)"
+--exec $MYSQL --ignore-spaces -e "SELECT COUNT (*)"
+--exec $MYSQL -b -i -e "SELECT COUNT (*)"
+
+#
+# Bug#37268 'binary' character set makes CLI-internal commands case sensitive
+#
+--replace_regex /\([0-9|-]*\)/(errno)/
+--error 1
+--exec $MYSQL --default-character-set=binary test -e "CONNECT test invalid_hostname" 2>&1
+--exec $MYSQL --default-character-set=binary test -e "DELIMITER //" 2>&1
+
+--echo End of 5.0 tests
+
+#
+# Bug #29903: The CMake build method does not produce the embedded library.
+#
+--disable_query_log
+--exec $MYSQL --server-arg=no-defaults test -e "quit"
+--enable_query_log
+
+#
+# Bug#26780: patch to add auto vertical output option to the cli.
+#
+# Make this wide enough that it will wrap almost everywhere.
+--exec $MYSQL test --auto-vertical-output --table -e "SELECT 1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0;"
+# Too short to wrap.
+--exec $MYSQL test --auto-vertical-output --table -e "SELECT 1;"
+
+#
+# Bug #25146: Some warnings/errors not shown when using --show-warnings
+#
+
+# This one should succeed with no warnings
+--exec $MYSQL --show-warnings test -e "create table t1 (id int)"
+
+# This should succeed, with warnings about conversion from nonexistent engine
+--exec $MYSQL --show-warnings test -e "create table t2 (id int) engine=nonexistent"
+
+# This should fail, with warnings as well
+--error 1
+--exec $MYSQL --show-warnings test -e "create table t2 (id int) engine=nonexistent2"
+
+drop tables t1, t2;
+
+#
+# mysql client with 'init-command' option
+#
+--exec $MYSQL --init-command="SET lc_messages=ru_RU" -e "SHOW VARIABLES LIKE 'lc_messages';"
+
+#
+# Bug #27884: mysql --html does not quote HTML special characters in output
+#
+--write_file $MYSQLTEST_VARDIR/tmp/bug27884.sql
+SELECT '< & >' AS `<`;
+EOF
+--exec $MYSQL --html test < $MYSQLTEST_VARDIR/tmp/bug27884.sql
+
+remove_file $MYSQLTEST_VARDIR/tmp/bug27884.sql;
+
+
+#
+# Bug #28203: mysql client + null byte
+#
+create table t1 (a char(5));
+insert into t1 values ('\0b\0');
+--exec $MYSQL test -e "select a from t1"
+--exec $MYSQL -r test -e "select a from t1"
+--exec $MYSQL -s test -e "select a from t1"
+--exec $MYSQL --table test -e "select a from t1"
+--exec $MYSQL --vertical test -e "select a from t1"
+--exec $MYSQL --html test -e "select a from t1"
+--exec $MYSQL --xml test -e "select a from t1"
+drop table t1;
+
+--echo
+--echo Bug #47147: mysql client option --skip-column-names does not apply to vertical output
+--echo
+--exec $MYSQL --skip-column-names --vertical test -e "select 1 as a"
+
+#
+# Bug#57450: mysql client enter in an infinite loop if the standard input is a directory
+#
+--error 1
+--exec $MYSQL < .
+
+--echo
+
+--echo #
+--echo # Bug #54899: --one-database option cannot handle DROP/CREATE DATABASE
+--echo # commands.
+--echo #
+--write_file $MYSQLTEST_VARDIR/tmp/bug54899.sql
+DROP DATABASE connected_db;
+CREATE DATABASE connected_db;
+USE connected_db;
+CREATE TABLE `table_in_connected_db`(a INT);
+EOF
+
+CREATE DATABASE connected_db;
+--exec $MYSQL --one-database connected_db < $MYSQLTEST_VARDIR/tmp/bug54899.sql
+USE connected_db;
+SHOW TABLES;
+DROP DATABASE connected_db;
+--remove_file $MYSQLTEST_VARDIR/tmp/bug54899.sql
+
+--echo
+
+--echo #
+--echo # Testing --one-database option
+--echo #
+--write_file $MYSQLTEST_VARDIR/tmp/one_db.sql
+CREATE TABLE t1 (i INT);
+CREATE TABLE test.t1 (i INT);
+USE test;
+# Following statements should be filtered.
+CREATE TABLE connected_db.t2 (i INT);
+CREATE TABLE t2 (i INT);
+EOF
+
+CREATE DATABASE connected_db;
+--exec $MYSQL --one-database connected_db < $MYSQLTEST_VARDIR/tmp/one_db.sql
+SHOW TABLES IN connected_db;
+SHOW TABLES IN test;
+USE test;
+DROP TABLE t1;
+DROP DATABASE connected_db;
+--remove_file $MYSQLTEST_VARDIR/tmp/one_db.sql
+
+--echo
+--write_file $MYSQLTEST_VARDIR/tmp/one_db.sql
+CREATE DATABASE test1;
+USE test1;
+USE test1;
+# Following statements should be filtered.
+CREATE TABLE connected_db.t1 (i INT);
+EOF
+
+--exec $MYSQL --one-database test < $MYSQLTEST_VARDIR/tmp/one_db.sql
+SHOW TABLES IN test;
+SHOW TABLES IN test1;
+DROP DATABASE test1;
+--remove_file $MYSQLTEST_VARDIR/tmp/one_db.sql
+
+--echo
+
+--echo #
+--echo # Checking --one-database option followed by the execution of
+--echo # connect command.
+--echo #
+--write_file $MYSQLTEST_VARDIR/tmp/one_db.sql
+CREATE TABLE t1 (i INT);
+CREATE TABLE test.t1 (i INT);
+CONNECT test;
+CREATE TABLE connected_db.t2 (i INT);
+CREATE TABLE t2 (i INT);
+USE connected_db;
+# Following statements should be filtered.
+CREATE TABLE connected_db.t3 (i INT);
+CREATE TABLE t3 (i INT);
+EOF
+
+CREATE DATABASE connected_db;
+--exec $MYSQL --one-database connected_db < $MYSQLTEST_VARDIR/tmp/one_db.sql
+SHOW TABLES IN connected_db;
+SHOW TABLES IN test;
+DROP TABLE test.t1;
+DROP TABLE test.t2;
+DROP DATABASE connected_db;
+--remove_file $MYSQLTEST_VARDIR/tmp/one_db.sql
+
+--echo
+
+--echo #
+--echo # Checking --one-database option with no database specified
+--echo # at command-line.
+--echo #
+--write_file $MYSQLTEST_VARDIR/tmp/one_db.sql
+# All following statements should be filtered.
+CREATE TABLE t1 (i INT);
+CREATE TABLE test.t1 (i INT);
+USE test;
+CREATE TABLE test.t2 (i INT);
+CREATE TABLE t2 (i INT);
+EOF
+
+--exec $MYSQL --one-database < $MYSQLTEST_VARDIR/tmp/one_db.sql
+SHOW TABLES IN test;
+--remove_file $MYSQLTEST_VARDIR/tmp/one_db.sql
+
+--echo
+
+--echo #
+--echo # Checking --one-database option with non_existent_db
+--echo # specified with USE command
+--echo #
+
+# CASE 1 : When 'connected_db' database exists and passed at commandline.
+--write_file $MYSQLTEST_VARDIR/tmp/one_db_1.sql
+CREATE TABLE `table_in_connected_db`(i INT);
+USE non_existent_db;
+# Following statement should be filtered out.
+CREATE TABLE `table_in_non_existent_db`(i INT);
+EOF
+
+# CASE 2 : When 'connected_db' database exists but dropped and recreated in
+# load file.
+--write_file $MYSQLTEST_VARDIR/tmp/one_db_2.sql
+DROP DATABASE connected_db;
+CREATE DATABASE connected_db;
+USE non_existent_db;
+# Following statements should be filtered out.
+CREATE TABLE `table_in_non_existent_db`(i INT);
+USE connected_db;
+# Following statements should not be filtered out.
+CREATE TABLE `table_in_connected_db`(i INT);
+EOF
+
+CREATE DATABASE connected_db;
+--exec $MYSQL --one-database connected_db < $MYSQLTEST_VARDIR/tmp/one_db_1.sql
+SHOW TABLES IN connected_db;
+--echo
+--exec $MYSQL --one-database connected_db < $MYSQLTEST_VARDIR/tmp/one_db_2.sql
+SHOW TABLES IN connected_db;
+DROP DATABASE connected_db;
+
+--remove_file $MYSQLTEST_VARDIR/tmp/one_db_1.sql
+--remove_file $MYSQLTEST_VARDIR/tmp/one_db_2.sql
+
+#
+# USE and names with backticks
+#
+--write_file $MYSQLTEST_VARDIR/tmp/backticks.sql
+\u aa`bb``cc
+SELECT DATABASE();
+USE test
+SELECT DATABASE();
+USE aa`bb``cc
+SELECT DATABASE();
+USE test
+SELECT DATABASE();
+USE `aa``bb````cc`
+SELECT DATABASE();
+EOF
+create database `aa``bb````cc`;
+--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/backticks.sql
+drop database `aa``bb````cc`;
+
+#
+# MySQL Bug#13639125 DELIMITER STRIPS THE NEXT NEW LINE IN A SQL STATEMENT
+#
+--write_file $MYSQLTEST_VARDIR/tmp/13639125.sql
+select ">>
+delimiter
+<<" as a;
+EOF
+--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/13639125.sql
+
+#
+# --skip-column-names and alignment
+#
+--exec $MYSQL -t -N -e "SELECT 'a' union select 'aaaaaaaaaaaaaaaaa'"
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-6572 "USE dbname" with a bad sequence erroneously connects to a wrong database
+--echo #
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
+
+
+--write_file $MYSQLTEST_VARDIR/tmp/mdev-6572.sql
+SET NAMES utf8;
+USE test😁 ;
+EOF
+--error 1
+--exec $MYSQL --default-character-set=utf8 < $MYSQLTEST_VARDIR/tmp/mdev-6572.sql 2>&1
+--remove_file $MYSQLTEST_VARDIR/tmp/mdev-6572.sql
+
+set GLOBAL sql_mode=default;
+--echo
+--echo End of tests
+
+#
+# MDEV-13187 incorrect backslash parsing in clients
+#
+create table `a1\``b1` (a int);
+show tables;
+insert `a1\``b1` values (1),(2);
+show create table `a1\``b1`;
+--exec $MYSQL_DUMP --compact test
+--exec $MYSQL_DUMP test > $MYSQLTEST_VARDIR/tmp/bug.sql
+insert `a1\``b1` values (4),(5);
+--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug.sql
+show create table `a1\``b1`;
+select * from `a1\``b1`;
+drop table `a1\``b1`;
+
+# same with ansi_quotes
+set sql_mode=ansi_quotes;
+create table "a1\""b1" (a int);
+show tables;
+insert "a1\""b1" values (1),(2);
+show create table "a1\""b1";
+--exec $MYSQL_DUMP --compact --compatible=postgres test
+--exec $MYSQL_DUMP --compatible=postgres test > $MYSQLTEST_VARDIR/tmp/bug.sql
+insert "a1\""b1" values (4),(5);
+--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug.sql
+show create table "a1\""b1";
+select * from "a1\""b1";
+drop table "a1\""b1";
+set sql_mode=default;
+
+#
+# mysql --local-infile
+#
+--let $ldli = load data local infile '$MYSQLTEST_VARDIR/tmp/bug.sql' into table test.t1;
+create table t1 (a text);
+--exec $MYSQL -e "$ldli"
+select count(*) from t1; truncate table t1;
+--exec $MYSQL --enable-local-infile -e "$ldli"
+select count(*) from t1; truncate table t1;
+--error 1
+--exec $MYSQL --disable-local-infile -e "$ldli"
+select count(*) from t1; truncate table t1;
- --echo ### FIXME: update libmariadb
- #--error 1
- #--exec $MYSQL -e "/*q*/$ldli"
- #select count(*) from t1; truncate table t1;
- #--exec $MYSQL --enable-local-infile -e "/*q*/$ldli"
- #select count(*) from t1; truncate table t1;
- # --error 1
- # --exec $MYSQL --disable-local-infile -e "/*q*/$ldli"
- #select count(*) from t1; truncate table t1;
++--error 1
++--exec $MYSQL -e "/*q*/$ldli"
++select count(*) from t1; truncate table t1;
++--exec $MYSQL --enable-local-infile -e "/*q*/$ldli"
++select count(*) from t1; truncate table t1;
++ --error 1
++ --exec $MYSQL --disable-local-infile -e "/*q*/$ldli"
++select count(*) from t1; truncate table t1;
+drop table t1;
diff --cc mysql-test/suite/galera_3nodes/suite.pm
index 4949954e7bd,a7c1bf79c06..3c2074320f3
--- a/mysql-test/suite/galera_3nodes/suite.pm
+++ b/mysql-test/suite/galera_3nodes/suite.pm
@@@ -49,20 -49,9 +49,10 @@@ push @::global_suppressions
qr|WSREP: discarding established \(time wait\) .*|,
qr(WSREP: There are no nodes in the same segment that will ever be able to become donors, yet there is a suitable donor outside. Will use that one.),
qr(WSREP: evs::proto.*),
- <<<<<<< HEAD
qr|WSREP: Ignoring possible split-brain \(allowed by configuration\) from view:.*|,
- ||||||| merged common ancestors
- qr|WSREP: Ignoring possible split-brain (allowed by configuration) from view:.*|,
- =======
+ qr|WSREP: Ignoring possible split-brain \(allowed by configuration\) from view:.*|,
qr(WSREP: no nodes coming from prim view, prim not possible),
- >>>>>>> 10.2
qr(WSREP: Member .* requested state transfer from .* but it is impossible to select State Transfer donor: Resource temporarily unavailable),
- <<<<<<< HEAD
- qr(WSREP: .* sending install message failed: Transport endpoint is not connected),
- qr(WSREP: .* sending install message failed: Resource temporarily unavailable),
- ||||||| merged common ancestors
- =======
qr(WSREP: user message in state LEAVING),
qr(WSREP: .* sending install message failed: Transport endpoint is not connected),
qr(WSREP: .* sending install message failed: Resource temporarily unavailable),
diff --cc mysql-test/suite/galera_3nodes/t/galera_garbd.test
index 5509c4628c9,2d03e8897b9..869bad8894f
--- a/mysql-test/suite/galera_3nodes/t/galera_garbd.test
+++ b/mysql-test/suite/galera_3nodes/t/galera_garbd.test
@@@ -10,7 -10,15 +10,14 @@@
--let $galera_connection_name = node_3
--let $galera_server_number = 3
--source include/galera_connect.inc
-
+ # Save original auto_increment_offset values.
+ --let $node_1=node_1
+ --let $node_2=node_2
+ --let $node_3=node_3
+ --source ../galera/include/auto_increment_offset_save.inc
+
+ --echo Killing node #3 to free ports for garbd ...
+ --connection node_3
--let $gp3 = `SELECT SUBSTR(@@wsrep_provider_options, LOCATE('base_port =', @@wsrep_provider_options) + LENGTH('base_port = '))`
--let $galera_port_3 = `SELECT SUBSTR('$gp3', 1, LOCATE(';', '$gp3') - 1)`
--source include/shutdown_mysqld.inc
1
0
12 Feb '19
revision-id: 31d6e9c3c83c5248789df14f13e6f14599170f9c (mariadb-10.3.12-49-g31d6e9c3c83)
parent(s): fa57e118445735fce44565b7dea48ec8bf86bbe4
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2019-02-12 10:48:19 +0100
message:
Fix of error messages with big help of serg
---
mysql-test/main/gis2.result | 2 +-
mysql-test/suite/innodb_gis/r/alter_spatial_index.result | 2 +-
sql/sql_table.cc | 2 +-
storage/innobase/handler/handler0alter.cc | 15 ++++++---------
4 files changed, 9 insertions(+), 12 deletions(-)
diff --git a/mysql-test/main/gis2.result b/mysql-test/main/gis2.result
index cb919ecdeb1..6eb1c54e8d4 100644
--- a/mysql-test/main/gis2.result
+++ b/mysql-test/main/gis2.result
@@ -33,6 +33,6 @@ SET timestamp=default;
create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1))));
set timestamp=10;
alter table t1 add column i int;
-ERROR 22007: Incorrect POINT value: 'GEOMETRYCOLLECTION' for column `test`.`(temporary)`.`p` at row 1
+ERROR 22007: Incorrect POINT value: 'GEOMETRYCOLLECTION' for column `test`.`t1`.`p` at row 1
drop table t1;
SET timestamp=default;
diff --git a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result
index d94e67d0765..703af3c40de 100644
--- a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result
+++ b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result
@@ -795,7 +795,7 @@ SET timestamp=default;
create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1)))) ENGINE=innodb;
set timestamp=10;
alter table t1 add column i int;
-ERROR 22007: Incorrect POINT value: 'GEOMETRYCOLLECTION' for column `test`.`(temporary)`.`p` at row 1
+ERROR 22007: Incorrect POINT value: 'GEOMETRYCOLLECTION' for column `test`.`t1`.`p` at row 1
drop table t1;
SET timestamp=default;
CREATE OR REPLACE TABLE t1 (a INT) ENGINE=InnoDB;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index c696ceb001e..7f2003b765b 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -9695,7 +9695,7 @@ do_continue:;
thd->create_and_open_tmp_table(new_db_type, &frm,
alter_ctx.get_tmp_path(),
alter_ctx.new_db.str,
- alter_ctx.tmp_name.str,
+ alter_ctx.new_name.str,
false, true)))
goto err_new_table_cleanup;
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index 02a6b2dd865..41607ec589e 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -5034,18 +5034,15 @@ prepare_inplace_alter_table_dict(
goto err_exit;
}
- size_t dblen = ctx->old_table->name.dblen() + 1;
- size_t tablen = altered_table->s->table_name.length;
+ size_t prefixlen= strlen(mysql_data_home) + 1;
+ size_t tablen = altered_table->s->path.length - prefixlen;
const char* part = ctx->old_table->name.part();
size_t partlen = part ? strlen(part) : 0;
char* new_table_name = static_cast<char*>(
- mem_heap_alloc(ctx->heap,
- dblen + tablen + partlen + 1));
- memcpy(new_table_name, ctx->old_table->name.m_name, dblen);
- memcpy(new_table_name + dblen,
- altered_table->s->table_name.str, tablen);
- memcpy(new_table_name + dblen + tablen,
- part ? part : "", partlen + 1);
+ mem_heap_alloc(ctx->heap, tablen + partlen + 1));
+ memcpy(new_table_name,
+ altered_table->s->path.str + prefixlen, tablen);
+ memcpy(new_table_name + tablen, part ? part : "", partlen + 1);
ulint n_cols = 0;
ulint n_v_cols = 0;
dtuple_t* defaults;
1
0
revision-id: af96bc658118d03c25fa4f4d9057d5d8612162dc (mariadb-25.3.19-20-gaf96bc65)
parent(s): 5c662d7516da67f09e9edb9c3bb33125a0646511 9f9f579cb17e4ef386dfb2a39227887ad5c7c649
author: Jan Lindström
committer: Jan Lindström
timestamp: 2019-02-12 11:03:50 +0200
message:
Merge branch '3.x' into mariadb-3.x
galera/src/replicator_smm.hpp | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
1
0
[Commits] 3a269a8b527: Record galera_var_load_data_splitting result after deprecating
by jan 11 Feb '19
by jan 11 Feb '19
11 Feb '19
revision-id: 3a269a8b5272c15751f565daa1566d1f61a1f04f (mariadb-10.4.1-165-g3a269a8b527)
parent(s): cbfbb70dd200b0b3701d812f62e72f89aa9245bf
author: Jan Lindström
committer: Jan Lindström
timestamp: 2019-02-11 14:59:59 +0200
message:
Record galera_var_load_data_splitting result after deprecating
warning.
---
mysql-test/suite/galera/r/galera_var_load_data_splitting.result | 4 ++++
1 file changed, 4 insertions(+)
diff --git a/mysql-test/suite/galera/r/galera_var_load_data_splitting.result b/mysql-test/suite/galera/r/galera_var_load_data_splitting.result
index 7c5fd44356e..9078e9ea985 100644
--- a/mysql-test/suite/galera/r/galera_var_load_data_splitting.result
+++ b/mysql-test/suite/galera/r/galera_var_load_data_splitting.result
@@ -3,6 +3,8 @@ connection node_1;
CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB;
connection node_2;
SET GLOBAL wsrep_load_data_splitting = TRUE;
+Warnings:
+Warning 1287 '@@wsrep_load_data_splitting' is deprecated and will be removed in a future release
connection node_2;
SELECT COUNT(*) = 95000 FROM t1;
COUNT(*) = 95000
@@ -11,4 +13,6 @@ wsrep_last_committed_diff
1
connection node_1;
SET GLOBAL wsrep_load_data_splitting = 1;;
+Warnings:
+Warning 1287 '@@wsrep_load_data_splitting' is deprecated and will be removed in a future release
DROP TABLE t1;
1
0
[Commits] cd00d03: MDEV-16188 Fixed the code of ha_partition::multi_range_read_info()
by IgorBabaev 11 Feb '19
by IgorBabaev 11 Feb '19
11 Feb '19
revision-id: cd00d03fe2d5b92d4451545e3b15477d027c83d0 (mariadb-10.3.6-113-gcd00d03)
parent(s): 3955d2a1539e7e41e3457c99f773d90c90f4f90f
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-02-10 21:15:48 -0800
message:
MDEV-16188 Fixed the code of ha_partition::multi_range_read_info()
The code was rewritten in the same way as the code of
ha_partition::multi_range_read_info_const() had been rewritten
earlier.
The fix allowed to run spider.partition_mrr.
---
.../sys_vars/r/sysvars_server_embedded,32bit.rdiff | 9 ---------
sql/ha_partition.cc | 21 +++++++++++----------
storage/connect/mysql-test/connect/r/xml.result | 2 +-
3 files changed, 12 insertions(+), 20 deletions(-)
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff
index e10b2c5..5d47090 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff
@@ -507,15 +507,6 @@
VARIABLE_COMMENT Maximum stored procedure recursion depth
NUMERIC_MIN_VALUE 0
NUMERIC_MAX_VALUE 255
-@@ -2261,7 +2261,7 @@
- VARIABLE_TYPE BIGINT UNSIGNED
- VARIABLE_COMMENT The maximum size of the container of a rowid filter
- NUMERIC_MIN_VALUE 1024
--NUMERIC_MAX_VALUE 18446744073709551615
-+NUMERIC_MAX_VALUE 4294967295
- NUMERIC_BLOCK_SIZE 1
- ENUM_VALUE_LIST NULL
- READ_ONLY NO
@@ -2284,7 +2284,7 @@
GLOBAL_VALUE_ORIGIN COMPILE-TIME
DEFAULT_VALUE 32
diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc
index 7d5c7c4..acf20b4 100644
--- a/sql/ha_partition.cc
+++ b/sql/ha_partition.cc
@@ -6346,9 +6346,12 @@ ha_rows ha_partition::multi_range_read_info(uint keyno, uint n_ranges,
uint i;
handler **file;
ha_rows rows;
+ Cost_estimate part_cost;
DBUG_ENTER("ha_partition::multi_range_read_info");
DBUG_PRINT("enter", ("partition this: %p", this));
+ cost->reset();
+
m_mrr_new_full_buffer_size= 0;
file= m_file;
do
@@ -6356,22 +6359,20 @@ ha_rows ha_partition::multi_range_read_info(uint keyno, uint n_ranges,
i= (uint)(file - m_file);
if (bitmap_is_set(&(m_part_info->read_partitions), (i)))
{
+ ha_rows tmp_rows;
m_mrr_buffer_size[i]= 0;
- if ((rows= (*file)->multi_range_read_info(keyno, n_ranges, keys,
- key_parts,
- &m_mrr_buffer_size[i],
- mrr_mode, cost)))
+ part_cost.reset();
+ if ((tmp_rows= (*file)->multi_range_read_info(keyno, n_ranges, keys,
+ key_parts,
+ &m_mrr_buffer_size[i],
+ mrr_mode, &part_cost)))
DBUG_RETURN(rows);
+ cost->add(&part_cost);
+ rows+= tmp_rows;
m_mrr_new_full_buffer_size+= m_mrr_buffer_size[i];
}
} while (*(++file));
- cost->reset();
- cost->avg_io_cost= 1;
- if (*mrr_mode & HA_MRR_INDEX_ONLY)
- cost->io_count= keyread_time(keyno, n_ranges, (uint) rows);
- else
- cost->io_count= read_time(keyno, n_ranges, rows);
DBUG_RETURN(0);
}
diff --git a/storage/connect/mysql-test/connect/r/xml.result b/storage/connect/mysql-test/connect/r/xml.result
index 99739b1..6a0c9db 100644
--- a/storage/connect/mysql-test/connect/r/xml.result
+++ b/storage/connect/mysql-test/connect/r/xml.result
@@ -323,7 +323,7 @@ HEX(c) 3F3F3F3F3F3F3F
Warnings:
Level Warning
Code 1366
-Message Incorrect string value: '\xC3\x81\xC3\x82\xC3\x83...' for column 'c' at row 1
+Message Incorrect string value: '\xC3\x81\xC3\x82\xC3\x83...' for column `test`.`t1`.`c` at row 1
Level Warning
Code 1105
Message Out of range value ÁÂÃÄÅÆÇ for column 'c' at row 1
1
0
10 Feb '19
revision-id: 3955d2a1539e7e41e3457c99f773d90c90f4f90f (mariadb-10.3.6-112-g3955d2a1539)
parent(s): 15fe81c571ef81ec834ac91f96c9adb4343ecd05
author: Galina Shalygina
committer: Galina Shalygina
timestamp: 2019-02-10 22:36:46 +0300
message:
MDEV-18413: Find constraint correlated indexes
Find indexes of one table which parts participate in one constraint.
These indexes are called constraint correlated.
New methods: TABLE::find_constraint_correlated_indexes() and
virtual method check_index_dependence() were added.
For each index it's own constraint correlated index map was created
where all indexes that are constraint correlated with the current are
marked.
The results of this task are used for MDEV-16188 (Use in-memory
PK filters built from range index scans).
---
mysql-test/main/rowid_filter.result | 765 +++++++++++++++++++++++++++++
mysql-test/main/rowid_filter.test | 100 +++-
mysql-test/main/rowid_filter_innodb.result | 765 +++++++++++++++++++++++++++++
sql/item.h | 7 +
sql/rowid_filter.cc | 14 +-
sql/structs.h | 2 +
sql/table.cc | 76 +++
sql/table.h | 3 +
8 files changed, 1726 insertions(+), 6 deletions(-)
diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result
index 4ad83adcadb..34df3035051 100644
--- a/mysql-test/main/rowid_filter.result
+++ b/mysql-test/main/rowid_filter.result
@@ -1174,5 +1174,770 @@ o_orderkey l_linenumber l_shipdate o_totalprice
5895 4 1997-03-03 201419.83
5895 5 1997-04-30 201419.83
5895 6 1997-04-19 201419.83
+#
+# MDEV-18413: find constraint correlated indexes
+#
+ALTER TABLE lineitem ADD CONSTRAINT l_date CHECK(l_shipdate < l_receiptdate);
+# Filter on l_shipdate is not used because it participates in
+# the same constraint as l_receiptdate.
+# Access is made on l_receiptdate.
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 Using index condition; Using where
+1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_receiptdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "i_l_receiptdate",
+ "key_length": "4",
+ "used_key_parts": ["l_receiptDATE"],
+ "rows": 18,
+ "filtered": 0.5662,
+ "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'",
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'"
+ },
+ "table": {
+ "table_name": "orders",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["o_orderkey"],
+ "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "rows": 1,
+ "filtered": 8.7333,
+ "attached_condition": "orders.o_totalprice between 200000 and 250000"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 18.00 0.57 38.89 Using index condition; Using where
+1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 8.73 14.29 Using where
+set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_receiptdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "i_l_receiptdate",
+ "key_length": "4",
+ "used_key_parts": ["l_receiptDATE"],
+ "r_loops": 1,
+ "rows": 18,
+ "r_rows": 18,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 0.5662,
+ "r_filtered": 38.889,
+ "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'",
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'"
+ },
+ "table": {
+ "table_name": "orders",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["o_orderkey"],
+ "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "r_loops": 7,
+ "rows": 1,
+ "r_rows": 1,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 8.7333,
+ "r_filtered": 14.286,
+ "attached_condition": "orders.o_totalprice between 200000 and 250000"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+l_shipdate l_receiptdate o_totalprice
+1996-10-07 1996-10-08 202623.92
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 Using index condition; Using where
+1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_receiptdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "i_l_receiptdate",
+ "key_length": "4",
+ "used_key_parts": ["l_receiptDATE"],
+ "rows": 18,
+ "filtered": 0.5662,
+ "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'",
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'"
+ },
+ "table": {
+ "table_name": "orders",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["o_orderkey"],
+ "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "rows": 1,
+ "filtered": 8.7333,
+ "attached_condition": "orders.o_totalprice between 200000 and 250000"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 18.00 0.57 38.89 Using index condition; Using where
+1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 8.73 14.29 Using where
+set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_receiptdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "i_l_receiptdate",
+ "key_length": "4",
+ "used_key_parts": ["l_receiptDATE"],
+ "r_loops": 1,
+ "rows": 18,
+ "r_rows": 18,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 0.5662,
+ "r_filtered": 38.889,
+ "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'",
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'"
+ },
+ "table": {
+ "table_name": "orders",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["o_orderkey"],
+ "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "r_loops": 7,
+ "rows": 1,
+ "r_rows": 1,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 8.7333,
+ "r_filtered": 14.286,
+ "attached_condition": "orders.o_totalprice between 200000 and 250000"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+l_shipdate l_receiptdate o_totalprice
+1996-10-07 1996-10-08 202623.92
+ALTER TABLE orders ADD COLUMN o_totaldiscount double;
+UPDATE orders SET o_totaldiscount = o_totalprice*(o_custkey/1000);
+CREATE INDEX i_o_totaldiscount on orders(o_totaldiscount);
+ALTER TABLE orders ADD CONSTRAINT o_price CHECK(o_totalprice > o_totaldiscount);
+# Filter on o_totalprice is not used because it participates in
+# the same constraint as o_discount.
+# Access is made on o_discount.
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "rows": 39,
+ "filtered": 3.2667,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "rows": 4,
+ "filtered": 3.0475,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 3.27 2.44 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where
+set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "r_loops": 1,
+ "rows": 39,
+ "r_rows": 41,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 3.2667,
+ "r_filtered": 2.439,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "r_loops": 1,
+ "rows": 4,
+ "r_rows": 6,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 3.0475,
+ "r_filtered": 66.667,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+o_totaldiscount o_totalprice l_shipdate
+18016.04288 219707.84 1996-10-02
+18016.04288 219707.84 1996-10-17
+18016.04288 219707.84 1996-11-04
+18016.04288 219707.84 1996-11-14
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "rows": 39,
+ "filtered": 3.2667,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "rows": 4,
+ "filtered": 3.0475,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 3.27 2.44 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where
+set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "r_loops": 1,
+ "rows": 39,
+ "r_rows": 41,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 3.2667,
+ "r_filtered": 2.439,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "r_loops": 1,
+ "rows": 4,
+ "r_rows": 6,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 3.0475,
+ "r_filtered": 66.667,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+o_totaldiscount o_totalprice l_shipdate
+18016.04288 219707.84 1996-10-02
+18016.04288 219707.84 1996-10-17
+18016.04288 219707.84 1996-11-04
+18016.04288 219707.84 1996-11-14
+CREATE VIEW v1 AS
+SELECT * FROM orders
+WHERE o_orderdate BETWEEN '1992-12-01' AND '1997-01-01';
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_o_orderdate",
+ "i_o_totalprice",
+ "i_o_totaldiscount"
+ ],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "rows": 39,
+ "filtered": 1.9905,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "rows": 4,
+ "filtered": 3.0475,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 1.99 2.44 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where
+set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_o_orderdate",
+ "i_o_totalprice",
+ "i_o_totaldiscount"
+ ],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "r_loops": 1,
+ "rows": 39,
+ "r_rows": 41,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 1.9905,
+ "r_filtered": 2.439,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "r_loops": 1,
+ "rows": 4,
+ "r_rows": 6,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 3.0475,
+ "r_filtered": 66.667,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+o_totaldiscount o_totalprice l_shipdate
+18016.04288 219707.84 1996-10-02
+18016.04288 219707.84 1996-10-17
+18016.04288 219707.84 1996-11-04
+18016.04288 219707.84 1996-11-14
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_o_orderdate",
+ "i_o_totalprice",
+ "i_o_totaldiscount"
+ ],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "rows": 39,
+ "filtered": 1.9905,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "rows": 4,
+ "filtered": 3.0475,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 1.99 2.44 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where
+set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_o_orderdate",
+ "i_o_totalprice",
+ "i_o_totaldiscount"
+ ],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "r_loops": 1,
+ "rows": 39,
+ "r_rows": 41,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 1.9905,
+ "r_filtered": 2.439,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "r_loops": 1,
+ "rows": 4,
+ "r_rows": 6,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 3.0475,
+ "r_filtered": 66.667,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+o_totaldiscount o_totalprice l_shipdate
+18016.04288 219707.84 1996-10-02
+18016.04288 219707.84 1996-10-17
+18016.04288 219707.84 1996-11-04
+18016.04288 219707.84 1996-11-14
+ALTER TABLE lineitem DROP CONSTRAINT l_date;
+ALTER TABLE orders DROP CONSTRAINT o_price;
+ALTER TABLE orders DROP COLUMN o_totaldiscount;
+DROP VIEW v1;
DROP DATABASE dbt3_s001;
set @@use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test
index e1b0c69d470..0e8a3de9c8f 100644
--- a/mysql-test/main/rowid_filter.test
+++ b/mysql-test/main/rowid_filter.test
@@ -127,7 +127,105 @@ eval $without_filter ANALYZE FORMAT=JSON $q4;
--sorted_result
eval $without_filter $q4;
+--echo #
+--echo # MDEV-18413: find constraint correlated indexes
+--echo #
+
+ALTER TABLE lineitem ADD CONSTRAINT l_date CHECK(l_shipdate < l_receiptdate);
+
+--echo # Filter on l_shipdate is not used because it participates in
+--echo # the same constraint as l_receiptdate.
+--echo # Access is made on l_receiptdate.
+let $q5=
+SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+ l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+ l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+ o_totalprice BETWEEN 200000 AND 250000;
+
+eval $with_filter EXPLAIN $q5;
+eval $with_filter EXPLAIN FORMAT=JSON $q5;
+eval $with_filter ANALYZE $q5;
+--source include/analyze-format.inc
+eval $with_filter ANALYZE FORMAT=JSON $q5;
+--sorted_result
+eval $with_filter $q5;
+
+eval $without_filter EXPLAIN $q5;
+eval $without_filter EXPLAIN FORMAT=JSON $q5;
+eval $without_filter ANALYZE $q5;
+--source include/analyze-format.inc
+eval $without_filter ANALYZE FORMAT=JSON $q5;
+--sorted_result
+eval $without_filter $q5;
+
+ALTER TABLE orders ADD COLUMN o_totaldiscount double;
+UPDATE orders SET o_totaldiscount = o_totalprice*(o_custkey/1000);
+CREATE INDEX i_o_totaldiscount on orders(o_totaldiscount);
+
+ALTER TABLE orders ADD CONSTRAINT o_price CHECK(o_totalprice > o_totaldiscount);
+
+--echo # Filter on o_totalprice is not used because it participates in
+--echo # the same constraint as o_discount.
+--echo # Access is made on o_discount.
+let $q6=
+SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+ o_totaldiscount BETWEEN 18000 AND 20000 AND
+ o_totalprice BETWEEN 200000 AND 220000 AND
+ l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+
+eval $with_filter EXPLAIN $q6;
+eval $with_filter EXPLAIN FORMAT=JSON $q6;
+eval $with_filter ANALYZE $q6;
+--source include/analyze-format.inc
+eval $with_filter ANALYZE FORMAT=JSON $q6;
+--sorted_result
+eval $with_filter $q6;
+
+eval $without_filter EXPLAIN $q6;
+eval $without_filter EXPLAIN FORMAT=JSON $q6;
+eval $without_filter ANALYZE $q6;
+--source include/analyze-format.inc
+eval $without_filter ANALYZE FORMAT=JSON $q6;
+--sorted_result
+eval $without_filter $q6;
+
+CREATE VIEW v1 AS
+SELECT * FROM orders
+WHERE o_orderdate BETWEEN '1992-12-01' AND '1997-01-01';
+
+let $q7=
+SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+ o_totaldiscount BETWEEN 18000 AND 20000 AND
+ o_totalprice BETWEEN 200000 AND 220000 AND
+ l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+
+eval $with_filter EXPLAIN $q7;
+eval $with_filter EXPLAIN FORMAT=JSON $q7;
+eval $with_filter ANALYZE $q7;
+--source include/analyze-format.inc
+eval $with_filter ANALYZE FORMAT=JSON $q7;
+--sorted_result
+eval $with_filter $q7;
+
+eval $without_filter EXPLAIN $q7;
+eval $without_filter EXPLAIN FORMAT=JSON $q7;
+eval $without_filter ANALYZE $q7;
+--source include/analyze-format.inc
+eval $without_filter ANALYZE FORMAT=JSON $q7;
+--sorted_result
+eval $without_filter $q7;
+
+ALTER TABLE lineitem DROP CONSTRAINT l_date;
+ALTER TABLE orders DROP CONSTRAINT o_price;
+ALTER TABLE orders DROP COLUMN o_totaldiscount;
+DROP VIEW v1;
+
DROP DATABASE dbt3_s001;
set @@use_stat_tables=@save_use_stat_tables;
-
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index f72db6b6bc0..65f68968f6e 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -1103,6 +1103,771 @@ o_orderkey l_linenumber l_shipdate o_totalprice
5895 4 1997-03-03 201419.83
5895 5 1997-04-30 201419.83
5895 6 1997-04-19 201419.83
+#
+# MDEV-18413: find constraint correlated indexes
+#
+ALTER TABLE lineitem ADD CONSTRAINT l_date CHECK(l_shipdate < l_receiptdate);
+# Filter on l_shipdate is not used because it participates in
+# the same constraint as l_receiptdate.
+# Access is made on l_receiptdate.
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 Using index condition; Using where
+1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_receiptdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "i_l_receiptdate",
+ "key_length": "4",
+ "used_key_parts": ["l_receiptDATE"],
+ "rows": 18,
+ "filtered": 0.5662,
+ "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'",
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'"
+ },
+ "table": {
+ "table_name": "orders",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["o_orderkey"],
+ "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "rows": 1,
+ "filtered": 5.6667,
+ "attached_condition": "orders.o_totalprice between 200000 and 250000"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 18.00 0.57 38.89 Using index condition; Using where
+1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 5.67 14.29 Using where
+set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_receiptdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "i_l_receiptdate",
+ "key_length": "4",
+ "used_key_parts": ["l_receiptDATE"],
+ "r_loops": 1,
+ "rows": 18,
+ "r_rows": 18,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 0.5662,
+ "r_filtered": 38.889,
+ "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'",
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'"
+ },
+ "table": {
+ "table_name": "orders",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["o_orderkey"],
+ "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "r_loops": 7,
+ "rows": 1,
+ "r_rows": 1,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 5.6667,
+ "r_filtered": 14.286,
+ "attached_condition": "orders.o_totalprice between 200000 and 250000"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+l_shipdate l_receiptdate o_totalprice
+1996-10-07 1996-10-08 202623.92
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 Using index condition; Using where
+1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_receiptdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "i_l_receiptdate",
+ "key_length": "4",
+ "used_key_parts": ["l_receiptDATE"],
+ "rows": 18,
+ "filtered": 0.5662,
+ "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'",
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'"
+ },
+ "table": {
+ "table_name": "orders",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["o_orderkey"],
+ "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "rows": 1,
+ "filtered": 5.6667,
+ "attached_condition": "orders.o_totalprice between 200000 and 250000"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 18.00 0.57 38.89 Using index condition; Using where
+1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 5.67 14.29 Using where
+set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_receiptdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "i_l_receiptdate",
+ "key_length": "4",
+ "used_key_parts": ["l_receiptDATE"],
+ "r_loops": 1,
+ "rows": 18,
+ "r_rows": 18,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 0.5662,
+ "r_filtered": 38.889,
+ "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'",
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'"
+ },
+ "table": {
+ "table_name": "orders",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["o_orderkey"],
+ "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "r_loops": 7,
+ "rows": 1,
+ "r_rows": 1,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 5.6667,
+ "r_filtered": 14.286,
+ "attached_condition": "orders.o_totalprice between 200000 and 250000"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT l_shipdate, l_receiptdate, o_totalprice
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
+l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
+o_totalprice BETWEEN 200000 AND 250000;
+l_shipdate l_receiptdate o_totalprice
+1996-10-07 1996-10-08 202623.92
+ALTER TABLE orders ADD COLUMN o_totaldiscount double;
+UPDATE orders SET o_totaldiscount = o_totalprice*(o_custkey/1000);
+CREATE INDEX i_o_totaldiscount on orders(o_totaldiscount);
+ALTER TABLE orders ADD CONSTRAINT o_price CHECK(o_totalprice > o_totaldiscount);
+# Filter on o_totalprice is not used because it participates in
+# the same constraint as o_discount.
+# Access is made on o_discount.
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "rows": 41,
+ "filtered": 3.3333,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "rows": 4,
+ "filtered": 3.0475,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 41.00 3.33 2.44 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where
+set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "r_loops": 1,
+ "rows": 41,
+ "r_rows": 41,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 3.3333,
+ "r_filtered": 2.439,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "r_loops": 1,
+ "rows": 4,
+ "r_rows": 6,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 3.0475,
+ "r_filtered": 66.667,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+o_totaldiscount o_totalprice l_shipdate
+18016.04288 219707.84 1996-10-02
+18016.04288 219707.84 1996-10-17
+18016.04288 219707.84 1996-11-04
+18016.04288 219707.84 1996-11-14
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "rows": 41,
+ "filtered": 3.3333,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "rows": 4,
+ "filtered": 3.0475,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 41.00 3.33 2.44 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where
+set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "r_loops": 1,
+ "rows": 41,
+ "r_rows": 41,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 3.3333,
+ "r_filtered": 2.439,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "r_loops": 1,
+ "rows": 4,
+ "r_rows": 6,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 3.0475,
+ "r_filtered": 66.667,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM orders, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+o_totaldiscount o_totalprice l_shipdate
+18016.04288 219707.84 1996-10-02
+18016.04288 219707.84 1996-10-17
+18016.04288 219707.84 1996-11-04
+18016.04288 219707.84 1996-11-14
+CREATE VIEW v1 AS
+SELECT * FROM orders
+WHERE o_orderdate BETWEEN '1992-12-01' AND '1997-01-01';
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_o_orderdate",
+ "i_o_totalprice",
+ "i_o_totaldiscount"
+ ],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "rows": 41,
+ "filtered": 2.0711,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "rows": 4,
+ "filtered": 3.0475,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 41.00 2.07 2.44 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where
+set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_o_orderdate",
+ "i_o_totalprice",
+ "i_o_totaldiscount"
+ ],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "r_loops": 1,
+ "rows": 41,
+ "r_rows": 41,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 2.0711,
+ "r_filtered": 2.439,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "r_loops": 1,
+ "rows": 4,
+ "r_rows": 6,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 3.0475,
+ "r_filtered": 66.667,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+o_totaldiscount o_totalprice l_shipdate
+18016.04288 219707.84 1996-10-02
+18016.04288 219707.84 1996-10-17
+18016.04288 219707.84 1996-11-04
+18016.04288 219707.84 1996-11-14
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_o_orderdate",
+ "i_o_totalprice",
+ "i_o_totaldiscount"
+ ],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "rows": 41,
+ "filtered": 2.0711,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "rows": 4,
+ "filtered": 3.0475,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 41.00 2.07 2.44 Using index condition; Using where
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where
+set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_o_orderdate",
+ "i_o_totalprice",
+ "i_o_totaldiscount"
+ ],
+ "key": "i_o_totaldiscount",
+ "key_length": "9",
+ "used_key_parts": ["o_totaldiscount"],
+ "r_loops": 1,
+ "rows": 41,
+ "r_rows": 41,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 2.0711,
+ "r_filtered": 2.439,
+ "index_condition": "orders.o_totaldiscount between 18000 and 20000",
+ "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "r_loops": 1,
+ "rows": 4,
+ "r_rows": 6,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 3.0475,
+ "r_filtered": 66.667,
+ "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT o_totaldiscount, o_totalprice, l_shipdate
+FROM v1, lineitem
+WHERE o_orderkey=l_orderkey AND
+o_totaldiscount BETWEEN 18000 AND 20000 AND
+o_totalprice BETWEEN 200000 AND 220000 AND
+l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
+o_totaldiscount o_totalprice l_shipdate
+18016.04288 219707.84 1996-10-02
+18016.04288 219707.84 1996-10-17
+18016.04288 219707.84 1996-11-04
+18016.04288 219707.84 1996-11-14
+ALTER TABLE lineitem DROP CONSTRAINT l_date;
+ALTER TABLE orders DROP CONSTRAINT o_price;
+ALTER TABLE orders DROP COLUMN o_totaldiscount;
+DROP VIEW v1;
DROP DATABASE dbt3_s001;
set @@use_stat_tables=@save_use_stat_tables;
SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/sql/item.h b/sql/item.h
index fd9261c3424..f8580a80edf 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1962,6 +1962,12 @@ class Item: public Value_source,
virtual bool check_valid_arguments_processor(void *arg) { return 0; }
virtual bool update_vcol_processor(void *arg) { return 0; }
virtual bool set_fields_as_dependent_processor(void *arg) { return 0; }
+ /*
+ Find if some of the key parts of table keys (the reference on table is
+ passed as an argument) participate in the expression.
+ If there is some, sets a bit for this key in the proper key map.
+ */
+ virtual bool check_index_dependence(void *arg) { return 0; }
/*============== End of Item processor list ======================*/
virtual Item *get_copy(THD *thd)=0;
@@ -3464,6 +3470,7 @@ class Item_field :public Item_ident,
DBUG_ASSERT(field_type() == MYSQL_TYPE_GEOMETRY);
return field->get_geometry_type();
}
+ bool check_index_dependence(void *arg);
friend class Item_default_value;
friend class Item_insert_value;
friend class st_select_lex_unit;
diff --git a/sql/rowid_filter.cc b/sql/rowid_filter.cc
index c899236e336..cea00097665 100644
--- a/sql/rowid_filter.cc
+++ b/sql/rowid_filter.cc
@@ -159,9 +159,12 @@ void TABLE::prune_range_rowid_filters()
Range_rowid_filter_cost_info **filter_ptr_2= filter_ptr_1 + 1;
for (j= i+1; j < range_rowid_filter_cost_info_elems; j++, filter_ptr_2++)
{
- key_map map= key_info[key_no].overlapped;
- map.intersect(key_info[(*filter_ptr_2)->key_no].overlapped);
- if (map.is_clear_all())
+ key_map map_1= key_info[key_no].overlapped;
+ map_1.merge(key_info[key_no].constraint_correlated);
+ key_map map_2= key_info[(*filter_ptr_2)->key_no].overlapped;
+ map_2.merge(key_info[(*filter_ptr_2)->key_no].constraint_correlated);
+ map_1.intersect(map_2);
+ if (map_1.is_clear_all())
{
(*filter_ptr_1)->abs_independent.set_bit((*filter_ptr_2)->key_no);
(*filter_ptr_2)->abs_independent.set_bit(key_no);
@@ -390,7 +393,8 @@ TABLE::best_range_rowid_filter_for_partial_join(uint access_key_no,
Range_rowid_filter_cost_info *best_filter= 0;
double best_filter_gain= 0;
- key_map *overlapped= &key_info[access_key_no].overlapped;
+ key_map no_filter_usage= key_info[access_key_no].overlapped;
+ no_filter_usage.merge(key_info[access_key_no].constraint_correlated);
for (uint i= 0; i < range_rowid_filter_cost_info_elems ; i++)
{
double curr_gain = 0;
@@ -401,7 +405,7 @@ TABLE::best_range_rowid_filter_for_partial_join(uint access_key_no,
the index by which the table is accessed
*/
if ((filter->key_no == access_key_no) ||
- overlapped->is_set(filter->key_no))
+ no_filter_usage.is_set(filter->key_no))
continue;
if (records < filter->cross_x)
diff --git a/sql/structs.h b/sql/structs.h
index 8aec29bac41..3628cd01748 100644
--- a/sql/structs.h
+++ b/sql/structs.h
@@ -124,6 +124,8 @@ typedef struct st_key {
(only key parts from key definitions are taken into account)
*/
key_map overlapped;
+ /* Set of keys constraint correlated with this key */
+ key_map constraint_correlated;
LEX_CSTRING name;
uint block_size;
enum ha_key_alg algorithm;
diff --git a/sql/table.cc b/sql/table.cc
index 6676ce5447d..c96a8db57e7 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -1194,6 +1194,8 @@ bool parse_vcol_defs(THD *thd, MEM_ROOT *mem_root, TABLE *table,
goto end;
}
+ table->find_constraint_correlated_indexes();
+
res=0;
end:
thd->restore_active_arena(table->expr_arena, &backup_arena);
@@ -1274,6 +1276,80 @@ void TABLE_SHARE::set_overlapped_keys()
}
+bool Item_field::check_index_dependence(void *arg)
+{
+ TABLE *table= (TABLE *)arg;
+
+ KEY *key= table->key_info;
+ for (uint j= 0; j < table->s->keys; j++, key++)
+ {
+ if (table->constraint_dependent_keys.is_set(j))
+ continue;
+
+ KEY_PART_INFO *key_part= key->key_part;
+ uint n= key->user_defined_key_parts;
+
+ for (uint k= 0; k < n; k++, key_part++)
+ {
+ if (this->field == key_part->field)
+ {
+ table->constraint_dependent_keys.set_bit(j);
+ break;
+ }
+ }
+ }
+ return false;
+}
+
+
+/**
+ @brief
+ Find keys that occur in the same constraint on this table
+
+ @details
+ Constraints on this table are checked only.
+
+ The method goes through constraints list trying to find at
+ least two keys which parts participate in some constraint.
+ These keys are called constraint correlated.
+
+ Each key has its own key map with the information about with
+ which keys it is constraint correlated. Bit in this map is set
+ only if keys are constraint correlated.
+ This method fills each keys constraint correlated key map.
+*/
+
+void TABLE::find_constraint_correlated_indexes()
+{
+ if (s->keys == 0)
+ return;
+
+ KEY *key= key_info;
+ for (uint i= 0; i < s->keys; i++, key++)
+ {
+ key->constraint_correlated.clear_all();
+ key->constraint_correlated.set_bit(i);
+ }
+
+ if (!check_constraints)
+ return;
+
+ for (Virtual_column_info **chk= check_constraints ; *chk ; chk++)
+ {
+ constraint_dependent_keys.clear_all();
+ (*chk)->expr->walk(&Item::check_index_dependence, 0, this);
+
+ if (constraint_dependent_keys.bits_set() <= 1)
+ continue;
+
+ uint key_no= 0;
+ key_map::Iterator ki(constraint_dependent_keys);
+ while ((key_no= ki++) != key_map::Iterator::BITMAP_END)
+ key_info[key_no].constraint_correlated.merge(constraint_dependent_keys);
+ }
+}
+
+
/**
Read data from a binary .frm file image into a TABLE_SHARE
diff --git a/sql/table.h b/sql/table.h
index 0a2dc63b068..377176fd10e 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1133,6 +1133,8 @@ struct TABLE
key_map keys_in_use_for_group_by;
/* Map of keys that can be used to calculate ORDER BY without sorting */
key_map keys_in_use_for_order_by;
+ /* Map of keys dependent on some constraint */
+ key_map constraint_dependent_keys;
KEY *key_info; /* data of keys in database */
Field **field; /* Pointer to fields */
@@ -1566,6 +1568,7 @@ struct TABLE
int delete_row();
void vers_update_fields();
void vers_update_end();
+ void find_constraint_correlated_indexes();
/** Number of additional fields used in versioned tables */
#define VERSIONING_FIELDS 2
1
0
[Commits] 53730224efd: Improve histogram collection performance by sampling
by vicentiu＠mariadb.org 10 Feb '19
by vicentiu＠mariadb.org 10 Feb '19
10 Feb '19
revision-id: 53730224efd987f97a6cc968ff5214ee499d84e0 (mariadb-10.4.1-163-g53730224efd)
parent(s): 3c305d3f1951f1667f84e48ddd98674c6318c39d
author: Vicențiu Ciorbaru
committer: Vicențiu Ciorbaru
timestamp: 2019-02-10 19:54:50 +0200
message:
Improve histogram collection performance by sampling
Histogram collection is done by sampling a percentage of rows from the table,
not looking at all individual ones.
The default implementation, to keep the server's Engine Indepenent
Statistics component working uses Bernoulli sampling. It does a simple
table scan, but only accepts rows that pass a dice roll. This
implementation is done as a storage engine interface method, so as to
allow faster and/or more efficient implementations for storage engines
internally.
The number of rows collected is capped to a minimum of 50000 and
increases logarithmically with a coffecient of 4096. The coffecient is
chosen so that we expect an error of less than 3% in our estimations
according to the paper:
"Random Sampling for Histogram Construction: How much is enough?”
– Surajit Chaudhuri, Rajeev Motwani, Vivek Narasayya, ACM SIGMOD, 1998.
This interface is also a precursor to allowing SELECT ... FROM table
with sampling to work.
Performance wise, for a table of 10 million rows and 13 columns, 6 int,
6 doubles, one string, the previous analyze table statistics took
1 minute 20 seconds to collect all data. Post implementation, the
time is less than 6 seconds. This was run on an InnoDB table, NVME SSD with
approximately 2GB/s read speed and 500MB/s write speed.
---
mysql-test/main/selectivity.result | 8 +++----
mysql-test/main/selectivity_innodb.result | 8 +++----
sql/handler.cc | 14 +++++++++++
sql/handler.h | 40 ++++++++++++++++++++++++++++++-
sql/sql_statistics.cc | 32 +++++++++++++++++++------
5 files changed, 86 insertions(+), 16 deletions(-)
diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result
index 00907235ecc..6d09c1c9b62 100644
--- a/mysql-test/main/selectivity.result
+++ b/mysql-test/main/selectivity.result
@@ -1290,9 +1290,9 @@ explain extended
select * from t1, t2, t1 as t3
where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 262144 100.00 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 262117 100.00 Using where
1 SIMPLE t2 ref c,d c 5 test.t1.b 5 100.00
-1 SIMPLE t3 ALL NULL NULL NULL NULL 262144 100.00 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 262117 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t3` where `test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`a` = `test`.`t2`.`d` and `test`.`t3`.`b` < 5 and `test`.`t1`.`a` < 2000
select * from t1, t2, t1 as t3
@@ -1307,9 +1307,9 @@ explain extended
select * from t1, t2, t1 as t3
where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t3 ALL NULL NULL NULL NULL 262144 0.00 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 262117 0.00 Using where
1 SIMPLE t2 ref c,d d 5 test.t3.a 7 100.00
-1 SIMPLE t1 ALL NULL NULL NULL NULL 262144 2.00 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t1 ALL NULL NULL NULL NULL 262117 2.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t3` where `test`.`t1`.`b` = `test`.`t2`.`c` and `test`.`t2`.`d` = `test`.`t3`.`a` and `test`.`t3`.`b` < 5 and `test`.`t1`.`a` < 2000
select * from t1, t2, t1 as t3
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result
index 93917065722..0b20a40f69f 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -1300,9 +1300,9 @@ explain extended
select * from t1, t2, t1 as t3
where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 262144 100.00 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 262117 100.00 Using where
1 SIMPLE t2 ref c,d c 5 test.t1.b 5 100.00
-1 SIMPLE t3 ALL NULL NULL NULL NULL 262144 100.00 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 262117 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t3` where `test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`a` = `test`.`t2`.`d` and `test`.`t3`.`b` < 5 and `test`.`t1`.`a` < 2000
select * from t1, t2, t1 as t3
@@ -1317,9 +1317,9 @@ explain extended
select * from t1, t2, t1 as t3
where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t3 ALL NULL NULL NULL NULL 262144 0.00 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 262117 0.00 Using where
1 SIMPLE t2 ref c,d d 5 test.t3.a 7 100.00
-1 SIMPLE t1 ALL NULL NULL NULL NULL 262144 2.00 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t1 ALL NULL NULL NULL NULL 262117 2.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t3` where `test`.`t1`.`b` = `test`.`t2`.`c` and `test`.`t2`.`d` = `test`.`t3`.`a` and `test`.`t3`.`b` < 5 and `test`.`t1`.`a` < 2000
select * from t1, t2, t1 as t3
diff --git a/sql/handler.cc b/sql/handler.cc
index 2e45b5883ea..16fb126918e 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -7547,3 +7547,17 @@ bool Vers_parse_info::check_sys_fields(const Lex_table_name &table_name,
"ROW END" : found_flag ? "ROW START" : "ROW START/END");
return true;
}
+
+int handler::random_sample(uchar *buf)
+{
+ int rc;
+ THD *thd= ha_thd();
+ do
+ {
+ if (table->in_use->check_killed(1))
+ return HA_ERR_ABORTED_BY_USER;
+ rc= rnd_next(buf);
+ } while (rc == HA_ERR_RECORD_DELETED || thd_rnd(thd) > sample_fraction);
+
+ return rc;
+}
diff --git a/sql/handler.h b/sql/handler.h
index dfb2333b24e..fcdadbeb42b 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -1913,6 +1913,11 @@ enum enum_stats_auto_recalc { HA_STATS_AUTO_RECALC_DEFAULT= 0,
HA_STATS_AUTO_RECALC_ON,
HA_STATS_AUTO_RECALC_OFF };
+enum sample_mode {
+ HA_SAMPLE_BERNOULLI= 0,
+ HA_SAMPLE_SYSTEM
+};
+
/**
A helper struct for schema DDL statements:
CREATE SCHEMA [IF NOT EXISTS] name [ schema_specification... ]
@@ -2947,9 +2952,11 @@ class handler :public Sql_alloc
/** Length of ref (1-8 or the clustered key length) */
uint ref_length;
FT_INFO *ft_handler;
- enum init_stat { NONE=0, INDEX, RND };
+ enum init_stat { NONE=0, INDEX, RND, SAMPLE };
init_stat inited, pre_inited;
+ double sample_fraction= 0;
+ enum sample_mode sample_mode;
const COND *pushed_cond;
/**
next_insert_id is the next value which should be inserted into the
@@ -3112,6 +3119,31 @@ class handler :public Sql_alloc
virtual int prepare_range_scan(const key_range *start_key, const key_range *end_key)
{ return 0; }
+ int ha_random_sample_init(THD *thd, enum sample_mode mode, double fraction)
+ __attribute__((warn_unused_result))
+ {
+ DBUG_ENTER("ha_random_sample_init");
+ DBUG_ASSERT(inited==NONE);
+ int result;
+ sample_mode= mode;
+ sample_fraction= fraction;
+ inited= (result= random_sample_init(mode, fraction)) ? NONE : SAMPLE;
+ DBUG_RETURN(result);
+ }
+ int ha_random_sample(uchar *buf)
+ __attribute__((warn_unused_result))
+ {
+ DBUG_ENTER("ha_random_sample");
+ DBUG_ASSERT(inited == SAMPLE);
+ DBUG_RETURN(random_sample(buf));
+ }
+ int ha_random_sample_end()
+ {
+ DBUG_ENTER("ha_random_sample_end");
+ inited= NONE;
+ DBUG_RETURN(random_sample_end());
+ }
+
int ha_rnd_init(bool scan) __attribute__ ((warn_unused_result))
{
DBUG_EXECUTE_IF("ha_rnd_init_fail", return HA_ERR_TABLE_DEF_CHANGED;);
@@ -4425,6 +4457,12 @@ class handler :public Sql_alloc
/* Note: ha_index_read_idx_map() may bypass index_init() */
virtual int index_init(uint idx, bool sorted) { return 0; }
virtual int index_end() { return 0; }
+ virtual int random_sample_init(enum sample_mode mode, double fraction)
+ {
+ return rnd_init(TRUE);
+ }
+ virtual int random_sample(uchar *buf);
+ virtual int random_sample_end() { return rnd_end(); }
/**
rnd_init() can be called two times without rnd_end() in between
(it only makes sense if scan=1).
diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc
index db214a1fe28..22a015821de 100644
--- a/sql/sql_statistics.cc
+++ b/sql/sql_statistics.cc
@@ -2727,12 +2727,16 @@ int collect_statistics_for_table(THD *thd, TABLE *table)
Field *table_field;
ha_rows rows= 0;
handler *file=table->file;
+ double sample_fraction;
+ const ha_rows MIN_THRESHOLD_FOR_SAMPLING= 50000;
DBUG_ENTER("collect_statistics_for_table");
table->collected_stats->cardinality_is_null= TRUE;
table->collected_stats->cardinality= 0;
+ table->file->info(HA_STATUS_VARIABLE);
+
for (field_ptr= table->field; *field_ptr; field_ptr++)
{
table_field= *field_ptr;
@@ -2743,12 +2747,27 @@ int collect_statistics_for_table(THD *thd, TABLE *table)
restore_record(table, s->default_values);
- /* Perform a full table scan to collect statistics on 'table's columns */
- if (!(rc= file->ha_rnd_init(TRUE)))
- {
+
+ if (file->records() < MIN_THRESHOLD_FOR_SAMPLING)
+ {
+ sample_fraction= 1;
+ }
+ else
+ {
+ sample_fraction= std::fmin(
+ (MIN_THRESHOLD_FOR_SAMPLING + 4096 *
+ log(200 * file->records())) / file->records(), 1);
+ }
+
+
+ /* Fetch samples from the table to collect statistics on table's columns */
+
+ if (!(rc= file->ha_random_sample_init(thd, HA_SAMPLE_BERNOULLI,
+ sample_fraction)))
+ {
DEBUG_SYNC(table->in_use, "statistics_collection_start");
- while ((rc= file->ha_rnd_next(table->record[0])) != HA_ERR_END_OF_FILE)
+ while ((rc= file->ha_random_sample(table->record[0])) != HA_ERR_END_OF_FILE)
{
if (thd->killed)
break;
@@ -2768,10 +2787,9 @@ int collect_statistics_for_table(THD *thd, TABLE *table)
break;
rows++;
}
- file->ha_rnd_end();
+ file->ha_random_sample_end();
}
rc= (rc == HA_ERR_END_OF_FILE && !thd->killed) ? 0 : 1;
-
/*
Calculate values for all statistical characteristics on columns and
and for each field f of 'table' save them in the write_stat structure
@@ -2780,7 +2798,7 @@ int collect_statistics_for_table(THD *thd, TABLE *table)
if (!rc)
{
table->collected_stats->cardinality_is_null= FALSE;
- table->collected_stats->cardinality= rows;
+ table->collected_stats->cardinality= rows / sample_fraction;
}
bitmap_clear_all(table->write_set);
1
0
[Commits] d11be23: MDEV-17096 Pushdown of simple derived tables to storage engines
by IgorBabaev 10 Feb '19
by IgorBabaev 10 Feb '19
10 Feb '19
revision-id: d11be23933def394585fae83a6f1ab561e3736f2 (mariadb-10.3.6-132-gd11be23)
parent(s): 3f9040085a0de4976f55bc7e4a2fa5fa8d923100
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-02-09 22:54:26 -0800
message:
MDEV-17096 Pushdown of simple derived tables to storage engines
Resolved the problem of forming a proper query string for FEDERATEDX.
Added test cases.
Cleanup of extra spaces.
---
.../federated/federatedx_create_handlers.result | 119 +++++++++++++++++++--
.../federated/federatedx_create_handlers.test | 60 +++++++++--
sql/derived_handler.cc | 8 +-
sql/item_cmpfunc.cc | 9 +-
sql/item_subselect.cc | 6 +-
sql/mysqld.h | 2 +
sql/select_handler.cc | 14 +--
sql/select_handler.h | 10 +-
sql/sql_derived.cc | 34 ++++--
sql/sql_explain.cc | 6 +-
sql/sql_select.cc | 8 +-
sql/table.h | 1 +
storage/federatedx/federatedx_pushdown.cc | 19 ++--
storage/federatedx/federatedx_pushdown.h | 4 +-
14 files changed, 233 insertions(+), 67 deletions(-)
diff --git a/mysql-test/suite/federated/federatedx_create_handlers.result b/mysql-test/suite/federated/federatedx_create_handlers.result
index fdad44c..a25b019 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.result
+++ b/mysql-test/suite/federated/federatedx_create_handlers.result
@@ -10,7 +10,7 @@ Warnings:
Note 1051 Unknown table 'federated.t1'
CREATE TABLE federated.t1 (
id int(20) NOT NULL,
-name varchar(16) NOT NULL default ''
+name varchar(16) NOT NULL default ''
)
DEFAULT CHARSET=latin1;
INSERT INTO federated.t1 VALUES
@@ -19,7 +19,7 @@ DROP TABLE IF EXISTS federated.t2;
Warnings:
Note 1051 Unknown table 'federated.t2'
CREATE TABLE federated.t2 (
-name varchar(16) NOT NULL default ''
+name varchar(16) NOT NULL default ''
)
DEFAULT CHARSET=latin1;
INSERT INTO federated.t2 VALUES
@@ -30,7 +30,7 @@ Warnings:
Note 1051 Unknown table 'federated.t1'
CREATE TABLE federated.t1 (
id int(20) NOT NULL,
-name varchar(16) NOT NULL default ''
+name varchar(16) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1';
@@ -38,7 +38,7 @@ DROP TABLE IF EXISTS federated.t2;
Warnings:
Note 1051 Unknown table 'federated.t2'
CREATE TABLE federated.t2 (
-name varchar(16) NOT NULL default ''
+name varchar(16) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t2';
@@ -123,7 +123,7 @@ ANALYZE
}
}
CREATE TABLE federated.t3 (
-name varchar(16) NOT NULL default ''
+name varchar(16) NOT NULL default ''
)
DEFAULT CHARSET=latin1;
INSERT INTO federated.t3 VALUES
@@ -190,7 +190,114 @@ id select_type table type possible_keys key key_len ref rows r_rows filtered r_f
1 PRIMARY t3 ALL NULL NULL NULL NULL 7 7.00 100.00 100.00
1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2 0.00 100.00 100.00
2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
-DROP TABLE federated.t1, federated.t2;
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+WHERE id IN (SELECT count(*)
+FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+name name
+xxx xxx
+EXPLAIN
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+WHERE id IN (SELECT count(*)
+FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 7
+1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2
+2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL
+3 MATERIALIZED t2 ALL NULL NULL NULL NULL 7 Using temporary
+ANALYZE FORMAT=JSON
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+WHERE id IN (SELECT count(*)
+FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "t3",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 7,
+ "r_rows": 7,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ },
+ "table": {
+ "table_name": "<derived2>",
+ "access_type": "ref",
+ "possible_keys": ["key0"],
+ "key": "key0",
+ "key_length": "18",
+ "used_key_parts": ["name"],
+ "ref": ["federated.t3.name"],
+ "r_loops": 7,
+ "rows": 2,
+ "r_rows": 0,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100,
+ "materialized": {
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "message": "Pushed derived"
+ },
+ "subqueries": [
+ {
+ "query_block": {
+ "select_id": 3,
+ "temporary_table": {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ALL",
+ "r_loops": 0,
+ "rows": 7,
+ "r_rows": null,
+ "filtered": 100,
+ "r_filtered": null
+ }
+ }
+ }
+ }
+ ]
+ }
+ }
+ }
+ }
+}
+SELECT t.id, federated.t3.name
+FROM federated.t3,
+( SELECT * FROM federated.t1 WHERE id < 3
+UNION
+SELECT * FROM federated.t1 WHERE id >= 5) t
+WHERE federated.t3.name=t.name;
+id name
+5 yyy
+7 yyy
+5 yyy
+7 yyy
+5 yyy
+7 yyy
+EXPLAIN
+SELECT t.id, federated.t3.name
+FROM federated.t3,
+( SELECT * FROM federated.t1 WHERE id < 3
+UNION
+SELECT * FROM federated.t1 WHERE id >= 5) t
+WHERE federated.t3.name=t.name;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 7
+1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2
+2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL
+DROP TABLE federated.t1, federated.t2, federated.t3;
connection slave;
DROP TABLE federated.t1, federated.t2;
connection default;
diff --git a/mysql-test/suite/federated/federatedx_create_handlers.test b/mysql-test/suite/federated/federatedx_create_handlers.test
index 0e586da..21539b6 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.test
+++ b/mysql-test/suite/federated/federatedx_create_handlers.test
@@ -7,7 +7,7 @@ DROP TABLE IF EXISTS federated.t1;
CREATE TABLE federated.t1 (
id int(20) NOT NULL,
- name varchar(16) NOT NULL default ''
+ name varchar(16) NOT NULL default ''
)
DEFAULT CHARSET=latin1;
@@ -17,7 +17,7 @@ INSERT INTO federated.t1 VALUES
DROP TABLE IF EXISTS federated.t2;
CREATE TABLE federated.t2 (
- name varchar(16) NOT NULL default ''
+ name varchar(16) NOT NULL default ''
)
DEFAULT CHARSET=latin1;
@@ -33,7 +33,7 @@ DROP TABLE IF EXISTS federated.t1;
eval
CREATE TABLE federated.t1 (
id int(20) NOT NULL,
- name varchar(16) NOT NULL default ''
+ name varchar(16) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
@@ -43,7 +43,7 @@ DROP TABLE IF EXISTS federated.t2;
--replace_result $SLAVE_MYPORT SLAVE_PORT
eval
CREATE TABLE federated.t2 (
- name varchar(16) NOT NULL default ''
+ name varchar(16) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t2';
@@ -76,11 +76,12 @@ SELECT id FROM federated.t1 WHERE id < 5;
ANALYZE
SELECT id FROM federated.t1 WHERE id < 5;
+--source include/analyze-format.inc
ANALYZE FORMAT=JSON
SELECT id FROM federated.t1 WHERE id < 5;
CREATE TABLE federated.t3 (
- name varchar(16) NOT NULL default ''
+ name varchar(16) NOT NULL default ''
)
DEFAULT CHARSET=latin1;
@@ -89,26 +90,63 @@ INSERT INTO federated.t3 VALUES
SELECT *
FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
-WHERE federated.t3.name=t.name;
+WHERE federated.t3.name=t.name;
EXPLAIN
SELECT *
FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
-WHERE federated.t3.name=t.name;
+WHERE federated.t3.name=t.name;
EXPLAIN FORMAT=JSON
SELECT *
FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
-WHERE federated.t3.name=t.name;
+WHERE federated.t3.name=t.name;
ANALYZE
SELECT *
FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
-WHERE federated.t3.name=t.name;
+WHERE federated.t3.name=t.name;
-DROP TABLE federated.t1, federated.t2;
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+ WHERE id IN (SELECT count(*)
+ FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
-connection slave;
+EXPLAIN
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+ WHERE id IN (SELECT count(*)
+ FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+
+--source include/analyze-format.inc
+ANALYZE FORMAT=JSON
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+ WHERE id IN (SELECT count(*)
+ FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+
+SELECT t.id, federated.t3.name
+FROM federated.t3,
+ ( SELECT * FROM federated.t1 WHERE id < 3
+ UNION
+ SELECT * FROM federated.t1 WHERE id >= 5) t
+WHERE federated.t3.name=t.name;
+
+EXPLAIN
+SELECT t.id, federated.t3.name
+FROM federated.t3,
+ ( SELECT * FROM federated.t1 WHERE id < 3
+ UNION
+ SELECT * FROM federated.t1 WHERE id >= 5) t
+WHERE federated.t3.name=t.name;
+
+
+DROP TABLE federated.t1, federated.t2, federated.t3;
+
+connection slave;
DROP TABLE federated.t1, federated.t2;
connection default;
diff --git a/sql/derived_handler.cc b/sql/derived_handler.cc
index 561c18a..1fa5e94 100644
--- a/sql/derived_handler.cc
+++ b/sql/derived_handler.cc
@@ -16,7 +16,7 @@ void derived_handler::set_derived(TABLE_LIST *tbl)
Pushdown_derived::Pushdown_derived(TABLE_LIST *tbl, derived_handler *h)
: derived(tbl), handler(h)
-{
+{
is_analyze= handler->thd->lex->analyze_stmt;
}
@@ -42,7 +42,7 @@ int Pushdown_derived::execute()
handler->end_scan();
DBUG_RETURN(0);
}
-
+
while (!(err= handler->next_row()))
{
if (unlikely(thd->check_killed()))
@@ -50,7 +50,7 @@ int Pushdown_derived::execute()
handler->end_scan();
DBUG_RETURN(-1);
}
-
+
if ((err= table->file->ha_write_tmp_row(table->record[0])))
{
bool is_duplicate;
@@ -81,4 +81,4 @@ int Pushdown_derived::execute()
handler->print_error(err, MYF(0));
DBUG_RETURN(-1); // Error not sent to client
}
-
+
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 4cd6304..821f51f 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1215,8 +1215,13 @@ bool Item_in_optimizer::eval_not_null_tables(void *opt_arg)
void Item_in_optimizer::print(String *str, enum_query_type query_type)
{
- restore_first_argument();
- Item_func::print(str, query_type);
+ if (query_type & QT_PARSABLE)
+ args[1]->print(str, query_type);
+ else
+ {
+ restore_first_argument();
+ Item_func::print(str, query_type);
+ }
}
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 0ace59f..7aa2ed4 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -3272,7 +3272,8 @@ Item_in_subselect::select_in_like_transformer(JOIN *join)
void Item_in_subselect::print(String *str, enum_query_type query_type)
{
- if (test_strategy(SUBS_IN_TO_EXISTS))
+ if (test_strategy(SUBS_IN_TO_EXISTS) &&
+ !(query_type & QT_PARSABLE))
str->append(STRING_WITH_LEN("<exists>"));
else
{
@@ -3499,7 +3500,8 @@ Item_allany_subselect::select_transformer(JOIN *join)
void Item_allany_subselect::print(String *str, enum_query_type query_type)
{
- if (test_strategy(SUBS_IN_TO_EXISTS))
+ if (test_strategy(SUBS_IN_TO_EXISTS) &&
+ !(query_type & QT_PARSABLE))
str->append(STRING_WITH_LEN("<exists>"));
else
{
diff --git a/sql/mysqld.h b/sql/mysqld.h
index 3d056fb..320dedc 100644
--- a/sql/mysqld.h
+++ b/sql/mysqld.h
@@ -745,6 +745,8 @@ enum enum_query_type
/// SHOW CREATE {VIEW|PROCEDURE|FUNCTION} and other cases where the
/// original representation is required, should set this flag.
QT_ITEM_ORIGINAL_FUNC_NULLIF= (1 << 7),
+ /// good for parsing
+ QT_PARSABLE= (1 << 8),
/// This value means focus on readability, not on ability to parse back, etc.
QT_EXPLAIN= QT_TO_SYSTEM_CHARSET |
diff --git a/sql/select_handler.cc b/sql/select_handler.cc
index 17475f4..9a8d391 100644
--- a/sql/select_handler.cc
+++ b/sql/select_handler.cc
@@ -6,7 +6,7 @@
Pushdown_select::Pushdown_select(SELECT_LEX *sel, select_handler *h)
: select(sel), handler(h)
-{
+{
is_analyze= handler->thd->lex->analyze_stmt;
}
@@ -35,7 +35,7 @@ bool Pushdown_select::init()
DBUG_RETURN(true);
if (handler->table->fill_item_list(&result_columns))
DBUG_RETURN(true);
- DBUG_RETURN(false);
+ DBUG_RETURN(false);
}
bool Pushdown_select::send_result_set_metadata()
@@ -74,7 +74,7 @@ bool Pushdown_select::send_data()
protocol->remove_last_row();
DBUG_RETURN(true);
}
-
+
thd->inc_sent_row_count(1);
if (thd->vio_ok())
@@ -88,13 +88,13 @@ bool Pushdown_select::send_eof()
THD *thd= handler->thd;
DBUG_ENTER("Pushdown_select::send_eof");
- /*
+ /*
Don't send EOF if we're in error condition (which implies we've already
sent or are sending an error)
*/
if (thd->is_error())
DBUG_RETURN(true);
- ::my_eof(thd);
+ ::my_eof(thd);
DBUG_RETURN(false);
}
@@ -113,10 +113,10 @@ int Pushdown_select::execute()
handler->end_scan();
DBUG_RETURN(0);
}
-
+
if (send_result_set_metadata())
DBUG_RETURN(-1);
-
+
while (!(err= handler->next_row()))
{
if (thd->check_killed() || send_data())
diff --git a/sql/select_handler.h b/sql/select_handler.h
index 3b1347e..19a1883 100644
--- a/sql/select_handler.h
+++ b/sql/select_handler.h
@@ -20,9 +20,9 @@ class select_handler
select_handler(THD *thd_arg, handlerton *ht_arg)
: thd(thd_arg), ht(ht_arg), table(0) {}
-
- virtual ~select_handler() {}
-
+
+ virtual ~select_handler() {}
+
/*
Functions to scan the select result set.
All these returns 0 if ok, error code in case of error.
@@ -32,7 +32,7 @@ class select_handler
virtual int init_scan() = 0;
/*
- Put the next produced row of the result set in table->record[0]
+ Put the next produced row of the result set in table->record[0]
and return 0. Return HA_ERR_END_OF_FILE if there are no more rows,
return other error number in case of fatal error.
*/
@@ -40,7 +40,7 @@ class select_handler
/* Finish scanning */
virtual int end_scan() = 0;
-
+
/* Report errors */
virtual void print_error(int error, myf errflag) = 0;
};
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 9ad22dd..564049e 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -385,7 +385,7 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived)
DBUG_RETURN(FALSE);
}
- if ((derived->dt_handler= derived->find_derived_handler(thd)))
+ if (derived->dt_handler)
{
derived->change_refs_to_fields();
derived->set_materialized_derived();
@@ -820,6 +820,24 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
if (derived->is_derived() && derived->is_merged_derived())
first_select->mark_as_belong_to_derived(derived);
+ derived->dt_handler= derived->find_derived_handler(thd);
+ if (derived->dt_handler)
+ {
+ char query_buff[4096];
+ String derived_query(query_buff, sizeof(query_buff), thd->charset());
+ derived_query.length(0);
+ derived->derived->print(&derived_query,
+ enum_query_type(QT_VIEW_INTERNAL |
+ QT_ITEM_ORIGINAL_FUNC_NULLIF |
+ QT_PARSABLE));
+ if (!thd->make_lex_string(&derived->derived_spec,
+ derived_query.ptr(), derived_query.length()))
+ {
+ delete derived->dt_handler;
+ derived->dt_handler= NULL;
+ }
+ }
+
exit:
/* Hide "Unknown column" or "Unknown function" error */
if (derived->view)
@@ -912,19 +930,17 @@ bool mysql_derived_optimize(THD *thd, LEX *lex, TABLE_LIST *derived)
DBUG_RETURN(FALSE);
}
- if (derived->is_materialized_derived() && !derived->dt_handler)
- derived->dt_handler= derived->find_derived_handler(thd);
- if (derived->dt_handler)
+ if (derived->is_materialized_derived() && derived->dt_handler)
{
if (!(derived->pushdown_derived=
new (thd->mem_root) Pushdown_derived(derived, derived->dt_handler)))
{
delete derived->dt_handler;
- derived->dt_handler= NULL;
- DBUG_RETURN(1);
+ derived->dt_handler= NULL;
+ DBUG_RETURN(TRUE);
}
- }
-
+ }
+
lex->current_select= first_select;
if (unit->is_unit_op())
@@ -1136,7 +1152,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
if (unit->executed)
DBUG_RETURN(FALSE);
res= derived->pushdown_derived->execute();
- unit->executed= true;
+ unit->executed= true;
delete derived->pushdown_derived;
DBUG_RETURN(res);
}
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index b8d80c7..7bb04fe 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -339,7 +339,7 @@ int print_explain_row(select_result_sink *result,
if (!select_type[0])
return 0;
-
+
item_list.push_back(new (mem_root) Item_int(thd, (int32) select_number),
mem_root);
item_list.push_back(new (mem_root) Item_string_sys(thd, select_type),
@@ -756,7 +756,7 @@ int Explain_select::print_explain(Explain_query *query,
{
print_explain_message_line(output, explain_flags, is_analyze,
select_id /*select number*/,
- select_type,
+ select_type,
NULL, /* rows */
NULL);
}
@@ -1225,7 +1225,7 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai
{
THD *thd= output->thd;
MEM_ROOT *mem_root= thd->mem_root;
-
+
List<Item> item_list;
Item *item_null= new (mem_root) Item_null(thd);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 7cbbdfe..e9040e1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4044,7 +4044,7 @@ void JOIN::exec_inner()
not the case.
*/
if (exec_const_order_group_cond.elements &&
- !(select_options & SELECT_DESCRIBE) &&
+ !(select_options & SELECT_DESCRIBE) &&
!select_lex->pushdown_select)
{
List_iterator_fast<Item> const_item_it(exec_const_order_group_cond);
@@ -4300,7 +4300,7 @@ mysql_select(THD *thd,
DBUG_RETURN(TRUE);
}
}
-
+
if ((err= join->optimize()))
{
goto err; // 1
@@ -4322,7 +4322,7 @@ mysql_select(THD *thd,
select_lex->where= join->conds_history;
select_lex->having= join->having_history;
}
-
+
err:
if (select_lex->pushdown_select)
@@ -25885,7 +25885,7 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
res= unit->exec();
}
}
- else
+ else
{
thd->lex->current_select= first;
unit->set_limit(unit->global_parameters());
diff --git a/sql/table.h b/sql/table.h
index 7b88344..f7bcdaa 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -2137,6 +2137,7 @@ struct TABLE_LIST
bool is_derived_with_recursive_reference;
bool block_handle_derived;
derived_handler *dt_handler;
+ LEX_CSTRING derived_spec;
Pushdown_derived *pushdown_derived;
ST_SCHEMA_TABLE *schema_table; /* Information_schema table */
st_select_lex *schema_select_lex;
diff --git a/storage/federatedx/federatedx_pushdown.cc b/storage/federatedx/federatedx_pushdown.cc
index bfe421c..c53b359 100644
--- a/storage/federatedx/federatedx_pushdown.cc
+++ b/storage/federatedx/federatedx_pushdown.cc
@@ -38,14 +38,14 @@ create_federatedx_derived_handler(THD* thd, TABLE_LIST *derived)
return 0;
}
}
-
+
handler= new ha_federatedx_derived_handler(thd, derived);
return handler;
}
-/*
+/*
Implementation class of the derived_handler interface for FEDERATEDX:
class implementation
*/
@@ -62,7 +62,6 @@ ha_federatedx_derived_handler::~ha_federatedx_derived_handler() {}
int ha_federatedx_derived_handler::init_scan()
{
- char query_buff[4096];
THD *thd;
int rc= 0;
@@ -76,12 +75,8 @@ int ha_federatedx_derived_handler::init_scan()
txn= h->get_txn(thd);
if ((rc= txn->acquire(share, thd, TRUE, iop)))
DBUG_RETURN(rc);
-
- String derived_query(query_buff, sizeof(query_buff), thd->charset());
- derived_query.length(0);
- derived->derived->print(&derived_query, QT_ORDINARY);
-
- if ((*iop)->query(derived_query.ptr(), derived_query.length()))
+
+ if ((*iop)->query(derived->derived_spec.str, derived->derived_spec.length))
goto err;
stored_result= (*iop)->store_result();
@@ -93,7 +88,7 @@ int ha_federatedx_derived_handler::init_scan()
err:
DBUG_RETURN(HA_FEDERATEDX_ERROR_WITH_REMOTE_SYSTEM);
}
-
+
int ha_federatedx_derived_handler::next_row()
{
int rc;
@@ -167,7 +162,7 @@ create_federatedx_select_handler(THD* thd, SELECT_LEX *sel)
return handler;
}
-/*
+/*
Implementation class of the select_handler interface for FEDERATEDX:
class implementation
*/
@@ -175,7 +170,7 @@ create_federatedx_select_handler(THD* thd, SELECT_LEX *sel)
ha_federatedx_select_handler::ha_federatedx_select_handler(THD *thd,
SELECT_LEX *sel)
: select_handler(thd, federatedx_hton),
- share(NULL), txn(NULL), iop(NULL), stored_result(NULL)
+ share(NULL), txn(NULL), iop(NULL), stored_result(NULL)
{
select= sel;
}
diff --git a/storage/federatedx/federatedx_pushdown.h b/storage/federatedx/federatedx_pushdown.h
index 961571b..673abcf 100644
--- a/storage/federatedx/federatedx_pushdown.h
+++ b/storage/federatedx/federatedx_pushdown.h
@@ -17,7 +17,7 @@
#include "derived_handler.h"
#include "select_handler.h"
-/*
+/*
Implementation class of the derived_handler interface for FEDERATEDX:
class declaration
*/
@@ -40,7 +40,7 @@ class ha_federatedx_derived_handler: public derived_handler
};
-/*
+/*
Implementation class of the select_handler interface for FEDERATEDX:
class declaration
*/
1
0