Analyzer bug when using CTEs in stored procedures

[max-hoffman]

Version:

> dolt version
dolt version 0.24.4

Working query:

> dolt sql -q "
with t as (
  select
    case
      when p1.pred = p2.actual then 1
      else 0
    end as correct,
    p1.actual
    from predictions as of 'd1rl89u7q511n3tp5q6o5etomvg1mrli' p1
    join predictions as of 'd1rl89u7q511n3tp5q6o5etomvg1mrli' p2
    on p1.row_id = p2.row_id
)
select
  sum(correct)/count(*),
  count(*) as row_number
  from t;
+-----------------------+------------+
| sum(correct)/count(*) | row_number |
+-----------------------+------------+
| 0.1022                | 10000      |
+-----------------------+------------+

Procedure failure:

> dolt sql -q "
CREATE PROCEDURE computeSummary(c VARCHAR(200))
BEGIN
with t as (
  select
    case
      when p1.pred = p2.actual then 1
      else 0
    end as correct,
    p1.actual
    from predictions as of 'd1rl89u7q511n3tp5q6o5etomvg1mrli' p1
    join predictions as of 'd1rl89u7q511n3tp5q6o5etomvg1mrli' p2
    on p1.row_id = p2.row_id
)
select
  sum(correct)/count(*),
  count(*) as row_number
  from t;
END;"
table not found: t

Main error:

table not found: t

Analyzer not resolving CTE table before procedure validation?

[timsehn]

This now fails with a different error.

$ dolt sql -q "
CREATE PROCEDURE computeSummary(c VARCHAR(200))
BEGIN
with t as (
  select
    case
      when p1.pred = p2.actual then 1
      else 0
    end as correct,
    p1.actual
    from predictions as of 'd1rl89u7q511n3tp5q6o5etomvg1mrli' p1
    join predictions as of 'd1rl89u7q511n3tp5q6o5etomvg1mrli' p2
    on p1.row_id = p2.row_id
)
select
  sum(correct)/count(*),
  count(*) as row_number
  from t;
END;"
Error parsing SQL
syntax error at position 388 near 'row_number'

CREATE PROCEDURE computeSummary(c VARCHAR(200))
BEGIN
with t as (
  select
    case
      when p1.pred = p2.actual then 1
      else 0
    end as correct,
    p1.actual
    from predictions as of 'd1rl89u7q511n3tp5q6o5etomvg1mrli' p1
    join predictions as of 'd1rl89u7q511n3tp5q6o5etomvg1mrli' p2
    on p1.row_id = p2.row_id
)
select
  sum(correct)/count(*),
  count(*) as row_number
  from t
^
syntax error at position 388 near 'row_number'
$

[jycor]

The syntax error is caused by recent changes dealing with reserved keywords. This new behavior is expected since row_number is a reserved keyword. Wrapping the row_number in backquotes gets us back the original error.