I have committed:
-
https://github.com/jOOQ/jOOQ/commit/d77e77696b10f1c6fe95c99f357330cb369eb077
-
https://github.com/jOOQ/jOOQ/commit/cf00bd9d6d71ec71f8a0912fc077766858971f68
... and some other changes. This fix will take care of the type of query
that you were writing. It doesn't work for most databases yet, in
particular not for Oracle, SQL Server, Sybase, and DB2.
Some caveats that I encountered:
- Some databases don't allow any ORDER BY clause in UNION subselects. In
those cases, the subselect must be transformed into a nested table
- The nested LIMIT clauses are not yet correctly emulated in Oracle, SQL
Server 2008, Sybase and DB2 when used in a UNION subselect
- Derived tables are currently enforced for UNION subselects also for MySQL
in situations where they wouldn't be strictly necessary
This whole thing will be an interesting blog post subject. How to correctly
transform the following query into valid SQL for all 17 supported RDBMS:
( -- Not all databases support these parentheses
SELECT a1
FROM t1
ORDER BY b1 -- Not all databases support ORDER BY in UNION subselects
LIMIT 1 -- Not all databases support LIMIT .. OFFSET
OFFSET 2
)
UNION
(
SELECT a2
FROM t2
UNION ALL -- Not all databases support nested set operations (e.g.
MySQL)
SELECT a3
FROM t3
)
ORDER BY a1 -- Not all databases expose the column name of the first
UNION subselect to ORDER BY
LIMIT 1
OFFSET 1
And besides, INTERSECT and EXCEPT aren't always supported either, but can
be emulated...
More updates will follow,
Cheers
Lukas
2014-10-15 17:01 GMT+02:00 Lukas Eder <[email protected]>:
>
>
> 2014-10-13 19:42 GMT+02:00 Alok Menghrajani <[email protected]>:
>
>> On Fri, Oct 10, 2014 at 6:42 AM, Lukas Eder <[email protected]> wrote:
>>
>>> Hi Alok,
>>>
>>> This is now implemented on GitHub master for jOOQ 3.5.0. The change for
>>> #3579 fixed a couple of issues for MySQL (and other databases), which have
>>> trouble with more standard renderings of UNIONs and other set operations.
>>> If you could provide some feedback, that would be greatly appreciated.
>>>
>>
>> It's better in some ways (the result is now a valid mysql query), but I'm
>> not sure the query matches what the programmer expects.
>>
>> 3.4.2 returns an invalid query:
>> select * from jooqtest2 where name = ? order by ts asc limit ? offset ?
>> union all select * from jooqtest2 where name = ? order by ts asc limit ?
>> offset ?
>>
>> 3.5.0 now returns a valid query. The limit is applied at the end (after
>> the union all), the result can only ever be a single row.
>> select * from jooqtest2 where name = ? union all select * from (select *
>> from jooqtest2 where name = ? order by ts asc limit ?) x order by ts asc
>> limit ?
>>
>>
>> I would expect the code to return two rows when each select matches:
>> (select * from jooqtest2 where name = ? order by ts asc limit ?) union
>> all (select * from jooqtest2 where name = ? order by ts asc limit ?)
>>
>
> You're right of course - I'm sorry, I must have misunderstood a previous
> E-Mail. Yes, I can reproduce this behaviour.
>
> Will investigate the options and keep you posted.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
