From 0595dd0f5674845637135feaa5cb7b28d0dc10a9 Mon Sep 17 00:00:00 2001 From: Monty Date: Thu, 12 Jan 2023 18:45:40 +0200 Subject: MDEV-30080 Wrong result with LEFT JOINs involving constant tables The reason things fails in 10.5 and above is that test_quick_select() returns -1 (impossible range) for empty tables if there are any conditions attached. This didn't happen in 10.4 as the cost for a range was more than for a table scan with 0 rows and get_key_scan_params() did not create any range plans and thus did not mark the range as impossible. The code that checked the 'impossible range' conditions did not take into account all cases of LEFT JOIN usage. Adding an extra check if the table is used with an ON condition in case of 'impossible range' fixes the issue. --- 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 98d5ccb7eb2..7bbe5c6a237 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5639,7 +5639,7 @@ make_join_statistics(JOIN *join, List &tables_list, caller to abort with a zero row result. */ TABLE_LIST *emb= s->table->pos_in_table_list->embedding; - if (emb && !emb->sj_on_expr) + if (emb && !emb->sj_on_expr && !*s->on_expr_ref) { /* Mark all tables in a multi-table join nest as const */ mark_join_nest_as_const(join, emb, &found_const_table_map, -- cgit v1.2.1 From 981a6b704475176d032c13461baffcaf88802d34 Mon Sep 17 00:00:00 2001 From: Monty Date: Thu, 12 Jan 2023 22:31:18 +0200 Subject: MDEV-30395 Wrong result with semijoin and Federated as outer table The problem was that federated engine does not support comparable rowids which was not taken into account by semijoin code. Fixed by checking that we don't use semijoin with tables that does not support comparable rowids. Other things: - Fixed some typos in the code comments --- 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 7bbe5c6a237..a721899a8be 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2309,7 +2309,7 @@ JOIN::optimize_inner() /* We have to remove constants and duplicates from group_list before calling make_join_statistics() as this may call get_best_group_min_max() - which needs a simplfied group_list. + which needs a simplified group_list. */ if (group_list && table_count == 1) { -- cgit v1.2.1