Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Cherry-pick gporca commits to fix Bug 669 #708

Open
wants to merge 2 commits into
base: main
Choose a base branch
from
Open

Cherry-pick gporca commits to fix Bug 669 #708

wants to merge 2 commits into from

Conversation

leborchuk
Copy link
Contributor

@leborchuk leborchuk commented Nov 8, 2024 •
edited
Loading

Here I chery-picked

Jul 30, 2022 Fix qp_with_clause testcase without asserts (#13878)
Oct 4, 2022 [ORCA] Avoid pushdown of predicate with set-returning function (#14201)

fix #669

Change logs

Describe your change clearly, including what problem is being solved or what feature is being added.

If it has some breaking backward or forward compatibility, please clary.

Why are the changes needed?

Describe why the changes are necessary.

Does this PR introduce any user-facing change?

If yes, please clarify the previous behavior and the change this PR proposes.

How was this patch tested?

Please detail how the changes were tested, including manual tests and any relevant unit or integration tests.

Contributor's Checklist

Here are some reminders and checklists before/when submitting your pull request, please check them:

  • Make sure your Pull Request has a clear title and commit message. You can take git-commit template as a reference.
  • Sign the Contributor License Agreement as prompted for your first-time contribution(One-time setup).
  • Learn the coding contribution guide, including our code conventions, workflow and more.
  • List your communication in the GitHub Issues or Discussions (if has or needed).
  • Document changes.
  • Add tests for the change
  • Pass make installcheck
  • Pass make -C src/test installcheck-cbdb-parallel
  • Feel free to request cloudberrydb/dev team for review and approval when your PR is ready🥳

@dgkimura @leborchuk
Fix qp_with_clause testcase without asserts (#13878)
30b8446 
- Fix off-by-one error in 71dd8b0239
- Add optimizer_trace_fallback to catch issue in non-assert build
@leborchuk leborchuk mentioned this pull request Nov 8, 2024
Open
2 tasks
@leborchuk
Copy link
Contributor Author

Tests still failed - patch did not help

The good news it's stable reproduced in my dev env, script for it -

CREATE TABLE city (
    id integer NOT NULL,
    name text NOT NULL,
    countrycode character(3) NOT NULL,
    district text NOT NULL,
    population integer NOT NULL
) distributed by(id);



CREATE TABLE country (
    code character(3) NOT NULL,
    name text NOT NULL,
    continent text NOT NULL,
    region text NOT NULL,
    surfacearea numeric(10,2) NOT NULL,
    indepyear smallint,
    population integer NOT NULL,
    lifeexpectancy real,
    gnp numeric(10,2),
    gnpold numeric(10,2),
    localname text NOT NULL,
    governmentform text NOT NULL,
    headofstate text,
    capital integer,
    code2 character(2) NOT NULL
) distributed by (code);



CREATE TABLE countrylanguage (
    countrycode character(3) NOT NULL,
    "language" text NOT NULL,
    isofficial boolean NOT NULL,
    percentage real NOT NULL
)distributed by (countrycode,language);

with diversecountries as
(select country.code,country.name,country.capital,d.CNT
 from country,
 (select countrylanguage.countrycode,count(*) as CNT from countrylanguage group by countrycode
  HAVING count(*) > 6) d
 where d.countrycode = country.code and country.gnp > 100000)
select * from
(
select
(select max(CNT) from diversecountries where  diversecountries.code = country.code) CNT,country.name COUNTRY,city.name CAPITAL
from country,city where country.capital = city.id) FOO where FOO.CNT is not null;

@dgkimura @leborchuk
[ORCA] Avoid pushdown of predicate with set-returning function (#14201)
4298c29 
Issue is that if a predicate with a set-returning function is pushed
down then it can lead to bad execution because there exist cases where
the below context cannot accept a set. Logic already existed to prevent
such a case, but it wasn't sufficient.

Following example demonstrates such a case:
  ```sql
  CREATE TABLE t(a int[]) ;
  INSERT INTO t VALUES (ARRAY[1, 2]);

  SELECT * FROM (SELECT unnest(t1.a) a_unnest FROM t t1, t t2) s WHERE a_unnest IS NOT NULL;
  ```

Also similarly, do not push down a filter through a projected column
containing a subquery.
@leborchuk
Copy link
Contributor Author

Commit Oct 4, 2022 "[ORCA] Avoid pushdown of predicate with set-returning function (#14201)" fixed the issue

Tests in my dev env:

parallel group (8 tests):  qp_executor qp_bitmapscan qp_olap_windowerr qp_with_clause qp_misc_jiras qp_olap_window qp_derived_table qp_dropped_cols
     qp_misc_jiras                ... ok        57522 ms (diff  643 ms)
     qp_with_clause               ... ok        33078 ms (diff  914 ms)
     qp_executor                  ... ok          679 ms (diff  100 ms)
     qp_olap_windowerr            ... ok        12873 ms (diff  639 ms)
     qp_olap_window               ... ok        57660 ms (diff 7267 ms)
     qp_derived_table             ... ok        89545 ms (diff 11761 ms)
     qp_bitmapscan                ... ok         7488 ms (diff 1297 ms)
     qp_dropped_cols              ... ok       101175 ms (diff 1328 ms)

PR could be reviewed now

@leborchuk leborchuk changed the title Fix qp_with_clause testcase without asserts (#13878) Cherry-pick gporca commits to fix Bug 669 Nov 10, 2024
jiaqizho
jiaqizho reviewed Nov 15, 2024
View reviewed changes
Copy link
Contributor

@jiaqizho jiaqizho left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM, let us waiting the CI re-enable.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@jiaqizho jiaqizho jiaqizho left review comments

At least 1 approving review is required to merge this pull request.

Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.

[Bug] orca test cases failed due to server closed the connection unexpectedly
3 participants
@leborchuk @jiaqizho @dgkimura