[opendbx] Bug with Sqlite3, UNION and NULL?

[Mariano M. P. <mar...@gm...>]

Hi Norbert. So long we don't have problems :)   We were doing some ORM work
on top of SqueakDBX and the ORM is trowing a query like this:

SELECT * FROM
    (SELECT t2.ID, t2.NAME, t2.SERIAL_NUMBER, NULL AS t4nullField1, NULL AS
t4nullField2, NULL AS t4nullField3, NULL AS t5nullField1, NULL AS
t5nullField2, NULL AS t5nullField3, NULL AS t5nullField4
 FROM NONPERISHABLE_ITEM t2, WAREHOUSE_ITEM_LINK t3
 WHERE t2.ID = t3.ITEM_ID AND t3.WAREHOUSE_ID = 1
UNION ALL
    SELECT NULL AS t4nullField1, NULL AS t4nullField2, NULL AS t4nullField3,
t2.ID, t2.NAME, t2.AGE, NULL AS t5nullField1, NULL AS t5nullField2, NULL AS
t5nullField3, NULL AS t5nullField4
 FROM PERISHABLE_ITEM t2, WAREHOUSE_ITEM_LINK t3
 WHERE t2.ID = t3.ITEM_ID AND t3.WAREHOUSE_ID = 1

UNION ALL
    SELECT NULL AS t4nullField1, NULL AS t4nullField2, NULL AS t4nullField3,
NULL AS t5nullField1, NULL AS t5nullField2, NULL AS t5nullField3, t2.ID,
t2.NAME, t2.SERIAL_NUMBER, t2.ASSEM_COST
 FROM UNASSEMBLED_ITEM t2, WAREHOUSE_ITEM_LINK t3
 WHERE t2.ID = t3.ITEM_ID AND t3.WAREHOUSE_ID = 1) t2


That fails with something...we have tried to isolate the problem to the
minimum. To reproduce do these steps:


'Create table test (c1 int NULL, c2 int NULL)'.
 'insert into test values(1, NULL)'.
'insert into test values(NULL, 3)'.
'select * from (select t1.c1, null t3 from test t1 UNION ALL select NULL t3,
t2.c2 from test t2)'.

Then after, fetch all rows. Then, if you take the last row (number 4), which
has (NULL, '3'), and you try to ask the type of position 2  you get an
UKNOWN instead of type for the integer.

Is this a bug? if true, where? OpenDBX? slite3 library? sqlite3 database?

This was tested on Linux with Sqlite3 and latest OpenDBX.

Thanks in advance,

-- 
Mariano
http://marianopeck.wordpress.com