#1151 INFORMATION_SCHEMA.COLUMNS regression or new feature?

current-release
closed-fixed
1
2011-07-01
2011-07-01
Lukas Eder
No

There seems to be a regression between versions 2.2.1 and 2.2.3/2.2.4 in the INFORMATION_SCHEMA.COLUMNS view (I didn't check 2.2.2). If it's intended, then I didn't see it in the release notes:
http://hsqldb.org/doc/2.0/changelist_2_0.txt

I think this is quite an important change between such minor releases for it not to be mentioned, that's why I thought it might be a regression. Here's what I observed. For this table:

--------------------------------------------------
CREATE TABLE t_arrays (
id integer not null,
string_array VARCHAR(20) ARRAY,
number_array INTEGER ARRAY,
date_array DATE ARRAY,

CONSTRAINT pk_t_arrays PRIMARY KEY (ID)
);
--------------------------------------------------

I used to get these values in 2.2.1:

--------------------------------------------------
select COLUMN_NAME, DATA_TYPE, DECLARED_DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'T_ARRAYS';

ID INTEGER INTEGER
STRING_ARRAY VARCHAR ARRAY VARCHAR ARRAY
NUMBER_ARRAY INTEGER ARRAY INTEGER ARRAY
DATE_ARRAY DATE ARRAY DATE ARRAY
--------------------------------------------------

Now I get these values in 2.2.4:

--------------------------------------------------
select COLUMN_NAME, DATA_TYPE, DECLARED_DATA_TYPE, DTD_IDENTIFIER
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'T_ARRAYS';

ID INTEGER INTEGER INTEGER
STRING_ARRAY ARRAY ARRAY VARCHAR(20) ARRAY
NUMBER_ARRAY ARRAY ARRAY INTEGER ARRAY
DATE_ARRAY ARRAY ARRAY DATE ARRAY
--------------------------------------------------

DATA_TYPE and DECLARED_DATA_TYPE only hold the "ARRAY" value, no reference to the array type anymore. On the other hand, there is a new column DTD_IDENTIFIER that holds more information than the original DECLARED_DATA_TYPE column (see VARCHAR(20), vs VARCHAR)...

What do you think about this?

Cheers
Lukas

Discussion

  • Fred Toussi

    Fred Toussi - 2011-07-01

    This is intentional.

    A missing table, INFORMATION_SCHEMA.ELEMENT_TYPES was introduced in 2.2.3 to contain the type of the elements of the array.

    You should equijoin this table to the COLUMNS table on the DTC_IDENTIFIER column in order to get the correct element type for each column of ARRAY type .

     
  • Fred Toussi

    Fred Toussi - 2011-07-01
    • priority: 5 --> 1
    • assigned_to: nobody --> fredt
    • status: open --> open-fixed
     
  • Lukas Eder

    Lukas Eder - 2011-07-01

    OK, the new table is more expressive anyway. I guess I won't be backwards-compatible in my library either...

    Thanks for the explanation!

     
  • Fred Toussi

    Fred Toussi - 2011-07-01
    • status: open-fixed --> closed-fixed
     

Log in to post a comment.