INSERT ... DIRECT SORTED fails

[GoogleCodeExporter]

Before submitting a bug, please check the FAQ:
http://www.h2database.com/html/faq.html

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. Execute this code

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;

import org.apache.commons.codec.binary.Base64;

public class SortTest {

    /**
     * @param args
     * @throws SQLException 
     */
    public static void main(String[] args) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:h2:test");
        Statement stmt = conn.createStatement();

        stmt.execute("create table if not exists A(ID INT PRIMARY KEY, NAME VARCHAR, RNUM INT)");
        stmt.execute("delete from A");
        stmt.execute("create table if not exists A_R(ID INT, FOREIGN KEY(ID) REFERENCES A(ID), RNUM INT)");
        stmt.execute("create index if not exists A_R_RNUM on A_R (RNUM)");
        PreparedStatement pstmt = conn.prepareStatement("insert into a(ID, NAME) values(?, ?)");
        Random r = new Random();
        byte[] ba = new byte[6];
        int count = 100000;
        for(int i = 0;i<count;i++) {
            pstmt.setInt(1, i);
            r.nextBytes(ba);
            pstmt.setString(2, new String(Base64.encodeBase64(ba)));
            pstmt.execute();
        }
        conn.commit();
        for(int i = 0;i<5;i++) {
            sort3(conn, "name asc");
        }
    }

    private static void sort3(Connection conn, String order) throws SQLException {
        Statement stmt = conn.createStatement();
        stmt.execute("CREATE MEMORY TEMPORARY TABLE ZZZ(ID INT, RNUM INT) NOT PERSISTENT");
        stmt.execute("insert into ZZZ(ID, RNUM) direct sorted select A.ID, ROWNUM() from A inner join A_R on A.ID=A_R.ID order by " + order);
        stmt.execute("drop index A_R_RNUM");
        stmt.execute("delete from A_R");
        stmt.execute("insert into A_R(ID,RNUM) direct sorted select ID, RNUM from ZZZ");
        conn.commit();
        stmt.execute("DROP TABLE ZZZ");
        stmt.execute("create index A_R_RNUM on A_R (RNUM)");
    }
}

What is the expected output? What do you see instead?

The code fails with this exception
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Obecná chyba: 
"java.lang.ClassCastException: org.h2.index.ScanIndex cannot be cast to 
org.h2.index.PageIndex"
General error: "java.lang.ClassCastException: org.h2.index.ScanIndex cannot be 
cast to org.h2.index.PageIndex"; SQL statement:
insert into ZZZ(ID, RNUM) direct sorted select A.ID, ROWNUM() from A inner join 
A_R on A.ID=A_R.ID order by name asc [50000-153]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
    at org.h2.message.DbException.get(DbException.java:156)
    at org.h2.message.DbException.convert(DbException.java:279)
    at org.h2.command.Command.executeUpdate(Command.java:216)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:177)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152)
    at aaa.SortTest.sort3(SortTest.java:45)
    at aaa.SortTest.main(SortTest.java:38)
Caused by: java.lang.ClassCastException: org.h2.index.ScanIndex cannot be cast 
to org.h2.index.PageIndex
    at org.h2.command.dml.Insert.update(Insert.java:82)
    at org.h2.command.CommandContainer.update(CommandContainer.java:69)
    at org.h2.command.Command.executeUpdate(Command.java:212)
    ... 4 more

What version of the product are you using? On what operating system, file
system, and virtual machine?
h2-1.3.153/Win7x64/NTFS/jdk1.6.0_24

Do you know a workaround?
Do not use INSERT ... DIRECT SORTED

Original issue reported on code.google.com by michal.b...@gmail.com on 14 Apr 2011 at 1:56

[GoogleCodeExporter]

Hi,

Thanks a lot! This will be fixed in the next release.

Original comment by thomas.t...@gmail.com on 26 Apr 2011 at 6:12

• Changed state: Accepted

[GoogleCodeExporter]

This is fixed in version 1.3.155

Original comment by thomas.t...@gmail.com on 27 May 2011 at 10:54

• Changed state: Fixed