Hey there,
I have different queries where I am collecting/grouping data around
their ID and their 2 topmost dates(last 2 dates of entry for each):
--> example:
SELECT
LETZTER.ANLDETAIL_WKN,
LETZTER.DATUM,
VORLETZTER.DATUM
FROM
TICKER
INNER JOIN
(
SELECT
ANLDETAIL_WKN,
DATUM
FROM
ANLKURSE
ORDER BY
ANLKURSE.DATUM DESC LIMIT 1
)AS LETZTER
ON LETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN
INNER JOIN
(
SELECT
ANLDETAIL_WKN,
DATUM
FROM
ANLKURSE
ORDER BY
ANLKURSE.DATUM DESC LIMIT 1,1
)AS VORLETZTER
ON VORLETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN;
Now this works fine up to h2 version 1.3.165
After that the qry does not deliver any results!
The queryplan indicates that the primary key is not being used.
When I strip the LIMIT ... it works though.
simplyfied queries - h2 version above 1.3.165:
--> with LIMIT 1:
EXPLAIN SELECT
LETZTER.ANLDETAIL_WKN,
LETZTER.DATUM
FROM
TICKER
INNER JOIN
(
SELECT
ANLDETAIL_WKN,
DATUM
FROM
ANLKURSE
ORDER BY
ANLKURSE.DATUM DESC LIMIT 1
)AS LETZTER
ON LETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN;
--> result:
SELECT
LETZTER.ANLDETAIL_WKN,
LETZTER.DATUM
FROM PUBLIC.TICKER
/* PUBLIC.TICKER.tableScan */
INNER JOIN (
SELECT
ANLDETAIL_WKN,
DATUM
FROM PUBLIC.ANLKURSE
/* PUBLIC.ANLKURSE.tableScan */
ORDER BY 2 DESC
LIMIT 1
) LETZTER
/* SELECT
ANLDETAIL_WKN,
DATUM
FROM PUBLIC.ANLKURSE
/++ PUBLIC.ANLKURSE.tableScan ++/
ORDER BY 2 DESC
LIMIT 1
*/
ON 1=1
WHERE LETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN
--> no LIMIT:
EXPLAIN SELECT
LETZTER.ANLDETAIL_WKN,
LETZTER.DATUM
FROM
TICKER
INNER JOIN
(
SELECT
ANLDETAIL_WKN,
DATUM
FROM
ANLKURSE
ORDER BY
ANLKURSE.DATUM DESC
)AS LETZTER
ON LETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN;
--> result:
SELECT
LETZTER.ANLDETAIL_WKN,
LETZTER.DATUM
FROM PUBLIC.TICKER
/* PUBLIC.TICKER.tableScan */
INNER JOIN (
SELECT
ANLDETAIL_WKN,
DATUM
FROM PUBLIC.ANLKURSE
/* PUBLIC.ANLKURSE.tableScan */
ORDER BY 2 DESC
) LETZTER
/* SELECT
ANLDETAIL_WKN,
DATUM
FROM PUBLIC.ANLKURSE
/++ PUBLIC.PRIMARY_KEY_44: ANLDETAIL_WKN IS ?1 ++/
WHERE ANLDETAIL_WKN IS ?1
ORDER BY 2 DESC: ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN
AND ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN
*/
ON 1=1
WHERE LETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN
I was wondering if there is any mistake on my side?
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
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.
