Can't SELECT CLOB column (derby)

pwierzgala commented 2 years ago

Hi, I think I found a bug with executing SELECT query for a specific columns. I don't understand what is specific about the column I try to select but I know that SELECT returns empty listy only when I include it. There is no error neither in python code nor in Derby logs.

I have a database which contains table TBL_UNITS:

Column Name     #   Data Type   Length  Scale   Not Null
ID          1   BIGINT      19  [NULL]  true
REF_ID          2   VARCHAR     36  [NULL]  false
NAME            3   VARCHAR     2048    [NULL]  false
DESCRIPTION     4   CLOB        65536   [NULL]  false
VERSION         5   BIGINT      19  [NULL]  false   
LAST_CHANGE     6   BIGINT      19  [NULL]  false       
CONVERSION_FACTOR   7   DOUBLE      52  [NULL]  false
SYNONYMS        8   VARCHAR     255 [NULL]  false   
F_UNIT_GROUP        9   BIGINT      19  [NULL]  false

Query SELECT * FROM TBL_UNITS returns []
Query SELECT DESCRIPTION FROM TBL_UNITS returns []
Any query without column DESCRITPTION return expected results.

Sample working query:

SELECT ID, NAME, CONVERSION_FACTOR, F_UNIT_GROUP FROM  TBL_UNITS fetch first 5 rows only

Results of the query:

[
    (505, 'LVL 2000', 1.78, 506),
    (507, 'm2*d', 0.00274, 508),
    (509, 'kcal', 0.00419, 510),
    (511, 'GJ', 1000.0, 510),
    (512, 'lb av', 0.45, 513)
]

It looks that DESCRIPTION is the only column with data type CLOB. Could it be a reason of this strange behavior? Please let know if you need any additional information.

Versions:

pydrda==0.4.7
derby==10.16.1.1
python==3.9.13
java==openjdk 17.0.4

nakagami commented 2 years ago

Thank you for your report.

Indeed, the CLOB columns are not selectable. I looked at the source code, but I am not sure why.

I need help from someone familiar with apache derby!

nakagami commented 12 months ago

I wrote unittest. Uncommenting here causes a FAIL. https://github.com/nakagami/pydrda/commit/04793e5f080b37d7e3d3948f8772ec84dd3a354d#diff-5da5550186846d2987bb659a477e5792443483c99c 0cf145621b05db626b697eR109-R114

Need a driver to fix it so that uncommenting does not cause a FAIL.

nakagami / pydrda

Can't SELECT CLOB column (derby) #14