The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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

asked 18 Oct '12, 08:07

Alex's gravatar image

Alex
895143645
accept rate: 25%

edited 18 Oct '12, 08:17

Comment Text Removed

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

(18 Oct '12, 15:47) Volker Barth

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.

permanent link

answered 18 Oct '12, 14:44

Andy%20Quick's gravatar image

Andy Quick
2.1k2436
accept rate: 43%

Andy,

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

Thank you by the solution

(26 Oct '12, 11:30) Alex
Comment Text Removed
1

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.

(05 Apr '13, 16:22) Alex

The bug has already been fixed. Thank you.

(12 Nov '14, 14:02) Alex

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;
}
permanent link

answered 05 Apr '13, 16:19

Alex's gravatar image

Alex
895143645
accept rate: 25%

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?

(08 Apr '13, 11:56) Andy Quick

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.

(08 Apr '13, 15:25) Andy Quick
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×130
×75
×59
×47
×44

question asked: 18 Oct '12, 08:07

question was seen: 2,355 times

last updated: 12 Nov '14, 14:02