8000 fix(engine): type json built over a correlated subquery by colinhacks · Pull Request #104 · colinhacks/reylo · GitHub
[go: up one dir, main page]
Skip to content

fix(engine): type json built over a correlated subquery #104

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
colinhacks merged 4 commits into from
Oct 24, 2026
Merged

fix(engine): type json built over a correlated subquery #104

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
4 commits
Select commit Hold shift + click to select a range
3882725
fix(postgres): type json built over a correlated subquery
colinhacks Oct 24, 2026
96d983e
fix(mysql): type json built over a correlated subquery
colinhacks Oct 24, 2026
ad9a5df
fix(mysql): keep the statement's with in a wrapped correlated probe
colinhacks Oct 24, 2026
0865967
build(engine): rebuild the postgres and mysql artifacts
colinhacks Oct 24, 2026
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
10000
Diff view
Diff view
180 changes: 170 additions & 10 deletions engine/mysql/src/mysql_shape.rs
View file
Open in desktop

Large diffs are not rendered by default.

192 changes: 171 additions & 21 deletions engine/postgres/src/jsonshape.rs
View file
Open in desktop

Large diffs are not rendered by default.

1 change: 1 addition & 0 deletions oracle/cases/json_correlated/agg_bare.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select json_agg(json_build_object('id', c.id)) from (select c.id from comment c where c.post_id = p.id) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/agg_correlated_outside.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_agg(json_build_object('id', c.id, 'body', c.body)), '[]') from (select c.id, c.body, c.post_id from comment c join person a on a.id = c.author_id) c where c.post_id = p.id) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/agg_jsonb.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(jsonb_agg(jsonb_build_object('id', c.id, 'body', c.body, 'at', c.created_at)), '[]') from (select c.id, c.body, c.created_at from comment c where c.post_id = p.id limit 2) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/agg_limit.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_agg(json_build_object('id', c.id, 'body', c.body) order by c.created_at desc), '[]') from (select c.id, c.body, c.created_at from comment c where c.post_id = p.id order by c.created_at desc limit 2) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/agg_no_limit.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_agg(json_build_object('id', c.id, 'body', c.body)), '[]') from (select c.id, c.body from comment c where c.post_id = p.id) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/agg_uncorrelated.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_agg(json_build_object('id', c.id, 'body', c.body)), '[]') from (select c.id, c.body from comment c order by c.created_at desc limit 2) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/array_agg_row.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(array_agg(c), '{}') from (select c.id, c.body from comment c where c.post_id = p.id limit 2) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/derived_expression.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_agg(json_build_object('n', c.n, 'title', p.title)), '[]') from (select c.id + p.id as n from comment c where c.post_id = p.id) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/derived_join_person.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_agg(json_build_object('id', c.id, 'author', a.first_name, 'deleted', a.deleted_at)), '[]') from (select c.id, c.author_id from comment c where c.post_id = p.id limit 2) c join person a on a.id = c.author_id) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/derived_left_join.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_agg(json_build_object('id', c.id, 'author', a.first_name)), '[]') from (select c.id, c.author_id from comment c where c.post_id = p.id limit 2) c left join person a on a.id = c.author_id and a.deleted_at is null) as comments from post p
22 changes: 22 additions & 0 deletions oracle/cases/json_correlated/expected.txt
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,22 @@
agg_bare.sql: many () -> { id: int4, comments: [{ id: int4 }]? }
agg_correlated_outside.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
agg_jsonb.sql: many () -> { id: int4, comments: [{ id: int4, body: text, at: timestamptz }] }
agg_limit.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
agg_no_limit.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
agg_uncorrelated.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
array_agg_row.sql: many () -> { id: int4, comments: record[] }
derived_expression.sql: many () -> { id: int4, comments: [{ n: int4?, title: text }] }
derived_join_person.sql: many () -> { id: int4, comments: [{ id: int4, author: text, deleted: timestamptz? }] }
derived_left_join.sql: many () -> { id: int4, comments: [{ id: int4, author: text? }] }
join_on_correlated.sql: many () -> { id: int4, comments: [{ id: int4, author: text }] }
lateral_coalesce_outside.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
lateral_inner_derived.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
lateral_inner_derived_bare.sql: many () -> { id: int4, comments: [{ id: int4, body: text }]? }
lateral_top_grouped.sql: many () -> { id: int4, comments: [{ id: int4, body: text? }] }
nested_subqueries.sql: many () -> { id: int4, posts: [{ title: text, comments: [{ body: text }] }] }
object_limit_one.sql: many () -> { id: int4, latest: { id: int4, body: text }? }
row_to_json_agg.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
to_json_agg.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
to_jsonb_one.sql: many () -> { id: int4, latest: { id: int4, body: text }? }
two_levels.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
with_correlated.sql: many () -> { id: int4, comments: json }
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/join_on_correlated.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_agg(json_build_object('id', c.id, 'author', a.first_name)), '[]') from comment c join person a on a.id = c.author_id and c.post_id = p.id) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/lateral_coalesce_outside.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, coalesce(x.comments, '[]') as comments from post p left join lateral (select json_agg(json_build_object('id', c.id, 'body', c.body)) as comments from comment c where c.post_id = p.id) x on true
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/lateral_inner_derived.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, coalesce(c.comments, '[]') as comments from post p left join lateral (select json_agg(json_build_object('id', c.id, 'body', c.body) order by c.created_at desc) as comments from (select c.id, c.body, c.created_at from comment c where c.post_id = p.id order by c.created_at desc limit 2) c) c on true
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/lateral_inner_derived_bare.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, c.comments from post p left join lateral (select json_agg(json_build_object('id', c.id, 'body', c.body)) as comments from (select c.id, c.body from comment c where c.post_id = p.id limit 2) c) c on true
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/lateral_top_grouped.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, coalesce(json_agg(json_build_object('id', c.id, 'body', c.body)) filter (where c.id is not null), '[]') as comments from post p left join lateral (select c.id, c.body from comment c where c.post_id = p.id order by c.created_at desc limit 2) c on true group by p.id
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/nested_subqueries.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select u.id, (select coalesce(json_agg(json_build_object('title', p.title, 'comments', (select coalesce(json_agg(json_build_object('body', c.body)), '[]') from (select c.body from comment c where c.post_id = p.id and c.author_id = u.id limit 2) c))), '[]') from post p where p.author_id = u.id) as posts from person u
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/object_limit_one.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select json_build_object('id', c.id, 'body', c.body) from (select c.id, c.body from comment c where c.post_id = p.id order by c.created_at desc limit 1) c) as latest from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/row_to_json_agg.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_agg(row_to_json(c)), '[]') from (select c.id, c.body from comment c where c.post_id = p.id limit 2) c) as comments from post p
5 changes: 5 additions & 0 deletions oracle/cases/json_correlated/setup.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,5 @@
-- JSON built over rows a derived table, a WITH query or a LATERAL subquery reads where it refers to
-- a query around it: the latest few children of each parent, as JSON.
create table person (id serial primary key, first_name text not null, last_name text not null, email text not null, deleted_at timestamptz);
create table post (id serial primary key, author_id int not null references person, title text not null, body text not null, status text not null, published_at timestamptz, deleted_at timestamptz);
create table comment (id serial primary key, post_id int not null references post, author_id int not null references person, body text not null, created_at timestamptz not null default now());
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/to_json_agg.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_agg(to_json(c)), '[]') from (select c.id, c.body from comment c where c.post_id = p.id limit 2) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/to_jsonb_one.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select to_jsonb(c) from (select c.id, c.body from comment c where c.post_id = p.id limit 1) c) as latest from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/two_levels.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_agg(json_build_object('id', c.id, 'body', c.body)), '[]') from (select c.id, c.body from (select c.id, c.body from comment c where c.post_id = p.id limit 5) c limit 2) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/json_correlated/with_correlated.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (with c as (select c.id, c.body from comment c where c.post_id = p.id limit 2) select coalesce(json_agg(json_build_object('id', c.id, 'body', c.body)), '[]') from c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/mysql_json_correlated/agg_limit.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_arrayagg(json_object('id', c.id, 'body', c.body)), json_array()) from (select c.id, c.body from comment c where c.post_id = p.id order by c.created_at desc limit 2) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/mysql_json_correlated/agg_no_limit.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_arrayagg(json_object('id', c.id, 'body', c.body)), json_array()) from (select c.id, c.body from comment c where c.post_id = p.id) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/mysql_json_correlated/agg_uncorrelated.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_arrayagg(json_object('id', c.id, 'body', c.body)), json_array()) from (select c.id, c.body from comment c order by c.created_at desc limit 2) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/mysql_json_correlated/derived_join_person.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_arrayagg(json_object('id', c.id, 'author', a.first_name, 'deleted', a.deleted_at)), json_array()) from (select c.id, c.author_id from comment c where c.post_id = p.id limit 2) c left join person a on a.id = c.author_id) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/mysql_json_correlated/dialect
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
mysql
11 changes: 11 additions & 0 deletions oracle/cases/mysql_json_correlated/expected.txt
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,11 @@
agg_limit.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
agg_no_limit.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
agg_uncorrelated.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
derived_join_person.sql: many () -> { id: int4, comments: [{ id: int4, author: text?, deleted: timestamp? }] }
join_on_correlated.sql: many () -> { id: int4, comments: [{ id: int4, author: text }] }
lateral_inner_derived.sql: many () -> { id: int4, comments: json? }
lateral_top_grouped.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
nested_subqueries.sql: many () -> { id: int4, posts: [{ title: text, comments: [{ body: text }] }] }
object_limit_one.sql: many () -> { id: int4, latest: { id: int4, body: text }? }
two_levels.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
with_correlated.sql: many () -> { id: int4, comments: [{ id: int4, body: text }] }
1 change: 1 addition & 0 deletions oracle/cases/mysql_json_correlated/join_on_correlated.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_arrayagg(json_object('id', c.id, 'author', a.first_name)), json_array()) from comment c join person a on a.id = c.author_id and c.post_id = p.id) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/mysql_json_correlated/lateral_inner_derived.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, coalesce(c.comments, json_array()) as comments from post p left join lateral (select json_arrayagg(json_object('id', c.id, 'body', c.body)) as comments from (select c.id, c.body from comment c where c.post_id = p.id order by c.created_at desc limit 2) c) c on true
1 change: 1 addition & 0 deletions oracle/cases/mysql_json_correlated/lateral_top_grouped.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, json_arrayagg(json_object('id', c.id, 'body', c.body)) as comments from post p join lateral (select c.id, c.body from comment c where c.post_id = p.id order by c.created_at desc limit 2) c on true group by p.id
1 change: 1 addition & 0 deletions oracle/cases/mysql_json_correlated/nested_subqueries.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select u.id, (select coalesce(json_arrayagg(json_object('title', p.title, 'comments', (select coalesce(json_arrayagg(json_object('body', c.body)), json_array()) from (select c.body from comment c where c.post_id = p.id and c.author_id = u.id limit 2) c))), json_array()) from post p where p.author_id = u.id) as posts from person u
1 change: 1 addition & 0 deletions oracle/cases/mysql_json_correlated/object_limit_one.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select json_object('id', c.id, 'body', c.body) from (select c.id, c.body from comment c where c.post_id = p.id order by c.created_at desc limit 1) c) as latest from post p
5 changes: 5 additions & 0 deletions oracle/cases/mysql_json_correlated/setup.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,5 @@
-- JSON built over rows a derived table, a WITH query or a LATERAL derived table reads where it
-- refers to a query around it: the latest few children of each parent, as JSON.
create table person (id int primary key, first_name varchar(20) not null, deleted_at datetime);
create table post (id int primary key, author_id int not null, title varchar(20) not null);
create table comment (id int primary key, post_id int not null, author_id int not null, body varchar(40) not null, created_at datetime not null);
1 change: 1 addition & 0 deletions oracle/cases/mysql_json_correlated/two_levels.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select coalesce(json_arrayagg(json_object('id', c.id, 'body', c.body)), json_array()) from (select c.id, c.body from (select c.id, c.body from comment c where c.post_id = p.id limit 5) c limit 2) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/mysql_json_correlated/with_correlated.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (with c as (select c.id, c.body from comment c where c.post_id = p.id limit 2) select coalesce(json_arrayagg(json_object('id', c.id, 'body', c.body)), json_array()) from c) as comments from post p
2 changes: 1 addition & 1 deletion oracle/cases/mysql_json_shapes/expected.txt
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -19,7 +19,7 @@ array_agg_literal.sql: many () -> { l: [bool] }
array_agg_nullable_items.sql: one () -> { emails: [text?]? }
array_agg_objects.sql: one () -> { list: [{ id: int4, name: text }]? }
array_agg_of_json_column.sql: one () -> { docs: [json?]?, wrapped: [{ doc: json? }]? }
array_agg_ordered_subquery.sql: many () -> { titles: [unknown?] }
array_agg_ordered_subquery.sql: many () -> { titles: [text] }
array_agg_placeholder_passed.sql: one (p: numeric) -> { l: [numeric|int8|float8|text?]? }
array_agg_truth.sql: one () -> { l: [bool|int8]? }
array_agg_truth_buffered.sql: one () -> { l: [bool|int8]? }
Expand Down
2 changes: 1 addition & 1 deletion oracle/cases/mysql_subquery_repair/expected.txt
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -23,7 +23,7 @@ subquery_grouped_outer.sql: many () -> { name: text, c: int8, n: int8 }
subquery_in_derived.sql: many () -> { id: int4, n: int8 }
subquery_in_expression.sql: many () -> { id: int4, n: int8?, last: int8 }
subquery_in_where.sql: many () -> { id: int4, name: text }
subquery_inside_cte.sql: many () -> { id: int4, n: int8? }
subquery_inside_cte.sql: many () -> { id: int4, n: int8 }
subquery_min_string.sql: many () -> { id: int4, first: text? }
subquery_no_alias.sql: many () -> { id: int4, (select count(*) from posts p where p.user_id = u.id): int8? }
subquery_no_from.sql: many () -> { id: int4, one: int8|int4, same: text, c: int8 }
Expand Down
4 changes: 2 additions & 2 deletions oracle/cases/mysql_subquery_with/expected.txt
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,6 +1,6 @@
derived_reads_with.sql: one () -> { n: int8 }
nested.sql: one () -> { o: [{ id: int4, n: int8 }] }
nested_correlated.sql: many () -> { id: int4, o: [{ id: unknown?, n: int8 }] }
nested_correlated.sql: many () -> { id: int4, o: [{ id: int4, n: int8 }] }
outer_and_own.sql: one () -> { n: int8 }
own_count.sql: one () -> { n: int8 }
own_count_correlated.sql: many () -> { id: int4, n: int8 }
Expand All @@ -11,5 +11,5 @@ recursive.sql: one () -> { c: int8, s: numeric? }
recursive_beside_self_named.sql: one () -> { c: int8? }
shadows_table.sql: one () -> { titles: [{ id: int4, title: text? }] }
shadows_table_count.sql: one () -> { n: int8 }
wider_shadowed.sql: many () -> { id: int4, o: { a: unknown?, b: unknown? }? }
wider_shadowed.sql: many () -> { id: int4, o: { a: text?, b: text }? }
with_inside_derived.sql: one () -> { n: int8 }
1 change: 1 addition & 0 deletions oracle/cases/sqlite_json_correlated/agg_limit.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select json_group_array(json_object('id', c.id, 'body', c.body)) from (select c.id, c.body from comment c where c.post_id = p.id order by c.created_at desc limit 2) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/sqlite_json_correlated/agg_no_limit.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select json_group_array(json_object('id', c.id, 'body', c.body)) from (select c.id, c.body from comment c where c.post_id = p.id) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/sqlite_json_correlated/agg_uncorrelated.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select json_group_array(json_object('id', c.id, 'body', c.body)) from (select c.id, c.body from comment c order by c.created_at desc limit 2) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/sqlite_json_correlated/derived_join_person.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ - 10BC8 0,0 +1 @@
select p.id, (select json_group_array(json_object('id', c.id, 'author', a.first_name, 'deleted', a.deleted_at)) from (select c.id, c.author_id from comment c where c.post_id = p.id limit 2) c left join person a on a.id = c.author_id) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/sqlite_json_correlated/dialect
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
sqlite
9 changes: 9 additions & 0 deletions oracle/cases/sqlite_json_correlated/expected.txt
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,9 @@
agg_limit.sql: many () -> { id: integer, comments: [{ id: integer, body: text }] }
agg_no_limit.sql: many () -> { id: integer, comments: [{ id: integer, body: text }] }
agg_uncorrelated.sql: many () -> { id: integer, comments: [{ id: integer, body: text }] }
derived_join_person.sql: many () -> { id: integer, comments: [{ id: integer, author: text?, deleted: text? }] }
join_on_correlated.sql: many () -> { id: integer, comments: [{ id: integer, author: text }] }
nested_subqueries.sql: many () -> { id: integer, posts: [{ title: text, comments: unknown }] }
object_limit_one.sql: many () -> { id: integer, latest: { id: integer, body: text }? }
two_levels.sql: many () -> { id: integer, comments: [{ id: integer, body: text }] }
with_correlated.sql: many () -> { id: integer, comments: [{ id: integer, body: text }] }
1 change: 1 addition & 0 deletions oracle/cases/sqlite_json_correlated/join_on_correlated.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select json_group_array(json_object('id', c.id, 'author', a.first_name)) from comment c join person a on a.id = c.author_id and c.post_id = p.id) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/sqlite_json_correlated/nested_subqueries.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select u.id, (select json_group_array(json_object('title', p.title, 'comments', (select json_group_array(json_object('body', c.body)) from (select c.body from comment c where c.post_id = p.id and c.author_id = u.id limit 2) c))) from post p where p.author_id = u.id) as posts from person u
1 change: 1 addition & 0 deletions oracle/cases/sqlite_json_correlated/object_limit_one.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select json_object('id', c.id, 'body', c.body) from (select c.id, c.body from comment c where c.post_id = p.id order by c.created_at desc limit 1) c) as latest from post p
5 changes: 5 additions & 0 deletions oracle/cases/sqlite_json_correlated/setup.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,5 @@
-- JSON built over rows a derived table or a WITH query reads where it refers to a query around
-- it: the latest few children of each parent, as JSON.
create table person (id integer primary key, first_name text not null, deleted_at text);
create table post (id integer primary key, author_id integer not null references person, title text not null);
create table comment (id integer primary key, post_id integer not null references post, author_id integer not null references person, body text not null, created_at text not null);
1 change: 1 addition & 0 deletions oracle/cases/sqlite_json_correlated/two_levels.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (select json_group_array(json_object('id', c.id, 'body', c.body)) from (select c.id, c.body from (select c.id, c.body from comment c where c.post_id = p.id limit 5) c limit 2) c) as comments from post p
1 change: 1 addition & 0 deletions oracle/cases/sqlite_json_correlated/with_correlated.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
select p.id, (with c as (select c.id, c.body from comment c where c.post_id = p.id limit 2) select json_group_array(json_object('id', c.id, 'body', c.body)) from c) as comments from post p
Binary file modified packages/mysql/engine.wasm
View file
Open in desktop
Binary file not shown.
Binary file modified packages/postgres/engine.wasm
View file
Open in desktop
Binary file not shown.
Loading
0