insert sorted select not works as expected
GoogleCodeExporter opened this issue · 5 comments
GoogleCodeExporter commented
What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. CREATE TABLE SRCBUG(AFLD INT NOT NULL);
2. CREATE TABLE TESTBUG2(PKFLD INT NOT NULL PRIMARY KEY, AFLD INT NOT NULL);
3. INSERT INTO SRCBUG(AFLD) VALUES (23), (3), (51), (17), (11), (43), (19);
4. SET @PKFLD_VALUE = 0;
5. INSERT INTO TESTBUG2(PKFLD, AFLD) SELECT SET(@PKFLD_VALUE, @PKFLD_VALUE +
1), AFLD FROM SRCBUG ORDER BY AFLD;
6. SELECT * FROM TESTBUG2;
What is the expected output? What do you see instead?
I expect rows inserted in selected-sorted order. I see rows inserted in
"creation" order.
What version of the product are you using? On what operating system, file
system, and virtual machine?
H2 1.3.176 (2014-04-05); lubuntu 14.04 64 bits; ext4; OpenJDK Runtime
Environment (IcedTea 2.5.4) (7u75-2.5.4-1~trusty1), OpenJDK 64-Bit Server VM
(build 24.75-b04, mixed mode)
Do you know a workaround?
Yes, use subquery in from:
SET @PKFLD_VALUE = 0;
INSERT INTO TESTBUG2(PKFLD, AFLD) SELECT SET(@PKFLD_VALUE, @PKFLD_VALUE + 1),
t1.AFLD FROM (SELECT t.AFLD FROM SRCBUG t ORDER BY t.AFLD) t1;
What is your use case, meaning why do you need this feature?
It is for cached search results with pagination for speed and not redundant
cpu/io usage: ... WHERE PKFLD >= @OFFSET + 1 AND PKFLD <= @OFFSET + @LIMIT
How important/urgent is the problem for you?
Not important, not urgent.
Please provide any additional information below.
I tried INSERT ... DIRECT SORTED ..., and variants but same result.
Original issue reported on code.google.com by software...@gmail.com
on 17 Mar 2015 at 3:55
GoogleCodeExporter commented
> I expect rows inserted
You mean you expect that the "select" statement, without using "order by",
returns the rows in a specific order? That's not guaranteed, whatever you do.
You need to change your expectation.
Original comment by thomas.t...@gmail.com
on 18 Mar 2015 at 6:51
- Changed state: Invalid
GoogleCodeExporter commented
Please see the "... ORDER BY AFLD;" in step 5.
I expect rows be inserted in the order returned by the select.
Original comment by software...@gmail.com
on 18 Mar 2015 at 1:06
GoogleCodeExporter commented
The issue tracker is not questions, it is for confirmed issued, and this here
is not.
Well the final "6. SELECT * FROM TESTBUG2;" does not have an "order by", and
that's the problem.
It doesn't matter in what "order" you insert rows. If the "select" statement at
step 6 does not have an "order by", then the order of the result of that query
is undefined. This is not just H2, it applies to SQL in general (to almost all
databases).
If you are not happy with that, please ask a question at StackOverflow.com.
Original comment by thomas.t...@gmail.com
on 18 Mar 2015 at 1:13
GoogleCodeExporter commented
Thanks for your answer.
I appreciate so much your work. Really.
Maybe I don't be clear enough:
Let us replace 6. by
6'. SELECT * FROM TESTBUG2 ORDER BY PKFLD;
According to the "ORDER BY" when rows were inserted, and the PKFLD values
generation, rows from TESTBUG2 becomes a sequence of AFLD. That's 6' must shows
AFLD sorted automatically because was sorted by PKFLD.
Original comment by software...@gmail.com
on 18 Mar 2015 at 1:23
GoogleCodeExporter commented
OK, I think I understand now what you want. Again, it would be better if you
used the mailing list or StackOverflow. Please do that next time.
The "order by" is done after the query itself.
You probably want to use this:
SELECT SET(@PKFLD_VALUE, @PKFLD_VALUE + 1), AFLD FROM (SELECT AFLD FROM SRCBUG
ORDER BY AFLD);
Original comment by thomas.t...@gmail.com
on 18 Mar 2015 at 1:32