- commits - lists.mariadb.org

[Commits] f6166101e0f: MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map
by sachin.setiya＠mariadb.com 19 Mar '19

19 Mar '19

revision-id: f6166101e0fa3dc354baee7c560ee473cdb59491 (mariadb-10.4.3-86-gf6166101e0f) parent(s): 8076c594ce206222d1daf70d4193095061dd5d2f author: sachinsetia1001(a)gmail.com committer: Sachin timestamp: 2019-03-19 16:43:43 +0530 message: MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map We do not support long unique index in partition. --- mysql-test/main/long_unique_bugs.result | 2 ++ mysql-test/main/long_unique_bugs.test | 7 +++++++ sql/share/errmsg-utf8.txt | 2 ++ sql/sql_partition.cc | 8 ++++++++ 4 files changed, 19 insertions(+) diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index dec5153ad85..5f15171a4f5 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -202,3 +202,5 @@ ERROR 23000: Duplicate entry ' insert into t1 values ('ббб'); ERROR 23000: Duplicate entry '�' for key 'a' drop table t1; +CREATE TABLE t1 (a int , unique(a) using hash) PARTITION BY HASH (a) PARTITIONS 2; +ERROR HY000: Long unique index is not supported in partition diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index db1cd0fbae4..674e844a374 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -1,4 +1,5 @@ --source include/have_innodb.inc +--source include/have_partition.inc # # MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list @@ -230,3 +231,9 @@ insert into t1 values ('бб'); --error ER_DUP_ENTRY insert into t1 values ('ббб'); drop table t1; + +# +# MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map +# +--error ER_LONG_UNIQUE_IN_PART_FUNC_ERROR +CREATE TABLE t1 (a int , unique(a) using hash) PARTITION BY HASH (a) PARTITIONS 2; diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index cc2ac272458..43a71e76df5 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7954,3 +7954,5 @@ ER_PERIOD_CONSTRAINT_DROP eng "Can't DROP CONSTRAINT `%s`. Use DROP PERIOD `%s` for this" ER_TOO_LONG_KEYPART 42000 S1009 eng "Specified key part was too long; max key part length is %u bytes" +ER_LONG_UNIQUE_IN_PART_FUNC_ERROR + eng "Long unique index is not supported in partition" diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc index 6b530a95efb..a2aeedc1fdc 100644 --- a/sql/sql_partition.cc +++ b/sql/sql_partition.cc @@ -369,6 +369,14 @@ static bool set_up_field_array(THD *thd, TABLE *table, ptr= table->field; while ((field= *(ptr++))) { + /* + We currently don't support long unique hash index in partition + */ + if (unlikely(field->flags & LONG_UNIQUE_HASH_FIELD)) + { + my_error(ER_LONG_UNIQUE_IN_PART_FUNC_ERROR, MYF(0)); + result= TRUE; + } if (field->flags & GET_FIXED_FIELDS_FLAG) { field->flags&= ~GET_FIXED_FIELDS_FLAG;

1 0

[Commits] d260871fccc: MDEV-18967 Load data in system version with long unique does not work
by sachin.setiya＠mariadb.com 19 Mar '19

19 Mar '19

revision-id: d260871fccc44d92ad8b7dec674a791c46123788 (mariadb-10.4.3-87-gd260871fccc) parent(s): f6166101e0fa3dc354baee7c560ee473cdb59491 author: Sachin committer: Sachin timestamp: 2019-03-19 16:43:43 +0530 message: MDEV-18967 Load data in system version with long unique does not work Update system versioning fields before generaled columns for left out fill_record --- mysql-test/main/long_unique_bugs.result | 9 +++++++++ mysql-test/main/long_unique_bugs.test | 13 +++++++++++++ sql/sql_base.cc | 4 ++-- 3 files changed, 24 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 5f15171a4f5..a0850c0b9f2 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -204,3 +204,12 @@ ERROR 23000: Duplicate entry ' drop table t1; CREATE TABLE t1 (a int , unique(a) using hash) PARTITION BY HASH (a) PARTITIONS 2; ERROR HY000: Long unique index is not supported in partition +CREATE TABLE t1 (data VARCHAR(4), unique(data) using hash) with system versioning; +INSERT INTO t1 VALUES ('A'); +SELECT * INTO OUTFILE 'load.data' from t1; +LOAD DATA INFILE 'load.data' INTO TABLE t1; +ERROR 23000: Duplicate entry 'A' for key 'data' +select * from t1; +data +A +DROP TABLE t1; diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index 674e844a374..d257ab511b9 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -237,3 +237,16 @@ drop table t1; # --error ER_LONG_UNIQUE_IN_PART_FUNC_ERROR CREATE TABLE t1 (a int , unique(a) using hash) PARTITION BY HASH (a) PARTITIONS 2; + +# +# MDEV-18967 Load data in system version with long unique does not work +# +CREATE TABLE t1 (data VARCHAR(4), unique(data) using hash) with system versioning; +INSERT INTO t1 VALUES ('A'); +SELECT * INTO OUTFILE 'load.data' from t1; +--error ER_DUP_ENTRY +LOAD DATA INFILE 'load.data' INTO TABLE t1; +select * from t1; +DROP TABLE t1; +--let $datadir= `select @@datadir` +--remove_file $datadir/test/load.data diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 5de8bcc6df6..a7debea0076 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -8439,12 +8439,12 @@ fill_record(THD *thd, TABLE *table_arg, List<Item> &fields, List<Item> &values, if (!update && table_arg->default_field && table_arg->update_default_fields(0, ignore_errors)) goto err; + if (table_arg->versioned() && !only_unvers_fields) + table_arg->vers_update_fields(); /* Update virtual fields */ if (table_arg->vfield && table_arg->update_virtual_fields(table_arg->file, VCOL_UPDATE_FOR_WRITE)) goto err; - if (table_arg->versioned() && !only_unvers_fields) - table_arg->vers_update_fields(); thd->abort_on_warning= save_abort_on_warning; thd->no_errors= save_no_errors; DBUG_RETURN(thd->is_error());

1 0

[Commits] f6c1055ad45: MDEV-18901 Wrong results after ADD UNIQUE INDEX(blob_column)
by sachin.setiya＠mariadb.com 19 Mar '19

19 Mar '19

