Parse error in CTE that includes pipe syntax

[philrz]

This query used to work ok, but now produces a parse error.

$ super -version
Version: 97f26ff28

$ super -c "
WITH room_use AS (
  SELECT dow,
         hr,
         device_name,
         AVG(motions) AS in_use
  FROM (
    SELECT dt,
           dow,
           hr,
           device_name,
           COUNT(*) AS motions
    FROM (
      SELECT CAST(log_time AS DATE) AS dt,
             EXTRACT(DOW FROM log_time) AS dow,
             EXTRACT(HOUR FROM log_time) AS hr,
             device_name
      FROM 'bench3.bsup'
      WHERE device_name LIKE 'room%'
        AND event_type = 'motion_start'
        AND log_time >= TIMESTAMP '2019-09-01 00:00:00'
    ) AS r
    WHERE dow IN (1,2,3,4,5)
      AND hr BETWEEN 9 AND 16
    GROUP BY dt,
             dow,
             hr,
             device_name
  ) AS s
  GROUP BY dow,
           hr,
           device_name
  | put left_key := {device_name, in_use}
), min_room_use AS (
  SELECT device_name,
        MIN(in_use) AS in_use
        FROM room_use
        GROUP BY device_name
  | put right_key := {device_name, in_use}
)
SELECT min_room_use.device_name,
       ru.dow,
       ru.hr,
       min_room_use.in_use
FROM room_use AS ru
JOIN min_room_use
  ON ru.left_key = min_room_use.right_key
ORDER BY min_room_use.device_name, ru.dow, ru.hr;"

parse error at line 33, column 3:
  | put left_key := {device_name, in_use}
= ^ ===

Details

Repro is with super commit 97f26ff, which is associated with the changes in #6316.

The SPQ above is a logical equivalent of the mgbench bench3/q3 SQL that I'd created to work around SuperDB not yet having support for correlated subqueries. It was working ok when I was last working on benchmarks, and via binary search I confirmed that the parse error shown above started happening with that merge of #6316. Here it is working ok at super commit 29525d3 that was right before that merged.

$ super -version
Version: 29525d39c

$ super -c "
WITH room_use AS (
  SELECT dow,
         hr,
         device_name,
         AVG(motions) AS in_use
  FROM (
    SELECT dt,
           dow,
           hr,
           device_name,
           COUNT(*) AS motions
    FROM (
      SELECT CAST(log_time AS DATE) AS dt,
             EXTRACT(DOW FROM log_time) AS dow,
             EXTRACT(HOUR FROM log_time) AS hr,
             device_name
      FROM 'bench3.bsup'
      WHERE device_name LIKE 'room%'
        AND event_type = 'motion_start'
        AND log_time >= TIMESTAMP '2019-09-01 00:00:00'
    ) AS r
    WHERE dow IN (1,2,3,4,5)
      AND hr BETWEEN 9 AND 16
    GROUP BY dt,
             dow,
             hr,
             device_name
  ) AS s
  GROUP BY dow,
           hr,
           device_name
  | put left_key := {device_name, in_use}
), min_room_use AS (
  SELECT device_name,
        MIN(in_use) AS in_use
        FROM room_use
        GROUP BY device_name
  | put right_key := {device_name, in_use}
)
SELECT min_room_use.device_name,
       ru.dow,
       ru.hr,
       min_room_use.in_use
FROM room_use AS ru
JOIN min_room_use
  ON ru.left_key = min_room_use.right_key
ORDER BY min_room_use.device_name, ru.dow, ru.hr;"

{device_name:"room_167",dow:2,hr:9,in_use:1.}
{device_name:"room_167",dow:2,hr:10,in_use:1.}
...

I doubt the full input data will be necessary to study this problem, but if anyone needs it, it's at https://super-issues.s3.us-east-2.amazonaws.com/super-6616/bench3.bsup.

[philrz]

@mccanne confirmed that it was user error on my part to attempt to include pipe syntax within my SQL CTE, so this failing after the merge of #6316 is expected. In the verbiage of the recently updated docs, "a CTE body is a SQL operator, not a pipe query." He also pointed out that I could have used named queries instead, but my first attempt at this caused a panic, so I've opened a separate issue #6622 to look into that. Closing this one.