From 192427e37d2b066f9f681cc1b998b2efdc407c55 Mon Sep 17 00:00:00 2001 From: Monty Date: Wed, 15 Feb 2023 13:56:33 +0200 Subject: MDEV-30333 Wrong result with not_null_range_scan and LEFT JOIN with empty table There was a bug in JOIN::make_notnull_conds_for_range_scans() when clearing TABLE->tmp_set, which was used to mark fields that could not be null. This function was only used if 'not_null_range_scan=on' is set. The effect was that tmp_set contained a 'random value' and this caused the optimizer to think that some fields could not be null. FLUSH TABLES clears tmp_set and because of this things worked temporarily. Fixed by clearing tmp_set properly. --- sql/sql_select.cc | 6 ++---- 1 file changed, 2 insertions(+), 4 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 42711270f60..c28f104804f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -29683,7 +29683,6 @@ void JOIN::make_notnull_conds_for_range_scans() { DBUG_ENTER("JOIN::make_notnull_conds_for_range_scans"); - if (impossible_where || !optimizer_flag(thd, OPTIMIZER_SWITCH_NOT_NULL_RANGE_SCAN)) { @@ -29769,7 +29768,6 @@ bool build_notnull_conds_for_range_scans(JOIN *join, Item *cond, table_map allowed) { THD *thd= join->thd; - DBUG_ENTER("build_notnull_conds_for_range_scans"); for (JOIN_TAB *s= join->join_tab; @@ -29777,13 +29775,13 @@ bool build_notnull_conds_for_range_scans(JOIN *join, Item *cond, { /* Clear all needed bitmaps to mark found fields */ if ((allowed & s->table->map) && - !(s->table->map && join->const_table_map)) + !(s->table->map & join->const_table_map)) bitmap_clear_all(&s->table->tmp_set); } /* Find all null-rejected fields assuming that cond is null-rejected and - only formulas over tables from 'allowed' are to be taken into account + only formulas over tables from 'allowed' are to be taken into account */ if (cond->find_not_null_fields(allowed)) DBUG_RETURN(true); -- cgit v1.2.1 From 4afa3b64c4e5ca12d755124befc373e0f35ff1e1 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Vicen=C8=9Biu=20Ciorbaru?= Date: Wed, 15 Feb 2023 16:20:25 +0200 Subject: MDEV-30324: Wrong result upon SELECT DISTINCT ... WITH TIES WITH TIES would not take effect if SELECT DISTINCT was used in a context where an INDEX is used to resolve the ORDER BY clause. WITH TIES relies on the `JOIN::order` to contain the non-constant fields to test the equality of ORDER BY fiels required for WITH TIES. The cause of the problem was a premature removal of the `JOIN::order` member during a DISTINCT optimization. This lead to WITH TIES code assuming ORDER BY only contained "constant" elements. Disable this optimization when WITH TIES is in effect. (side-note: the order by removal does not impact any current tests, thus it will be removed in a future version) Reviewed by: monty@mariadb.org --- sql/sql_select.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e0beda6ec61..57bf9363b47 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4170,7 +4170,7 @@ JOIN::optimize_distinct() } /* Optimize "select distinct b from t1 order by key_part_1 limit #" */ - if (order && skip_sort_order) + if (order && skip_sort_order && !unit->lim.is_with_ties()) { /* Should already have been optimized away */ DBUG_ASSERT(ordered_index_usage == ordered_index_order_by); -- cgit v1.2.1