Alena0704 opened a new pull request, #2042: URL: https://github.com/apache/cloudberry/pull/2042
Disable decorrelation for count() with no grouping columns. In SQL standard, GROUP BY clause can represent two distinct operations: normal GROUP BY with grouping columns, and scalar GROUP BY without grouping columns. Their main difference is that the scalar GROUP BY always outputs exactly one row, even when input relation is empty. This especially matters for COUNT(*) and COUNT(attr) aggregates since in empty input their output is 0, not NULL. During subquery decorrelation in ORCA, when pulling predicates through GpAgg, new grouping columns are added to it. This is fine for normal GROUP BYs, but for scalar GROUP BY (the case when there were no grouping columns originally), it changes behavior on empty input relations, which produces invalid output. This seems to be a well-known bug in existing database literature, known as the "COUNT bug". This behavior was noticed previously in ORCA, and a "COALESCE fix" was added, converting NULLs back to 0. However, this fix was added in a previous transformation (CSubqueryHandler), so it was unnecessary in some cases, and also didn't cover all of them. Fixing this properly will require a partial rewrite of CDecorrelator to use better decorrelation algorithms that don't miss these edge cases. As a temporary solution, this patch disables decorrelation for GpAgg with no grouping columns, if COUNT(*) or COUNT(attr) is present, as well as the COALESCE fix. This unfortunately results in less optimal plans in some cases, but distinguishing correct decorrelation from incorrect ones is complicated and requires big rewrites. Tests affected by this: Search space size is reduced in 13 minidump tests, plans themselves weren't affected. Unnecessary COALESCE is removed from 7 minidump tests. Swap joins in InferPredicatesFromMultiSubquery.mdp, without affecting performance. Fix ScalarCorrelatedSubqueryCountStar.mdp and ScalarSubqueryCountStarInJoin.mdp, since previously they were fixing incorrect behavior. Change NullIf-With-Subquery.mdp and UnnestSQJoins.mdp to correlated versions (COALESCE fix worked for them before, so they were correct). Change plans of several regression tests in subselect.sql, subselect_gp.sql, subselect_gp_indexes.sql and eagerfree.sql, replacing them with correlated plans. Unfortunately, they were the ones where decorrelation was safe even without COALESCE fix, but there is no easy way to determine that with current architecture (COALESCE fix was applied to them previously regardless). Ported from greengage #1658 and open-gpdb #412. Co-Authored-By: Maxim Michkov [[email protected]](mailto:[email protected]) to reproduce the case ``` postgres=# drop table if exists cb; NOTICE: table "cb" does not exist, skipping DROP TABLE postgres=# create table cb(a int, b int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. CREATE TABLE postgres=# insert into cb values (0, 1); -- b=1, поэтому для a=0 нет совпадений -> count=0 INSERT 0 1 postgres=# set optimizer = on; SET postgres=# select * from cb x where x.a in (select count(*) from cb y where y.b = x.a); -- ждём (0|1) a | b ---+--- (0 rows) postgres=# postgres=# set optimizer = off; SET postgres=# select * from cb x where x.a in (select count(*) from cb y where y.b = x.a); -- ждём (0|1) a | b ---+--- 0 | 1 (1 row) ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
