Hi Sergei, Thank you for your review!
> If DISTINCT was coverted to a GROUP BY, why would the engine need to > know whether there was DISTINCT or not originally? There is no DISTINCT > on the execution plan now, that should be sufficient, shouldn't it? > > Why does the query fail with GROUP BY? For the select query in the test case (SELECT distinct b FROM tbl_a WHERE b=999), the optimizer seems to convert DISTINCT to GROUP by and then optimize away GROUP BY. The, we get select_distinct = 0, no_order = 1, group_optimized_away = 1. Please see sql/sql_select.cc:2721-2781. In such a case, group_list is NULL and thus the Spider SE misunderstand that the query has neither DISTINCT and GROUP BY without my fix. Optimizing group_list away itself might be a bug but there seems no problem with other storage engines. Regards, Nayuta On July 22, 2021, Sergei Golubchik <[email protected]> wrote: > Hi, Nayuta! > > On Jul 21, Nayuta Yanagisawa wrote: > > revision-id: 5e16a49f5d9 (mariadb-10.4.20-32-g5e16a49f5d9) > > parent(s): 78735dcaf75 > > author: Nayuta Yanagisawa > > committer: Nayuta Yanagisawa > > timestamp: 2021-07-21 09:58:34 +0000 > > message: > > > > MDEV-26013 distinct not work properly in some cases for spider > tables > > > > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > > index d8116b20cbe..cfd04d93662 100644 > > --- a/sql/sql_select.cc > > +++ b/sql/sql_select.cc > > @@ -3274,9 +3274,17 @@ bool JOIN::make_aggr_tables_info() > > > > if (ht && ht->create_group_by) > > { > > - /* Check if the storage engine can intercept the query */ > > - Query query= {&all_fields, select_distinct, tables_list, conds, > > - group_list, order ? order : group_list, having}; > > + /* > > + Check if the storage engine can intercept the query. > > + > > + JOIN::optimize_stage2() might convert DISTINCT into GROUP BY and > then > > + optimize away GROUP BY. In such a case, we need to notify a > storage engine > > + supporting a group by handler of the existence of the original > DISTINCT. > > + Thus, we set select_distinct || (no_order && group_optimized_away) > to > > + Query::distinct. > > + */ > > + Query query= {&all_fields, select_distinct || (no_order && > group_optimized_away), > > + tables_list, conds, group_list, order ? order : group_list, > having}; > > If DISTINCT was coverted to a GROUP BY, why would the engine need to > know whether there was DISTINCT or not originally? There is no > DISTINCT > on the execution plan now, that should be sufficient, shouldn't it? > > Why does the query fail with GROUP BY? > > > group_by_handler *gbh= ht->create_group_by(thd, &query); > > > > if (gbh) > > Regards, > Sergei > VP of MariaDB Server Engineering > and [email protected]
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