revision-id: f6c1055ad45070e79f991a41b8c20af30d728464 (mariadb-10.4.3-88-gf6c1055ad45) parent(s): d260871fccc44d92ad8b7dec674a791c46123788 author: Sachin committer: Sachin timestamp: 2019-03-19 16:43:43 +0530 message: MDEV-18901 Wrong results after ADD UNIQUE INDEX(blob_column) Add test case for MDEV-18901 as MDEV-18967 and MDEV-18922 solves this issue --- mysql-test/main/long_unique_bugs.result | 24 ++++++++++++++++++++++++ mysql-test/main/long_unique_bugs.test | 22 ++++++++++++++++++++++ 2 files changed, 46 insertions(+) diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index a0850c0b9f2..9a1ce1a3482 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -213,3 +213,27 @@ select * from t1; data A DROP TABLE t1; +CREATE TABLE t1 (data VARCHAR(7961)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('f'), ('o'), ('o'); +SELECT * INTO OUTFILE 'load.data' from t1; +ALTER IGNORE TABLE t1 ADD UNIQUE INDEX (data); +SELECT * FROM t1; +data +f +o +ALTER TABLE t1 ADD SYSTEM VERSIONING ; +SELECT * FROM t1; +data +f +o +REPLACE INTO t1 VALUES ('f'), ('o'), ('o'); +SELECT * FROM t1; +data +f +o +LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1; +SELECT * FROM t1; +data +f +o +DROP TABLE t1; diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index d257ab511b9..14dfc3786fd 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -250,3 +250,25 @@ select * from t1; DROP TABLE t1; --let $datadir= `select @@datadir` --remove_file $datadir/test/load.data + +# +# MDEV-18901 Wrong results after ADD UNIQUE INDEX(blob_column) +# +--source include/have_innodb.inc +CREATE TABLE t1 (data VARCHAR(7961)) ENGINE=InnoDB; + +INSERT INTO t1 VALUES ('f'), ('o'), ('o'); +SELECT * INTO OUTFILE 'load.data' from t1; + +ALTER IGNORE TABLE t1 ADD UNIQUE INDEX (data); +SELECT * FROM t1; +ALTER TABLE t1 ADD SYSTEM VERSIONING ; +SELECT * FROM t1; +REPLACE INTO t1 VALUES ('f'), ('o'), ('o'); +SELECT * FROM t1; +# This should be equivalent to the REPLACE above +LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +--let $datadir= `select @@datadir` +--remove_file $datadir/test/load.data

1 0

[Commits] 500d909: MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly
by varun 18 Mar '19

18 Mar '19

revision-id: 500d909a795022255fb6f6c44cd310636afc0b5a (mariadb-10.3.6-226-g500d909) parent(s): 88d89ee0bae24b71416c2af4f4c2f2be7b6a033a author: Varun Gupta committer: Varun Gupta timestamp: 2019-03-11 20:20:35 +0530 message: MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly Keys with multi-key parts were not being printed correctly, only the first key part was getting printed. Fixed it by making sure append_range_all_keyparts function is called for the remaining keyparts. --- mysql-test/main/opt_trace.result | 425 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 17 ++ sql/opt_range.cc | 3 +- 3 files changed, 443 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 4c3e2b3..a4c2b86 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -6022,3 +6022,428 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { set @@optimizer_switch= @save_optimizer_switch; drop table t1,t2; set optimizer_trace='enabled=off'; +# +# MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k (a int); +insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C; +create table t1 ( a int, b int, c int, d int, key (a,b), key(c,d)); +insert into t1 select a,a,a,a from one_k; +set optimizer_trace=1; +explain format=json select * from t1 where a > 10 and b < 10 and c=0 and d=1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a", "c"], + "key": "c", + "key_length": "10", + "used_key_parts": ["c", "d"], + "ref": ["const", "const"], + "rows": 1, + "filtered": 98.9, + "attached_condition": "t1.a > 10 and t1.b < 10" + } + } +} +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain format=json select * from t1 where a > 10 and b < 10 and c=0 and d=1 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t1.d AS d from t1 where t1.a > 10 and t1.b < 10 and t1.c = 0 and t1.d = 1" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a > 10 and t1.b < 10 and t1.c = 0 and t1.d = 1", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "t1.a > 10 and t1.b < 10 and multiple equal(0, t1.c) and multiple equal(1, t1.d)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "t1.a > 10 and t1.b < 10 and multiple equal(0, t1.c) and multiple equal(1, t1.d)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.a > 10 and t1.b < 10 and multiple equal(0, t1.c) and multiple equal(1, t1.d)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t1", + "field": "c", + "equals": "0", + "null_rejecting": false + }, + { + "table": "t1", + "field": "d", + "equals": "1", + "null_rejecting": false + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 208.25 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a", "b"] + }, + { + "index": "c", + "usable": true, + "key_parts": ["c", "d"] + } + ], + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": ["10 < a AND NULL < b < 10"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 989, + "cost": 1272.8, + "chosen": false, + "cause": "cost" + }, + { + "index": "c", + "ranges": ["0 <= c <= 0 AND 1 <= d <= 1"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": false, + "rows": 1, + "cost": 2.3783, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "c", + "rows": 1, + "ranges": ["0 <= c <= 0 AND 1 <= d <= 1"] + }, + "rows_for_plan": 1, + "cost_for_plan": 2.3783, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "c", + "selectivity_from_index": 0.001 + }, + { + "index_name": "a", + "selectivity_from_index": 0.989 + } + ], + "selectivity_for_columns": [], + "cond_selectivity": 9.9e-4 + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "c", + "used_range_estimates": true, + "rows": 1, + "cost": 2, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.c = 0 and t1.d = 1 and t1.a > 10 and t1.b < 10", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": "t1.a > 10 and t1.b < 10" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +explain format=json select * from t1 force index(a) where a=10 and b=20; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "10", + "used_key_parts": ["a", "b"], + "ref": ["const", "const"], + "rows": 1, + "filtered": 100 + } + } +} +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain format=json select * from t1 force index(a) where a=10 and b=20 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t1.d AS d from t1 FORCE INDEX (a) where t1.a = 10 and t1.b = 20" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a = 10 and t1.b = 20", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "multiple equal(10, t1.a) and multiple equal(20, t1.b)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "multiple equal(10, t1.a) and multiple equal(20, t1.b)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(10, t1.a) and multiple equal(20, t1.b)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t1", + "field": "a", + "equals": "10", + "null_rejecting": false + }, + { + "table": "t1", + "field": "b", + "equals": "20", + "null_rejecting": false + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 2e308 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a", "b"] + }, + { + "index": "c", + "usable": false, + "cause": "not applicable" + } + ], + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": ["10 <= a <= 10 AND 20 <= b <= 20"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": false, + "rows": 1, + "cost": 2.3783, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a", + "rows": 1, + "ranges": ["10 <= a <= 10 AND 20 <= b <= 20"] + }, + "rows_for_plan": 1, + "cost_for_plan": 2.3783, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "a", + "selectivity_from_index": 0.001 + } + ], + "selectivity_for_columns": [], + "cond_selectivity": 0.001 + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "used_range_estimates": true, + "rows": 1, + "cost": 2, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.a = 10 and t1.b = 20", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +drop table t1,t0,one_k; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index e59a11f..30b24d9 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -374,3 +374,20 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; set @@optimizer_switch= @save_optimizer_switch; drop table t1,t2; set optimizer_trace='enabled=off'; + +--echo # +--echo # MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k (a int); +insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C; +create table t1 ( a int, b int, c int, d int, key (a,b), key(c,d)); +insert into t1 select a,a,a,a from one_k; +set optimizer_trace=1; +explain format=json select * from t1 where a > 10 and b < 10 and c=0 and d=1; +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; +explain format=json select * from t1 force index(a) where a=10 and b=20; +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; +drop table t1,t0,one_k; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 1e60bb9..92161f7 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -15812,8 +15812,7 @@ static void append_range_all_keyparts(Json_writer_array *range_trace, if (keypart_range->next_key_part && keypart_range->next_key_part->part == - keypart_range->part + 1 && - keypart_range->is_singlepoint()) + keypart_range->part + 1) { append_range_all_keyparts(range_trace, range_string, range_so_far, keypart_range->next_key_part, key_parts);

2 1

[Commits] 39bc7ab0c12: MDEV-18769 Assertion `fixed == 1' failed in Item_cond_or::val_int
by Galina 18 Mar '19

18 Mar '19

revision-id: 39bc7ab0c120281c574631f1a2a335ee78863c26 (mariadb-10.4.3-52-g39bc7ab0c12) parent(s): 5a796f1f41a5bc0afb638cc342095e59a5bb15df author: Galina Shalygina committer: Galina Shalygina timestamp: 2019-03-18 23:36:33 +0300 message: MDEV-18769 Assertion `fixed == 1' failed in Item_cond_or::val_int This bug is caused by pushdown from HAVING into WHERE. It appears because condition that is pushed wasn't fixed. It is also discovered that condition pushdown from HAVING into WHERE is done wrong. There is no need to build clones for some conditions that can be pushed. They can be simply moved from HAVING into WHERE without cloning. build_pushable_cond_for_having_pushdown(), remove_pushed_top_conjuncts_for_having() methods are changed. It is found that there is no transformation made for fields of pushed condition. field_transformer_for_having_pushdown transformer is added. New tests are added. Some comments are changed. --- mysql-test/main/derived_cond_pushdown.result | 31 +- mysql-test/main/having.result | 2 +- mysql-test/main/having_cond_pushdown.result | 2313 ++++++++++++++++++++++++++ mysql-test/main/having_cond_pushdown.test | 670 ++++++++ mysql-test/main/union.result | 2 +- sql/item.h | 8 + sql/item_cmpfunc.cc | 57 +- sql/item_cmpfunc.h | 1 + sql/opt_subselect.cc | 157 +- sql/sql_lex.cc | 386 ++--- sql/sql_lex.h | 2 +- sql/sql_select.cc | 3 +- 12 files changed, 3348 insertions(+), 284 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index a13ce8575de..54068c4d6dd 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -8218,12 +8218,10 @@ EXPLAIN "query_block": { "select_id": 1, "table": { - "table_name": "<subquery2>", - "access_type": "system", - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, + "message": "Impossible WHERE" + }, + "subqueries": [ + { "query_block": { "select_id": 2, "table": { @@ -8231,26 +8229,7 @@ EXPLAIN } } } - }, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 2, - "filtered": 100, - "attached_condition": "v1.c = NULL", - "materialized": { - "query_block": { - "select_id": 3, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 2, - "filtered": 100, - "attached_condition": "t1.c = NULL" - } - } - } - } + ] } } DROP VIEW v1; diff --git a/mysql-test/main/having.result b/mysql-test/main/having.result index dd710db715a..837940a55ef 100644 --- a/mysql-test/main/having.result +++ b/mysql-test/main/having.result @@ -483,7 +483,7 @@ HAVING (table2.f2 = 8); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 select `test`.`table1`.`f1` AS `f1`,7 AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where 0 group by `test`.`table1`.`f1`,7 having multiple equal(8, 7) +Note 1003 select `test`.`table1`.`f1` AS `f1`,7 AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where 0 group by `test`.`table1`.`f1`,7 having 1 DROP TABLE t1; # # Bug#52336 Segfault / crash in 5.1 copy_fields (param=0x9872980) at sql_select.cc:15355 diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result index ef7368bd1fb..8bcceb59d72 100644 --- a/mysql-test/main/having_cond_pushdown.result +++ b/mysql-test/main/having_cond_pushdown.result @@ -1918,3 +1918,2316 @@ SELECT a FROM t1 WHERE b = 1 AND b = 2 GROUP BY a HAVING a <= 3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE DROP TABLE t1; +# +# MDEV-18769: unfixed OR condition pushed from HAVING into WHERE +# +CREATE TABLE t1(a INT, b INT, c INT); +INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2); +# nothing to push +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +a b MAX(t1.c) +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +a b MAX(t1.c) +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.b = 13 and max(t1.c) > 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.b = 13 and max(t1.c) > 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + } + } +} +# extracted AND formula +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +a b MAX(t1.c) +2 13 2 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +a b MAX(t1.c) +2 13 2 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and t1.b < 14" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and t1.b < 14" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +a b MAX(t1.c) +1 22 1 +2 13 2 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +a b MAX(t1.c) +1 22 1 +2 13 2 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.b > 15)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.b > 15)" + } + } + } + } +} +# extracted AND formula : equality in the inner AND formula +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +a b MAX(t1.c) +2 13 2 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +a b MAX(t1.c) +2 13 2 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.a = 2 and t1.b > 15)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.a = 2 and t1.b > 15)" + } + } + } + } +} +# extracted OR formula +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +a b MAX(t1.c) +1 14 3 +1 22 1 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +a b MAX(t1.c) +1 14 3 +1 22 1 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a < 2 or t1.b = 13 and t1.a > 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2) OR (t1.b = 13 AND t1.a > 2) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a < 2 or t1.b = 13 and t1.a > 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +a b MAX(t1.c) +1 14 3 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +a b MAX(t1.c) +1 14 3 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 2 or t1.b = 13 and t1.a > 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 2 or t1.b = 13 and t1.a > 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +a b MAX(t1.c) +1 14 3 +3 13 4 +3 14 2 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +a b MAX(t1.c) +1 14 3 +3 13 4 +3 14 2 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 2 or t1.a > 2 and (t1.b = 13 or t1.b = 14)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 2 or t1.a > 2 and (t1.b = 13 or t1.b = 14)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a < 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a < 2 or t1.a = 1 or t1.a = 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a < 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a < 2 or t1.a = 1 or t1.a = 2" + } + } + } + } +} +# extracted OR formula : one multiple equality in the left part +of OR condition, part of it pushed +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +2 13 2 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +2 13 2 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2 or t1.a = 1 or t1.a = 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2 or t1.a = 1 or t1.a = 2" + } + } + } + } +} +# extracted OR formula : two multiple equalities in the left part +of OR condition, one pushed +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +2 13 2 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +2 13 2 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and max(t1.c) = 3 or max(t1.c) < 5 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2 or t1.a = 1 or t1.a = 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and max(t1.c) = 3 or max(t1.c) < 5 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2 or t1.a = 1 or t1.a = 2" + } + } + } + } +} +# conjunctive subformula : equality pushdown +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.c) = 3", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (MAX(t1.c) = 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.c) = 3", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } +} +# conjunctive subformula : equalities pushdown +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b = 14)", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 3" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) +GROUP BY t1.a,t1.c +HAVING (MAX(t1.b) = 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 14", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 3" + } + } +} +# conjunctive subformula : multiple equality consists of +two equalities pushdown +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +a b MAX(t1.c) +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +a b MAX(t1.c) +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b = 14)", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 1) +GROUP BY t1.a,t1.c +HAVING (MAX(t1.b) = 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 14", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +# +# Pushdown from HAVING into non-empty WHERE +# +# inequality : inequality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +a b MAX(t1.c) +1 14 3 +2 13 2 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +a b MAX(t1.c) +1 14 3 +2 13 2 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b > 2 and t1.a < 3" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) AND (t1.a < 3) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b > 2 and t1.a < 3" + } + } + } + } +} +# equality : inequality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +a b MAX(t1.c) +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +a b MAX(t1.c) +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 3 and t1.b > 2" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) AND (t1.a = 3) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 3 and t1.b > 2" + } + } +} +# inequality : equality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 3" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) AND (t1.a < 3) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 3" + } + } + } + } +} +# equality : equality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.b = 14" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a = 1" + } + } +} +# equality : equality in WHERE (equal through constant) +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = 1 and t1.a = 1" + } + } +} +# inequality : AND formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c > 0 and t1.c < 3 and t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a > 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c > 0 and t1.c < 3 and t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 0 and t1.c < 3 and t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) AND (t1.a > 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 0 and t1.c < 3 and t1.a > 1" + } + } + } + } +} +# equality : AND formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c > 0 and t1.c < 3" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c > 0 and t1.c < 3" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "const_condition": "1", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c < 3" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c < 3" + } + } +} +# inequality : OR formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND (t1.a < 2) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 2" + } + } + } + } +} +# AND formula : inequality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +a MAX(t1.b) c +2 13 2 +3 14 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +a MAX(t1.b) c +2 13 2 +3 14 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 4 and t1.a > 0" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.a < 4) AND (t1.a > 0) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 4 and t1.a > 0" + } + } + } + } +} +# OR formula : inequality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +a MAX(t1.b) c +2 13 2 +3 14 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +a MAX(t1.b) c +2 13 2 +3 14 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and (t1.a < 4 or t1.a > 0)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND ((t1.a < 4) OR (t1.a > 0)) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and (t1.a < 4 or t1.a > 0)" + } + } + } + } +} +# OR formula : equality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +a MAX(t1.b) c +1 22 3 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +a MAX(t1.b) c +1 22 3 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "const_condition": "1", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND ((t1.a < 4) OR (t1.a > 0)) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } +} +# AND formula : AND formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.c < 3 and t1.a < 4" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) AND (t1.c < 3)) AND +(t1.a < 4) +GROUP BY t1.a +HAVING (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.c < 3 and t1.a < 4" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.c < 3 and t1.a < 4 and t1.c > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) AND (t1.c < 3)) AND +((t1.a < 4) AND (t1.c > 1)) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.c < 3 and t1.a < 4 and t1.c > 1" + } + } + } + } +} +# AND formula : OR formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND +(t1.a < 4) +GROUP BY t1.a +HAVING (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +3 13 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +3 13 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4 and t1.c > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND +(t1.a < 4) AND (t1.c > 1) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4 and t1.c > 1" + } + } + } + } +} +# OR formula : OR formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.a < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) OR (t1.c > 1); +a MAX(t1.b) c +1 22 1 +1 14 3 +2 13 2 +3 14 2 +3 13 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.a < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) OR (t1.c > 1); +a MAX(t1.b) c +1 22 1 +1 14 3 +2 13 2 +3 14 2 +3 13 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.a < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) OR (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.a < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) OR (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.a < 3) and (t1.a < 4 or t1.c > 1)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.a < 3)) AND +((t1.a < 4) OR (t1.c > 1)) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.a < 3) and (t1.a < 4 or t1.c > 1)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +a MAX(t1.b) c +1 14 3 +3 14 2 +3 13 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +a MAX(t1.b) c +1 14 3 +3 14 2 +3 13 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.a = 3) and (t1.a = 4 or t1.c > 1)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a = 1) OR (t1.a = 3)) AND +((t1.a = 4) OR (t1.c > 1)) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.a = 3) and (t1.a = 4 or t1.c > 1)" + } + } + } + } +} +# equality : pushdown through equality +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND (t1.c = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +# OR formula : pushdown through equality +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND +((t1.c = 1) OR (t1.c = 2)) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +# AND formula : pushdown through equality +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +a MAX(t1.b) c +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +a MAX(t1.b) c +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE noticed after reading const tables" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) AND +(t1.a > 2) AND (t1.a = t1.c) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +a MAX(t1.b) c +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +a MAX(t1.b) c +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE noticed after reading const tables" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) AND +(t1.a > 2) AND (t1.a = t1.c) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +# prepare statement +PREPARE stmt1 from " +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) +"; +execute stmt1; +a MAX(t1.b) c +1 22 3 +execute stmt1; +a MAX(t1.b) c +1 22 3 +deallocate prepare stmt1; +DROP TABLE t1; diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test index 2fbb5708c50..3b556a01687 100644 --- a/mysql-test/main/having_cond_pushdown.test +++ b/mysql-test/main/having_cond_pushdown.test @@ -486,3 +486,673 @@ EXPLAIN SELECT a FROM t1 WHERE b = 1 AND b = 2 GROUP BY a HAVING a <= 3; DROP TABLE t1; + +--echo # +--echo # MDEV-18769: unfixed OR condition pushed from HAVING into WHERE +--echo # + +CREATE TABLE t1(a INT, b INT, c INT); +INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2); + +--echo # nothing to push +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +eval $no_pushdown explain format=json $query; + +--echo # extracted AND formula +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +--echo # extracted AND formula : equality in the inner AND formula +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +--echo # extracted OR formula +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2) OR (t1.b = 13 AND t1.a > 2) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown explain format=json $query; + +--echo # extracted OR formula : one multiple equality in the left part +--echo of OR condition, part of it pushed + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown explain format=json $query; + +--echo # extracted OR formula : two multiple equalities in the left part +--echo of OR condition, one pushed +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : equality pushdown +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (MAX(t1.c) = 3); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : equalities pushdown +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) +GROUP BY t1.a,t1.c +HAVING (MAX(t1.b) = 14); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : multiple equality consists of +--echo two equalities pushdown +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 1) +GROUP BY t1.a,t1.c +HAVING (MAX(t1.b) = 14); +eval $no_pushdown explain format=json $query; + +--echo # +--echo # Pushdown from HAVING into non-empty WHERE +--echo # + +--echo # inequality : inequality in WHERE +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) AND (t1.a < 3) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : inequality in WHERE +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) AND (t1.a = 3) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # inequality : equality in WHERE +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) AND (t1.a < 3) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : equality in WHERE +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : equality in WHERE (equal through constant) +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # inequality : AND formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a > 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) AND (t1.a > 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : AND formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # inequality : OR formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND (t1.a < 2) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # AND formula : inequality in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.a < 4) AND (t1.a > 0) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # OR formula : inequality in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND ((t1.a < 4) OR (t1.a > 0)) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # OR formula : equality in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND ((t1.a < 4) OR (t1.a > 0)) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # AND formula : AND formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) AND (t1.c < 3)) AND + (t1.a < 4) +GROUP BY t1.a +HAVING (t1.c > 1); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) AND (t1.c < 3)) AND + ((t1.a < 4) AND (t1.c > 1)) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +--echo # AND formula : OR formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND + (t1.a < 4) +GROUP BY t1.a +HAVING (t1.c > 1); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND + (t1.a < 4) AND (t1.c > 1) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +--echo # OR formula : OR formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.a < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) OR (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.a < 3)) AND + ((t1.a < 4) OR (t1.c > 1)) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a = 1) OR (t1.a = 3)) AND + ((t1.a = 4) OR (t1.c > 1)) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +--echo # equality : pushdown through equality +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND (t1.c = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # OR formula : pushdown through equality +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND + ((t1.c = 1) OR (t1.c = 2)) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # AND formula : pushdown through equality +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) AND + (t1.a > 2) AND (t1.a = t1.c) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) AND + (t1.a > 2) AND (t1.a = t1.c) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # prepare statement +PREPARE stmt1 from " +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) +"; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +DROP TABLE t1; diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result index ce14b68ae2d..a0421bae922 100644 --- a/mysql-test/main/union.result +++ b/mysql-test/main/union.result @@ -2332,7 +2332,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select 1 AS `1`,2 AS `2` union all /* select#2 */ select 1 AS `i`,count(0) AS `COUNT(*)` from `test`.`t2` where 0 group by 1 having multiple equal(10, `i`) +Note 1003 /* select#1 */ select 1 AS `1`,2 AS `2` union all /* select#2 */ select 1 AS `i`,count(0) AS `COUNT(*)` from `test`.`t2` where 0 group by 1 having 1 DROP TABLE t1,t2; # # Start of 10.3 tests diff --git a/sql/item.h b/sql/item.h index 0ffbd2f8bc4..b382272863e 100644 --- a/sql/item.h +++ b/sql/item.h @@ -2077,6 +2077,10 @@ class Item: public Value_source, { return this; } virtual Item *in_predicate_to_in_subs_transformer(THD *thd, uchar *arg) { return this; } + virtual Item *field_transformer_for_having_pushdown(THD *thd, uchar *arg) + { return this; } + virtual Item *multiple_equality_transformer(THD *thd, uchar *arg) + { return this; } virtual bool expr_cache_is_needed(THD *) { return FALSE; } virtual Item *safe_charset_converter(THD *thd, CHARSET_INFO *tocs); bool needs_charset_converter(uint32 length, CHARSET_INFO *tocs) const @@ -5350,6 +5354,8 @@ class Item_ref :public Item_ident, } bool with_sum_func() const { return m_with_sum_func; } With_sum_func_cache* get_with_sum_func_cache() { return this; } + Item *field_transformer_for_having_pushdown(THD *thd, uchar *arg) + { return (*ref)->field_transformer_for_having_pushdown(thd, arg); } }; @@ -5742,6 +5748,8 @@ class Item_direct_view_ref :public Item_direct_ref } Item *get_copy(THD *thd) { return get_item_copy<Item_direct_view_ref>(thd, this); } + Item *field_transformer_for_having_pushdown(THD *thd, uchar *arg) + { return this; } }; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 86725de8ae7..1463e1bf2cf 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -7378,31 +7378,37 @@ Item_equal::excl_dep_on_group_fields_for_having_pushdown(st_select_lex *sel) /** @brief - Create from this multiple equality equalities that can be pushed down + Transform multiple equality into the list of equalities @param thd the thread handle - @param equalities the result list of created equalities + @param equalities the list where created equalities are stored @param checker the checker callback function to be applied to the nodes - of the tree of the object + of the tree of the object to check if multiple equality + elements can be used to create equalities @param arg parameter to be passed to the checker @details - The method traverses this multiple equality trying to create from it - new equalities that can be pushed down. It creates equalities with - the constant used in this multiple equality if it exists or the first - item for which checker returns non-NULL result and all other items - in this multiple equality for which checker returns non-NULL result. + The method transforms multiple equality into the list of equalities in + such way: + it goes through the elements of the multiple equality and checks with + checker if this elements can be used in equality creation. + If the element can be used the method creates equality using this + element and: - Example: + 1. Constant, if there is a constant defined in this multiple equality. MULT_EQ(1,a,b) => Created equalities: {(1=a),(1=b)} + or 2. The first element in this multiple equality that is passed + by checker. + MULT_EQ(a,b,c,d) => Created equalities: {(a=b),(a=c),(a=d)} + All created equalities are collected into the equalities list. @retval true if an error occurs @retval false otherwise @@ -7450,3 +7456,36 @@ bool Item_equal::create_pushable_equalities(THD *thd, } return false; } + + +/** + Transform multiple equality into the AND condition of equalities. + + MULT_EQ(1,a,b) + => + (a = 1) AND (b = 1) + + Equalities are built in Item_equal::create_pushable_equalities() method + using elements of this multiple equality. +*/ + +Item *Item_equal::multiple_equality_transformer(THD *thd, uchar *arg) +{ + List<Item> equalities; + Pushdown_checker checker= + &Item::pushable_equality_checker_for_having_pushdown; + if (create_pushable_equalities(thd, &equalities, checker, (uchar *)this)) + return 0; + + switch (equalities.elements) + { + case 0: + return 0; + case 1: + return equalities.head(); + break; + default: + return new (thd->mem_root) Item_cond_and(thd, equalities); + break; + } +} diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index d78977544c7..7145fcf3bfa 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -3207,6 +3207,7 @@ class Item_equal: public Item_bool_func uint elements_count() { return equal_items.elements; } friend class Item_equal_fields_iterator; bool count_sargable_conds(void *arg); + Item *multiple_equality_transformer(THD *thd, uchar *arg); friend class Item_equal_iterator<List_iterator_fast,Item>; friend class Item_equal_iterator<List_iterator,Item>; friend Item *eliminate_item_equal(THD *thd, COND *cond, diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index ddccddd55ff..a49c56e49e1 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5673,16 +5673,12 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, It checks if after the merge the multiple equalities are knowingly true or false equalities. It attaches to cond the conditions from new_conds list and the result - of the merge of multiple equalities. The multiple equalities are - attached only to the upper level of AND-condition cond. So they - should be pushed down to the inner levels of cond AND-condition - if needed. It is done by propagate_new_equalities(). + of the merge of multiple equalities. */ COND_EQUAL *cond_equal= &((Item_cond_and *) cond)->m_cond_equal; List<Item_equal> *cond_equalities= &cond_equal->current_level; List<Item> *and_args= ((Item_cond_and *)cond)->argument_list(); and_args->disjoin((List<Item> *) cond_equalities); - and_args->append(&new_conds); while ((equality= it++)) { @@ -5692,22 +5688,40 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, List_iterator_fast<Item_equal> ei(*cond_equalities); while ((equality= ei++)) { - if (equality->const_item() && !equality->val_int()) - is_simplified_cond= true; - equality->fixed= 0; + equality->unfix_fields(); if (equality->fix_fields(thd, NULL)) return NULL; } + li.rewind(); + while ((item=li++)) + { + /** + If and_new_conditions_to_optimized_cond() is called for + HAVING pushdown optimization there can be some equalities + on inner levels of new_conds elements that are still not + transformed into the multiple equalities. + To transform them build_equal_items() is called. + */ + if (thd->having_pushdown && + item->type() == Item::COND_ITEM && + ((Item_cond *)item)->functype() == Item_func::COND_OR_FUNC) + { + COND_EQUAL *cond_eq_or= 0; + item= item->build_equal_items(thd, + &((Item_cond_and *) cond)->m_cond_equal, + MY_TEST(OPT_LINK_EQUAL_FIELDS), + &cond_eq_or); + item->unfix_fields(); + if (item->fix_fields(thd, NULL)) + return NULL; + } + if (item->const_item() && !item->val_int()) + is_simplified_cond= true; + and_args->push_back(item, thd->mem_root); + } and_args->append((List<Item> *) cond_equalities); *cond_eq= &((Item_cond_and *) cond)->m_cond_equal; - - propagate_new_equalities(thd, cond, cond_equalities, - cond_equal->upper_levels, - &is_simplified_cond); - cond= cond->propagate_equal_fields(thd, - Item::Context_boolean(), - cond_equal); } else { @@ -5737,67 +5751,104 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, new_conds_list.push_back(cond, thd->mem_root)) return NULL; - if (new_conds.elements > 0) - { - li.rewind(); - while ((item=li++)) - { - if (!item->is_fixed() && item->fix_fields(thd, NULL)) - return NULL; - if (item->const_item() && !item->val_int()) - is_simplified_cond= true; - } - new_conds_list.append(&new_conds); - } - if (is_mult_eq) { Item_equal *eq_cond= (Item_equal *)cond; eq_cond->upper_levels= 0; eq_cond->merge_into_list(thd, &new_cond_equal.current_level, false, false); + } - while ((equality= it++)) - { - if (equality->const_item() && !equality->val_int()) - is_simplified_cond= true; - } + List_iterator_fast<Item_equal> ei(new_cond_equal.current_level); + while ((equality=ei++)) + { + equality->unfix_fields(); + if (equality->fix_fields(thd, NULL)) + return NULL; + } - if (new_cond_equal.current_level.elements + - new_conds_list.elements == 1) + Item_cond_and *and_cond= 0; + COND_EQUAL *inherited= 0; + if (new_conds_list.elements + + new_conds.elements + + new_cond_equal.current_level.elements > 1) + { + and_cond= new (thd->mem_root) Item_cond_and(thd); + and_cond->m_cond_equal.copy(new_cond_equal); + inherited= &and_cond->m_cond_equal; + } + + li.rewind(); + while ((item=li++)) + { + /** + If and_new_conditions_to_optimized_cond() is called for + HAVING pushdown optimization there can be some equalities + on inner levels of new_conds elements that are still not + transformed into the multiple equalities. + To transform them build_equal_items() is called. + */ + if (thd->having_pushdown && + item->type() == Item::COND_ITEM && + ((Item_cond *)item)->functype() == Item_func::COND_OR_FUNC) { - it.rewind(); - equality= it++; - equality->fixed= 0; - if (equality->fix_fields(thd, NULL)) + COND_EQUAL *cond_eq_or= 0; + item= item->build_equal_items(thd, + inherited, + MY_TEST(OPT_LINK_EQUAL_FIELDS), + &cond_eq_or); + item->unfix_fields(); + if (item->fix_fields(thd, NULL)) return NULL; } - (*cond_eq)->copy(new_cond_equal); + if (item->const_item() && !item->val_int()) + is_simplified_cond= true; + new_conds_list.push_back(item, thd->mem_root); } new_conds_list.append((List<Item> *)&new_cond_equal.current_level); - if (new_conds_list.elements > 1) + if (and_cond) { - Item_cond_and *and_cond= - new (thd->mem_root) Item_cond_and(thd, new_conds_list); - - and_cond->m_cond_equal.copy(new_cond_equal); + and_cond->argument_list()->append(&new_conds_list); cond= (Item *)and_cond; - *cond_eq= &((Item_cond_and *)cond)->m_cond_equal; + *cond_eq= &((Item_cond_and *) cond)->m_cond_equal; } else { List_iterator_fast<Item> iter(new_conds_list); cond= iter++; + if (cond->type() == Item::FUNC_ITEM && + ((Item_func *)cond)->functype() == Item_func::MULT_EQUAL_FUNC) + { + if (!(*cond_eq)) + *cond_eq= new COND_EQUAL(); + (*cond_eq)->copy(new_cond_equal); + } + else + *cond_eq= 0; } + } - if (!cond->is_fixed() && cond->fix_fields(thd, NULL)) - return NULL; + if (!cond) + return NULL; - if (new_cond_equal.current_level.elements > 0) - cond= cond->propagate_equal_fields(thd, - Item::Context_boolean(), - &new_cond_equal); + if (cond->fix_fields_if_needed(thd, NULL)) + return NULL; + + if (*cond_eq) + { + /** + The multiple equalities are attached only to the upper level + of AND-condition cond. So they hould be pushed down to the + inner levels of cond AND-condition if needed. + */ + propagate_new_equalities(thd, cond, + &(*cond_eq)->current_level, + 0, + &is_simplified_cond); + cond= cond->propagate_equal_fields(thd, + Item::Context_boolean(), + *cond_eq); } /* @@ -5805,7 +5856,7 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, true or false equalities the method calls removes_eq_cond() to remove them from cond and set the cond_value to the appropriate value. */ - if (is_simplified_cond) + if (cond && is_simplified_cond) cond= cond->remove_eq_conds(thd, cond_value, true); return cond; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index c204f96f303..1b1e0c9f135 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -9721,11 +9721,11 @@ void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond, @param cond The condition that should be marked (or its subformulas) @details - In the case when OR-condition can be pushed into the HAVING clause - of the materialized derived table/view/IN subquery and some of - its parts can be pushed into the WHERE clause it can cause - repeatable pushdown in the pushdown from HAVING into WHERE clause. - Example: + Consider pushdown into the materialized derived table/view/IN subquery. + Consider OR condition that can be pushed into HAVING and some + parts of this OR condition that can be pushed into WHERE. + + On example: SELECT * FROM t1, @@ -9736,8 +9736,15 @@ void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond, WHERE ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) AND (t1.a=v1.a); - after the pushdown into the materialized views/derived tables optimization - is done: + + Here ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) or1 + can be pushed down into the HAVING of the materialized + derived table dt. + + (dt.a>2) OR (dt.a<3) part of or1 depends only on grouping fields + of dt and can be pushed into WHERE. + + As a result: SELECT * FROM t1, @@ -9750,15 +9757,15 @@ void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond, WHERE ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) AND (t1.a=v1.a); - In the optimization stage for the select that defines derived table - in the pushdown from HAVING into WHERE optimization - (dt.a>2) OR (dt.a<3) will be again extracted from - ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) - and pushed into the WHERE clause of the select that defines derived table. - To avoid it after conditions are pushed into the materialized derived - tables/views or IN subqueries OR-conditions that were pushed are marked - with NO_EXTRACTION_FL flag to avoid repeatable pushdown. + Here (dt.a>2) OR (dt.a<3) also remains in HAVING of dt. + When SELECT that defines df is processed HAVING pushdown optimization + is made. In HAVING pushdown optimization it will extract + (dt.a>2) OR (dt.a<3) condition from or1 again and push it into WHERE. + This will cause duplicate conditions in WHERE of dt. + + To avoid repeatable pushdown such OR conditions as or1 describen + above are marked with NO_EXTRACTION_FL. */ void st_select_lex::mark_or_conds_to_avoid_pushdown(Item *cond) @@ -9786,31 +9793,53 @@ void st_select_lex::mark_or_conds_to_avoid_pushdown(Item *cond) /** @brief - Gets conditions that can be pushed down for pushdown from HAVING into WHERE + Get conditions that can be pushed from HAVING into WHERE - @param thd The thread handle - @param cond The condition from which the condition depended on grouping - fields is to be extracted - @param checker The checker callback function to be applied to the nodes - of the tree of the object + @param thd The thread handle + @param cond The condition from which the condition that + can be pushed is extracted + @param conds_moved_from_where The map of conditions that are extracted from + WHERE @details - The method finds out what conditions can be extracted from cond depended - only on the grouping fields of this SELECT or fields equal to them. - If the condition that can be pushed is AND-condition it is splitted up - and for each its element it is checked if it can be pushed. - Pushable elements are attached to the attach_to_conds list. - If the condition isn't AND-condition it is entirely pushed into - the attach_to_conds list. If the condition that is extracted is a multiple - equality it is transformed into the set of equalities. - - attach_to_conds list is created to be passed to - and_new_conditions_to_optimized_cond() method so extracted conditions can - be joined to the already optimized WHERE clause in the right way. - - @note - The method is similar to st_select_lex::build_cond_for_grouping_fields() and - Item::build_pushable_cond(). + The method collects in attach_to_conds list conditions from cond + that can be pushed from HAVING into WHERE. + Conditions that can be pushed are collected in attach_to_conds in this way: + 1. if cond is an AND condition its parts that can be pushed into WHERE + are added to attach_to_conds list separately. + 2. in all other cases conditions are pushed into the list entirely. + + Conditions that can be pushed were marked with FULL_EXTRACTION_FL in + check_cond_extraction_for_grouping_fields() method. + Conditions that can't be pushed were marked with NO_EXTRACTION_FL. + Conditions which parts can be pushed weren't marked. + + There are two types of conditions that can be pushed: + 1. Condition that can be simply moved from HAVING + (if cond is marked with FULL_EXTRACTION_FL or + cond is an AND condition and some of its parts are marked with + FULL_EXTRACTION_FL) + In this case condition is transformed and pushed into attach_to_conds + list. + 2. Part of some other condition c1 that can't be entirely pushed + (if с1 isn't marked with any flag). + + For example: + + SELECT t1.a,MAX(t1.b),t1.c + FROM t1 + GROUP BY t1.a + HAVING ((t1.a > 5) AND (t1.c < 3)) OR (t1.a = 3); + + Here (t1.a > 5) OR (t1.a = 3) in HAVING can be pushed into WHERE. + + In this case build_pushable_cond() is called for c1. + This method builds a clone of the c1 part that is marked with + FULL_EXTRACTION_FL flag. This clone is added to attach_to_conds list. + + Transformation mentioned above is made with multiple_equality_transformer + transformer. It transforms all multiple equalities in the extracted + condition into the set of equalities. @retval true - if an error occurs @@ -9819,92 +9848,92 @@ void st_select_lex::mark_or_conds_to_avoid_pushdown(Item *cond) bool st_select_lex::build_pushable_cond_for_having_pushdown(THD *thd, - Item *cond) + Item *cond, Bitmap<64> *conds_moved_from_where) { + conds_moved_from_where->clear_all(); + List<Item> equalities; Pushdown_checker checker= &Item::pushable_equality_checker_for_having_pushdown; - - bool is_multiple_equality= cond->type() == Item::FUNC_ITEM && - ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC; + bool and_cond= + (cond->type() == Item::COND_ITEM && + ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) + ? true : false; if (cond->get_extraction_flag() == NO_EXTRACTION_FL) return false; - if (cond->type() == Item::COND_ITEM) + if (cond->get_extraction_flag() == FULL_EXTRACTION_FL) { - bool cond_and= false; - if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) - cond_and= true; - List<Item> equalities; - List<Item> new_conds; - List_iterator<Item> li(*((Item_cond*) cond)->argument_list()); - Item *item; - - while ((item=li++)) + Item *result= cond->transform(thd, + &Item::multiple_equality_transformer, + (uchar *)this); + if (!result) + return true; + if (!and_cond) { - if (item->get_extraction_flag() == NO_EXTRACTION_FL) - continue; - - if (item->type() == Item::FUNC_ITEM && - ((Item_func*) item)->functype() == Item_func::MULT_EQUAL_FUNC) + if (attach_to_conds.push_back(result, thd->mem_root)) + return true; + conds_moved_from_where->set_bit(attach_to_conds.elements - 1); + } + else + { + List_iterator<Item> li(*((Item_cond*) cond)->argument_list()); + Item *item; + while ((item=li++)) { - equalities.empty(); - if (((Item_equal*) item)->create_pushable_equalities(thd, &equalities, - checker, (uchar *)this)) + if (attach_to_conds.push_back(item, thd->mem_root)) return true; - if (equalities.elements != 0) - { - if (cond_and) - new_conds.append(&equalities); - else - { - Item_cond_and *new_cond= - new (thd->mem_root) Item_cond_and(thd, equalities); - if (!new_cond || new_conds.push_back(new_cond, thd->mem_root)) - return true; - } - } - else if (!cond_and) - return true; - continue; + conds_moved_from_where->set_bit(attach_to_conds.elements - 1); } + } + return false; + } - Item *fix= item->build_pushable_cond(thd, checker, (uchar *)this); - - if (!fix && !cond_and) - { - attach_to_conds.empty(); - return false; - } + if (cond->type() == Item::COND_ITEM) + { + if (!and_cond) + { + Item *fix= cond->build_pushable_cond(thd, checker, (uchar *)this); if (!fix) - continue; - - if (new_conds.push_back(fix, thd->mem_root)) + return false; + if (attach_to_conds.push_back(fix, thd->mem_root)) return true; } - if (!cond_and) + else { - Item_cond_or *new_cond= new (thd->mem_root) Item_cond_or(thd, new_conds); - if (attach_to_conds.push_back(new_cond, thd->mem_root)) - return true; + List_iterator<Item> li(*((Item_cond*) cond)->argument_list()); + Item *item; + while ((item=li++)) + { + if (item->get_extraction_flag() == NO_EXTRACTION_FL) + continue; + else if (item->get_extraction_flag() == FULL_EXTRACTION_FL) + { + Item *result= item->transform(thd, + &Item::multiple_equality_transformer, + (uchar *)item); + if (!result || attach_to_conds.push_back(result, thd->mem_root)) + return true; + conds_moved_from_where->set_bit(attach_to_conds.elements - 1); + } + else + { + Item *fix= item->build_pushable_cond(thd, checker, (uchar *)this); + if (!fix) + continue; + if (attach_to_conds.push_back(fix, thd->mem_root)) + return true; + } + } } - else - attach_to_conds.append(&new_conds); - } - else if (is_multiple_equality) - { - List<Item> equalities; - Item_equal *item_equal= (Item_equal *)cond; - if (item_equal->create_pushable_equalities(thd, &equalities, - checker, (uchar *)this)) - return true; - attach_to_conds.append(&equalities); return false; } - else if (cond->get_extraction_flag() != NO_EXTRACTION_FL) + else { - Item *copy= cond->build_clone(thd); - if (attach_to_conds.push_back(copy, thd->mem_root)) + Item *fix= cond->build_pushable_cond(thd, checker, (uchar *)this); + if (!fix) + return false; + if (attach_to_conds.push_back(fix, thd->mem_root)) return true; } return false; @@ -9912,7 +9941,7 @@ st_select_lex::build_pushable_cond_for_having_pushdown(THD *thd, /** - Check if the item is equal to some field in Field_pair 'field_pair' + Check if item is equal to some field in Field_pair 'field_pair' from 'pair_list' and return found 'field_pair' if it exists. */ @@ -9938,15 +9967,17 @@ Field_pair *get_corresponding_field_pair(Item *item, /** @brief - Collect fields in multiple equalities usable for pushdown from having + Collect fields from multiple equalities which fields are equal to grouping @param thd The thread handle @details - This method looks through the multiple equalities of the WHERE clause - trying to find any of them whose fields are used in the GROUP BY of the - SELECT. Any field from these multiple equality is included into the - the list of fields against which any candidate for pushing is checked. + This method checks multiple equalities of the WHERE clause of this SELECT + trying to find those of them which has at least one element that + participates in the GROUP BY of this select. + + If such multiple equality is found the method saves information on + all elements of this multiple equality. @retval true - if an error occurs @@ -9987,48 +10018,6 @@ bool st_select_lex::collect_fields_equal_to_grouping(THD *thd) return false; } -/** - @brief - Cleanup and fix of the condition that is ready to be pushed down - - @param thd The thread handle - @param cond The condition to be processed - - @details - This method recursively traverses cond making cleanup and fix - where needed. - There is no need to make cleanup and fix for multiple equalities as - they are created so they can be immediately pushed down. - - @retval - true - if an error occurs - false - otherwise -*/ - -static -bool cleanup_condition_pushed_from_having(THD *thd, Item *cond) -{ - if (cond->type() == Item::FUNC_ITEM && - ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC) - return false; - - if (cond->type() == Item::COND_ITEM) - { - List_iterator_fast<Item> it(*((Item_cond *)cond)->argument_list()); - Item *item; - - while ((item=it++)) - cleanup_condition_pushed_from_having(thd, item); - } - else - { - cond->walk(&Item::cleanup_excluding_const_fields_processor, 0, 0); - if (cond->fix_fields(thd, NULL)) - return true; - } - return false; -} - /** @brief @@ -10038,17 +10027,8 @@ bool cleanup_condition_pushed_from_having(THD *thd, Item *cond) @param cond The condition which subformulas are to be removed @details - The function behavior is similar to remove_pushed_top_conjuncts() - except the case when 'cond' is the AND-condition. - As in the pushdown from HAVING into WHERE conditions are not just cloned - so they can be later pushed down as it is for pushdown into materialized - derived tables/views or IN subqueries, but also should be removed from - the HAVING clause. - The multiple equalities of the HAVING clause are not removed in this - function, but rather marked as to be removed later. Their removal is - done in substitute_for_best_equal_field() called for HAVING at the moment - when all multiple equalities referencing the top level multiple equalities - have been already eliminated. + This method removes from cond all subformulas that can be moved from HAVING + into WHERE. @retval condition without removed subformulas @@ -10057,43 +10037,44 @@ bool cleanup_condition_pushed_from_having(THD *thd, Item *cond) Item *remove_pushed_top_conjuncts_for_having(THD *thd, Item *cond) { + if (cond->get_extraction_flag() == NO_EXTRACTION_FL) + { + cond->clear_extraction_flag(); + return cond; + } if (cond->get_extraction_flag() == FULL_EXTRACTION_FL) { cond->clear_extraction_flag(); - if (cond->type() == Item::FUNC_ITEM && - ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC) - { - cond->set_extraction_flag(DELETION_FL); - return cond; - } return 0; } - if (cond->type() != Item::COND_ITEM) - return cond; - if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) + if (cond->type() == Item::COND_ITEM && + ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) { - List<Item> *cond_arg_list= ((Item_cond_and *)cond)->argument_list(); - List_iterator<Item> li(*cond_arg_list); + List_iterator<Item> li(*((Item_cond*) cond)->argument_list()); Item *item; - while ((item= li++)) + while ((item=li++)) { - if (item->get_extraction_flag() == FULL_EXTRACTION_FL) - { + if (item->get_extraction_flag() == NO_EXTRACTION_FL) item->clear_extraction_flag(); + else if (item->get_extraction_flag() == FULL_EXTRACTION_FL) + { if (item->type() == Item::FUNC_ITEM && ((Item_func*) item)->functype() == Item_func::MULT_EQUAL_FUNC) item->set_extraction_flag(DELETION_FL); else + { + item->clear_extraction_flag(); li.remove(); + } } } - switch (cond_arg_list->elements) + switch (((Item_cond*) cond)->argument_list()->elements) { case 0: return 0; case 1: - return (cond_arg_list->head()); + return (((Item_cond*) cond)->argument_list()->head()); default: return cond; } @@ -10104,19 +10085,20 @@ Item *remove_pushed_top_conjuncts_for_having(THD *thd, Item *cond) /** @brief - Extract condition that can be pushed from HAVING clause into WHERE clause + Extract condition that can be pushed from HAVING into WHERE - @param thd the thread handle - @param having the HAVING clause of this select - @param having_equal multiple equalities of HAVING + @param thd The thread handle + @param having The HAVING clause of this select + @param having_equal Multiple equalities of HAVING @details - This function builds the most restrictive condition depending only on - the fields used in the GROUP BY of this select (directly or indirectly + This method builds a set of the conditions dependent only on + fields used in the GROUP BY of this select (directly or indirectly through equality) that can be extracted from the HAVING clause of this - select and pushes it into the WHERE clause of this select. + select. It saves these conditions into attach_to_conds list and removes + some of them from HAVING. - Example of the transformation: + Example of the HAVING pushdown transformation: SELECT t1.a,MAX(t1.b) FROM t1 @@ -10131,14 +10113,23 @@ Item *remove_pushed_top_conjuncts_for_having(THD *thd, Item *cond) GROUP BY t1.a HAVING (MAX(c)>12); + In this method (t1.a>2) is not attached to the WHERE clause. + It is pushed into the attach_to_conds list to be attached to + the WHERE clause later. + In details: 1. Collect fields used in the GROUP BY grouping_fields of this SELECT 2. Collect fields equal to grouping_fields from the WHERE clause of this SELECT and add them to the grouping_fields list. 3. Extract the most restrictive condition from the HAVING clause of this select that depends only on the grouping fields (directly or indirectly - through equality). Store it in the attach_to_conds list. + through equality). + + Partition it in parts if possible and store these parts in + the attach_to_conds list. + Condition is partitioned if it is AND condition. 4. Remove pushable conditions from the HAVING clause if it's possible. + 5. Unwrap fields of pushable conditions so they can be pushed into WHERE. @note This method is similar to st_select_lex::pushdown_cond_into_where_clause(). @@ -10169,14 +10160,16 @@ Item *st_select_lex::pushdown_from_having_into_where(THD *thd, Item *having) /* 3. Extract the most restrictive condition from the HAVING clause of this select that depends only on the grouping fields (directly or indirectly - through equality). Store it in the attach_to_conds list. + through equality). */ thd->having_pushdown= true; List_iterator_fast<Item> it(attach_to_conds); Item *item; + Bitmap<64> conds_moved_from_where; check_cond_extraction_for_grouping_fields(thd, having, &Item::dep_on_grouping_fields_checker_for_having_pushdown); - if (build_pushable_cond_for_having_pushdown(thd, having)) + if (build_pushable_cond_for_having_pushdown(thd, having, + &conds_moved_from_where)) { attach_to_conds.empty(); goto exit; @@ -10187,15 +10180,27 @@ Item *st_select_lex::pushdown_from_having_into_where(THD *thd, Item *having) 4. Remove pushable conditions from the HAVING clause if it's possible. */ having= remove_pushed_top_conjuncts_for_having(thd, having); - it.rewind(); + uint i= 0; while ((item=it++)) { - if (cleanup_condition_pushed_from_having(thd, item)) + /* + 5. Unwrap fields of pushable conditions so they can be pushed + into WHERE. + */ + item= item->transform(thd, + &Item::field_transformer_for_having_pushdown, + (uchar *)this); + + if (conds_moved_from_where.is_set(i)) + item->walk(&Item::cleanup_processor, 0, 0); + item->unfix_fields(); + if (item->fix_fields_if_needed(thd, NULL)) { attach_to_conds.empty(); goto exit; } + i++; } /* Refresh having_equal as some of the multiple equalities of @@ -10218,7 +10223,6 @@ Item *st_select_lex::pushdown_from_having_into_where(THD *thd, Item *having) } exit: thd->lex->current_select= save_curr_select; - thd->having_pushdown= false; return having; } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index b78a010d4b7..b62841284a2 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1522,7 +1522,7 @@ class st_select_lex: public st_select_lex_node { return !olap && !explicit_limit && !tvc; } bool build_pushable_cond_for_having_pushdown(THD *thd, - Item *cond); + Item *cond, Bitmap<64> *conds_moved_from_where); void pushdown_cond_into_where_clause(THD *thd, Item *extracted_cond, Item **remaining_cond, Item_transformer transformer, diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b5b69a8f6c0..aef2c01c8a3 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1949,10 +1949,9 @@ JOIN::optimize_inner() conds= and_new_conditions_to_optimized_cond(thd, conds, &cond_equal, select_lex->attach_to_conds, &cond_value, true); - if (conds && !conds->is_fixed() && conds->fix_fields(thd, &conds)) - DBUG_RETURN(1); sel->attach_to_conds.empty(); } + thd->having_pushdown= false; } if (optimizer_flag(thd, OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_SUBQUERY))

