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.

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

opt: Unexpected error when using recursive CTE #45869

Closed
andy-kimball opened this issue Mar 9, 2020 · 2 comments · Fixed by #45877
Closed

opt: Unexpected error when using recursive CTE #45869

andy-kimball opened this issue Mar 9, 2020 · 2 comments · Fixed by #45877
Assignees
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption.

Comments

@andy-kimball
Copy link
Contributor

REPRO:

root@:26257/defaultdb> with recursive x(a) as
  ((values ('a'), ('b'))
   union all
   (with z as (select * from x)
    select z.a || z.a as a from z cross join z as z1
    where length(z.a) < 3))
select * from x;

ERROR: couldn't find WITH expression "x" with ID 1
HINT: references to WITH expressions from correlated subqueries are unsupported

EXPECTED: No error, PG is able to run this query.

@RaduBerinde
Copy link
Member

The error comes from the execbuilder. Looks like there's a problem in the optbuilder, we are building the inner WITH on top of the recursive WITH:

build
WITH RECURSIVE x(a) AS (
  (VALUES ('a'), ('b'))
  UNION ALL
  (WITH z AS (SELECT * FROM x) SELECT z.a || z.a AS a FROM z CROSS JOIN z AS z1 WHERE length(z.a) < 3)
)
SELECT * FROM x
----
with &2 (z)
 ├── columns: a:7
 ├── with-scan &1 (x)
 │    ├── columns: a:3
 │    └── mapping:
 │         └──  a:2 => a:3
 └── with &3 (x)
      ├── columns: a:7
      ├── recursive-c-t-e
      │    ├── columns: a:2
      │    ├── working table binding: &1
      │    ├── initial columns: column1:1
      │    ├── recursive columns: a:6
      │    ├── values
      │    │    ├── columns: column1:1!null
      │    │    ├── ('a',)
      │    │    └── ('b',)
      │    └── project
      │         ├── columns: a:6
      │         ├── select
      │         │    ├── columns: a:4 a:5
      │         │    ├── inner-join (cross)
      │         │    │    ├── columns: a:4 a:5
      │         │    │    ├── with-scan &2 (z)
      │         │    │    │    ├── columns: a:4
      │         │    │    │    └── mapping:
      │         │    │    │         └──  a:3 => a:4
      │         │    │    ├── with-scan &2 (z)
      │         │    │    │    ├── columns: a:5
      │         │    │    │    └── mapping:
      │         │    │    │         └──  a:3 => a:5
      │         │    │    └── filters (true)
      │         │    └── filters
      │         │         └── length(a:4) < 3
      │         └── projections
      │              └── a:4 || a:4 [as=a:6]
      └── with-scan &3 (x)
           ├── columns: a:7
           └── mapping:
                └──  a:2 => a:7

@yuzefovich
Copy link
Member

Looks related to #42373.

@awoods187 awoods187 added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption. and removed C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) labels Mar 9, 2020
craig bot pushed a commit that referenced this issue Mar 11, 2020
45655: sql: add more detailed test for operations during a primary key change r=lucy-zhang a=rohany

The existing operations test did not catch various bugs
uncovered in #45347, so this test expands upon the existing
schema change operations tests with a larger test case.

Release note: None

45877: opt: fix bug when WITH was used inside WITH RECURSIVE r=RaduBerinde a=RaduBerinde

We were incorrectly building the inner CTE above the recursive CTE.

Fixes #45869.

Release note (bug fix): fixed query errors in cases where a CTE is used inside a
recursive CTE.

Release justification: Category 2: Bug fixes and low-risk updates to new functionality.

45973: engine: fix different serialization of MVCCMetadata used for r=sumeerbhola a=sumeerbhola

non-MVCC data that is merged using C++ code in RocksDB and
Go code in Pebble

The serialization now matches the C++ code.

Fixes #45811

Release note: None

45992: sql: decrease job adopt interval for schema change tests r=lucy-zhang a=lucy-zhang

This PR decreases the job adopt interval to 100 ms for some schema
change tests for retries, so that the job registry adopts the jobs to
retry more quickly. These test were taking over a minute after the
schema change job refactor, and now take about 1 second.

Closes #45970.

Release justification: This change only fixes tests.
Release note: None

Co-authored-by: Rohan Yadav <rohany@alumni.cmu.edu>
Co-authored-by: Radu Berinde <radu@cockroachlabs.com>
Co-authored-by: sumeerbhola <sumeer@cockroachlabs.com>
Co-authored-by: Lucy Zhang <lucy-zhang@users.noreply.github.com>
@craig craig bot closed this as completed in 67a932f Mar 11, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

4 participants