Hi Dev,
Hope this is the place to post bugs?
When interleaving a constant in between columns making up a (primary?)
index the order direction is ignored and the full index is used instead.
Code to reproduce, fails with 1.4.199 and 1.4.200:
CREATE TABLE TEST
(
AAA INTEGER NOT NULL,
BBB INTEGER NOT NULL
);
-- if you leave out this index both selects below will work correctly
ALTER TABLE TEST ADD PRIMARY KEY (AAA, BBB);
INSERT INTO TEST (AAA, BBB)
VALUES
(1,1),
(1,2),
(1,3),
(2,1),
(2,2),
(2,3);
-- next select is correct: ordered on AAA ASC, BBB DESC
--
-- AAA DUMMY BBB
-- 1 1 3
-- 1 1 2
-- 1 1 1
-- 2 1 3
-- 2 1 2
-- 2 1 1
--
SELECT AAA, 1 AS DUMMY, BBB
FROM TEST
ORDER BY AAA ASC, BBB DESC;
-- next select is wrong: instead of ordering AAA ASC, BBB DESC
-- it apparently thinks it can use the complete primary index
-- and produces AAA ASC, BBB ASC
--
-- AAA DUMMY BBB
-- 1 1 1
-- 1 1 2
-- 1 1 3
-- 2 1 1
-- 2 1 2
-- 2 1 3
--
SELECT AAA, 1 AS DUMMY, BBB
FROM TEST
ORDER BY AAA ASC, DUMMY ASC, BBB DESC;
Cheers,
Erik
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/162e6b25-85da-4efa-9a6d-aeb5683ee2c9n%40googlegroups.com.