1 0

[Commits] b286bde2ac3: MDEV-17847: Galera test failure on MW-328[A|B|C]
by jan 18 Mar '19

18 Mar '19

revision-id: b286bde2ac3b564b75cb66787454626a04e639c0 (mariadb-10.1.38-63-gb286bde2ac3) parent(s): 6c08174e365c1a2db76e51dedd2a8292464472d6 author: Jan Lindström committer: Jan Lindström timestamp: 2019-03-18 08:20:19 +0200 message: MDEV-17847: Galera test failure on MW-328[A|B|C] Infinite procedure eats all CPU time causing test timeout. Lets sleep very small amount of time after every update to avoid it. --- mysql-test/suite/galera/disabled.def | 3 --- mysql-test/suite/galera/r/MW-328A.result | 1 + mysql-test/suite/galera/r/MW-328B.result | 1 + mysql-test/suite/galera/r/MW-328C.result | 1 + mysql-test/suite/galera/t/MW-328-header.inc | 1 + mysql-test/suite/galera/t/MW-328A.test | 3 +-- mysql-test/suite/galera/t/MW-328B.test | 3 +-- mysql-test/suite/galera/t/MW-328C.test | 3 +-- 8 files changed, 7 insertions(+), 9 deletions(-) diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index 2c0ef3f8e20..c851b97d119 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -11,9 +11,6 @@ ############################################################################## GAL-419 : MDEV-13549 Galera test failures -MW-328A : MDEV-17847 Galera test failure on MW-328[A|B|C] -MW-328B : MDEV-17847 Galera test failure on MW-328[A|B|C] -MW-328C : MDEV-17847 Galera test failure on MW-328[A|B|C] MW-329 : wsrep_local_replays not stable MW-336 : MDEV-13549 Timeout in wait_condition.inc for PROCESSLIST MW-416 : MDEV-13549 Galera test failures diff --git a/mysql-test/suite/galera/r/MW-328A.result b/mysql-test/suite/galera/r/MW-328A.result index daed2469f11..48b51aa25a7 100644 --- a/mysql-test/suite/galera/r/MW-328A.result +++ b/mysql-test/suite/galera/r/MW-328A.result @@ -7,6 +7,7 @@ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; SET SESSION wsrep_sync_wait = 0; WHILE 1 DO UPDATE t1 SET f2 = LEFT(MD5(RAND()), 4); +DO SLEEP(0.1); END WHILE; END| CALL proc_update();; diff --git a/mysql-test/suite/galera/r/MW-328B.result b/mysql-test/suite/galera/r/MW-328B.result index 780988938f6..706940ba7af 100644 --- a/mysql-test/suite/galera/r/MW-328B.result +++ b/mysql-test/suite/galera/r/MW-328B.result @@ -7,6 +7,7 @@ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; SET SESSION wsrep_sync_wait = 0; WHILE 1 DO UPDATE t1 SET f2 = LEFT(MD5(RAND()), 4); +DO SLEEP(0.1); END WHILE; END| CALL proc_update();; diff --git a/mysql-test/suite/galera/r/MW-328C.result b/mysql-test/suite/galera/r/MW-328C.result index 5cd74f05171..889036b2a0b 100644 --- a/mysql-test/suite/galera/r/MW-328C.result +++ b/mysql-test/suite/galera/r/MW-328C.result @@ -7,6 +7,7 @@ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; SET SESSION wsrep_sync_wait = 0; WHILE 1 DO UPDATE t1 SET f2 = LEFT(MD5(RAND()), 4); +DO SLEEP(0.1); END WHILE; END| CALL proc_update();; diff --git a/mysql-test/suite/galera/t/MW-328-header.inc b/mysql-test/suite/galera/t/MW-328-header.inc index f0a6ccaccc6..b4fc1511549 100644 --- a/mysql-test/suite/galera/t/MW-328-header.inc +++ b/mysql-test/suite/galera/t/MW-328-header.inc @@ -18,6 +18,7 @@ BEGIN SET SESSION wsrep_sync_wait = 0; WHILE 1 DO UPDATE t1 SET f2 = LEFT(MD5(RAND()), 4); + DO SLEEP(0.1); END WHILE; END| diff --git a/mysql-test/suite/galera/t/MW-328A.test b/mysql-test/suite/galera/t/MW-328A.test index 09aad1bcf60..34d8dba1f19 100644 --- a/mysql-test/suite/galera/t/MW-328A.test +++ b/mysql-test/suite/galera/t/MW-328A.test @@ -9,11 +9,10 @@ --source include/big_test.inc --source include/galera_cluster.inc ---source include/have_innodb.inc --source suite/galera/t/MW-328-header.inc --connection node_2 ---let $count = 100 +--let $count = 200 --let $successes = 0 --let $deadlocks = 0 diff --git a/mysql-test/suite/galera/t/MW-328B.test b/mysql-test/suite/galera/t/MW-328B.test index 000b0d8a9ab..f43300d3d53 100644 --- a/mysql-test/suite/galera/t/MW-328B.test +++ b/mysql-test/suite/galera/t/MW-328B.test @@ -9,11 +9,10 @@ --source include/big_test.inc --source include/galera_cluster.inc ---source include/have_innodb.inc --source suite/galera/t/MW-328-header.inc --connection node_2 ---let $count = 100 +--let $count = 200 SET SESSION wsrep_retry_autocommit = 0; diff --git a/mysql-test/suite/galera/t/MW-328C.test b/mysql-test/suite/galera/t/MW-328C.test index 72a8480923c..a81071e5336 100644 --- a/mysql-test/suite/galera/t/MW-328C.test +++ b/mysql-test/suite/galera/t/MW-328C.test @@ -9,11 +9,10 @@ --source include/big_test.inc --source include/galera_cluster.inc ---source include/have_innodb.inc --source suite/galera/t/MW-328-header.inc --connection node_2 ---let $count = 100 +--let $count = 200 SET SESSION wsrep_retry_autocommit = 10000;

