Hi, we are running across an issue with subqueries in left joins. Using
subqueries in LEFT JOIN (or LEFT OUTER JOIN, RIGHT JOIN, etc) throws a
syntax error on valid SQL (tested with postgres).
Without subqueries it returns the correct result: (x1, p, 1, null, null,
null, null, null, null).
The query is valid SQL, we tested with Postgres and MySql, both can
correctly execute with and without subqueries. This seems to be the same
problem that was reported back in April, maybe there was a regression at
some point?
To reproduce:
CREATE TABLE "tri" (
"s" varchar,
"p" varchar,
"o" varchar
);
INSERT INTO "tri" VALUES ('x1', 'p', '1');
INSERT INTO "tri" VALUES ('x2', 's', '2');
COMMIT;
SELECT
*
FROM
(SELECT * FROM tri) t1
LEFT JOIN ((SELECT * FROM tri) t2 JOIN (SELECT * FROM tri) t3 ON t3."p" =
't') ON t2."p" = 's'
WHERE
t1."p" = 'p'
The error is:
Syntax error in SQL statement "SELECT
*
FROM
(SELECT * FROM tri) t1
RIGHT JOIN ((SELECT * FROM tri) t2[*] JOIN (SELECT * FROM tri) t3 ON
t3.""p"" = 't') ON t2.""p"" = 's'
WHERE
t1.""p"" = 'p'
";
expected "UNION, MINUS, EXCEPT, INTERSECT, ORDER, LIMIT, FOR, )"; SQL
statement:
SELECT
*
FROM
(SELECT * FROM tri) t1
RIGHT JOIN ((SELECT * FROM tri) t2 JOIN (SELECT * FROM tri) t3 ON t3."p" =
't') ON t2."p" = 's'
WHERE
t1."p" = 'p'
<http://192.168.0.101:8082/query.do?jsessionid=9af83fcdb9acdbce260563460d1463a1#>
[42001-169]<http://192.168.0.101:8082/query.do?jsessionid=9af83fcdb9acdbce260563460d1463a1#>
42001/42001
(Help)<http://h2database.com/javadoc/org/h2/constant/ErrorCode.html#c42001>
The version without subqueries is:
SELECT
*
FROM
tri t1
LEFT JOIN (tri t2 JOIN tri t3 ON t3."p" = 't') ON t2."p" = 's'
WHERE
t1."p" = 'p'
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/q-5HLPofSBoJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
