New post
Question
· Oct 23, 2022

SQL Query returns sorted Integer ID column as string

#Key Question #Python #SQL #InterSystems IRIS

This is not an issue in ObjectScript, due to its typeless nature. But it's essential for external programming languages that care a bit more about types of variables.

And in any case, it's still reproducible in ObjectScript. I have table

CREATE TABLE some_table (
        id INTEGER NOT NULL, 
        x INTEGER, 
        y INTEGER, 
        z VARCHAR(50), 
        PRIMARY KEY (id)
)

And data

INSERT INTO some_table (id, x, y, z) VALUES (1, 1, 2, 'z1');
INSERT INTO some_table (id, x, y, z) VALUES (2, 2, 3, 'z2');
INSERT INTO some_table (id, x, y, z) VALUES (3, 3, 4, 'z3');
INSERT INTO some_table (id, x, y, z) VALUES (4, 4, 5, 'z4');

Pretty simple.

Let's check and fetch all the data. $ListBuild is more sensitive to the type of variables, so, I use it, to check the result.

USER>set rs= ##class(%SQL.Statement).%ExecDirect(,"SELECT id, x, y, z FROM some_table") 

USER>while rs.%Next() { zwrite $listbuild(rs.id, rs.x, rs.y, rs.z) }
$lb(1,1,2,"z1")
$lb(2,2,3,"z2")
$lb(3,3,4,"z3")
$lb(4,4,5,"z4")

The expected result, the first three columns are INTEGER, and the last one is String. Let's sort it by id

USER>set rs= ##class(%SQL.Statement).%ExecDirect(,"SELECT id, x, y, z FROM some_table ORDER BY id") 

USER>while rs.%Next() { zwrite $listbuild(rs.id, rs.x, rs.y, rs.z) }
$lb("1",1,2,"z1")
$lb("2",2,3,"z2")
$lb("3",3,4,"z3")
$lb("4",4,5,"z4")

And now, the first column, which is id, represented as a String

But, if I would like to sort it by some other field, it works well, and all types are correct

USER>set rs= ##class(%SQL.Statement).%ExecDirect(,"SELECT id, x, y, z FROM some_table ORDER BY x DESC") 

USER>while rs.%Next() { zwrite $listbuild(rs.id, rs.x, rs.y, rs.z) }
$lb(4,4,5,"z4")
$lb(3,3,4,"z3")
$lb(2,2,3,"z2")
$lb(1,1,2,"z1")

Is there any workaround for this behavior? Why it changes the type of the resulting column?

Product version: IRIS 2022.1
$ZV: IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2022.2 (Build 345U) Thu Sep 22 2022 18:24:07 EDT
Discussion (11)1
Log in or sign up to continue
Robert Cemper · Oct 23, 2022

A possible workaround 

SAMPLES>set rs= ##class(%SQL.Statement).%ExecDirect(,"SELECT +id id1, x, y, z FROM some_table ORDER BY id")

SAMPLES>while rs.%Next() { zwrite $listbuild(rs.id1, rs.x, rs.y, rs.z) }
$lb(1,1,2,"z1")
$lb(2,2,3,"z2")
$lb(3,3,4,"z3")
$lb(4,4,5,"z4")

SAMPLES>

The point is to consume your value NOT from temp order global.

tested in 
Cache for Windows (x86-64) 2018.1.7 (Build 721U) Fri Mar 18 2022 22:07:35 EDT
Should also work in IRIS

0 0
Dmitry Maslennikov · Oct 23, 2022

Yeah, it would be kind of ok, if I would have the option to write SQL Query. But in my case, it's not an option kind of.

For instance, using ORM in Python, the table above is defined this way

        Table(
            "some_table",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("x", Integer),
            Column("y", Integer),
            Column("z", String(50)),
        )

Insert some data, into this table

        connection.execute(
            cls.tables.some_table.insert(),
            [
                {"id": 1, "x": 1, "y": 2, "z": "z1"},
                {"id": 2, "x": 2, "y": 3, "z": "z2"},
                {"id": 3, "x": 3, "y": 4, "z": "z3"},
                {"id": 4, "x": 4, "y": 5, "z": "z4"},
            ],
        )

And now, select that data. This code is a part of a test, but I hope the idea is quite clear. Where stmt will be compiled to SELECT id from some_table ORDER BY id

        table = self.tables.some_table
        stmt = (
            select(table.c.id)
            .order_by(table.c.id)
        )
        self._assert_result(
            stmt,
            [(1, ), (2,), (3,), (4,)],
        )

This particular test will fail. It gets [('1', ), ('2',), ('3',), ('4',)] while expects [(1, ), (2,), (3,), (4,)]

0 0
Vitaliy Serdtsev · Oct 24, 2022

Have you looked at the type of fields in the metadata?
For me, the INTEGER type (4) is returned for ID, and the BIGINT type (-5) is returned for %ID.

Try

SELECT id, x, y, z FROM some_table order by %id

and

d rs.%GetMetadata().%Display()

What are the default values for AdaptiveMode, AllowRowIDUpdate, IdKeyCPF, PKEY_IS_IDKEY ?

Try disabling AdaptiveMode.

2 0
Benjamin De Boe · Oct 27, 2022

@Vitaliy Serdtsev is right in pointing to the resultset's metadata. That's where IRIS SQL registers column datatypes and he already pointed out that this is obviously not impacted by the presence of an ORDER BY. 

The resultset object returns the SQL-side value to ObjectScript, but on that ObjectScript side, the datatype no longer matters as it isn't strongly typed and still 1="1". Therefore, I don't think this constitutes an error.

FWIW, the reason you're seeing this is that due to the ORDER BY clause we're picking up that id value from the index' subscript rather than from the master map.

1 0