1 0

[Commits] a96b528e396: Merge branch 'tempesta-tech-sysprg/MDEV-17262' into 10.2
by jan 17 Mar '19

17 Mar '19

revision-id: a96b528e3968ea567fc40e553c00b491380a3e22 (mariadb-10.2.22-77-ga96b528e396) parent(s): e2c769b7106df10d6833863ecc5dfacef9eb88ce b748affcf0724b70b6a54c13b9fa617c118aa043 author: Jan Lindström committer: Jan Lindström timestamp: 2019-03-17 09:41:22 +0200 message: Merge branch 'tempesta-tech-sysprg/MDEV-17262' into 10.2 sql/field.cc | 1 - sql/item.cc | 1 - sql/sql_load.cc | 2 +- sql/wsrep_thd.cc | 9 +++++++++ 4 files changed, 10 insertions(+), 3 deletions(-)

1 0

[Commits] 5e044f7: MDEV-18945 Assertion `fixed == 1' failed in Item_cond_and::val_int
by IgorBabaev 16 Mar '19

16 Mar '19

revision-id: 5e044f78c0a9a8cd40dedff0e4bc857c0bd76b95 (mariadb-10.2.18-336-g5e044f7) parent(s): 1f020299f816263e347c852eb2a494b5ef1cbf0d author: Igor Babaev committer: Igor Babaev timestamp: 2019-03-16 15:08:17 -0700 message: MDEV-18945 Assertion `fixed == 1' failed in Item_cond_and::val_int In the function make_cond_for_table_from_pred a call of ix_fields() missed checking of the return code. As a result an extracted constant condition could be not well formed and this caused an assertion failure. --- mysql-test/r/update.result | 13 +++++++++++++ mysql-test/t/update.test | 19 +++++++++++++++++++ sql/sql_select.cc | 3 ++- 3 files changed, 34 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index 73ebb73..9e19abc 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -719,3 +719,16 @@ Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 drop table t1, t2; # End of MariaDB 10.0 tests +# +# MDEV-18945: multi-table update with constant table and +# comparison of date field with integer field +# +CREATE TABLE t1 (i1 int, d1 date , i2 int , d2 date) engine=myisam; +INSERT INTO t1 VALUES (19,'0000-00-00',73,'2008-05-21'); +CREATE TABLE t2 (d1 date , i1 int, i2 int , d2 date) engine=myisam; +INSERT INTO t2 VALUES +('2006-01-12',-102,45,'2023-11-25'),('2034-12-19',-102,45,'2001-11-20'); +UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2; +ERROR 22007: Incorrect datetime value: '19' for column `test`.`t1`.`i1` at row 1 +DROP TABLE t1,t2; +# End of MariaDB 10.2 tests diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index e5ef0b1..8470910 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -654,3 +654,22 @@ show status like 'Handler_read%'; drop table t1, t2; --echo # End of MariaDB 10.0 tests + +--echo # +--echo # MDEV-18945: multi-table update with constant table and +--echo # comparison of date field with integer field +--echo # + +CREATE TABLE t1 (i1 int, d1 date , i2 int , d2 date) engine=myisam; +INSERT INTO t1 VALUES (19,'0000-00-00',73,'2008-05-21'); + +CREATE TABLE t2 (d1 date , i1 int, i2 int , d2 date) engine=myisam; +INSERT INTO t2 VALUES + ('2006-01-12',-102,45,'2023-11-25'),('2034-12-19',-102,45,'2001-11-20'); + +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2; + +DROP TABLE t1,t2; + +--echo # End of MariaDB 10.2 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1dfd652..6253c39 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -20614,7 +20614,8 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, the new parent Item. This should not be expensive because all children of Item_cond_and should be fixed by now. */ - new_cond->fix_fields(thd, 0); + if (new_cond->fix_fields(thd, 0)) + return (COND*) 0; new_cond->used_tables_cache= ((Item_cond_and*) cond)->used_tables_cache & tables;

