You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by ma...@apache.org on 2024/11/28 10:06:22 UTC
(cloudberry) 01/02: Fix qp_with_clause testcase without asserts (#13878)
This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit f37fec2778121fb53de7d9746dce1be35e362a9e
Author: David Kimura <dk...@vmware.com>
AuthorDate: Fri Jul 29 14:28:11 2022 -0700
Fix qp_with_clause testcase without asserts (#13878)
- Fix off-by-one error in 71dd8b0239
- Add optimizer_trace_fallback to catch issue in non-assert build
---
.../src/operators/CExpressionPreprocessor.cpp | 2 +-
src/test/regress/expected/qp_with_clause.out | 4 +
...ith_clause.out => qp_with_clause_optimizer.out} | 101 ++++++++++++++++++---
src/test/regress/sql/qp_with_clause.sql | 6 ++
4 files changed, 98 insertions(+), 15 deletions(-)
diff --git a/src/backend/gporca/libgpopt/src/operators/CExpressionPreprocessor.cpp b/src/backend/gporca/libgpopt/src/operators/CExpressionPreprocessor.cpp
index d9afaf498c..d570a5197f 100644
--- a/src/backend/gporca/libgpopt/src/operators/CExpressionPreprocessor.cpp
+++ b/src/backend/gporca/libgpopt/src/operators/CExpressionPreprocessor.cpp
@@ -2844,7 +2844,7 @@ CExpressionPreprocessor::PexprTransposeSelectAndProject(CMemoryPool *mp,
exprhdl.Attach(pprojexpr);
exprhdl.DeriveProps(nullptr /*pdpctxt*/);
- if (exprhdl.Arity() > 0 && exprhdl.DeriveHasNonScalarFunction(1))
+ if (exprhdl.Arity() > 1 && exprhdl.DeriveHasNonScalarFunction(1))
{
// Bail if project expression contains a set-returning function
pdrgpexpr->Release();
diff --git a/src/test/regress/expected/qp_with_clause.out b/src/test/regress/expected/qp_with_clause.out
index 10c02bae0c..88d1c8d345 100644
--- a/src/test/regress/expected/qp_with_clause.out
+++ b/src/test/regress/expected/qp_with_clause.out
@@ -16,6 +16,7 @@ NOTICE: table "country" does not exist, skipping
DROP TABLE IF EXISTS countrylanguage cascade;
NOTICE: table "countrylanguage" does not exist, skipping
--end_ignore
+SET optimizer_trace_fallback=on;
BEGIN;
--SET client_encoding = 'LATIN1';
CREATE TABLE city (
@@ -614,6 +615,7 @@ where e1.code = e2.code order by e2.COUNTRY,e1.language LIMIT 20;
GIB | Gibraltar | Gibraltar | English | t | 88.9 | GIB | Gibraltar | Gibraltar | English | t | 88.9
(20 rows)
+SET optimizer_trace_fallback=off;
-- query 2 using multiple CTEs with same names as tables.
with country as
(select country.code,country.name COUNTRY, city.name CAPITAL, language, isofficial, percentage
@@ -655,6 +657,7 @@ order by COUNTRY,percentage1 LIMIT 20;-- queries using same name for CTEs and th
GIB | Gibraltar | Gibraltar | English | t | 88.9 | Gibraltar
(20 rows)
+SET optimizer_trace_fallback=on;
-- query1
with c1 as
(select country.code,country.name COUNTRY, city.name CAPITAL, language, isofficial, percentage
@@ -11129,3 +11132,4 @@ NOTICE: drop cascades to table countrylanguage
NOTICE: drop cascades to table country
NOTICE: drop cascades to table city
-- end_ignore
+RESET optimizer_trace_fallback;
diff --git a/src/test/regress/expected/qp_with_clause.out b/src/test/regress/expected/qp_with_clause_optimizer.out
similarity index 99%
copy from src/test/regress/expected/qp_with_clause.out
copy to src/test/regress/expected/qp_with_clause_optimizer.out
index 10c02bae0c..12b1ef76cd 100644
--- a/src/test/regress/expected/qp_with_clause.out
+++ b/src/test/regress/expected/qp_with_clause_optimizer.out
@@ -16,6 +16,7 @@ NOTICE: table "country" does not exist, skipping
DROP TABLE IF EXISTS countrylanguage cascade;
NOTICE: table "countrylanguage" does not exist, skipping
--end_ignore
+SET optimizer_trace_fallback=on;
BEGIN;
--SET client_encoding = 'LATIN1';
CREATE TABLE city (
@@ -614,6 +615,7 @@ where e1.code = e2.code order by e2.COUNTRY,e1.language LIMIT 20;
GIB | Gibraltar | Gibraltar | English | t | 88.9 | GIB | Gibraltar | Gibraltar | English | t | 88.9
(20 rows)
+SET optimizer_trace_fallback=off;
-- query 2 using multiple CTEs with same names as tables.
with country as
(select country.code,country.name COUNTRY, city.name CAPITAL, language, isofficial, percentage
@@ -655,6 +657,7 @@ order by COUNTRY,percentage1 LIMIT 20;-- queries using same name for CTEs and th
GIB | Gibraltar | Gibraltar | English | t | 88.9 | Gibraltar
(20 rows)
+SET optimizer_trace_fallback=on;
-- query1
with c1 as
(select country.code,country.name COUNTRY, city.name CAPITAL, language, isofficial, percentage
@@ -2029,6 +2032,8 @@ and CITY_CNT/LANG_CNT > (select max(CITY_CNT/LANG_CNT) from allcountrystats,co
WHERE allcountrystats.code = country.code
and FOO.region = country.region
group by FOO.region order by FOO.region;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
region_city_cnt | region_lang_cnt | region
-----------------+-----------------+---------------------------
840 | 192 | Caribbean
@@ -2138,6 +2143,8 @@ where longlivingregions.region = denseregions.region and allcountrystats.code =
and country.indepyear > 1900
order by name
LIMIT 50;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
city_cnt | lang_cnt | name | REGION_LIFETIME | REGION_POP | lang_count | REGION_GNP | region
----------+----------+---------------------------------------+------------------+------------+------------+------------+---------------------------
4 | 5 | Afghanistan | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
@@ -2868,6 +2875,8 @@ and CITY_CNT/LANG_CNT > (select max(CITY_CNT/LANG_CNT) from allcountrystats,co
WHERE allcountrystats.code = country.code
and FOO.region = country.region
group by FOO.region order by FOO.region;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
region_city_cnt | region_lang_cnt | region
-----------------+-----------------+---------------------------
840 | 192 | Caribbean
@@ -2977,6 +2986,8 @@ where longlivingregions.region = denseregions.region and allcountrystats.code =
and country.indepyear > 1900
order by name
LIMIT 50;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
city_cnt | lang_cnt | name | REGION_SURFACE_AREA | REGION_LIFETIME | REGION_POP | lang_count | REGION_GNP | region
----------+----------+---------------------------------------+---------------------+------------------+------------+------------+------------+---------------------------
4 | 5 | Afghanistan | 90749795.00 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
@@ -3270,6 +3281,8 @@ and CITY_CNT/LANG_CNT > (select max(CITY_CNT/LANG_CNT) from allcountrystats,co
WHERE allcountrystats.code = country.code
and FOO.region = country.region
group by FOO.region order by FOO.region;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
region_city_cnt | region_lang_cnt | region
-----------------+-----------------+---------------------------
840 | 192 | Caribbean
@@ -4482,6 +4495,8 @@ and CITY_CNT/LANG_CNT > (select max(CITY_CNT/LANG_CNT) from allcountrystats,co
WHERE allcountrystats.code = country.code
and FOO.region = country.region
group by FOO.region order by FOO.region;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
region_city_cnt | region_lang_cnt | region
-----------------+-----------------+---------------------------
840 | 192 | Caribbean
@@ -4595,6 +4610,8 @@ where longlivingregions.region = denseregions.region and allcountrystats.code =
and country.indepyear > 1900
order by name
LIMIT 50;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
city_cnt | lang_cnt | name | REGION_SURFACE_AREA | REGION_LIFETIME | REGION_POP | lang_count | REGION_GNP | region
----------+----------+---------------------------------------+---------------------+------------------+------------+------------+------------+---------------------------
4 | 5 | Afghanistan | 90749795.00 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
@@ -5277,6 +5294,8 @@ and CITY_CNT/LANG_CNT > (select max(CITY_CNT/LANG_CNT) from allcountrystats,co
WHERE allcountrystats.code = country.code
and FOO.region = country.region
group by FOO.region order by FOO.region;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
region_city_cnt | region_lang_cnt | region
-----------------+-----------------+---------------------------
840 | 192 | Caribbean
@@ -5390,6 +5409,8 @@ where longlivingregions.region = denseregions.region and allcountrystats.code =
and country.indepyear > 1900
order by name
LIMIT 50;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
city_cnt | lang_cnt | name | REGION_SURFACE_AREA | REGION_LIFETIME | REGION_POP | lang_count | REGION_GNP | region
----------+----------+---------------------------------------+---------------------+------------------+------------+------------+------------+---------------------------
4 | 5 | Afghanistan | 90749795.00 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
@@ -6119,6 +6140,8 @@ and CITY_CNT/LANG_CNT > (select max(CITY_CNT/LANG_CNT) from allcountrystats,co
WHERE allcountrystats.code = country.code
and FOO.region = country.region
group by FOO.region order by FOO.region;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
region_city_cnt | region_lang_cnt | region
-----------------+-----------------+---------------------------
840 | 192 | Caribbean
@@ -6228,6 +6251,8 @@ where longlivingregions.region = denseregions.region and allcountrystats.code =
and country.indepyear > 1900
order by name
LIMIT 50;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
city_cnt | lang_cnt | name | REGION_SURFACE_AREA | REGION_LIFETIME | REGION_POP | lang_count | REGION_GNP | region
----------+----------+---------------------------------------+---------------------+------------------+------------+------------+------------+---------------------------
4 | 5 | Afghanistan | 90749795.00 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
@@ -6392,6 +6417,15 @@ CREATE TABLE countrylanguage_ao (
isofficial boolean NOT NULL,
percentage real NOT NULL
) with (appendonly=true) distributed by (countrycode,language);
+ALTER TABLE ONLY city_ao
+ ADD CONSTRAINT city_ao_pkey PRIMARY KEY (id);
+ERROR: append-only tables do not support unique indexes
+ALTER TABLE ONLY country_ao
+ ADD CONSTRAINT country_ao_pkey PRIMARY KEY (code);
+ERROR: append-only tables do not support unique indexes
+ALTER TABLE ONLY countrylanguage_ao
+ ADD CONSTRAINT countrylanguage_ao_pkey PRIMARY KEY (countrycode, "language");
+ERROR: append-only tables do not support unique indexes
create index bitmap_city_ao_countrycode on city_ao using bitmap(countrycode);
create index bitmap_country_ao_gf on country_ao using bitmap(governmentform);
create index bitmap_country_ao_region on country_ao using bitmap(region);
@@ -7008,6 +7042,8 @@ and CITY_AO_CNT/LANG_CNT > (select max(CITY_AO_CNT/LANG_CNT) from allcountry_a
WHERE allcountry_aostats.code = country_ao.code
and FOO.region = country_ao.region
group by FOO.region order by FOO.region;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
region_city_ao_cnt | region_lang_cnt | region
--------------------+-----------------+---------------------------
840 | 192 | Caribbean
@@ -7121,6 +7157,8 @@ where longlivingregions.region = denseregions.region and allcountry_aostats.code
and country_ao.indepyear > 1900
order by name
LIMIT 50;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
city_ao_cnt | lang_cnt | name | REGION_SURFACE_AREA | REGION_LIFETIME | REGION_POP | lang_count | REGION_GNP | region
-------------+----------+---------------------------------------+---------------------+------------------+------------+------------+------------+---------------------------
4 | 5 | Afghanistan | 90749795.00 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
@@ -7285,6 +7323,15 @@ CREATE TABLE countrylanguage_co (
isofficial boolean NOT NULL,
percentage real NOT NULL
) with (appendonly=true,orientation=column) distributed by (countrycode,language);
+ALTER TABLE ONLY city_co
+ ADD CONSTRAINT city_co_pkey PRIMARY KEY (id);
+ERROR: append-only tables do not support unique indexes
+ALTER TABLE ONLY country_co
+ ADD CONSTRAINT country_co_pkey PRIMARY KEY (code);
+ERROR: append-only tables do not support unique indexes
+ALTER TABLE ONLY countrylanguage_co
+ ADD CONSTRAINT countrylanguage_co_pkey PRIMARY KEY (countrycode, "language");
+ERROR: append-only tables do not support unique indexes
create index bitmap_city_co_countrycode on city_co using bitmap(countrycode);
create index bitmap_country_co_gf on country_co using bitmap(governmentform);
create index bitmap_country_co_region on country_co using bitmap(region);
@@ -7895,6 +7942,8 @@ and CITY_CO_CNT/LANG_CNT > (select max(CITY_CO_CNT/LANG_CNT) from allcountry_c
WHERE allcountry_costats.code = country_co.code
and FOO.region = country_co.region
group by FOO.region order by FOO.region;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
region_city_co_cnt | region_lang_cnt | region
--------------------+-----------------+---------------------------
840 | 192 | Caribbean
@@ -8008,6 +8057,8 @@ where longlivingregions.region = denseregions.region and allcountry_costats.code
and country_co.indepyear > 1900
order by name
LIMIT 50;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
city_co_cnt | lang_cnt | name | REGION_SURFACE_AREA | REGION_LIFETIME | REGION_POP | lang_count | REGION_GNP | region
-------------+----------+---------------------------------------+---------------------+------------------+------------+------------+------------+---------------------------
4 | 5 | Afghanistan | 90749795.00 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
@@ -8245,6 +8296,8 @@ where longlivingregions.region = denseregions.region and allcountrystats.code =
and country.indepyear > 1900
order by name
LIMIT 50;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
city_cnt | lang_cnt | name | REGION_SURFACE_AREA | REGION_LIFETIME | REGION_POP | lang_count | REGION_GNP | region
----------+----------+---------------------------------------+---------------------+------------------+------------+------------+------------+---------------------------
4 | 5 | Afghanistan | 90749795.00 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
@@ -8545,7 +8598,6 @@ select LC from lang_total;
ERROR: column "lc" does not exist
LINE 5: select LC from lang_total;
^
-HINT: Perhaps you meant to reference the column "lang_total.cc" or the column "lang_total.clc".
-- query 3 use column list when there are duplicate names within the CTE
with capitals("CO_C","C_ID","CAPITAL",country) as
(select country.code,id,city.name,country.name from city,country
@@ -9483,6 +9535,22 @@ where longlivingregions.region = denseregions.region and allcountrystats.code =
and country.indepyear > 1900
);
\d+ view_with_shared_scans;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Queries on master-only tables
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Queries on master-only tables
View "qp_with_clause.view_with_shared_scans"
Column | Type | Collation | Nullable | Default | Storage | Description
---------------------+------------------+-----------+----------+---------+----------+-------------
@@ -9585,6 +9653,8 @@ UNION ALL
WHERE longlivingregions.region = denseregions.region AND allcountrystats.code = country.code AND country.region = longlivingregions.region AND country.indepyear > 1900;
select city_cnt,lang_cnt,name,region from view_with_shared_scans order by name LIMIT 50;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
city_cnt | lang_cnt | name | region
----------+----------+---------------------------------------+---------------------------
4 | 5 | Afghanistan | Southern and Central Asia
@@ -9640,6 +9710,8 @@ select city_cnt,lang_cnt,name,region from view_with_shared_scans order by name L
(50 rows)
select city_cnt,lang_cnt,name,"REGION_POP","REGION_GNP",region from view_with_shared_scans where region = 'Eastern Europe';
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
city_cnt | lang_cnt | name | REGION_POP | REGION_GNP | region
----------+----------+--------------------+------------+------------+----------------
189 | 12 | Russian Federation | 307026000 | 659980.00 | Eastern Europe
@@ -9660,7 +9732,7 @@ drop table if exists tbl87;
NOTICE: table "tbl87" does not exist, skipping
-- end_ignore
create table tbl87(code char(3), n numeric);
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'code' as the Cloudberry Database data distribution key for this table.
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'code' 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.
insert into tbl87 values ('abc',1);
insert into tbl87 values ('xyz',2);
@@ -9722,11 +9794,11 @@ NOTICE: table "manager" does not exist, skipping
--end_ignore
-------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE foo (key INTEGER, value INTEGER);
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'key' as the Cloudberry Database data distribution key for this table.
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'key' 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.
INSERT INTO foo SELECT i, i % 10 from generate_series(1, 100) i;
CREATE TABLE bar(bar_key INTEGER, bar_value INTEGER);
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'bar_key' as the Cloudberry Database data distribution key for this table.
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'bar_key' 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.
INSERT INTO bar SELECT i, i % 5 FROM generate_series(1, 1000) i;
SET enable_hashjoin = OFF;
@@ -10631,11 +10703,11 @@ SET gp_cte_sharing = ON;
-- ORDER BY 1, 2 DESC LIMIT 100;
-------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE emp (ename CHARACTER VARYING(50), empno INTEGER, mgr INTEGER, deptno INTEGER);
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ename' as the Cloudberry Database data distribution key for this table.
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ename' 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.
INSERT INTO emp SELECT i || 'NAME', i, i%6, i%16 FROM generate_series(1, 100) i;
CREATE TABLE manager (dept_mgr_no INTEGER);
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dept_mgr_no' as the Cloudberry Database data distribution key for this table.
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dept_mgr_no' 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.
INSERT INTO manager SELECT i FROM generate_series(1, 100) i;
SET enable_hashjoin = OFF;
@@ -11093,10 +11165,10 @@ ORDER BY 1, 2, 3, 4 DESC LIMIT 25;
set gp_cte_sharing=on;
-- start_ignore
CREATE TABLE car (a int, b int);
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table.
+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 zoo (c int, d int);
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c' as the Cloudberry Database data distribution key for this table.
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c' 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.
insert into car select i, (i+1) from generate_series(1,10) i;
insert into zoo values (4,4);
@@ -11119,13 +11191,14 @@ NOTICE: drop cascades to table emp
NOTICE: drop cascades to table bar
NOTICE: drop cascades to table foo
NOTICE: drop cascades to table tbl87
-NOTICE: drop cascades to table countrylanguage_co
-NOTICE: drop cascades to table country_co
-NOTICE: drop cascades to table city_co
-NOTICE: drop cascades to table countrylanguage_ao
-NOTICE: drop cascades to table country_ao
-NOTICE: drop cascades to table city_ao
+NOTICE: drop cascades to append only columnar table countrylanguage_co
+NOTICE: drop cascades to append only columnar table country_co
+NOTICE: drop cascades to append only columnar table city_co
+NOTICE: drop cascades to append only table countrylanguage_ao
+NOTICE: drop cascades to append only table country_ao
+NOTICE: drop cascades to append only table city_ao
NOTICE: drop cascades to table countrylanguage
NOTICE: drop cascades to table country
NOTICE: drop cascades to table city
-- end_ignore
+RESET optimizer_trace_fallback;
diff --git a/src/test/regress/sql/qp_with_clause.sql b/src/test/regress/sql/qp_with_clause.sql
index ad018427f5..b31b7353a6 100644
--- a/src/test/regress/sql/qp_with_clause.sql
+++ b/src/test/regress/sql/qp_with_clause.sql
@@ -21,6 +21,8 @@ DROP TABLE IF EXISTS countrylanguage cascade;
--end_ignore
+SET optimizer_trace_fallback=on;
+
BEGIN;
--SET client_encoding = 'LATIN1';
@@ -5605,6 +5607,7 @@ select * from
(select * from country where percentage > 50) e2
where e1.code = e2.code order by e2.COUNTRY,e1.language LIMIT 20;
+SET optimizer_trace_fallback=off;
-- query 2 using multiple CTEs with same names as tables.
with country as
(select country.code,country.name COUNTRY, city.name CAPITAL, language, isofficial, percentage
@@ -5622,6 +5625,7 @@ where e1.code = e2.code order by e2.COUNTRY,e1.language
select code1,country1,capital1,language1,isofficial1,percentage1,country.COUNTRY from country,countrylanguage where country.code = countrylanguage.code1
and country.percentage = countrylanguage.percentage1
order by COUNTRY,percentage1 LIMIT 20;-- queries using same name for CTEs and the subquery aliases in the main query
+SET optimizer_trace_fallback=on;
-- query1
with c1 as
@@ -10347,3 +10351,5 @@ SELECT * FROM c as c1, zoo WHERE zoo.c = c1.b;
-- start_ignore
drop schema qp_with_clause cascade;
-- end_ignore
+
+RESET optimizer_trace_fallback;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org