Re: [Commits] 326db1a: Mdev-14853 Grant does not work correctly when table contains...
by Sachin Setiya 26 Apr '18
by Sachin Setiya 26 Apr '18
26 Apr '18
Hi Vicentiu,
Now, I have run test with embedded mysql server and it passes.
May be embedded server does not support advance feature of grant like roles
, I dont know.
Thanks
sachin
On Thu, Apr 26, 2018 at 5:39 PM, Vicențiu Ciorbaru <cvicentiu(a)gmail.com>
wrote:
> Hi Sachin!
>
> Did you run this test on embedded server? Usually grant related tests
> require some form of not_embedded.inc include. (check roles suite for
> examples)
>
> Vicentiu
>
> On Thu, 26 Apr 2018 at 00:20 sachin <sachin.setiya(a)mariadb.com> wrote:
>
>> revision-id: 326db1a2aaa9b275a1a21a863e8cd2d9fa1b1d5f
>> (mariadb-10.3.6-46-g326db1a)
>> parent(s): 9477a2a9ba17c0db362e2bb39d5048e369096f39
>> author: Sachin Setiya
>> committer: Sachin Setiya
>> timestamp: 2018-04-26 12:47:25 +0530
>> message:
>>
>> Mdev-14853 Grant does not work correctly when table contains...
>> SYSTEM_INVISIBLE or COMPLETELY_INVISIBLE
>>
>> This commit does multiple things to solve this mdev
>> 1st add field into the parameter of check_column_grant_in_table_ref, so
>> that
>> we can find out field invisibility.
>> 2nd If field->invisible >= INVISIBLE_SYSTEM skip access check and simple
>> grant access.
>>
>> ---
>> mysql-test/main/invisible_field_grant.result | 111
>> +++++++++++++++++++++++++++
>> mysql-test/main/invisible_field_grant.test | 77 +++++++++++++++++++
>> sql/sp_rcontext.cc | 9 ++-
>> sql/sql_acl.cc | 11 ++-
>> sql/sql_acl.h | 2 +-
>> sql/sql_base.cc | 4 +-
>> 6 files changed, 206 insertions(+), 8 deletions(-)
>>
>> diff --git a/mysql-test/main/invisible_field_grant.result
>> b/mysql-test/main/invisible_field_grant.result
>> new file mode 100644
>> index 0000000..c3ccbb1
>> --- /dev/null
>> +++ b/mysql-test/main/invisible_field_grant.result
>> @@ -0,0 +1,111 @@
>> +set @old_debug= @@debug_dbug;
>> +create user user_1;
>> +show grants for user_1;
>> +Grants for user_1@%
>> +GRANT USAGE ON *.* TO 'user_1'@'%'
>> +# create user
>> +create database d;
>> +use d;
>> +
>> +#System_Invisible
>> +set debug_dbug= "+d,test_pseudo_invisible";
>> +create table t1(a int);
>> +set debug_dbug=@old_debug;
>> +insert into t1 values(1);
>> +select a,invisible from t1;
>> +a invisible
>> +1 9
>> +grant insert(a) on t1 to user_1;
>> +grant update(a) on t1 to user_1;
>> +grant select(a) on t1 to user_1;
>> +grant delete on t1 to user_1;
>> +connect con1, localhost, user_1,,test;
>> +connection con1;
>> +select user();
>> +user()
>> +user_1@localhost
>> +use d;
>> +select * from t1;
>> +a
>> +1
>> +insert into t1 values(2);
>> +select * from t1;
>> +a
>> +1
>> +2
>> +insert into t1(a) values(3);
>> +select * from t1;
>> +a
>> +1
>> +2
>> +3
>> +select invisible,a from t1;
>> +invisible a
>> +9 1
>> +9 2
>> +9 3
>> +delete from t1 where a =1;
>> +update t1 set a=1 where a=3;
>> +select * from t1;
>> +a
>> +2
>> +1
>> +disconnect con1;
>> +
>> +#Cleanup
>> +connection default;
>> +drop table t1;
>> +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1;
>> +
>> +#Completely Invisible
>> +set debug_dbug= "+d,test_completely_invisible";
>> +create table t1(a int);
>> +insert into t1 values(1);
>> +select a,invisible from t1;
>> +a invisible
>> +1 9
>> +set debug_dbug=@old_debug;
>> +grant insert(a) on t1 to user_1;
>> +grant update(a) on t1 to user_1;
>> +grant select(a) on t1 to user_1;
>> +grant delete on t1 to user_1;
>> +connect con1, localhost, user_1,,test;
>> +connection con1;
>> +select user();
>> +user()
>> +user_1@localhost
>> +use d;
>> +select * from t1;
>> +a
>> +1
>> +insert into t1 values(2);
>> +select * from t1;
>> +a
>> +1
>> +2
>> +insert into t1(a) values(3);
>> +select * from t1;
>> +a
>> +1
>> +2
>> +3
>> +select invisible,a from t1;
>> +ERROR 42S22: Unknown column 'invisible' in 'field list'
>> +delete from t1 where a =1;
>> +update t1 set a=1 where a=3;
>> +select * from t1;
>> +a
>> +2
>> +1
>> +disconnect con1;
>> +
>> +#Final Cleanup
>> +connection default;
>> +set debug_dbug= "+d,test_completely_invisible";
>> +select a,invisible from t1;
>> +a invisible
>> +2 9
>> +1 9
>> +drop user user_1;
>> +drop database d;
>> +set @old_debug= @@debug_dbug;
>> diff --git a/mysql-test/main/invisible_field_grant.test
>> b/mysql-test/main/invisible_field_grant.test
>> new file mode 100644
>> index 0000000..0d627e5
>> --- /dev/null
>> +++ b/mysql-test/main/invisible_field_grant.test
>> @@ -0,0 +1,77 @@
>> +--source include/have_debug.inc
>> +##TEST for invisible coloumn level 2
>> +set @old_debug= @@debug_dbug;
>> +create user user_1;
>> +show grants for user_1;
>> +--echo # create user
>> +create database d;
>> +use d;
>> +
>> +--echo
>> +--echo #System_Invisible
>> +set debug_dbug= "+d,test_pseudo_invisible";
>> +create table t1(a int);
>> +set debug_dbug=@old_debug;
>> +insert into t1 values(1);
>> +select a,invisible from t1;
>> +grant insert(a) on t1 to user_1;
>> +grant update(a) on t1 to user_1;
>> +grant select(a) on t1 to user_1;
>> +grant delete on t1 to user_1;
>> +connect (con1, localhost, user_1,,test);
>> +connection con1;
>> +select user();
>> +use d;
>> +select * from t1;
>> +insert into t1 values(2);
>> +select * from t1;
>> +insert into t1(a) values(3);
>> +select * from t1;
>> +select invisible,a from t1;
>> +delete from t1 where a =1;
>> +update t1 set a=1 where a=3;
>> +select * from t1;
>> +disconnect con1;
>> +--source include/wait_until_disconnected.inc
>> +
>> +--echo
>> +--echo #Cleanup
>> +--connection default
>> +drop table t1;
>> +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1;
>> +
>> +--echo
>> +--echo #Completely Invisible
>> +set debug_dbug= "+d,test_completely_invisible";
>> +create table t1(a int);
>> +insert into t1 values(1);
>> +select a,invisible from t1;
>> +set debug_dbug=@old_debug;
>> +grant insert(a) on t1 to user_1;
>> +grant update(a) on t1 to user_1;
>> +grant select(a) on t1 to user_1;
>> +grant delete on t1 to user_1;
>> +connect (con1, localhost, user_1,,test);
>> +connection con1;
>> +select user();
>> +use d;
>> +select * from t1;
>> +insert into t1 values(2);
>> +select * from t1;
>> +insert into t1(a) values(3);
>> +select * from t1;
>> +--error ER_BAD_FIELD_ERROR
>> +select invisible,a from t1;
>> +delete from t1 where a =1;
>> +update t1 set a=1 where a=3;
>> +select * from t1;
>> +disconnect con1;
>> +--source include/wait_until_disconnected.inc
>> +--echo
>> +--echo #Final Cleanup
>> +connection default;
>> +set debug_dbug= "+d,test_completely_invisible";
>> +select a,invisible from t1;
>> +drop user user_1;
>> +drop database d;
>> +set @old_debug= @@debug_dbug;
>> diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc
>> index 2e9ae23..dc103fa 100644
>> --- a/sql/sp_rcontext.cc
>> +++ b/sql/sp_rcontext.cc
>> @@ -196,11 +196,12 @@ bool sp_rcontext::init_var_table(THD *thd,
>> */
>> static inline bool
>> check_column_grant_for_type_ref(THD *thd, TABLE_LIST *table_list,
>> - const char *str, size_t length)
>> + const char *str, size_t length,
>> + Field *fld)
>> {
>> #ifndef NO_EMBEDDED_ACCESS_CHECKS
>> table_list->table->grant.want_privilege= SELECT_ACL;
>> - return check_column_grant_in_table_ref(thd, table_list, str, length);
>> + return check_column_grant_in_table_ref(thd, table_list, str, length,
>> fld);
>> #else
>> return false;
>> #endif
>> @@ -238,7 +239,7 @@ bool Qualified_column_ident::resolve_type_ref(THD
>> *thd, Column_definition *def)
>> {
>> if (!(rc= check_column_grant_for_type_ref(thd, table_list,
>> m_column.str,
>> - m_column.length)))
>> + m_column.length, src)))
>> {
>> *def= Column_definition(thd, src, NULL/*No defaults,no
>> constraints*/);
>> def->flags&= (uint) ~NOT_NULL_FLAG;
>> @@ -302,7 +303,7 @@ bool Table_ident::resolve_table_rowtype_ref(THD *thd,
>> LEX_CSTRING tmp= src[0]->field_name;
>> Spvar_definition *def;
>> if ((rc= check_column_grant_for_type_ref(thd, table_list,
>> - tmp.str, tmp.length)) ||
>> + tmp.str,
>> tmp.length,src[0])) ||
>> (rc= !(src[0]->field_name.str= thd->strmake(tmp.str,
>> tmp.length))) ||
>> (rc= !(def= new (thd->mem_root) Spvar_definition(thd, *src))))
>> break;
>> diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
>> index de4e201..1c154a1 100644
>> --- a/sql/sql_acl.cc
>> +++ b/sql/sql_acl.cc
>> @@ -7775,6 +7775,8 @@ bool check_grant_column(THD *thd, GRANT_INFO *grant,
>> table_ref table reference where to check the field
>> name name of field to check
>> length length of name
>> + fld use fld object to check invisibility when it is
>> + not 0, not_found_field, view_ref_found
>>
>> DESCRIPTION
>> Check the access rights to a column depending on the type of table
>> @@ -7789,13 +7791,17 @@ bool check_grant_column(THD *thd, GRANT_INFO
>> *grant,
>> */
>>
>> bool check_column_grant_in_table_ref(THD *thd, TABLE_LIST * table_ref,
>> - const char *name, size_t length)
>> + const char *name, size_t length,
>> + Field *fld)
>> {
>> GRANT_INFO *grant;
>> const char *db_name;
>> const char *table_name;
>> Security_context *sctx= table_ref->security_ctx ?
>> table_ref->security_ctx : thd->security_ctx;
>> + if (fld && fld != not_found_field && fld != view_ref_found
>> + && fld->invisible >= INVISIBLE_SYSTEM)
>> + return false;
>>
>> if (table_ref->view || table_ref->field_translation)
>> {
>> @@ -7871,6 +7877,9 @@ bool check_grant_all_columns(THD *thd, ulong
>> want_access_arg,
>>
>> for (; !fields->end_of_fields(); fields->next())
>> {
>> + if (fields->field() &&
>> + fields->field()->invisible >= INVISIBLE_SYSTEM)
>> + continue;
>> LEX_CSTRING *field_name= fields->name();
>>
>> if (table_name != fields->get_table_name())
>> diff --git a/sql/sql_acl.h b/sql/sql_acl.h
>> index a608ef0..6da7d4d 100644
>> --- a/sql/sql_acl.h
>> +++ b/sql/sql_acl.h
>> @@ -239,7 +239,7 @@ bool check_grant_column (THD *thd, GRANT_INFO *grant,
>> const char *db_name, const char *table_name,
>> const char *name, size_t length,
>> Security_context *sctx);
>> bool check_column_grant_in_table_ref(THD *thd, TABLE_LIST * table_ref,
>> - const char *name, size_t length);
>> + const char *name, size_t length,
>> Field *fld);
>> bool check_grant_all_columns(THD *thd, ulong want_access,
>> Field_iterator_table_ref *fields);
>> bool check_grant_routine(THD *thd, ulong want_access,
>> diff --git a/sql/sql_base.cc b/sql/sql_base.cc
>> index 0081365..383341c 100644
>> --- a/sql/sql_base.cc
>> +++ b/sql/sql_base.cc
>> @@ -5880,7 +5880,7 @@ find_field_in_table_ref(THD *thd, TABLE_LIST
>> *table_list,
>> #ifndef NO_EMBEDDED_ACCESS_CHECKS
>> /* Check if there are sufficient access rights to the found field. */
>> if (check_privileges &&
>> - check_column_grant_in_table_ref(thd, *actual_table, name,
>> length))
>> + check_column_grant_in_table_ref(thd, *actual_table, name,
>> length, fld))
>> fld= WRONG_GRANT;
>> else
>> #endif
>> @@ -6057,7 +6057,7 @@ find_field_in_tables(THD *thd, Item_ident *item,
>> #ifndef NO_EMBEDDED_ACCESS_CHECKS
>> /* Check if there are sufficient access rights to the found field.
>> */
>> if (found && check_privileges &&
>> - check_column_grant_in_table_ref(thd, table_ref, name, length))
>> + check_column_grant_in_table_ref(thd, table_ref, name, length,
>> found))
>> found= WRONG_GRANT;
>> #endif
>> }
>> _______________________________________________
>> commits mailing list
>> commits(a)mariadb.org
>> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
>
>
--
Regards
Sachin Setiya
Software Engineer at MariaDB
1
0
[Commits] bea915f: MDEV-15965 Invisible columns and LOAD DATA don't work well together:...
by sachin 26 Apr '18
by sachin 26 Apr '18
26 Apr '18
revision-id: bea915f21a2a2771ce7f9e19dcb9d6893f33f7bd (mariadb-10.3.6-47-gbea915f)
parent(s): 326db1a2aaa9b275a1a21a863e8cd2d9fa1b1d5f
author: Sachin Setiya
committer: Sachin Setiya
timestamp: 2018-04-26 16:49:27 +0530
message:
MDEV-15965 Invisible columns and LOAD DATA don't work well together:...
ER_WARN_TOO_FEW_RECORDS
Fix mysql_load iterator to skip invisible fields.
---
mysql-test/main/invisible_field.result | 61 ++++++++++++++++++++++++++++++++++
mysql-test/main/invisible_field.test | 25 ++++++++++++++
sql/sql_load.cc | 2 ++
3 files changed, 88 insertions(+)
diff --git a/mysql-test/main/invisible_field.result b/mysql-test/main/invisible_field.result
index 5cea77f..876a808 100644
--- a/mysql-test/main/invisible_field.result
+++ b/mysql-test/main/invisible_field.result
@@ -556,3 +556,64 @@ INSERT INTO t1 (c,t) VALUES ('foo','2000-01-01 00:00:00');
CREATE TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW SET @a= 1;
INSERT INTO t1 SELECT * FROM t1;
DROP TABLE t1;
+create or replace table t1 (a int, b int invisible);
+insert into t1 values (1),(2);
+select * from t1 into outfile 'f';
+load data infile 'f' into table t1;
+select a,b from t1;
+a b
+1 NULL
+2 NULL
+1 NULL
+2 NULL
+load data infile 'f' into table t1 (a,@v) SET b=@v;
+select a,b from t1;
+a b
+1 NULL
+2 NULL
+1 NULL
+2 NULL
+1 NULL
+2 NULL
+load data infile 'f' into table t1 (a,@v) SET b=a;
+select a,b from t1;
+a b
+1 NULL
+2 NULL
+1 NULL
+2 NULL
+1 NULL
+2 NULL
+1 1
+2 2
+truncate table t1;
+insert into t1(a,b) values (1,1),(2,2);
+select a,b from t1 into outfile 'a';
+load data infile 'a' into table t1(a,b);
+select a,b from t1;
+a b
+1 1
+2 2
+1 1
+2 2
+load data infile 'a' into table t1 (a,@v) SET b=@v;
+select a,b from t1;
+a b
+1 1
+2 2
+1 1
+2 2
+1 1
+2 2
+load data infile 'a' into table t1 (a,@v) SET b=@v+2;
+select a,b from t1;
+a b
+1 1
+2 2
+1 1
+2 2
+1 1
+2 2
+1 3
+2 4
+drop table t1;
diff --git a/mysql-test/main/invisible_field.test b/mysql-test/main/invisible_field.test
index cfe89d7..0e3994a 100644
--- a/mysql-test/main/invisible_field.test
+++ b/mysql-test/main/invisible_field.test
@@ -246,3 +246,28 @@ CREATE TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW SET @a= 1;
INSERT INTO t1 SELECT * FROM t1;
# Cleanup
DROP TABLE t1;
+##LOAD DATA MDEV-15965 Invisible columns and LOAD DATA don't work well
+## together: ER_WARN_TOO_FEW_RECORDS
+create or replace table t1 (a int, b int invisible);
+insert into t1 values (1),(2);
+
+select * from t1 into outfile 'f';
+load data infile 'f' into table t1;
+select a,b from t1;
+load data infile 'f' into table t1 (a,@v) SET b=@v;
+select a,b from t1;
+load data infile 'f' into table t1 (a,@v) SET b=a;
+select a,b from t1;
+truncate table t1;
+
+insert into t1(a,b) values (1,1),(2,2);
+select a,b from t1 into outfile 'a';
+load data infile 'a' into table t1(a,b);
+select a,b from t1;
+load data infile 'a' into table t1 (a,@v) SET b=@v;
+select a,b from t1;
+load data infile 'a' into table t1 (a,@v) SET b=@v+2;
+select a,b from t1;
+
+#cleanup
+drop table t1;
diff --git a/sql/sql_load.cc b/sql/sql_load.cc
index cfa92f1..fbb3926 100644
--- a/sql/sql_load.cc
+++ b/sql/sql_load.cc
@@ -444,6 +444,8 @@ int mysql_load(THD *thd, const sql_exchange *ex, TABLE_LIST *table_list,
field_iterator.set(table_list);
for (; !field_iterator.end_of_fields(); field_iterator.next())
{
+ if (field_iterator.field()->invisible > VISIBLE)
+ continue;
Item *item;
if (!(item= field_iterator.create_item(thd)))
DBUG_RETURN(TRUE);
1
0
revision-id: dc0613edc41590a03a75b5b75e7200c11435ad1a (mariadb-10.1.32-69-gdc0613edc41)
parent(s): 779343235b0c33d6d661387456859d623d7f8781
author: Jan Lindström
committer: Jan Lindström
timestamp: 2018-04-26 12:23:19 +0300
message:
MDEV-15809: Test failure on galera.MW-44
Disable general log while truncating mysql.general_log to
avoid queries to be inserted there.
---
mysql-test/suite/galera/disabled.def | 2 +-
mysql-test/suite/galera/r/MW-44.result | 14 ++++++++++----
mysql-test/suite/galera/t/MW-44.test | 9 +++++++--
3 files changed, 18 insertions(+), 7 deletions(-)
diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def
index 4d83e2ba2ce..4c0a5c7fd9f 100644
--- a/mysql-test/suite/galera/disabled.def
+++ b/mysql-test/suite/galera/disabled.def
@@ -30,4 +30,4 @@ galera.MW-328A : have_deadlocks test not stable
galera_var_retry_autocommit : MDEV-15794 Test failure on galera.galera_var_retry_autocommit
galera_var_auto_inc_control_on : MDEV-15803 Test failure on galera.galera_var_auto_inc_control_on
query_cache : MDEV-15805 Test failure on galera.query_cache
-galera.MW-44 : MDEV-15809 Test failure on galera.MW-44
+
diff --git a/mysql-test/suite/galera/r/MW-44.result b/mysql-test/suite/galera/r/MW-44.result
index 03de2205772..459a61030a4 100644
--- a/mysql-test/suite/galera/r/MW-44.result
+++ b/mysql-test/suite/galera/r/MW-44.result
@@ -1,12 +1,18 @@
-SET @@global.wsrep_replicate_myisam=OFF;
+SET GLOBAL general_log='OFF';
TRUNCATE TABLE mysql.general_log;
SELECT COUNT(*) from mysql.general_log;
COUNT(*)
-1
+0
+SELECT * FROM mysql.general_log;
+event_time user_host thread_id server_id command_type argument
+SET GLOBAL general_log='OFF';
TRUNCATE TABLE mysql.general_log;
SELECT COUNT(*) from mysql.general_log;
COUNT(*)
-1
+0
+SELECT * FROM mysql.general_log;
+event_time user_host thread_id server_id command_type argument
+SET GLOBAL general_log='ON';
SELECT COUNT(*) from mysql.general_log;
COUNT(*)
1
@@ -18,8 +24,8 @@ SET SESSION wsrep_osu_method=TOI;
SELECT COUNT(*) = 2 FROM mysql.general_log WHERE argument LIKE 'CREATE%' OR argument LIKE 'ALTER%';
COUNT(*) = 2
1
+SET GLOBAL general_log='ON';
SELECT COUNT(*) = 0 FROM mysql.general_log WHERE argument NOT LIKE 'SELECT%';
COUNT(*) = 0
1
DROP TABLE t1;
-SET @@global.wsrep_replicate_myisam=Default;
diff --git a/mysql-test/suite/galera/t/MW-44.test b/mysql-test/suite/galera/t/MW-44.test
index b0e58e2bafd..09f444fdff4 100644
--- a/mysql-test/suite/galera/t/MW-44.test
+++ b/mysql-test/suite/galera/t/MW-44.test
@@ -5,16 +5,20 @@
--source include/galera_cluster.inc
--source include/have_innodb.inc
-SET @@global.wsrep_replicate_myisam=OFF;
--connection node_1
+SET GLOBAL general_log='OFF';
TRUNCATE TABLE mysql.general_log;
SELECT COUNT(*) from mysql.general_log;
+SELECT * FROM mysql.general_log;
--connection node_2
+SET GLOBAL general_log='OFF';
TRUNCATE TABLE mysql.general_log;
SELECT COUNT(*) from mysql.general_log;
+SELECT * FROM mysql.general_log;
--connection node_1
+SET GLOBAL general_log='ON';
SELECT COUNT(*) from mysql.general_log;
SET SESSION wsrep_osu_method=TOI;
CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB;
@@ -25,6 +29,7 @@ SET SESSION wsrep_osu_method=TOI;
SELECT COUNT(*) = 2 FROM mysql.general_log WHERE argument LIKE 'CREATE%' OR argument LIKE 'ALTER%';
--connection node_2
+SET GLOBAL general_log='ON';
SELECT COUNT(*) = 0 FROM mysql.general_log WHERE argument NOT LIKE 'SELECT%';
DROP TABLE t1;
-SET @@global.wsrep_replicate_myisam=Default;
+
1
0
[Commits] 326db1a: Mdev-14853 Grant does not work correctly when table contains...
by sachin 26 Apr '18
by sachin 26 Apr '18
26 Apr '18
revision-id: 326db1a2aaa9b275a1a21a863e8cd2d9fa1b1d5f (mariadb-10.3.6-46-g326db1a)
parent(s): 9477a2a9ba17c0db362e2bb39d5048e369096f39
author: Sachin Setiya
committer: Sachin Setiya
timestamp: 2018-04-26 12:47:25 +0530
message:
Mdev-14853 Grant does not work correctly when table contains...
SYSTEM_INVISIBLE or COMPLETELY_INVISIBLE
This commit does multiple things to solve this mdev
1st add field into the parameter of check_column_grant_in_table_ref, so that
we can find out field invisibility.
2nd If field->invisible >= INVISIBLE_SYSTEM skip access check and simple
grant access.
---
mysql-test/main/invisible_field_grant.result | 111 +++++++++++++++++++++++++++
mysql-test/main/invisible_field_grant.test | 77 +++++++++++++++++++
sql/sp_rcontext.cc | 9 ++-
sql/sql_acl.cc | 11 ++-
sql/sql_acl.h | 2 +-
sql/sql_base.cc | 4 +-
6 files changed, 206 insertions(+), 8 deletions(-)
diff --git a/mysql-test/main/invisible_field_grant.result b/mysql-test/main/invisible_field_grant.result
new file mode 100644
index 0000000..c3ccbb1
--- /dev/null
+++ b/mysql-test/main/invisible_field_grant.result
@@ -0,0 +1,111 @@
+set @old_debug= @@debug_dbug;
+create user user_1;
+show grants for user_1;
+Grants for user_1@%
+GRANT USAGE ON *.* TO 'user_1'@'%'
+# create user
+create database d;
+use d;
+
+#System_Invisible
+set debug_dbug= "+d,test_pseudo_invisible";
+create table t1(a int);
+set debug_dbug=@old_debug;
+insert into t1 values(1);
+select a,invisible from t1;
+a invisible
+1 9
+grant insert(a) on t1 to user_1;
+grant update(a) on t1 to user_1;
+grant select(a) on t1 to user_1;
+grant delete on t1 to user_1;
+connect con1, localhost, user_1,,test;
+connection con1;
+select user();
+user()
+user_1@localhost
+use d;
+select * from t1;
+a
+1
+insert into t1 values(2);
+select * from t1;
+a
+1
+2
+insert into t1(a) values(3);
+select * from t1;
+a
+1
+2
+3
+select invisible,a from t1;
+invisible a
+9 1
+9 2
+9 3
+delete from t1 where a =1;
+update t1 set a=1 where a=3;
+select * from t1;
+a
+2
+1
+disconnect con1;
+
+#Cleanup
+connection default;
+drop table t1;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1;
+
+#Completely Invisible
+set debug_dbug= "+d,test_completely_invisible";
+create table t1(a int);
+insert into t1 values(1);
+select a,invisible from t1;
+a invisible
+1 9
+set debug_dbug=@old_debug;
+grant insert(a) on t1 to user_1;
+grant update(a) on t1 to user_1;
+grant select(a) on t1 to user_1;
+grant delete on t1 to user_1;
+connect con1, localhost, user_1,,test;
+connection con1;
+select user();
+user()
+user_1@localhost
+use d;
+select * from t1;
+a
+1
+insert into t1 values(2);
+select * from t1;
+a
+1
+2
+insert into t1(a) values(3);
+select * from t1;
+a
+1
+2
+3
+select invisible,a from t1;
+ERROR 42S22: Unknown column 'invisible' in 'field list'
+delete from t1 where a =1;
+update t1 set a=1 where a=3;
+select * from t1;
+a
+2
+1
+disconnect con1;
+
+#Final Cleanup
+connection default;
+set debug_dbug= "+d,test_completely_invisible";
+select a,invisible from t1;
+a invisible
+2 9
+1 9
+drop user user_1;
+drop database d;
+set @old_debug= @@debug_dbug;
diff --git a/mysql-test/main/invisible_field_grant.test b/mysql-test/main/invisible_field_grant.test
new file mode 100644
index 0000000..0d627e5
--- /dev/null
+++ b/mysql-test/main/invisible_field_grant.test
@@ -0,0 +1,77 @@
+--source include/have_debug.inc
+##TEST for invisible coloumn level 2
+set @old_debug= @@debug_dbug;
+create user user_1;
+show grants for user_1;
+--echo # create user
+create database d;
+use d;
+
+--echo
+--echo #System_Invisible
+set debug_dbug= "+d,test_pseudo_invisible";
+create table t1(a int);
+set debug_dbug=@old_debug;
+insert into t1 values(1);
+select a,invisible from t1;
+grant insert(a) on t1 to user_1;
+grant update(a) on t1 to user_1;
+grant select(a) on t1 to user_1;
+grant delete on t1 to user_1;
+connect (con1, localhost, user_1,,test);
+connection con1;
+select user();
+use d;
+select * from t1;
+insert into t1 values(2);
+select * from t1;
+insert into t1(a) values(3);
+select * from t1;
+select invisible,a from t1;
+delete from t1 where a =1;
+update t1 set a=1 where a=3;
+select * from t1;
+disconnect con1;
+--source include/wait_until_disconnected.inc
+
+--echo
+--echo #Cleanup
+--connection default
+drop table t1;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1;
+
+--echo
+--echo #Completely Invisible
+set debug_dbug= "+d,test_completely_invisible";
+create table t1(a int);
+insert into t1 values(1);
+select a,invisible from t1;
+set debug_dbug=@old_debug;
+grant insert(a) on t1 to user_1;
+grant update(a) on t1 to user_1;
+grant select(a) on t1 to user_1;
+grant delete on t1 to user_1;
+connect (con1, localhost, user_1,,test);
+connection con1;
+select user();
+use d;
+select * from t1;
+insert into t1 values(2);
+select * from t1;
+insert into t1(a) values(3);
+select * from t1;
+--error ER_BAD_FIELD_ERROR
+select invisible,a from t1;
+delete from t1 where a =1;
+update t1 set a=1 where a=3;
+select * from t1;
+disconnect con1;
+--source include/wait_until_disconnected.inc
+--echo
+--echo #Final Cleanup
+connection default;
+set debug_dbug= "+d,test_completely_invisible";
+select a,invisible from t1;
+drop user user_1;
+drop database d;
+set @old_debug= @@debug_dbug;
diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc
index 2e9ae23..dc103fa 100644
--- a/sql/sp_rcontext.cc
+++ b/sql/sp_rcontext.cc
@@ -196,11 +196,12 @@ bool sp_rcontext::init_var_table(THD *thd,
*/
static inline bool
check_column_grant_for_type_ref(THD *thd, TABLE_LIST *table_list,
- const char *str, size_t length)
+ const char *str, size_t length,
+ Field *fld)
{
#ifndef NO_EMBEDDED_ACCESS_CHECKS
table_list->table->grant.want_privilege= SELECT_ACL;
- return check_column_grant_in_table_ref(thd, table_list, str, length);
+ return check_column_grant_in_table_ref(thd, table_list, str, length, fld);
#else
return false;
#endif
@@ -238,7 +239,7 @@ bool Qualified_column_ident::resolve_type_ref(THD *thd, Column_definition *def)
{
if (!(rc= check_column_grant_for_type_ref(thd, table_list,
m_column.str,
- m_column.length)))
+ m_column.length, src)))
{
*def= Column_definition(thd, src, NULL/*No defaults,no constraints*/);
def->flags&= (uint) ~NOT_NULL_FLAG;
@@ -302,7 +303,7 @@ bool Table_ident::resolve_table_rowtype_ref(THD *thd,
LEX_CSTRING tmp= src[0]->field_name;
Spvar_definition *def;
if ((rc= check_column_grant_for_type_ref(thd, table_list,
- tmp.str, tmp.length)) ||
+ tmp.str, tmp.length,src[0])) ||
(rc= !(src[0]->field_name.str= thd->strmake(tmp.str, tmp.length))) ||
(rc= !(def= new (thd->mem_root) Spvar_definition(thd, *src))))
break;
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index de4e201..1c154a1 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -7775,6 +7775,8 @@ bool check_grant_column(THD *thd, GRANT_INFO *grant,
table_ref table reference where to check the field
name name of field to check
length length of name
+ fld use fld object to check invisibility when it is
+ not 0, not_found_field, view_ref_found
DESCRIPTION
Check the access rights to a column depending on the type of table
@@ -7789,13 +7791,17 @@ bool check_grant_column(THD *thd, GRANT_INFO *grant,
*/
bool check_column_grant_in_table_ref(THD *thd, TABLE_LIST * table_ref,
- const char *name, size_t length)
+ const char *name, size_t length,
+ Field *fld)
{
GRANT_INFO *grant;
const char *db_name;
const char *table_name;
Security_context *sctx= table_ref->security_ctx ?
table_ref->security_ctx : thd->security_ctx;
+ if (fld && fld != not_found_field && fld != view_ref_found
+ && fld->invisible >= INVISIBLE_SYSTEM)
+ return false;
if (table_ref->view || table_ref->field_translation)
{
@@ -7871,6 +7877,9 @@ bool check_grant_all_columns(THD *thd, ulong want_access_arg,
for (; !fields->end_of_fields(); fields->next())
{
+ if (fields->field() &&
+ fields->field()->invisible >= INVISIBLE_SYSTEM)
+ continue;
LEX_CSTRING *field_name= fields->name();
if (table_name != fields->get_table_name())
diff --git a/sql/sql_acl.h b/sql/sql_acl.h
index a608ef0..6da7d4d 100644
--- a/sql/sql_acl.h
+++ b/sql/sql_acl.h
@@ -239,7 +239,7 @@ bool check_grant_column (THD *thd, GRANT_INFO *grant,
const char *db_name, const char *table_name,
const char *name, size_t length, Security_context *sctx);
bool check_column_grant_in_table_ref(THD *thd, TABLE_LIST * table_ref,
- const char *name, size_t length);
+ const char *name, size_t length, Field *fld);
bool check_grant_all_columns(THD *thd, ulong want_access,
Field_iterator_table_ref *fields);
bool check_grant_routine(THD *thd, ulong want_access,
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 0081365..383341c 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5880,7 +5880,7 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list,
#ifndef NO_EMBEDDED_ACCESS_CHECKS
/* Check if there are sufficient access rights to the found field. */
if (check_privileges &&
- check_column_grant_in_table_ref(thd, *actual_table, name, length))
+ check_column_grant_in_table_ref(thd, *actual_table, name, length, fld))
fld= WRONG_GRANT;
else
#endif
@@ -6057,7 +6057,7 @@ find_field_in_tables(THD *thd, Item_ident *item,
#ifndef NO_EMBEDDED_ACCESS_CHECKS
/* Check if there are sufficient access rights to the found field. */
if (found && check_privileges &&
- check_column_grant_in_table_ref(thd, table_ref, name, length))
+ check_column_grant_in_table_ref(thd, table_ref, name, length, found))
found= WRONG_GRANT;
#endif
}
1
0
revision-id: 5bba69f816129dff8f85e7e87c91b90e2d34b8e1 (mariadb-10.3.6-49-g5bba69f)
parent(s): b4ee699a89ccf4f3cf52a18236e821f0d8466e8d
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-04-25 18:22:56 -0700
message:
Fixed a compiler error
---
tests/mysql_client_test.c | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index 0140a63..fd33648 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -20295,9 +20295,10 @@ static void test_bulk_autoinc()
static void print_metadata(MYSQL_RES *rs_metadata, int num_fields)
{
+ int i;
MYSQL_FIELD *fields= mysql_fetch_fields(rs_metadata);
- for (int i = 0; i < num_fields; ++i)
+ for (i = 0; i < num_fields; ++i)
{
mct_log(" - %d: name: '%s'/'%s'; table: '%s'/'%s'; "
"db: '%s'; catalog: '%s'; length: %d; max_length: %d; "
1
0
[Commits] ddb1e1d009a: MDEV-15732: Assertion `next_free_value % real_increment == offset && next_free_value >= reserved_until' failed in sequence_definition::adjust_values upon SETVAL for sequence with INCREMENT 0
by Oleksandr Byelkin 25 Apr '18
by Oleksandr Byelkin 25 Apr '18
25 Apr '18
revision-id: ddb1e1d009aaf0272a46ffdd9977bcde32c89305 (mariadb-10.3.6-49-gddb1e1d009a)
parent(s): b4ee699a89ccf4f3cf52a18236e821f0d8466e8d
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-04-25 21:32:47 +0200
message:
MDEV-15732: Assertion `next_free_value % real_increment == offset && next_free_value >= reserved_until' failed in sequence_definition::adjust_values upon SETVAL for sequence with INCREMENT 0
there was 2 problems with "next_free_value >= reserved_until" condition:
1) it should be <=
2) SEQUENCE::set_value handle next_free_value & reserved_until after adjust_values() call, so it is incorect to put assert on it in adjust_values()
---
mysql-test/suite/sql_sequence/setval.result | 15 +++++++++++++++
mysql-test/suite/sql_sequence/setval.test | 17 +++++++++++++++++
sql/sql_sequence.cc | 3 +--
3 files changed, 33 insertions(+), 2 deletions(-)
diff --git a/mysql-test/suite/sql_sequence/setval.result b/mysql-test/suite/sql_sequence/setval.result
index b7cda27cf96..504d460fea5 100644
--- a/mysql-test/suite/sql_sequence/setval.result
+++ b/mysql-test/suite/sql_sequence/setval.result
@@ -254,3 +254,18 @@ def SETVAL(s1,10) 8 20 2 Y 32896 0 63
SETVAL(s1,10)
10
DROP SEQUENCE s1;
+#
+# MDEV-15732: Assertion `next_free_value % real_increment == offset &&
+# next_free_value >= reserved_until' failed in
+# sequence_definition::adjust_values upon SETVAL for sequence with
+# INCREMENT 0
+#
+CREATE SEQUENCE s INCREMENT 0;
+SELECT NEXTVAL(s);
+NEXTVAL(s)
+1
+SELECT SETVAL(s, 10);
+SETVAL(s, 10)
+10
+DROP SEQUENCE s;
+# End of 10.3 tests
diff --git a/mysql-test/suite/sql_sequence/setval.test b/mysql-test/suite/sql_sequence/setval.test
index 8f8059fdcae..1993bdbe077 100644
--- a/mysql-test/suite/sql_sequence/setval.test
+++ b/mysql-test/suite/sql_sequence/setval.test
@@ -137,3 +137,20 @@ SELECT SETVAL(s1,10);
DROP SEQUENCE s1;
--enable_ps_protocol
--disable_metadata
+
+--echo #
+--echo # MDEV-15732: Assertion `next_free_value % real_increment == offset &&
+--echo # next_free_value >= reserved_until' failed in
+--echo # sequence_definition::adjust_values upon SETVAL for sequence with
+--echo # INCREMENT 0
+--echo #
+
+CREATE SEQUENCE s INCREMENT 0;
+SELECT NEXTVAL(s);
+SELECT SETVAL(s, 10);
+
+# Cleanup
+DROP SEQUENCE s;
+
+
+--echo # End of 10.3 tests
diff --git a/sql/sql_sequence.cc b/sql/sql_sequence.cc
index 18f0028908f..4d0f15386e7 100644
--- a/sql/sql_sequence.cc
+++ b/sql/sql_sequence.cc
@@ -560,8 +560,7 @@ void sequence_definition::adjust_values(longlong next_value)
else
{
next_free_value+= to_add;
- DBUG_ASSERT(next_free_value % real_increment == offset &&
- next_free_value >= reserved_until);
+ DBUG_ASSERT(next_free_value % real_increment == offset);
}
}
}
1
0
revision-id: 705878cfe770ffd62b743e9e9f5eb70442e0d54c (mariadb-10.3.6-49-g705878cfe77)
parent(s): a50fb913cf24b3234f2e9de7b0d8740031ac4985
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-04-25 17:16:17 +0200
message:
postreview fix
---
sql/sql_class.cc | 1 -
1 file changed, 1 deletion(-)
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 0ec95e11829..09a2d205d85 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -2664,7 +2664,6 @@ void THD::prepare_explain_fields(select_result *result,
uint8 explain_flags,
bool is_analyze)
{
- ;
if (lex->explain_json)
make_explain_json_field_list(*field_list, is_analyze);
else
1
0
[Commits] 48d155b: MDEV-12387 Push conditions into materialized subqueries
by galina.shalygina＠mariadb.com 25 Apr '18
by galina.shalygina＠mariadb.com 25 Apr '18
25 Apr '18
revision-id: 48d155b5126807c31223cb25f7df473227c1f601 (mariadb-10.3.4-61-g48d155b)
parent(s): ccae16dfc128bfb95421c98a7ce331c38fc747ce
author: Galina Shalygina
committer: Galina Shalygina
timestamp: 2018-04-25 16:57:15 +0200
message:
MDEV-12387 Push conditions into materialized subqueries
Comments changed, tests changed
---
mysql-test/r/in_subq_cond_pushdown.result | 980 +++++++++++++++---------------
mysql-test/t/in_subq_cond_pushdown.test | 182 +++---
sql/opt_subselect.cc | 24 +-
3 files changed, 595 insertions(+), 591 deletions(-)
diff --git a/mysql-test/r/in_subq_cond_pushdown.result b/mysql-test/r/in_subq_cond_pushdown.result
index 97abd21..6676d3b 100644
--- a/mysql-test/r/in_subq_cond_pushdown.result
+++ b/mysql-test/r/in_subq_cond_pushdown.result
@@ -1,6 +1,6 @@
-create table t1 (a int, b int, c int, d int);
-create table t2 (e int, f int, g int);
-create table t3 (x int, y int);
+create table t1 (t1_a int, t1_b int, t1_c int, t1_d int);
+create table t2 (t2_e int, t2_f int, t2_g int);
+create table t3 (t3_x int, t3_y int);
insert into t1 values
(1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4),
(4,2,24,4), (3,2,23,1), (1,2,40,2), (3,4,17,2),
@@ -14,31 +14,31 @@ insert into t3 values
(1,35), (3,23), (3,17), (2,15);
create view v1 as
(
-select * from t3 where x<=3
+select t3_x as v1_x, t3_y as v1_y from t3 where t3_x<=3
);
# conjunctive subformula : pushing into HAVING
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where c<25 and
-(a,c) in (select e,max(g) from t2 where e<5 group by e);
-a b c d
+where t1_c<25 and
+(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
+t1_a t1_b t1_c t1_d
4 2 24 4
3 2 23 1
select * from t1
-where c<25 and
-(a,c) in (select e,max(g) from t2 where e<5 group by e);
-a b c d
+where t1_c<25 and
+(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
+t1_a t1_b t1_c t1_d
4 2 24 4
3 2 23 1
explain select * from t1
-where c<25 and
-(a,c) in (select e,max(g) from t2 where e<5 group by e);
+where t1_c<25 and
+(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1
-where c<25 and
-(a,c) in (select e,max(g) from t2 where e<5 group by e);
+where t1_c<25 and
+(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
EXPLAIN
{
"query_block": {
@@ -48,7 +48,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t1.c < 25 and t1.a is not null and t1.c is not null"
+ "attached_condition": "t1.t1_c < 25 and t1.t1_a is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -56,22 +56,22 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "8",
- "used_key_parts": ["e", "max(g)"],
- "ref": ["test.t1.a", "test.t1.c"],
+ "used_key_parts": ["t2_e", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
"unique": 1,
"query_block": {
"select_id": 2,
- "having_condition": "`max(g)` < 25",
+ "having_condition": "`max(t2_g)` < 25",
"temporary_table": {
"table": {
"table_name": "t2",
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5"
+ "attached_condition": "t2.t2_e < 5"
}
}
}
@@ -81,51 +81,51 @@ EXPLAIN
}
# extracted and formula : pushing into HAVING
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where c>55 and b<4 and
-(a,b,c) in
+where t1_c>55 and t1_b<4 and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
2 3 70 3
select * from t1
-where c>55 and b<4 and
-(a,b,c) in
+where t1_c>55 and t1_b<4 and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
2 3 70 3
explain select * from t1
-where c>55 and b<4 and
-(a,b,c) in
+where t1_c>55 and t1_b<4 and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1
-where c>55 and b<4 and
-(a,b,c) in
+where t1_c>55 and t1_b<4 and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
EXPLAIN
@@ -137,7 +137,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t1.c > 55 and t1.b < 4 and t1.a is not null and t1.b is not null and t1.c is not null"
+ "attached_condition": "t1.t1_c > 55 and t1.t1_b < 4 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -145,22 +145,22 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "12",
- "used_key_parts": ["e", "f", "max(g)"],
- "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+ "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
"unique": 1,
"query_block": {
"select_id": 2,
- "having_condition": "`max(g)` > 55 and t2.f < 4",
+ "having_condition": "`max(t2_g)` > 55 and t2.t2_f < 4",
"temporary_table": {
"table": {
"table_name": "t2",
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5"
+ "attached_condition": "t2.t2_e < 5"
}
}
}
@@ -170,53 +170,53 @@ EXPLAIN
}
# extracted or formula : pushing into HAVING
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where (c>60 or c<25) and
-(a,b,c) in
+where (t1_c>60 or t1_c<25) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
4 2 24 4
2 3 70 3
select * from t1
-where (c>60 or c<25) and
-(a,b,c) in
+where (t1_c>60 or t1_c<25) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
4 2 24 4
2 3 70 3
explain select * from t1
-where (c>60 or c<25) and
-(a,b,c) in
+where (t1_c>60 or t1_c<25) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1
-where (c>60 or c<25) and
-(a,b,c) in
+where (t1_c>60 or t1_c<25) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
EXPLAIN
@@ -228,7 +228,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "(t1.c > 60 or t1.c < 25) and t1.a is not null and t1.b is not null and t1.c is not null"
+ "attached_condition": "(t1.t1_c > 60 or t1.t1_c < 25) and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -236,22 +236,22 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "12",
- "used_key_parts": ["e", "f", "max(g)"],
- "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+ "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
"unique": 1,
"query_block": {
"select_id": 2,
- "having_condition": "`max(g)` > 60 or `max(g)` < 25",
+ "having_condition": "`max(t2_g)` > 60 or `max(t2_g)` < 25",
"temporary_table": {
"table": {
"table_name": "t2",
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5"
+ "attached_condition": "t2.t2_e < 5"
}
}
}
@@ -261,51 +261,51 @@ EXPLAIN
}
# extracted and-or formula : pushing into HAVING
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((c>60 or c<25) and b>2) and
-(a,b,c) in
+where ((t1_c>60 or t1_c<25) and t1_b>2) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
2 3 70 3
select * from t1
-where ((c>60 or c<25) and b>2) and
-(a,b,c) in
+where ((t1_c>60 or t1_c<25) and t1_b>2) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
2 3 70 3
explain select * from t1
-where ((c>60 or c<25) and b>2) and
-(a,b,c) in
+where ((t1_c>60 or t1_c<25) and t1_b>2) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1
-where ((c>60 or c<25) and b>2) and
-(a,b,c) in
+where ((t1_c>60 or t1_c<25) and t1_b>2) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
EXPLAIN
@@ -317,7 +317,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "(t1.c > 60 or t1.c < 25) and t1.b > 2 and t1.a is not null and t1.b is not null and t1.c is not null"
+ "attached_condition": "(t1.t1_c > 60 or t1.t1_c < 25) and t1.t1_b > 2 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -325,22 +325,22 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "12",
- "used_key_parts": ["e", "f", "max(g)"],
- "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+ "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
"unique": 1,
"query_block": {
"select_id": 2,
- "having_condition": "(`max(g)` > 60 or `max(g)` < 25) and t2.f > 2",
+ "having_condition": "(`max(t2_g)` > 60 or `max(t2_g)` < 25) and t2.t2_f > 2",
"temporary_table": {
"table": {
"table_name": "t2",
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5"
+ "attached_condition": "t2.t2_e < 5"
}
}
}
@@ -350,49 +350,53 @@ EXPLAIN
}
# conjunctive subformula : pushing into HAVING
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((a<2 or d>2) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_d>3) and t1_b>1) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
+4 2 24 4
+1 2 40 2
select * from t1
-where ((a<2 or d>2) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_d>3) and t1_b>1) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
+4 2 24 4
+1 2 40 2
explain select * from t1
-where ((a<2 or d>2) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_d>3) and t1_b>1) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1
-where ((a<2 or d>2) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_d>3) and t1_b>1) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
EXPLAIN
@@ -404,7 +408,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "(t1.a < 2 or t1.d > 2) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null"
+ "attached_condition": "(t1.t1_a < 2 or t1.t1_d > 3) and t1.t1_b > 1 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -412,22 +416,22 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "12",
- "used_key_parts": ["e", "f", "max(g)"],
- "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+ "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
"unique": 1,
"query_block": {
"select_id": 2,
- "having_condition": "t2.f > 3",
+ "having_condition": "t2.t2_f > 1",
"temporary_table": {
"table": {
"table_name": "t2",
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5"
+ "attached_condition": "t2.t2_e < 5"
}
}
}
@@ -437,51 +441,51 @@ EXPLAIN
}
# using view in subquery definition : pushing into HAVING
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where c>20 and
-(a,c) in
+where t1_c>20 and
+(t1_a,t1_c) in
(
-select x,max(y)
+select v1_x,max(v1_y)
from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
)
;
-a b c d
+t1_a t1_b t1_c t1_d
3 2 23 1
select * from t1
-where c>20 and
-(a,c) in
+where t1_c>20 and
+(t1_a,t1_c) in
(
-select x,max(y)
+select v1_x,max(v1_y)
from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
)
;
-a b c d
+t1_a t1_b t1_c t1_d
3 2 23 1
explain select * from t1
-where c>20 and
-(a,c) in
+where t1_c>20 and
+(t1_a,t1_c) in
(
-select x,max(y)
+select v1_x,max(v1_y)
from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 Using where; Using temporary
explain format=json select * from t1
-where c>20 and
-(a,c) in
+where t1_c>20 and
+(t1_a,t1_c) in
(
-select x,max(y)
+select v1_x,max(v1_y)
from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
)
;
EXPLAIN
@@ -493,7 +497,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t1.c > 20 and t1.a is not null and t1.c is not null"
+ "attached_condition": "t1.t1_c > 20 and t1.t1_a is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -501,22 +505,22 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "8",
- "used_key_parts": ["x", "max(y)"],
- "ref": ["test.t1.a", "test.t1.c"],
+ "used_key_parts": ["v1_x", "max(v1_y)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
"unique": 1,
"query_block": {
"select_id": 2,
- "having_condition": "`max(y)` > 20",
+ "having_condition": "`max(v1_y)` > 20",
"temporary_table": {
"table": {
"table_name": "t3",
"access_type": "ALL",
"rows": 8,
"filtered": 100,
- "attached_condition": "t3.x > 1 and t3.x <= 3"
+ "attached_condition": "t3.t3_x > 1 and t3.t3_x <= 3"
}
}
}
@@ -526,52 +530,52 @@ EXPLAIN
}
# using equality : pushing into WHERE
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1,v1
-where c>20 and c=y and
-(a,c) in
+where t1_c>20 and t1_c=v1_y and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d x y
+t1_a t1_b t1_c t1_d v1_x v1_y
3 2 23 1 3 23
select * from t1,v1
-where c>20 and c=y and
-(a,c) in
+where t1_c>20 and t1_c=v1_y and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d x y
+t1_a t1_b t1_c t1_d v1_x v1_y
3 2 23 1 3 23
explain select * from t1,v1
-where c>20 and c=y and
-(a,c) in
+where t1_c>20 and t1_c=v1_y and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t3.y 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t3.t3_y 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1,v1
-where c>20 and c=y and
-(a,c) in
+where t1_c>20 and t1_c=v1_y and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
EXPLAIN
@@ -583,7 +587,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 8,
"filtered": 100,
- "attached_condition": "t3.y > 20 and t3.x <= 3 and t3.y is not null"
+ "attached_condition": "t3.t3_y > 20 and t3.t3_x <= 3 and t3.t3_y is not null"
},
"block-nl-join": {
"table": {
@@ -595,7 +599,7 @@ EXPLAIN
"buffer_type": "flat",
"buffer_size": "256Kb",
"join_type": "BNL",
- "attached_condition": "t1.c = t3.y and t1.a is not null"
+ "attached_condition": "t1.t1_c = t3.t3_y and t1.t1_a is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -603,22 +607,22 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "8",
- "used_key_parts": ["e", "max(g)"],
- "ref": ["test.t1.a", "test.t3.y"],
+ "used_key_parts": ["t2_e", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t3.t3_y"],
"rows": 1,
"filtered": 100,
"materialized": {
"unique": 1,
"query_block": {
"select_id": 2,
- "having_condition": "`max(g)` > 20",
+ "having_condition": "`max(t2_g)` > 20",
"temporary_table": {
"table": {
"table_name": "t2",
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5"
+ "attached_condition": "t2.t2_e < 5"
}
}
}
@@ -628,53 +632,53 @@ EXPLAIN
}
# conjunctive subformula : pushing into WHERE
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where a<2 and
-(a,c) in
+where t1_a<2 and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 3 40 1
1 2 40 2
select * from t1
-where a<2 and
-(a,c) in
+where t1_a<2 and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 3 40 1
1 2 40 2
explain select * from t1
-where a<2 and
-(a,c) in
+where t1_a<2 and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1
-where a<2 and
-(a,c) in
+where t1_a<2 and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
EXPLAIN
@@ -686,7 +690,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t1.a < 2 and t1.a is not null and t1.c is not null"
+ "attached_condition": "t1.t1_a < 2 and t1.t1_a is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -694,8 +698,8 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "8",
- "used_key_parts": ["e", "max(g)"],
- "ref": ["test.t1.a", "test.t1.c"],
+ "used_key_parts": ["t2_e", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
@@ -708,7 +712,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5 and t2.e < 2"
+ "attached_condition": "t2.t2_e < 5 and t2.t2_e < 2"
}
}
}
@@ -718,53 +722,53 @@ EXPLAIN
}
# extracted and formula : pushing into WHERE
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where a>2 and a<5 and
-(a,c) in
+where t1_a>2 and t1_a<5 and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
4 2 24 4
3 2 23 1
select * from t1
-where a>2 and a<5 and
-(a,c) in
+where t1_a>2 and t1_a<5 and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
4 2 24 4
3 2 23 1
explain select * from t1
-where a>2 and a<5 and
-(a,c) in
+where t1_a>2 and t1_a<5 and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1
-where a>2 and a<5 and
-(a,c) in
+where t1_a>2 and t1_a<5 and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
EXPLAIN
@@ -776,7 +780,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t1.a > 2 and t1.a < 5 and t1.a is not null and t1.c is not null"
+ "attached_condition": "t1.t1_a > 2 and t1.t1_a < 5 and t1.t1_a is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -784,8 +788,8 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "8",
- "used_key_parts": ["e", "max(g)"],
- "ref": ["test.t1.a", "test.t1.c"],
+ "used_key_parts": ["t2_e", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
@@ -798,7 +802,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5 and t2.e > 2 and t2.e < 5"
+ "attached_condition": "t2.t2_e < 5 and t2.t2_e > 2 and t2.t2_e < 5"
}
}
}
@@ -808,55 +812,55 @@ EXPLAIN
}
# extracted or formula : pushing into WHERE
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where (a<2 or a>=4) and
-(a,c) in
+where (t1_a<2 or t1_a>=4) and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 3 40 1
4 2 24 4
1 2 40 2
select * from t1
-where (a<2 or a>=4) and
-(a,c) in
+where (t1_a<2 or t1_a>=4) and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 3 40 1
4 2 24 4
1 2 40 2
explain select * from t1
-where (a<2 or a>=4) and
-(a,c) in
+where (t1_a<2 or t1_a>=4) and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1
-where (a<2 or a>=4) and
-(a,c) in
+where (t1_a<2 or t1_a>=4) and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
EXPLAIN
@@ -868,7 +872,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "(t1.a < 2 or t1.a >= 4) and t1.a is not null and t1.c is not null"
+ "attached_condition": "(t1.t1_a < 2 or t1.t1_a >= 4) and t1.t1_a is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -876,8 +880,8 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "8",
- "used_key_parts": ["e", "max(g)"],
- "ref": ["test.t1.a", "test.t1.c"],
+ "used_key_parts": ["t2_e", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
@@ -890,7 +894,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e >= 4)"
+ "attached_condition": "t2.t2_e < 5 and (t2.t2_e < 2 or t2.t2_e >= 4)"
}
}
}
@@ -900,51 +904,51 @@ EXPLAIN
}
# extracted and-or formula : pushing into WHERE
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 4 35 3
select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 4 35 3
explain select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
)
;
EXPLAIN
@@ -956,7 +960,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "(t1.a < 2 or t1.a = 5) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null"
+ "attached_condition": "(t1.t1_a < 2 or t1.t1_a = 5) and t1.t1_b > 3 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -964,8 +968,8 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "12",
- "used_key_parts": ["e", "f", "max(g)"],
- "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+ "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
@@ -978,7 +982,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3"
+ "attached_condition": "t2.t2_e < 5 and (t2.t2_e < 2 or t2.t2_e = 5) and t2.t2_f > 3"
}
}
}
@@ -988,51 +992,51 @@ EXPLAIN
}
# extracted and-or formula : pushing into WHERE
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 4 35 3
select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 4 35 3
explain select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
)
;
EXPLAIN
@@ -1044,7 +1048,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "(t1.a < 2 or t1.a = 5) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null"
+ "attached_condition": "(t1.t1_a < 2 or t1.t1_a = 5) and t1.t1_b > 3 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -1052,8 +1056,8 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "12",
- "used_key_parts": ["e", "f", "max(g)"],
- "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+ "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
@@ -1066,7 +1070,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3"
+ "attached_condition": "t2.t2_e < 5 and (t2.t2_e < 2 or t2.t2_e = 5) and t2.t2_f > 3"
}
}
}
@@ -1076,51 +1080,51 @@ EXPLAIN
}
# conjunctive subformula : pushing into WHERE
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((b<3 or d>2) and a<2) and
-(a,b,c) in
+where ((t1_b<3 or t1_d>2) and t1_a<2) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 2 40 2
select * from t1
-where ((b<3 or d>2) and a<2) and
-(a,b,c) in
+where ((t1_b<3 or t1_d>2) and t1_a<2) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 2 40 2
explain select * from t1
-where ((b<3 or d>2) and a<2) and
-(a,b,c) in
+where ((t1_b<3 or t1_d>2) and t1_a<2) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1
-where ((b<3 or d>2) and a<2) and
-(a,b,c) in
+where ((t1_b<3 or t1_d>2) and t1_a<2) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
EXPLAIN
@@ -1132,7 +1136,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "(t1.b < 3 or t1.d > 2) and t1.a < 2 and t1.a is not null and t1.b is not null and t1.c is not null"
+ "attached_condition": "(t1.t1_b < 3 or t1.t1_d > 2) and t1.t1_a < 2 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -1140,8 +1144,8 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "12",
- "used_key_parts": ["e", "f", "max(g)"],
- "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+ "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
@@ -1154,7 +1158,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5 and t2.e < 2"
+ "attached_condition": "t2.t2_e < 5 and t2.t2_e < 2"
}
}
}
@@ -1164,51 +1168,51 @@ EXPLAIN
}
# using equalities : pushing into WHERE
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where d=1 and a=d and
-(a,c) in
+where t1_d=1 and t1_a=t1_d and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 3 40 1
select * from t1
-where d=1 and a=d and
-(a,c) in
+where t1_d=1 and t1_a=t1_d and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 3 40 1
explain select * from t1
-where d=1 and a=d and
-(a,c) in
+where t1_d=1 and t1_a=t1_d and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 const,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 const,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where
explain format=json select * from t1
-where d=1 and a=d and
-(a,c) in
+where t1_d=1 and t1_a=t1_d and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
EXPLAIN
@@ -1220,7 +1224,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t1.a = 1 and t1.d = 1 and t1.c is not null"
+ "attached_condition": "t1.t1_a = 1 and t1.t1_d = 1 and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -1228,8 +1232,8 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "8",
- "used_key_parts": ["e", "max(g)"],
- "ref": ["const", "test.t1.c"],
+ "used_key_parts": ["t2_e", "max(t2_g)"],
+ "ref": ["const", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
@@ -1241,7 +1245,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e = 1"
+ "attached_condition": "t2.t2_e = 1"
}
}
}
@@ -1250,51 +1254,51 @@ EXPLAIN
}
# using equality : pushing into WHERE
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where d>1 and a=d and
-(a,c) in
+where t1_d>1 and t1_a=t1_d and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
4 2 24 4
select * from t1
-where d>1 and a=d and
-(a,c) in
+where t1_d>1 and t1_a=t1_d and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
4 2 24 4
explain select * from t1
-where d>1 and a=d and
-(a,c) in
+where t1_d>1 and t1_a=t1_d and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1
-where d>1 and a=d and
-(a,c) in
+where t1_d>1 and t1_a=t1_d and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
EXPLAIN
@@ -1306,7 +1310,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t1.d = t1.a and t1.a > 1 and t1.a is not null and t1.c is not null"
+ "attached_condition": "t1.t1_d = t1.t1_a and t1.t1_a > 1 and t1.t1_a is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -1314,8 +1318,8 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "8",
- "used_key_parts": ["e", "max(g)"],
- "ref": ["test.t1.a", "test.t1.c"],
+ "used_key_parts": ["t2_e", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
@@ -1328,7 +1332,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5 and t2.e > 1"
+ "attached_condition": "t2.t2_e < 5 and t2.t2_e > 1"
}
}
}
@@ -1338,51 +1342,51 @@ EXPLAIN
}
# using view in subquery definition : pushing into WHERE
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where a<3 and
-(a,c) in
+where t1_a<3 and
+(t1_a,t1_c) in
(
-select x,max(y)
+select v1_x,max(v1_y)
from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
)
;
-a b c d
+t1_a t1_b t1_c t1_d
2 1 15 4
select * from t1
-where a<3 and
-(a,c) in
+where t1_a<3 and
+(t1_a,t1_c) in
(
-select x,max(y)
+select v1_x,max(v1_y)
from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
)
;
-a b c d
+t1_a t1_b t1_c t1_d
2 1 15 4
explain select * from t1
-where a<3 and
-(a,c) in
+where t1_a<3 and
+(t1_a,t1_c) in
(
-select x,max(y)
+select v1_x,max(v1_y)
from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 Using where; Using temporary
explain format=json select * from t1
-where a<3 and
-(a,c) in
+where t1_a<3 and
+(t1_a,t1_c) in
(
-select x,max(y)
+select v1_x,max(v1_y)
from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
)
;
EXPLAIN
@@ -1394,7 +1398,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t1.a < 3 and t1.a is not null and t1.c is not null"
+ "attached_condition": "t1.t1_a < 3 and t1.t1_a is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -1402,8 +1406,8 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "8",
- "used_key_parts": ["x", "max(y)"],
- "ref": ["test.t1.a", "test.t1.c"],
+ "used_key_parts": ["v1_x", "max(v1_y)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
@@ -1416,7 +1420,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 8,
"filtered": 100,
- "attached_condition": "t3.x > 1 and t3.x <= 3 and t3.x < 3"
+ "attached_condition": "t3.t3_x > 1 and t3.t3_x <= 3 and t3.t3_x < 3"
}
}
}
@@ -1426,54 +1430,54 @@ EXPLAIN
}
# using equality : pushing into WHERE
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1,v1
-where t1.a=v1.x and v1.x<2 and v1.y>30 and
-(t1.a,t1.c) in
+where t1_a=v1_x and v1_x<2 and v1_y>30 and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d x y
+t1_a t1_b t1_c t1_d v1_x v1_y
1 3 40 1 1 35
1 2 40 2 1 35
select * from t1,v1
-where t1.a=v1.x and v1.x<2 and v1.y>30 and
-(t1.a,t1.c) in
+where t1_a=v1_x and v1_x<2 and v1_y>30 and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d x y
+t1_a t1_b t1_c t1_d v1_x v1_y
1 3 40 1 1 35
1 2 40 2 1 35
explain select * from t1,v1
-where t1.a=v1.x and v1.x<2 and v1.y>30 and
-(t1.a,t1.c) in
+where t1_a=v1_x and v1_x<2 and v1_y>30 and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t3.x,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t3.t3_x,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1,v1
-where t1.a=v1.x and v1.x<2 and v1.y>30 and
-(t1.a,t1.c) in
+where t1_a=v1_x and v1_x<2 and v1_y>30 and
+(t1_a,t1_c) in
(
-select e,max(g)
+select t2_e,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
EXPLAIN
@@ -1485,7 +1489,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 8,
"filtered": 100,
- "attached_condition": "t3.x < 2 and t3.y > 30 and t3.x <= 3 and t3.x is not null"
+ "attached_condition": "t3.t3_x < 2 and t3.t3_y > 30 and t3.t3_x <= 3 and t3.t3_x is not null"
},
"block-nl-join": {
"table": {
@@ -1497,7 +1501,7 @@ EXPLAIN
"buffer_type": "flat",
"buffer_size": "256Kb",
"join_type": "BNL",
- "attached_condition": "t1.a = t3.x and t1.c is not null"
+ "attached_condition": "t1.t1_a = t3.t3_x and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -1505,8 +1509,8 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "8",
- "used_key_parts": ["e", "max(g)"],
- "ref": ["test.t3.x", "test.t1.c"],
+ "used_key_parts": ["t2_e", "max(t2_g)"],
+ "ref": ["test.t3.t3_x", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
@@ -1519,7 +1523,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5 and t2.e <= 3"
+ "attached_condition": "t2.t2_e < 5 and t2.t2_e <= 3"
}
}
}
@@ -1530,51 +1534,51 @@ EXPLAIN
# conjunctive subformula : pushing into WHERE
# extracted or formula : pushing into HAVING
set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((b<3 or b=4) and a<3) and
-(a,b,c) in
+where ((t1_b<3 or t1_b=4) and t1_a<3) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 2 40 2
select * from t1
-where ((b<3 or b=4) and a<3) and
-(a,b,c) in
+where ((t1_b<3 or t1_b=4) and t1_a<3) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
-a b c d
+t1_a t1_b t1_c t1_d
1 2 40 2
explain select * from t1
-where ((b<3 or b=4) and a<3) and
-(a,b,c) in
+where ((t1_b<3 or t1_b=4) and t1_a<3) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary
explain format=json select * from t1
-where ((b<3 or b=4) and a<3) and
-(a,b,c) in
+where ((t1_b<3 or t1_b=4) and t1_a<3) and
+(t1_a,t1_b,t1_c) in
(
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
)
;
EXPLAIN
@@ -1586,7 +1590,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "(t1.b < 3 or t1.b = 4) and t1.a < 3 and t1.a is not null and t1.b is not null and t1.c is not null"
+ "attached_condition": "(t1.t1_b < 3 or t1.t1_b = 4) and t1.t1_a < 3 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
},
"table": {
"table_name": "<subquery2>",
@@ -1594,22 +1598,22 @@ EXPLAIN
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "12",
- "used_key_parts": ["e", "f", "max(g)"],
- "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+ "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+ "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
"rows": 1,
"filtered": 100,
"materialized": {
"unique": 1,
"query_block": {
"select_id": 2,
- "having_condition": "t2.f < 3 or t2.f = 4",
+ "having_condition": "t2.t2_f < 3 or t2.t2_f = 4",
"temporary_table": {
"table": {
"table_name": "t2",
"access_type": "ALL",
"rows": 12,
"filtered": 100,
- "attached_condition": "t2.e < 5 and t2.e < 3"
+ "attached_condition": "t2.t2_e < 5 and t2.t2_e < 3"
}
}
}
diff --git a/mysql-test/t/in_subq_cond_pushdown.test b/mysql-test/t/in_subq_cond_pushdown.test
index a022491..a40ba1a 100644
--- a/mysql-test/t/in_subq_cond_pushdown.test
+++ b/mysql-test/t/in_subq_cond_pushdown.test
@@ -1,8 +1,8 @@
let $no_pushdown= set statement optimizer_switch='condition_pushdown_for_subquery=off' for;
-create table t1 (a int, b int, c int, d int);
-create table t2 (e int, f int, g int);
-create table t3 (x int, y int);
+create table t1 (t1_a int, t1_b int, t1_c int, t1_d int);
+create table t2 (t2_e int, t2_f int, t2_g int);
+create table t3 (t3_x int, t3_y int);
insert into t1 values
(1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4),
@@ -20,14 +20,14 @@ insert into t3 values
create view v1 as
(
- select * from t3 where x<=3
+ select t3_x as v1_x, t3_y as v1_y from t3 where t3_x<=3
);
--echo # conjunctive subformula : pushing into HAVING
let $query=
select * from t1
-where c<25 and
- (a,c) in (select e,max(g) from t2 where e<5 group by e);
+where t1_c<25 and
+ (t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
eval $no_pushdown $query;
eval $query;
@@ -37,13 +37,13 @@ eval explain format=json $query;
--echo # extracted and formula : pushing into HAVING
let $query=
select * from t1
-where c>55 and b<4 and
- (a,b,c) in
+where t1_c>55 and t1_b<4 and
+ (t1_a,t1_b,t1_c) in
(
- select e,f,max(g)
+ select t2_e,t2_f,max(t2_g)
from t2
- where e<5
- group by e
+ where t2_e<5
+ group by t2_e
)
;
@@ -55,13 +55,13 @@ eval explain format=json $query;
--echo # extracted or formula : pushing into HAVING
let $query=
select * from t1
-where (c>60 or c<25) and
- (a,b,c) in
+where (t1_c>60 or t1_c<25) and
+ (t1_a,t1_b,t1_c) in
(
- select e,f,max(g)
+ select t2_e,t2_f,max(t2_g)
from t2
- where e<5
- group by e
+ where t2_e<5
+ group by t2_e
)
;
@@ -73,13 +73,13 @@ eval explain format=json $query;
--echo # extracted and-or formula : pushing into HAVING
let $query=
select * from t1
-where ((c>60 or c<25) and b>2) and
- (a,b,c) in
+where ((t1_c>60 or t1_c<25) and t1_b>2) and
+ (t1_a,t1_b,t1_c) in
(
- select e,f,max(g)
+ select t2_e,t2_f,max(t2_g)
from t2
- where e<5
- group by e
+ where t2_e<5
+ group by t2_e
)
;
@@ -91,13 +91,13 @@ eval explain format=json $query;
--echo # conjunctive subformula : pushing into HAVING
let $query=
select * from t1
-where ((a<2 or d>2) and b>3) and
- (a,b,c) in
+where ((t1_a<2 or t1_d>3) and t1_b>1) and
+ (t1_a,t1_b,t1_c) in
(
- select e,f,max(g)
+ select t2_e,t2_f,max(t2_g)
from t2
- where e<5
- group by e
+ where t2_e<5
+ group by t2_e
)
;
@@ -109,13 +109,13 @@ eval explain format=json $query;
--echo # using view in subquery definition : pushing into HAVING
let $query=
select * from t1
-where c>20 and
- (a,c) in
+where t1_c>20 and
+ (t1_a,t1_c) in
(
- select x,max(y)
+ select v1_x,max(v1_y)
from v1
- where x>1
- group by x
+ where v1_x>1
+ group by v1_x
)
;
@@ -127,13 +127,13 @@ eval explain format=json $query;
--echo # using equality : pushing into WHERE
let $query=
select * from t1,v1
-where c>20 and c=y and
- (a,c) in
+where t1_c>20 and t1_c=v1_y and
+ (t1_a,t1_c) in
(
- select e,max(g)
+ select t2_e,max(t2_g)
from t2
- where e<5
- group by e
+ where t2_e<5
+ group by t2_e
)
;
@@ -145,13 +145,13 @@ eval explain format=json $query;
--echo # conjunctive subformula : pushing into WHERE
let $query=
select * from t1
-where a<2 and
- (a,c) in
+where t1_a<2 and
+ (t1_a,t1_c) in
(
- select e,max(g)
+ select t2_e,max(t2_g)
from t2
- where e<5
- group by e
+ where t2_e<5
+ group by t2_e
)
;
@@ -163,13 +163,13 @@ eval explain format=json $query;
--echo # extracted and formula : pushing into WHERE
let $query=
select * from t1
-where a>2 and a<5 and
- (a,c) in
+where t1_a>2 and t1_a<5 and
+ (t1_a,t1_c) in
(
- select e,max(g)
+ select t2_e,max(t2_g)
from t2
- where e<5
- group by e
+ where t2_e<5
+ group by t2_e
)
;
@@ -181,13 +181,13 @@ eval explain format=json $query;
--echo # extracted or formula : pushing into WHERE
let $query=
select * from t1
-where (a<2 or a>=4) and
- (a,c) in
+where (t1_a<2 or t1_a>=4) and
+ (t1_a,t1_c) in
(
- select e,max(g)
+ select t2_e,max(t2_g)
from t2
- where e<5
- group by e
+ where t2_e<5
+ group by t2_e
)
;
@@ -199,13 +199,13 @@ eval explain format=json $query;
--echo # extracted and-or formula : pushing into WHERE
let $query=
select * from t1
-where ((a<2 or a=5) and b>3) and
- (a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+ (t1_a,t1_b,t1_c) in
(
- select e,f,max(g)
+ select t2_e,t2_f,max(t2_g)
from t2
- where e<5
- group by e,f
+ where t2_e<5
+ group by t2_e,t2_f
)
;
@@ -217,13 +217,13 @@ eval explain format=json $query;
--echo # extracted and-or formula : pushing into WHERE
let $query=
select * from t1
-where ((a<2 or a=5) and b>3) and
- (a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+ (t1_a,t1_b,t1_c) in
(
- select e,f,max(g)
+ select t2_e,t2_f,max(t2_g)
from t2
- where e<5
- group by e,f
+ where t2_e<5
+ group by t2_e,t2_f
)
;
@@ -235,13 +235,13 @@ eval explain format=json $query;
--echo # conjunctive subformula : pushing into WHERE
let $query=
select * from t1
-where ((b<3 or d>2) and a<2) and
- (a,b,c) in
+where ((t1_b<3 or t1_d>2) and t1_a<2) and
+ (t1_a,t1_b,t1_c) in
(
- select e,f,max(g)
+ select t2_e,t2_f,max(t2_g)
from t2
- where e<5
- group by e
+ where t2_e<5
+ group by t2_e
)
;
@@ -253,13 +253,13 @@ eval explain format=json $query;
--echo # using equalities : pushing into WHERE
let $query=
select * from t1
-where d=1 and a=d and
- (a,c) in
+where t1_d=1 and t1_a=t1_d and
+ (t1_a,t1_c) in
(
- select e,max(g)
+ select t2_e,max(t2_g)
from t2
- where e<5
- group by e
+ where t2_e<5
+ group by t2_e
)
;
@@ -271,13 +271,13 @@ eval explain format=json $query;
--echo # using equality : pushing into WHERE
let $query=
select * from t1
-where d>1 and a=d and
- (a,c) in
+where t1_d>1 and t1_a=t1_d and
+ (t1_a,t1_c) in
(
- select e,max(g)
+ select t2_e,max(t2_g)
from t2
- where e<5
- group by e
+ where t2_e<5
+ group by t2_e
)
;
@@ -289,13 +289,13 @@ eval explain format=json $query;
--echo # using view in subquery definition : pushing into WHERE
let $query=
select * from t1
-where a<3 and
- (a,c) in
+where t1_a<3 and
+ (t1_a,t1_c) in
(
- select x,max(y)
+ select v1_x,max(v1_y)
from v1
- where x>1
- group by x
+ where v1_x>1
+ group by v1_x
)
;
@@ -307,13 +307,13 @@ eval explain format=json $query;
--echo # using equality : pushing into WHERE
let $query=
select * from t1,v1
-where t1.a=v1.x and v1.x<2 and v1.y>30 and
- (t1.a,t1.c) in
+where t1_a=v1_x and v1_x<2 and v1_y>30 and
+ (t1_a,t1_c) in
(
- select e,max(g)
+ select t2_e,max(t2_g)
from t2
- where e<5
- group by e
+ where t2_e<5
+ group by t2_e
)
;
@@ -326,13 +326,13 @@ eval explain format=json $query;
--echo # extracted or formula : pushing into HAVING
let $query=
select * from t1
-where ((b<3 or b=4) and a<3) and
- (a,b,c) in
+where ((t1_b<3 or t1_b=4) and t1_a<3) and
+ (t1_a,t1_b,t1_c) in
(
- select e,f,max(g)
+ select t2_e,t2_f,max(t2_g)
from t2
- where e<5
- group by e
+ where t2_e<5
+ group by t2_e
)
;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index c3a392c..49747e4 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -6495,7 +6495,7 @@ Item *Item_direct_view_ref::in_subq_field_transformer_for_having(THD *thd,
Find fields that are used in the GROUP BY of the select
@param thd the thread handle
- @param sel the select of the IN subquery predicate select
+ @param sel the select of the IN subquery predicate
@param fields fields of the left part of the IN subquery predicate
@details
@@ -6568,7 +6568,7 @@ void grouping_fields_in_the_in_subq_left_part(THD *thd,
right_part and delete cond_where from the extr.
5. Transforms extr so it can be pushed into the HAVING clause of the right_part
@note
- This method is similar with pushdown_cond_for_derived
+ This method is similar with pushdown_cond_for_derived()
@retval TRUE if an error occurs
@retval FALSE otherwise
@@ -6591,10 +6591,9 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
/*
Create the list of In_subq_field items for this IN subquery:
it consists of the pairs of fields from the left part of the IN subquery
- 'left_part' and the respective fields from the right part of the
- IN subquery (fields from the projections list of the select of the right
- part of the IN subquery 'right_part' that stay on the same places in
- the list of projections as the fields from the left_part).
+ 'left_part' and the respective fields from the select of the right part of
+ the IN subquery 'right_part' (fields that stay on the same places as fields
+ of left_part in the projection list of right_part).
*/
comparable_fields.empty();
List_iterator_fast<Item> it(sel->join->fields_list);
@@ -6611,7 +6610,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
}
/*
- Build the new condition from the cond that can be pushed into sel
+ Build the new condition from cond that can be pushed into sel
*/
Item *extracted_cond;
cond->check_pushable_cond(0, this);
@@ -6658,7 +6657,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
}
/*
- Checks what can be pushed into the WHERE clause of the sel from the
+ Checks what can be pushed into the WHERE clause of sel from the
extracted condition
*/
Item *cond_over_grouping_fields;
@@ -6670,7 +6669,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
/*
Transforms the references to the left_part fields so they can be pushed
- into the sel of the WHERE clause.
+ into sel of the WHERE clause.
*/
if (cond_over_grouping_fields)
cond_over_grouping_fields=
@@ -6681,7 +6680,8 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
if (cond_over_grouping_fields)
{
/*
- Removes from extracted_cond all parts that can be pushed into the WHERE clause
+ Removes from extracted_cond all parts that can be pushed into the
+ WHERE clause
*/
extracted_cond= remove_pushed_top_conjuncts(thd, extracted_cond);
@@ -6693,8 +6693,8 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
goto exit;
}
/*
- Transforms the references to the left_part fields so they can be pushed into
- the sel of the HAVING clause
+ Transforms the references to the left_part fields so the transformed
+ condition can be pushed into sel of the HAVING clause
*/
extracted_cond= extracted_cond->transform(thd,
&Item::in_subq_field_transformer_for_having,
1
0
[Commits] 92d2a8a3d7b: MDEV-15837: Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM'
by varunraiko1803＠gmail.com 25 Apr '18
by varunraiko1803＠gmail.com 25 Apr '18
25 Apr '18
revision-id: 92d2a8a3d7b2b1530b53fe2a2d1d5ac3e6506d34 (mariadb-10.2.5-613-g92d2a8a3d7b)
parent(s): f033fbd9f2366619c52186a1a902066495539141
author: Varun Gupta
committer: Varun Gupta
timestamp: 2018-04-25 19:43:25 +0530
message:
MDEV-15837: Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM'
failed in compare_order_elements function
The issue here is the function compare_order_lists() is called for the order by list of the window functions
so that those window function that can be computed together are adjacent.
So in the function compare_order_list we iterate over all the elements in the order list of the two functions and
compare the items in their order by clause.
The function compare_order_elements() is called for each item in the
order by clause. This function assumes that all the items that are in the order by list would be of the type
Item::FIELD_ITEM.
The case we have is that we have constants in the order by clause. We should ignore the constant and only compare
items of the type Item::FIELD_ITEM in compare_order_elements()
---
mysql-test/r/win.result | 12 ++++++++++++
mysql-test/t/win.test | 9 +++++++++
sql/sql_window.cc | 16 ++++++++++++++++
3 files changed, 37 insertions(+)
diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index e3cb40e8343..8c6e3d79e80 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -3299,3 +3299,15 @@ ROW_NUMBER() OVER() i
SELECT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
ROW_NUMBER() OVER() i
DROP TABLE t1;
+#
+# MDEV-15837: Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM'
+# failed in compare_order_elements function
+#
+CREATE TABLE t1 (a1 int);
+insert into t1 values (1),(2),(3);
+SELECT rank() OVER (ORDER BY 1), ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4))) FROM t1;
+rank() OVER (ORDER BY 1) ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4)))
+1 2
+1 1
+1 3
+drop table t1;
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index 95ffb6d9909..6422ebc5d4b 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -2067,3 +2067,12 @@ SELECT DISTINCT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
SELECT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
DROP TABLE t1;
+--echo #
+--echo # MDEV-15837: Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM'
+--echo # failed in compare_order_elements function
+--echo #
+
+CREATE TABLE t1 (a1 int);
+insert into t1 values (1),(2),(3);
+SELECT rank() OVER (ORDER BY 1), ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4))) FROM t1;
+drop table t1;
diff --git a/sql/sql_window.cc b/sql/sql_window.cc
index 4e1e64365ae..e556d75442f 100644
--- a/sql/sql_window.cc
+++ b/sql/sql_window.cc
@@ -342,6 +342,22 @@ int compare_order_lists(SQL_I_List<ORDER> *part_list1,
for ( ; elem1 && elem2; elem1= elem1->next, elem2= elem2->next)
{
int cmp;
+ // remove all constants as we don't need them for comparision
+ while(elem1 && ((*elem1->item)->real_item())->const_item())
+ {
+ elem1= elem1->next;
+ continue;
+ }
+
+ while(elem2 && ((*elem2->item)->real_item())->const_item())
+ {
+ elem2= elem2->next;
+ continue;
+ }
+
+ if (!elem1 || !elem2)
+ break;
+
if ((cmp= compare_order_elements(elem1, elem2)))
return cmp;
}
1
0
[Commits] 99c40e0628d: MDEV-13727 rpl.rpl_concurrency_error failed
by andrei.elkin＠pp.inet.fi 25 Apr '18
by andrei.elkin＠pp.inet.fi 25 Apr '18
25 Apr '18
revision-id: 99c40e0628d1157a835d0225d2810637774c9174 (mariadb-10.3.6-26-g99c40e0628d)
parent(s): 38c799c9a5e5aadd3f4df157a4151dd1f71d5bcb
author: Andrei Elkin
committer: Andrei Elkin
timestamp: 2018-04-25 14:16:38 +0300
message:
MDEV-13727 rpl.rpl_concurrency_error failed
The test actually revealed a flaw in MDEV-8305
which inadvertently enrolled the trigger and
stored function into slow query reporting which was aimed
exclusively to the stored procedure.
Specifically to the test, a query on the master was logged
with a timestamp of the query's top-level statement but its (post
update) trigger computed one more (later) timestamp which got
inserted into another table.
Master-vs-slave whole seconds timestamp discrepancy became evident
thanks to different execution time of the trigger combined with the
fact of the logged with micro-second fractional part master timestamp
was truncated on the slave. On master when the fractional part was
close to 1 the trigger execution added up its own latency to overflow
to next second value. That's how the master timestamp surprisingly
turned out to bigger than the slave's one.
Fixed with slight refactoring of MDEV-8305 to reuse always existing
timestamp resetting mechanism engaged prior to a stored procedure's
next statement execution.
Now the resetter is augmented to also deal with THD::start_utime et al.
---
mysql-test/main/func_time.result | 35 +++++++++++++++++++
mysql-test/main/func_time.test | 75 ++++++++++++++++++++++++++++++++++++++++
sql/sp_head.cc | 22 +-----------
3 files changed, 111 insertions(+), 21 deletions(-)
diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result
index 2772f850ce9..4a999280159 100644
--- a/mysql-test/main/func_time.result
+++ b/mysql-test/main/func_time.result
@@ -3484,3 +3484,38 @@ t1 CREATE TABLE `t1` (
`c5` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
+CREATE TABLE t_ts (a timestamp(6));
+CREATE TABLE t_trig (a timestamp(6));
+CREATE TABLE t1 (a timestamp(6));
+CREATE TABLE t2 (a timestamp(6));
+CREATE FUNCTION fn_sleep_before_now() returns int
+BEGIN
+INSERT INTO t_ts SET a= current_timestamp(6);
+RETURN 0;
+END//
+CREATE TRIGGER trg_insert_t_ts AFTER INSERT ON t_ts FOR EACH ROW
+BEGIN
+INSERT into t_trig set a= current_timestamp(6);
+END//
+CREATE PROCEDURE sp()
+BEGIN
+INSERT INTO t1 SET a=current_timestamp(6);
+INSERT INTO t2 SET a=current_timestamp(6);
+END//
+SET @sav_slow_query_log= @@session.slow_query_log;
+SET @@session.slow_query_log= ON;
+SELECT current_timestamp(6),fn_sleep_before_now() INTO @ts_cur, @ts_func;
+SELECT a FROM t_ts LIMIT 1 into @ts_func;
+SELECT a FROM t_trig LIMIT 1 into @ts_trig;
+DELETE FROM t_ts;
+DELETE FROM t_trig;
+SET @@session.slow_query_log= OFF;
+SELECT current_timestamp(6),fn_sleep_before_now() INTO @ts_cur, @func_ts;
+SELECT a FROM t_ts LIMIT 1 into @ts_func;
+SELECT a FROM t_trig LIMIT 1 into @ts_trig;
+CALL sp();
+SET @@session.slow_query_log= @sav_slow_query_log;
+DROP PROCEDURE sp;
+DROP FUNCTION fn_sleep_before_now;
+DROP TRIGGER trg_insert_t_ts;
+DROP TABLE t_ts, t_trig, t1, t2;
diff --git a/mysql-test/main/func_time.test b/mysql-test/main/func_time.test
index 5417cb20a92..9f44761d938 100644
--- a/mysql-test/main/func_time.test
+++ b/mysql-test/main/func_time.test
@@ -2057,3 +2057,78 @@ EXECUTE IMMEDIATE
USING NULL, '10', 10, 10.0, 10e0, TIME'10:20:30';
SHOW CREATE TABLE t1;
DROP TABLE t1;
+
+
+############
+# MDEV-13727
+# Current timestamp functions inside stored functions must return the
+# value of the top-level statement's timestamp (its start time).
+# This must hold regardless of @@slow_query_log option.
+# In contrast the current timestamp of stored procedure
+# monotonically grows from statement to statement.
+
+CREATE TABLE t_ts (a timestamp(6));
+CREATE TABLE t_trig (a timestamp(6));
+CREATE TABLE t1 (a timestamp(6));
+CREATE TABLE t2 (a timestamp(6));
+delimiter //;
+CREATE FUNCTION fn_sleep_before_now() returns int
+BEGIN
+ INSERT INTO t_ts SET a= current_timestamp(6);
+ RETURN 0;
+END//
+CREATE TRIGGER trg_insert_t_ts AFTER INSERT ON t_ts FOR EACH ROW
+BEGIN
+ INSERT into t_trig set a= current_timestamp(6);
+END//
+CREATE PROCEDURE sp()
+BEGIN
+ INSERT INTO t1 SET a=current_timestamp(6);
+ INSERT INTO t2 SET a=current_timestamp(6);
+END//
+delimiter ;//
+
+SET @sav_slow_query_log= @@session.slow_query_log;
+
+# @@slow_query_log ON check
+SET @@session.slow_query_log= ON;
+SELECT current_timestamp(6),fn_sleep_before_now() INTO @ts_cur, @ts_func;
+
+SELECT a FROM t_ts LIMIT 1 into @ts_func;
+SELECT a FROM t_trig LIMIT 1 into @ts_trig;
+if (!`SELECT @ts_cur = @ts_func AND @ts_func = @ts_trig`)
+{
+ SELECT @ts_cur, @ts_func, @ts_trig;
+ --die Error: timestamps must be equal but they diverge
+}
+DELETE FROM t_ts;
+DELETE FROM t_trig;
+
+# @@slow_query_log OFF check
+SET @@session.slow_query_log= OFF;
+SELECT current_timestamp(6),fn_sleep_before_now() INTO @ts_cur, @func_ts;
+SELECT a FROM t_ts LIMIT 1 into @ts_func;
+SELECT a FROM t_trig LIMIT 1 into @ts_trig;
+if (!`SELECT @ts_cur = @ts_func AND @ts_func = @ts_trig`)
+{
+ SELECT @ts_cur, @ts_func, @ts_trig;
+ --die Error: timestamps must be equal but they diverge
+}
+
+CALL sp();
+if (!`SELECT t2.a > t1.a FROM t1,t2`)
+{
+ SELECT t1.a, t2.a FROM t1,t2;
+ --die Error: timestamps must be growing monotonically
+}
+
+# Cleanup
+SET @@session.slow_query_log= @sav_slow_query_log;
+DROP PROCEDURE sp;
+DROP FUNCTION fn_sleep_before_now;
+DROP TRIGGER trg_insert_t_ts;
+DROP TABLE t_ts, t_trig, t1, t2;
+
+#
+# End of MDEV-13727
+###################
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index f7847bae89d..ac3e490e598 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -66,7 +66,7 @@ extern "C" uchar *sp_table_key(const uchar *ptr, size_t *plen, my_bool first);
static void reset_start_time_for_sp(THD *thd)
{
if (!thd->in_sub_stmt)
- thd->set_start_time();
+ thd->set_time();
}
@@ -3459,9 +3459,7 @@ int
sp_instr_stmt::execute(THD *thd, uint *nextp)
{
int res;
- bool save_enable_slow_log;
const CSET_STRING query_backup= thd->query_string;
- QUERY_START_TIME_INFO time_info;
Sub_statement_state backup_state;
DBUG_ENTER("sp_instr_stmt::execute");
DBUG_PRINT("info", ("command: %d", m_lex_keeper.sql_command()));
@@ -3471,15 +3469,6 @@ sp_instr_stmt::execute(THD *thd, uint *nextp)
thd->profiling.set_query_source(m_query.str, m_query.length);
#endif
- if ((save_enable_slow_log= thd->enable_slow_log))
- {
- /*
- Save start time info for the CALL statement and overwrite it with the
- current time for log_slow_statement() to log the individual query timing.
- */
- thd->backup_query_start_time(&time_info);
- thd->set_time();
- }
thd->store_slow_query_state(&backup_state);
if (!(res= alloc_query(thd, m_query.str, m_query.length)) &&
@@ -3515,12 +3504,6 @@ sp_instr_stmt::execute(THD *thd, uint *nextp)
if (log_slow)
log_slow_statement(thd);
- /*
- Restore enable_slow_log, that can be changed by a admin or call
- command
- */
- thd->enable_slow_log= save_enable_slow_log;
-
/* Add the number of rows to thd for the 'call' statistics */
thd->add_slow_query_state(&backup_state);
}
@@ -3543,9 +3526,6 @@ sp_instr_stmt::execute(THD *thd, uint *nextp)
thd->get_stmt_da()->reset_diagnostics_area();
}
}
- /* Restore the original query start time */
- if (thd->enable_slow_log)
- thd->restore_query_start_time(&time_info);
DBUG_RETURN(res || thd->is_error());
}
1
0