Hello,
I encountered an issue using aggregation windows within a JOIN and want to
see if I'm doing something wrong or a bug. I am using version 1.4.198 from
Maven in Java with a server running in-process. I'm issuing these commands
to the server via JDBC.
Here's the set up:
DROP TABLE IF EXISTS Foo;
CREATE TABLE Foo (A INT, B INT);
INSERT INTO Foo VALUES (0, 0);
INSERT INTO Foo VALUES (1, 2);
INSERT INTO Foo VALUES (2, 4);
Which gives us:
A, B
0, 0
1, 2
2, 4
Here is a query using an aggregation window:
SELECT a, ARRAY_AGG(b) OVER (ORDER BY b ROWS BETWEEN 1 FOLLOWING AND 2
FOLLOWING) AS c
FROM Foo;
which correctly gives:
A, C
0, [2, 4]
1, [4]
2, null
The problem is when the aggregation is nested within a JOIN:
SELECT f1.a, f2.c
FROM Foo AS f1
JOIN (
SELECT a, ARRAY_AGG(b) OVER (ORDER BY b ROWS BETWEEN 1 FOLLOWING AND 2
FOLLOWING) AS c
FROM Foo) AS f2
ON (f1.a = f2.a);
This gives a whole bunch of nulls:
A, C
0, null
1, null
2, null
But if I instead create an intermediary table with the aggregated data I
get what I expect:
DROP TABLE IF EXISTS Bar;
CREATE TABLE Bar AS
SELECT a, ARRAY_AGG(b) OVER (ORDER BY b ROWS BETWEEN 1 FOLLOWING AND 2
FOLLOWING) AS c
FROM Foo;
SELECT f1.a, f2.c
FROM Foo AS f1
JOIN Bar as f2
ON (f1.a = f2.a);
Which correctly gives:
A, C
0, [2, 4]
1, [4]
2, null
Any idea what's going on here? Did I goof my SQL?
Thanks!
- Bryan
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
