error parsing SQL with in-line views and JOIN/LEFT JOIN
90 views
Skip to first unread message
Mariano Rodriguez Muro
Jul 4, 2013, 9:43:55 AM7/4/13
to h2-da...@googlegroups.com, marti...@gmail.com, mindauga...@gmail.com
Hi,
we are having issues with H2 SQL parser. It appears that it is having problems understanding queries that contain
inline views, together with normal tables and JOIN/LEFT JOIN combined in the same query.
The error appears only when there are all these things combined (first a table, then an inline view with both, another inline view and a JOIN or LEFT JOIN).
It would also seems its only a parsing error.
We include the minimal cases to reproduce it. We tested with Postgres, MySQL and DB2 and the query is valid in all of them.
Is there any chance to get this one fixed? Is it a hard one?
Thank you in advance
CREATE TABLE "TABLE1" (
"ID" integer NOT NULL
);
-- THIS DOESNT WORK
SELECT *
FROM
"TABLE1" QVIEW1,
(
(
SELECT *
FROM
"TABLE1" QVIEW2
) QVIEW4
JOIN
"TABLE1" QVIEW5
ON ( 1= 1)
) v1
-- THIS DOESNT WORK
SELECT *
FROM
"TABLE1" QVIEW1,
(
(
(
SELECT *
FROM
"TABLE1" QVIEW2
)
UNION ALL
(
SELECT *
FROM
"TABLE1" QVIEW3
)
) QVIEW4
LEFT OUTER JOIN
"TABLE1" QVIEW5
ON ( 1= 1)
)
-- THIS DOESNT WORK
SELECT *
FROM
"TABLE1" QVIEW1,
(
(
(
SELECT *
FROM
"TABLE1" QVIEW2
)
UNION ALL
(
SELECT *
FROM
"TABLE1" QVIEW3
)
) QVIEW4
JOIN
"TABLE1" QVIEW5
ON ( 1= 1)
)
-- THIS WORKS
SELECT *
FROM
"TABLE1" QVIEW1,
(
(
(
SELECT *
FROM
"TABLE1" QVIEW2
)
UNION ALL
(
SELECT *
FROM
"TABLE1" QVIEW3
)
)
) V1
Noel Grandin
Jul 4, 2013, 10:19:46 AM7/4/13
to h2-da...@googlegroups.com, Mariano Rodriguez Muro, marti...@gmail.com, mindauga...@gmail.com
Hi
We don't support JOIN outside of the context of a SELECT.
But you should be able to modify it easily enough to work in all the
databases.
For example, your first query should look like:
We don't support JOIN outside of the context of a SELECT.
But you should be able to modify it easily enough to work in all the
databases.
For example, your first query should look like:
SELECT *
FROM
"TABLE1" QVIEW1,
(
SELECT * FROM // <<<<<<<<<<<<<<<<
FROM
"TABLE1" QVIEW1,
(
(
SELECT *
FROM
"TABLE1" QVIEW2
) QVIEW4
JOIN
"TABLE1" QVIEW5
ON ( 1= 1)
) v1
Regards, Noel.
SELECT *
FROM
"TABLE1" QVIEW2
) QVIEW4
JOIN
"TABLE1" QVIEW5
ON ( 1= 1)
) v1
Reply all
Reply to author
Forward
0 new messages