Ultralite cannot execute properly a select command with two chained subqueries

[Former Member]

Hello guys,

We've got a problem when we execute a select command with an inner select command. If this inner select command uses another select command, the ResultSet keeps invalid, and we cannot retrieve the column values properly. As we're using the UltraliteJ for Android, any attempt to get any Resultset value results in a JNI crash.

We've made a repro to this scenario:

---

-- Create a new database (TestDatabase.udb)   
-- ulinit -p "4096" -S "0" --max_hash_size=4 --timestamp_increment=1 -y "TestDatabase.udb"

-- Create a new table
CREATE TABLE Test(id int PRIMARY KEY, col1 VARCHAR(10), col2 INT, col3 LONG BINARY)
GO

-- Feed the test table with some data
INSERT INTO Test VALUES(1, 'Row1', 1, 0x1)
GO
INSERT INTO Test VALUES(2, 'Row2', 2, 0x2)
GO
INSERT INTO Test VALUES(3, 'Row3', 3, 0x3)
GO

-- This query below executes fine
SELECT 
     P.id
    ,(SELECT col1 FROM Test T WHERE T.id = P.id)       as col1
    ,(SELECT TOP 1 col2 FROM Test T WHERE T.id = P.id) as col2
    ,(SELECT TOP 1 col3 FROM Test T WHERE T.id = P.id) as col3
FROM Test P

-- Here is the output
-- id,col1,col2,col3
-- 1,'Row1',1,0x0001
-- 2,'Row2',2,0x0002
-- 3,'Row3',3,0x0003

-- But this query doesn't work 😞
SELECT  A.id, A.col1, A.col2, A.col3
FROM ( 
     SELECT P.id
    ,(SELECT col1 FROM Test T WHERE T.id = P.id)       as col1
    ,(SELECT TOP 1 col2 FROM Test T WHERE T.id = P.id) as col2
    ,(SELECT TOP 1 col3 FROM Test T WHERE T.id = P.id) as col3
 FROM Test P) AS A (id, col1, col2, col3)

-- And here is the output
-- id,col1,col2,col3
-- 1,'',0,
-- 2,'',0,
-- 3,'',0,

---

Unfortunately, we cannot use any workaround, since this query is built dynamically in the Java code.

I hope you have some ideia what's going on.

Thanks

[VolkerBarth]

Besides the obvious bug: What kind of Java tool (ORM) does build such strange queries?

[Former Member · Accepted solution]

Alex,

I reproduced this problem with 12.0.1.3769, but the problem does not occur with 12.0.1.3787 and up. I am making an educated guess that the bug was fixed with engineering case 718317. The fix will be in the next 12.0.1 EBF.

[Former Member · Accepted solution]

Andy,

I think this bug is related with this one: http://sqlanywhere-forum.sap.com/questions/13134/ultralite-strange-behavior-with-union-and-subselect...

Thank you by the solution

[Former Member · Accepted solution]

Andy, we had a problem after testing the 3851 EBF. Please, see my comment in the answer below, where we could attached a useful code snippet to reproduce the scenario.

[Former Member · Accepted solution]

The bug has already been fixed. Thank you.

[Former Member]

Andy,

We've tested again with the 3851 EBF.

In the Interactive SQL (Win32), it has worked fine. We can retrieve each one of the result set columns and see them in the output window.

However in Android, using the UltraliteJ, the application doesn't crash anymore, but we cannot retrieve the result set values. Any attempt to get any one the result set columns results in a strange behavior: string-type columns returns empty (""), numeric-type columns returns 0 and binary-type columns returns a 0-sized byte array.

The following code is an unit test with this repro: Thanks in advance.

public ResultSet testUltraliteZ() throws Exception {
    if (this.dbConn_General == null) {
        this.dbConn_General = new DatabaseConnection();
    }
    Connection ulConn_Select = this.dbConn_General.getUltraliteConnection();

    /*PreparedStatement ps = ulConn_Select
            .prepareStatement("DROP TABLE Test");
    ps.execute();*/

    PreparedStatement ps = ulConn_Select
            .prepareStatement("CREATE TABLE Test(id int PRIMARY KEY, col1 VARCHAR(10), col2 INT, col3 LONG BINARY)");
    ps.execute();

    ps = ulConn_Select
            .prepareStatement("INSERT INTO Test VALUES(1, 'Row1', 1, 0x1)");
    ps.execute();

    ps = ulConn_Select
            .prepareStatement("INSERT INTO Test VALUES(2, 'Row2', 2, 0x2)");
    ps.execute();

    ps = ulConn_Select
            .prepareStatement("INSERT INTO Test VALUES(3, 'Row3', 3, 0x3)");
    ps.execute();

    ps = ulConn_Select
            .prepareStatement("SELECT  A.id, A.col1, A.col2, A.col3 "
                    + "FROM (  "
                    + "SELECT P.id "
                    + ",(SELECT col1 FROM Test T WHERE T.id = P.id)       as col1 "
                    + ",(SELECT TOP 1 col2 FROM Test T WHERE T.id = P.id) as col2 "
                    + ",(SELECT TOP 1 col3 FROM Test T WHERE T.id = P.id) as col3 "
                    + "FROM Test P) AS A (id, col1, col2, col3)");

    ResultSet rs = ps.executeQuery();

    rs.first();

    // col1 receives a "" value, but should receive "Row1"
    String col1 = rs.getString("col1");

    // bytes receives a [] value, but should receive 0x1
    bytes = rs.getBytes(4);

    rs.next();
    col1 = rs.getString("col1");
    assertEquals(3, rs.getRowCount(0));
    return rs;
}

[Former Member]

I cannot reproduce this, but I am seeing another problem. I am seeing that the byte array length for column 4 is 2. It should be 1. I am looking into this.

Are you calling commit() on the connection that does the inserts?

[Former Member]

You shouldn't need to commit() the connection if it is the same connection for the inserts and the query, but I still cannot reproduce the problem.

One idiosyncrasy of UltraLite string literals: 0x1 for a binary literal will give 2 bytes (00 01). If you want to specify single bytes in a string literal in a SQL statement for UltraLite, use '\\x01' (for example) to get one byte 01. The literal '\\x01\\x02' gives two bytes 01 02, and so on.