use of LIMIT in JOINs above version 1.3.165 obstructs using primary key

[Dani]

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?

[Dani]

--> same query - version 1.3.165


--> LIMIT 1

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.PRIMARY_KEY_44: ANLDETAIL_WKN IS ?1 ++/
WHERE ANLDETAIL_WKN IS ?1
ORDER BY 2 DESC

LIMIT 1: ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN

[Dani]

Hi,

again as I stated before the queries worked fine up to h2 version
1.3.165
With the higher versions they keep running till I cancel them.
The only difference I found is indicated by the queryplan (above). The
db engine seems to use a different approach. Also when I leave out
LIMIT it seems to make a difference.

The primary key is ANLDETAIL_WKN + DATUM (ID + date)
I used to get the last and the second last date for every ID.
MAX(date) will fail because the last date can be different for every
ID!
The second last date is not always the day before the last date!


Example for possible result:

ANLDETAIL_WKN LETZTER.DATUM VORLETZTER.DATUM

840400 2012-05-31 2012-05-01
A0JLZ2 2011-01-10 2009-10-04

Does anyone have an idea what else I could try or whats going on
here!?
This used to work!

Thanks

[Thomas Mueller]

Hi,

Could you simplify your test case? See
http://h2database.com/html/build.html#support

Regards,
Thomas

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>

[Dani]

Hi,

OK, I uploaded 3 files that hopefully will help to circle in on this!
They all have 3 tables with the same structure.
Table QUERIES contains 2 queries --> copy/paste, they should run fine.

username xy
no password


This one gets the results the way I would like them to be (and they
have been with previous versions):

1.3.165_A --
http://www.file-upload.net/download-4408331/test_A_1.3.165.h2.db.html


This one is exactly the same only with less data and the INNER JOIN
delivers only 1 match which it should NOT:

1.3.165_B
http://www.file-upload.net/download-4408294/test_B_1.3.165.h2.db.html


And finally this one is of the next H2 version and should be opened
accordingly.
It delivers totally different results but is the same as 1.3.165_B:

1.3.166_B
http://www.file-upload.net/download-4408298/test_B_1.3.166.h2.db.html



I tested with the according org.h2.Drivers 1.3.165 and 1.3.166 with
EQ.
I hope this is what you call a test case ;-)

Thanks a lot,


Dani

[Thomas Mueller]

Hi,

I think this is due to issue 387: WHERE condition getting pushed into
sub-query with LIMIT. I'm afraid you will not be able to use such
subqueries any longer, and have to find a different way to solve this
problem. I'm sorry.

With "simple test case" I actually meant a "a short, self contained,
correct (compilable), example of the problem". See http://sscce.org/ -
A simple Java program that created the needed tables and data, and
then runs the query. It should contain only those tables, columns, and
data that are not need to reproduce the problem, making the test case
as simple as possible.

Regards,
Thomas

[Dani]

Thank you Thomas for looking into this.

I was affraid it could be something like that.
So I will have to use version 1.3.165 for a while...

Hm, I tried using TOP x and Max() functions but those won't get me
there.

I need the last and the second last date for every ID
where the last date can be different for every ID!

Maybe anyone else can think of an approach?

Thanks again

[Thomas Mueller]

Hi,

Maybe you could ask at StackOverflow? It sounds like a generic SQL
problem, not related to H2. It sounds like an interesting problem,
there is a very good chance that people will help you there.

Regards,
Thomas

[Rami Ojares]

Take the sorting and limiting out of the subquery.
Like this:

SELECT LETZTER.ANLDETAIL_WKN, LETZTER.DATUM, VORLETZTER.DATUM
FROM TICKER

INNER JOIN (
SELECT ANLDETAIL_WKN, DATUM
FROM ANLKURSE

)AS LETZTER
ON LETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN

INNER JOIN (
SELECT ANLDETAIL_WKN, DATUM
FROM ANLKURSE

)AS VORLETZTER
ON VORLETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN

AND VORLETZTER.ID<> LETZTER.ID

ORDER BY LETZTER.DATUM DESC, VORLETZTER.DATUM DESC
LIMIT 1

This assumes that table ANLKURSE has a unique key column.
I have not tested this but just wrote it here in case it might prove
helpful to you.

And in the future formulate your questions about sql in such a fashion
that it is
simple for others to try out.

Eg. Create the following tables
<3 create table statements>
Execute the following query
<select statement>
Expected result
<something>
Current result
<something else>

- Rami

[Dani]

Thanks Rami,

