PullUpCorrelatedPredicateAggRule is wrong for non-null-propagating expression #19835
Labels
type/bug
Something isn't working
Milestone
Comments
maingoh
changed the title
|
It seems to return empty map, instead of null: dev=> select * from t;
┌───┐
│ x │
├───┤
└───┘
(0 rows)
dev=> select map_from_entries( array( select row(x,x) from t ));
┌──────────────────┐
│ map_from_entries │
├──────────────────┤
│ {} │
└──────────────────┘
(1 row)Could you provide a reproduciable example? |
|
Sorry I wasn't really sure of why I had null values sometimes, but I could reproduce now. I have 3 tables: CREATE TABLE foo (
id int
);
CREATE TABLE bar (
id int,
foo_id int,
baz_id int
);
CREATE TABLE baz (
id int
);
INSERT INTO foo (id) VALUES (1);
INSERT INTO bar (id, foo_id, baz_id) VALUES (1, 1, 123);
INSERT INTO bar (id, foo_id, baz_id) VALUES (2, 1, 123);
select
map_from_entries(array(
select
row('foo', 'bar') -- could be anything here
from bar
inner join baz
on baz.id
= bar.baz_id
where bar.foo_id = foo.id
)) as my_map
from foo
where foo.id = 1
;
-[ RECORD 1 ]--
my_map | <null>The fact that I have one condition in the select
map_from_entries(array(
select
row('foo', 'bar') -- could be anything here
from bar
inner join baz
on baz.id
= bar.baz_id
and bar.foo_id = foo.id
)) as my_map
from foo
where foo.id = 1
;
-[ RECORD 1 ]
my_map | {}In my opinion both expressions are equivalent no ? It actually generates different query plans whether the condition is in the |
|
We can see some |
|
So the problem is not in CREATE TABLE foo (
id int
);
CREATE TABLE bar (
id int,
foo_id int
);
insert into foo values (1),(2);
-- RW returns 2 null rows, but PG returns 2 empty arrays
select
array(
select
1
from bar
where bar.foo_id = foo.id
)
from foo;
|
|
|
|
Hmmm, another case: select foo.id, (select coalesce( max(id), 114514) from bar where bar.foo_id = foo.id) from foo;
-- got
┌────┬──────────┐
│ id │ ?column? │
├────┼──────────┤
│ 1 │ ∅ │
│ 2 │ ∅ │
└────┴──────────┘
-- expected
+----+----------+
| id | coalesce |
|----+----------|
| 1 | 114514 |
| 2 | 114514 |
+----+----------+ |
Sounds similar to #15590? |
xxchan
changed the title
8 tasks
|
Wow I didn't expect a resolution as fast, thank you @xxchan !
Indeed, sorry as the map feature was quite new I thought it was likely the cause. I have a small question though, as the function is called select
map_from_entries(
select row(1, 1)
from bar
where bar.foo_id = foo.id
)
from foo;Or wrapped by parenthesis: select
map_from_entries((
select row(1, 1)
from bar
where bar.foo_id = foo.id
))
from foo; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Describe the bug
I couldn't create a sample as I don't know how to how to create an empty array of rows. But a query like below return a
nullmap when the subquery return no rows. I expected it to return an empty map instead.Also, couldn't we simplify the syntax by removing the explicit
array()?Error message/log
No response
To Reproduce
Expected behavior
Return an empty map instead of null
How did you deploy RisingWave?
No response
The version of RisingWave
version | PostgreSQL 13.14.0-RisingWave-2.0.4 (2d75798)
Additional context
No response
The text was updated successfully, but these errors were encountered: