insert sorted select not works as expected

[GoogleCodeExporter]

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]

> 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]

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]

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]

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]

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