Question
Dmitry Maslennikov · Oct 23, 2022

SQL Query returns sorted Integer ID column as string

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
1
0 269
Discussion (11)1
Log in or sign up to continue

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

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

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 idxyFROM some_table order by %id

and

rs.%GetMetadata().%Display()

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

Try disabling AdaptiveMode.

@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.

thanks for confirming my diagnose
- that's why I used "+id as id1" to break the link to index global
- and :BTW:   ORDER BY 1  ; just using column position shows the same bug

And how it can help me, when I do SQL request through DB-API

SELECT id FROM table, gives me one type, and SELECT id FROM table ORDER BY id gives another type

In both cases, I expect the same time, both types I've asked for id, but the result is very different.

I'd say it's a bug in Query Generator.
A workaround in DB-API just might hide the real cause. 
If you are lucky the bug is fixed by the next release or the next after or some future release

I've already patched DB-API as a workaround, and see that a fix in IRIS makes not so much sense since the issue still is in older versions. So, I would fix it on the driver's side.

that makes sense. 
version dependency is always a pain!  yes