unique key for
ANLKURSE is over ANLDETAIL_WKN and DATUM (ID and date)
TICKER is ANLDETAIL_WKN

Your query does not show the result for every ID.

I came up with the following solution. It is fast but I don't like it
because in case I need data from another column of ANLKURSE the GROUP
BY clause will be a problem. I would have to add some more JOINS on
top:


SELECT
TICKER.ANLDETAIL_WKN,
VORLETZTER.VORLETZTER_DATUM,
VORLETZTER.LETZTER_DATUM

FROM
TICKER
INNER JOIN
(
SELECT

MAX(DATUM) AS VORLETZTER_DATUM,
LETZTER_DATUM,
ANLDETAIL_WKN
FROM
ANLKURSE
INNER JOIN
(
SELECT
MAX(DATUM) AS LETZTER_DATUM,
ANLDETAIL_WKN AS LETZTER_WKN
FROM
ANLKURSE
GROUP BY
ANLDETAIL_WKN
) AS LETZTER
ON LETZTER.LETZTER_WKN = ANLKURSE.ANLDETAIL_WKN
AND LETZTER.LETZTER_DATUM <> ANLKURSE.DATUM
GROUP BY
ANLDETAIL_WKN) AS VORLETZTER
ON VORLETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN;


script for a test db ->

SET CLUSTER '';
SET DEFAULT_TABLE_TYPE 0;
SET WRITE_DELAY 500;
SET DEFAULT_LOCK_TIMEOUT 2000;
SET CACHE_SIZE 16384;
;
CREATE USER IF NOT EXISTS XY PASSWORD '' ADMIN;
CREATE SEQUENCE
PUBLIC.SYSTEM_SEQUENCE_547EF21C_5CAB_4AD2_8ED4_B85103E8CB7B START WITH
529 BELONGS_TO_TABLE;
CREATE SEQUENCE
PUBLIC.SYSTEM_SEQUENCE_F1EDB90A_3E59_4C50_BE7F_67E7BFABB2CF START WITH
136 BELONGS_TO_TABLE;
CREATE SEQUENCE
PUBLIC.SYSTEM_SEQUENCE_14A944B3_8EC2_488B_A30D_22510B08BEC7 START WITH
120 BELONGS_TO_TABLE;
CREATE CACHED TABLE PUBLIC.TICKER(
ANLDETAIL_WKN VARCHAR(25) NOT NULL SELECTIVITY 100
);
ALTER TABLE PUBLIC.TICKER ADD CONSTRAINT PUBLIC.CONSTRAINT_17 PRIMARY
KEY(ANLDETAIL_WKN);
-- 2 +/- SELECT COUNT(*) FROM PUBLIC.TICKER;
INSERT INTO PUBLIC.TICKER(ANLDETAIL_WKN) VALUES
('855018'),
('620200');
CREATE CACHED TABLE PUBLIC.ANLKURSE(
ANLDETAIL_WKN VARCHAR(25) NOT NULL SELECTIVITY 1,
KURS DECIMAL(10, 4) DEFAULT 0 SELECTIVITY 72,
DATUM DATE NOT NULL SELECTIVITY 37
);
ALTER TABLE PUBLIC.ANLKURSE ADD CONSTRAINT PUBLIC.CONSTRAINT_1 PRIMARY
KEY(ANLDETAIL_WKN, DATUM);
-- 20 +/- SELECT COUNT(*) FROM PUBLIC.ANLKURSE;
INSERT INTO PUBLIC.ANLKURSE(ANLDETAIL_WKN, KURS, DATUM) VALUES
('620200', 36.1600, DATE '2012-05-29'),
('855018', 46.4300, DATE '2012-05-29'),
('855018', 45.0000, DATE '2012-05-28'),
('855018', 43.8500, DATE '2012-05-25'),
('855018', 44.0000, DATE '2012-05-24'),
('855018', 42.7900, DATE '2012-05-23'),
('855018', 45.1500, DATE '2012-05-22'),
('855018', 43.0300, DATE '2012-05-21'),
('620200', 35.3600, DATE '2012-05-28'),
('620200', 34.8900, DATE '2012-05-25'),
('620200', 35.5900, DATE '2012-05-24'),
('620200', 35.5000, DATE '2012-05-23'),
('620200', 36.8000, DATE '2012-05-22'),
('620200', 36.0000, DATE '2012-05-21'),
('620200', 35.3300, DATE '2012-05-30'),
('855018', 44.6500, DATE '2012-05-30'),
('620200', 34.6300, DATE '2012-05-31'),
('620200', 34.3400, DATE '2012-06-01'),
('855018', 43.0600, DATE '2012-06-01'),
('840400', 72.8500, DATE '2012-05-31');
CREATE INDEX PUBLIC.ANLKURSE_DATUM ON PUBLIC.ANLKURSE(DATUM);
CREATE INDEX PUBLIC.ANLKURSE_KURS ON PUBLIC.ANLKURSE(KURS);
CREATE INDEX PUBLIC.ANLKURSE_ANLDETAIL_WKN ON
PUBLIC.ANLKURSE(ANLDETAIL_WKN);

