#1210 Error when unnesting arrays from "preceding tablereferences"
See also possibly related issue here:
https://sourceforge.net/tracker/?func=detail&aid=3480923&group_id=23316&atid=378131
I'm running a test against this schema:
--------------------------------------
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)
);
INSERT INTO t_arrays VALUES (1, null, null, null);
INSERT INTO t_arrays VALUES (2, ARRAY[], ARRAY[], ARRAY[]);
INSERT INTO t_arrays VALUES (3, ARRAY['a'], ARRAY[1], ARRAY['1981-07-10']);
INSERT INTO t_arrays VALUES (4, ARRAY['a', 'b'], ARRAY[1, 2], ARRAY['1981-07-10', '2000-01-01']);
--------------------------------------
The test query looks like this:
--------------------------------------
select
"PUBLIC"."T_ARRAYS"."ID",
"t"."COLUMN_VALUE"
from
"PUBLIC"."T_ARRAYS",
(select * from unnest("PUBLIC"."T_ARRAYS"."STRING_ARRAY") as "t"("COLUMN_VALUE")) as "t"
order by "PUBLIC"."T_ARRAYS"."ID" asc
--------------------------------------
According to the documentation of UNNEST the above should work:
http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#N127AD
The documentation reads:
"The array expression can contain references to any column of the table references preceding the current table reference."
In the example, "STRING_ARRAY" is a column of "T_ARRAYS", which has been referenced precedingly. Nevertheless, I get the following exception:
--------------------------------------
java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: STRING_ARRAY
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
--------------------------------------
It does work like this, however:
--------------------------------------
select
"PUBLIC"."T_ARRAYS"."ID",
"t"."COLUMN_VALUE"
from
"PUBLIC"."T_ARRAYS",
unnest(STRING_ARRAY) as "t"("COLUMN_VALUE")
order by "PUBLIC"."T_ARRAYS"."ID" asc
--------------------------------------
I think I had seen this working in a previous version of HSQLDB, but I'm not 100% sure.
According to the documentation, the first example should not work, as there is no "table reference preceding the current table reference". In this context, "preceding" refers to the table references in the same joined table, not an "outer" table.
The second example shows how UNNEST can have such a referernce.