go-mysql-server icon indicating copy to clipboard operation
go-mysql-server copied to clipboard

CTE w/ stored procedures

Open max-hoffman opened this issue 3 years ago • 0 comments

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?

max-hoffman avatar Apr 05 '21 17:04 max-hoffman