1 0

[Commits] bf402941170: Merge remote-tracking branch 'origin/10.1' into 10.2
by jan 16 Mar '19

16 Mar '19

revision-id: bf4029411706f3c684d0990cb0133481a9a9e578 (mariadb-10.2.22-75-gbf402941170) parent(s): 1f020299f816263e347c852eb2a494b5ef1cbf0d 6c08174e365c1a2db76e51dedd2a8292464472d6 author: Jan Lindström committer: Jan Lindström timestamp: 2019-03-16 08:30:47 +0200 message: Merge remote-tracking branch 'origin/10.1' into 10.2 mysql-test/mysql-test-run.pl | 2 -- mysql-test/r/func_math.result | 40 ++++++++++++++++++++++++++++++++++++++++ mysql-test/t/func_math.test | 27 +++++++++++++++++++++++++++ sql/item_sum.cc | 12 ++++++++++++ 4 files changed, 79 insertions(+), 2 deletions(-) diff --cc mysql-test/r/func_math.result index ff2e929cffc,4215a82e3f0..d79d425841e --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@@ -826,20 -805,48 +826,60 @@@ SELECT STDDEV_POP(ROUND(0,@A:=2009)) FR STDDEV_POP(ROUND(0,@A:=2009)) 0.0000 # -# End of 10.0 tests -# -# + # Start of 10.1 tests + # + # + # MDEV-17643 Assertion `nr >= 0.0' failed in Item_sum_std::val_real() + # + CREATE TABLE t1 ( pk int NOT NULL, i1 int NOT NULL, d1 date NOT NULL, t1 time); + INSERT INTO t1 VALUES (7,9,'2007-08-15','03:55:02'),(8,7,'1993-06-05','04:17:51'),(9,7,'2034-07-01','17:31:12'),(10,0,'1998-08-24','08:09:27'); + SELECT DISTINCT STDDEV_SAMP(EXPORT_SET(t1, -1379790335835635712, (i1 + 'o'), (MD5(d1)))) FROM t1; + STDDEV_SAMP(EXPORT_SET(t1, -1379790335835635712, (i1 + 'o'), (MD5(d1)))) + NULL + Warnings: + Warning 1292 Truncated incorrect DOUBLE value: 'o' + Warning 1292 Truncated incorrect DOUBLE value: '98e466c7ff40fe6b95cde24200f376303-13797903358356357128e466c7ff40fe6b95cde24200f376303-13797903358356357128e466c7ff40fe6b95cde242' + Warning 1292 Truncated incorrect DOUBLE value: 'o' + Warning 1292 Truncated incorrect DOUBLE value: '-1379790335835635712e315457d879863c6ccf2ddee5562fc24-1379790335835635712e315457d879863c6ccf2ddee5562fc24-1379790335835635712e315' + Warning 1292 Truncated incorrect DOUBLE value: 'o' + Warning 1292 Truncated incorrect DOUBLE value: '7b4dd517b633f1f6304b773523b5279747b4dd517b633f1f6304b773523b5279747b4dd517b633f1f6304b773523b527974-1379790335835635712b4dd517b6' + Warning 1292 Truncated incorrect DOUBLE value: 'o' + Warning 1292 Truncated incorrect DOUBLE value: '-1379790335835635712b0e107767ea830fd3318893e40412a43-1379790335835635712b0e107767ea830fd3318893e40412a43-1379790335835635712b0e1' + DROP TABLE t1; + CREATE TABLE t1 (a VARCHAR(128)); + INSERT INTO t1 VALUES ('1e310'); + INSERT INTO t1 VALUES ('-1e310'); + INSERT INTO t1 VALUES ('0'); + SELECT STDDEV_SAMP(a) FROM t1; + STDDEV_SAMP(a) + NULL + Warnings: + Warning 1292 Truncated incorrect DOUBLE value: '1e310' + Warning 1292 Truncated incorrect DOUBLE value: '-1e310' + DROP TABLE t1; + CREATE OR REPLACE TABLE t1 (a DOUBLE); + INSERT INTO t1 VALUES (1.7e+308); + INSERT INTO t1 VALUES (-1.7e+308); + INSERT INTO t1 VALUES (0); + SELECT STDDEV_SAMP(a) FROM t1; + STDDEV_SAMP(a) + NULL + DROP TABLE t1; + # -# End of 10.1 tests +# Start of 10.2 tests +# +# Test zero +select 0=0, 0=-0, 0.0= -0.0, 0.0 = -(0.0), 0.0E1=-0.0E1, 0.0E1=-(0.0E1); +0=0 0=-0 0.0= -0.0 0.0 = -(0.0) 0.0E1=-0.0E1 0.0E1=-(0.0E1) +1 1 1 1 1 1 +# +# CRC32 tests # +select CRC32(NULL), CRC32(''), CRC32('MySQL'), CRC32('mysql'), CRC32('01234567'), CRC32('012345678'); +CRC32(NULL) CRC32('') CRC32('MySQL') CRC32('mysql') CRC32('01234567') CRC32('012345678') +NULL 0 3259397556 2501908538 763378421 939184570 +explain extended select (3-2)+1, (3/2)*1, 3-(2+1), 3/(2*1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select 3 - 2 + 1 AS `(3-2)+1`,3 / 2 * 1 AS `(3/2)*1`,3 - (2 + 1) AS `3-(2+1)`,3 / (2 * 1) AS `3/(2*1)` diff --cc mysql-test/t/func_math.test index 0b819278a44,4c2d7921593..ec226d472b3 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@@ -596,20 -596,39 +596,47 @@@ DROP TABLE t1 --echo # SELECT STDDEV_POP(ROUND(0,@A:=2009)) FROM (SELECT 1 UNION SELECT 2) fake_table; ---echo # ---echo # End of 10.0 tests ---echo # - - + --echo # + --echo # Start of 10.1 tests + --echo # + + --echo # + --echo # MDEV-17643 Assertion `nr >= 0.0' failed in Item_sum_std::val_real() + --echo # + + CREATE TABLE t1 ( pk int NOT NULL, i1 int NOT NULL, d1 date NOT NULL, t1 time); + INSERT INTO t1 VALUES (7,9,'2007-08-15','03:55:02'),(8,7,'1993-06-05','04:17:51'),(9,7,'2034-07-01','17:31:12'),(10,0,'1998-08-24','08:09:27'); + SELECT DISTINCT STDDEV_SAMP(EXPORT_SET(t1, -1379790335835635712, (i1 + 'o'), (MD5(d1)))) FROM t1; + DROP TABLE t1; + + CREATE TABLE t1 (a VARCHAR(128)); + INSERT INTO t1 VALUES ('1e310'); + INSERT INTO t1 VALUES ('-1e310'); + INSERT INTO t1 VALUES ('0'); + SELECT STDDEV_SAMP(a) FROM t1; + DROP TABLE t1; + + CREATE OR REPLACE TABLE t1 (a DOUBLE); + INSERT INTO t1 VALUES (1.7e+308); + INSERT INTO t1 VALUES (-1.7e+308); + INSERT INTO t1 VALUES (0); + SELECT STDDEV_SAMP(a) FROM t1; + DROP TABLE t1; + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # Test zero +select 0=0, 0=-0, 0.0= -0.0, 0.0 = -(0.0), 0.0E1=-0.0E1, 0.0E1=-(0.0E1); --echo # ---echo # End of 10.1 tests +--echo # CRC32 tests --echo # + +select CRC32(NULL), CRC32(''), CRC32('MySQL'), CRC32('mysql'), CRC32('01234567'), CRC32('012345678'); + +# +# MDEV-13673 Bad result in view +# +explain extended select (3-2)+1, (3/2)*1, 3-(2+1), 3/(2*1);

