Bug: Trivial query returns result in wrong order

40 views

Skip to first unread message

Erik

unread,

Nov 19, 2020, 5:36:20 AM11/19/20

to H2 Database

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