[Rami Ojares]

Here is an example that shows how to make the table creation readable
and understandable.
Another improvement would be to come up with understandable column names
and formulate the query in normal language.

Since I don't know exactly what is the question you are trying to formulate
I am merely shooting in the dark.

-- create a test schema
CREATE SCHEMA TEST;
SET SCHEMA TEST;

-- create a table ticker that represents ....
CREATE TABLE TICKER(ANLDETAIL_WKN VARCHAR(25) PRIMARY KEY);

INSERT INTO PUBLIC.TICKER(ANLDETAIL_WKN) VALUES ('855018'), ('620200');

-- create a table anlkurse that represents ...
CREATE TABLE ANLKURSE(
ANLDETAIL_WKN VARCHAR(25) NOT NULL,
KURS DECIMAL(10, 4) DEFAULT 0,
DATUM DATE NOT NULL
);
ALTER TABLE ANLKURSE ADD CONSTRAINT PUBLIC.CONSTRAINT_1 PRIMARY
KEY(ANLDETAIL_WKN, DATUM);

[Rami Ojares]

And here is the query adapted to the composite primary key of anlkurse
table.
Can you explain more clearly what you mean when you say:

"Your query does not show the result for every ID."

SELECT LETZTER.ANLDETAIL_WKN, LETZTER.DATUM, VORLETZTER.DATUM
FROM TICKER

JOIN (
SELECT ANLDETAIL_WKN, DATUM
FROM ANLKURSE
)AS LETZTER
ON LETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN

JOIN (
SELECT ANLDETAIL_WKN, DATUM
FROM ANLKURSE

)AS VORLETZTER
ON VORLETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN
AND CAST(FORMATDATETIME(VORLETZTER.DATUM, 'yyyyMMdd') AS BIGINT) +
CAST(VORLETZTER.ANLDETAIL_WKN AS BIGINT) <>
CAST(FORMATDATETIME(LETZTER.DATUM, 'yyyyMMdd') AS BIGINT) +
CAST(LETZTER.ANLDETAIL_WKN AS BIGINT)

[Dani]

Rami,

well, I thought you wanted a scipt that reproduces a test case.
That is what it does!

The CAST part is very interesting, I learned something here :-)

As I stated before, this is what I am looking for and what the query I
came up with in my last post delivers:


ANLDETAIL_WKN VORLETZTER_DATUM LETZTER_DATUM
620200 2012-05-31 2012-06-01
855018 2012-05-30 2012-06-01

So I am trying to get VORLETZTER_DATUM , LETZTER_DATUM for every
ANLDETAIL_WKN in the same recordset!
I was referring to ANLDETAIL_WKN as the ID
I am sorry for the cofusion.

The output of your query is:

ANLDETAIL_WKN DATUM DATUM
620200 2012-06-01 2012-05-31


I ran your query against my database the table ANLKURSE contains about
200.000 records and 200 different ANLDETAIL_WKN
The query took about 1.50 min the one I provided less than a second.

As I said before ANLKURSE contains more columns than provided in this
example.
And also when I JOIN more Tables my query is getting a bit complicated
even though it runs fine.

That is why I would prefer a 'clean' query like the one you provided
and the one I posted in my first post that used to do the job up to h2
version 1.3.165
-- possibly due to issue 387: WHERE condition getting pushed into
sub-query with LIMIT
so it should not have worked in the first place.


Anyway, thanks Rami for taking the time

[Thomas Mueller]

Hi,

Why so complicated, why don't you just use a simple join?

SELECT *
FROM TICKER
INNER JOIN ANLKURSE AS LETZTER ON ..
INNER JOIN ANLKURSE AS VORLETZTER ON ...
ORDER BY ...
LIMIT 1

Again, please use StackOverflow for such questions. The H2 Group is
not the right place for basic SQL join questions.

Regards,
Thomas