#425 Syntax not supported by XC

[Abbas Butt]

Test case taken form with.sql
In PG we get
test=# WITH outermost(x) AS (
test(# SELECT 1
test(# UNION (WITH innermost as (SELECT 2)
test(# SELECT * FROM innermost
test(# UNION SELECT 3)
test(# )
test-# SELECT * FROM outermost;
x
---
1
2
3
(3 rows)

where as on XC we get a syntax error

test=# WITH outermost(x) AS (
test(# SELECT 1
test(# UNION (WITH innermost as (SELECT 2)
test(# SELECT * FROM innermost
test(# UNION SELECT 3)
test(# )
test-# SELECT * FROM outermost;
ERROR: relation "innermost" does not exist
LINE 4: SELECT * FROM innermost

[Abbas Butt]

This test case was found missing in XC when compared with the corresponding test case file in PG. For this syntax being not supported we have to defer synchronizing the with.sql file between XC and PG.

[Koichi Suzuki]

This is a beta blocker. Found this works with 1.0.x but doesn't with the current head, as of June 12th.

[Amit Khandekar]

This testcase has been extracted from PG REL_92_STABLE branch.

This testcase reproduces a bug in PG 9.2 . The fix has been committed in PG in master, but only after 9.2 was released. And then this fix was backported to different release branches. So the fix is also backported to REL_92_STABLE branch, but this branch hasn't been merged to XC REL1_1_STABLE branch yet. Once this branch is merged into XC, this testcase should pass on 1.1.

This testcase will continue to fail on XC master branch, until we merge PG master into XC master.

I applied the PG commit onto XC master, and the testcase passes.

Let's keep this bug open until we merge the branch and verify that the testcase passes after the merge on 1.1 .

Here's the commit that happened on PG REL_92_STABLE branch

commit 3786b9b4668743b99e837fe276e14be1f1bdfde9
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Tue Jul 31 17:56:27 2012 -0400

And here is the commit that went into PG master branch:

commit f6ce81f55a3c6932d5f88bc89374f404688f054e
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Tue Jul 31 17:56:21 2012 -0400

Commit description:

Fix WITH attached to a nested set operation (UNION/INTERSECT/EXCEPT).

Parse analysis neglected to cover the case of a WITH clause attached to an
intermediate-level set operation; it only handled WITH at the top level
or WITH attached to a leaf-level SELECT. Per report from Adam Mackler.

In HEAD, I rearranged the order of SelectStmt's fields to put withClause
with the other fields that can appear on non-leaf SelectStmts. In back
branches, leave it alone to avoid a possible ABI break for third-party
code.

Back-patch to 8.4 where WITH support was added.