[CALCITE-6007] CTE as subquery without alias doesn't have correct alias setup

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.34.0, 1.35.0
• Fix Version/s: 1.36.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Wenrui Meng
• Reporter: Wenrui Meng
• Created: 14/Sep/23 21:39
• Updated: 10/Nov/23 11:34
• Resolved: 27/Sep/23 18:49

Description

SELECT
  a,
  b
FROM (
  WITH
    sub AS (
    SELECT
      1 AS a,
      2 AS b)
  SELECT
    *
  FROM
    sub)
WHERE
  a IS NOT null

It will generate the following SQL statement after validation

SELECT
  EXPR$0.a,
  EXPR$0.b
FROM (
  WITH
    sub AS (
    SELECT
      1 AS a,
      2 AS b)
  SELECT
    sub.a AS a, sub.b AS b
  FROM
    sub)
WHERE
  EXPR$0.a IS NOT null

The validated SQL become invalid since there is no EXPR$0 alias append for the SqlWith sub query but used in the expression outside.

Issue Links

• links to: GitHub Pull Request #3440