Hi Dev,
If you select all columns from a primary index but insert a constant column
in between
and then order on all columns, the query will use the full primary key
instead of
using the specified order.
Code to reproduce, bug shows on 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 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 query is correct: ordered on AAA ASC, BBB DES
--
-- 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 query is wrong: instead of ordering AAA ASC, BBB DESC
-- it apparently thinks it can use the complete primary index
-- and produces AAS 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 AS X
FROM TEST
ORDER BY AAA ASC, DUMMY ASC, X 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/e5b73560-4063-4108-836d-d3a5eb24d7a8n%40googlegroups.com.