1 0

[Commits] 89a5832: MDEV-18943: Group Concat with limit not working with views
by varun 16 Mar '19

16 Mar '19

revision-id: 89a583247bdfaea92598919f01079101b8003e02 (mariadb-10.3.12-86-g89a5832) parent(s): 51e48b9f8981986257a1cfbdf75e4fc29a5959c1 author: Varun Gupta committer: Varun Gupta timestamp: 2019-03-16 09:02:14 +0530 message: MDEV-18943: Group Concat with limit not working with views Adjusted the Item_func_group_concat::print function to take into account limit if present with GROUP_CONCAT --- mysql-test/main/func_gconcat.result | 14 ++++++++++++++ mysql-test/main/func_gconcat.test | 12 ++++++++++++ sql/item_sum.cc | 14 +++++++++++++- 3 files changed, 39 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/func_gconcat.result b/mysql-test/main/func_gconcat.result index 723a195..1701bb3 100644 --- a/mysql-test/main/func_gconcat.result +++ b/mysql-test/main/func_gconcat.result @@ -1378,5 +1378,19 @@ group_concat(a,b limit ?) 1a,1b,2x,2y drop table t2; # +# MDEV-18943: Group Concat with limit not working with views +# +create table t1 (a int, b varchar(10)); +insert into t1 values(1,'a'),(1,'b'),(NULL,'c'),(2,'x'),(2,'y'); +select group_concat(a,b limit 2) from t1; +group_concat(a,b limit 2) +1a,1b +create view v1 as select group_concat(a,b limit 2) from t1; +select * from v1; +group_concat(a,b limit 2) +1a,1b +drop view v1; +drop table t1; +# # End of 10.3 tests # diff --git a/mysql-test/main/func_gconcat.test b/mysql-test/main/func_gconcat.test index 5cbc696..b8ab96b 100644 --- a/mysql-test/main/func_gconcat.test +++ b/mysql-test/main/func_gconcat.test @@ -986,5 +986,17 @@ execute STMT using @x; drop table t2; --echo # +--echo # MDEV-18943: Group Concat with limit not working with views +--echo # + +create table t1 (a int, b varchar(10)); +insert into t1 values(1,'a'),(1,'b'),(NULL,'c'),(2,'x'),(2,'y'); +select group_concat(a,b limit 2) from t1; +create view v1 as select group_concat(a,b limit 2) from t1; +select * from v1; +drop view v1; +drop table t1; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 44e5a86..4fe547a 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -4159,7 +4159,19 @@ void Item_func_group_concat::print(String *str, enum_query_type query_type) } str->append(STRING_WITH_LEN(" separator \'")); str->append_for_single_quote(separator->ptr(), separator->length()); - str->append(STRING_WITH_LEN("\')")); + str->append(STRING_WITH_LEN("\'")); + + if (limit_clause) + { + str->append(STRING_WITH_LEN(" limit ")); + if (offset_limit) + { + offset_limit->print(str, query_type); + str->append(','); + } + row_limit->print(str, query_type); + } + str->append(STRING_WITH_LEN(")")); }

1 0