HSQLDB: INSERT INTO ... (SELECT NULL, * FROM) leads to "duplicate column name"

In HSQLDB, I have two TEXT tables, mapped to CSV files. See project CSV Cruncher.

CREATE TEXT TABLE session_telephony_pins ( Op VARCHAR(4092), id ... )

I am trying to add a column to this "input" table, so that the result would have an unique ID for each table.

So this seems quite logical: Add that column, defined as a sequence,

CREATE TEXT TABLE output ( crunchCounter BIGINT
  GENERATED BY DEFAULT AS SEQUENCE crunchCounter PRIMARY KEY, op VARCHAR(4092), ... )

and then use null to let the DB select the value:

INSERT INTO output (SELECT NULL AS crunchCounter, * FROM apollo_session_occurrence)
-- I also tried withhout `AS ...`

However if fails with:

SQLSyntaxErrorException duplicate column name in derived table

Tables and column types:

* APOLLO_SESSION_OCCURRENCE
  - OP                           CHARACTER VARYING
  - SESSION_OCCURRENCE_ID        SMALLINT
  - SESSION_ID                   SMALLINT
  - START_TIME                   TIMESTAMP
  - END_TIME                     TIMESTAMP
  - UID                          UUID
* OUTPUT
  - CRUNCHCOUNTER                BIGINT
  - OP                           CHARACTER VARYING
  - SESSION_OCCURRENCE_ID        CHARACTER VARYING
  - SESSION_ID                   CHARACTER VARYING
  - START_TIME                   CHARACTER VARYING
  - END_TIME                     CHARACTER VARYING
  - UID                          CHARACTER VARYING

I think in MySQL this would work, but haven't tested.

The types should not be an issue. If I drop the crunchCounter column and just do INSERT INTO output (SELECT * FROM apollo_session_occurrence), it works fine.

I could theoretically use NEXT VALUE FOR crunchCounter but that wouldn't change columns metadata. This happens when HSQLDB checks the metadata.

What's going on? And,

How should I fill the output table with the unique column?

Edit: Interestingly, it works well with other tables and specifically named result columns:

INSERT INTO output (SELECT NULL AS crunchCounter,  jobName, buildNumber, config, ar, arFile, deployDur, warmupDur, scale,
  CAST(warmupDur AS DOUBLE) / CAST(deployDur AS DOUBLE) AS warmupSlower
  FROM concat ORDER BY deployDur)

* CONCAT
 - JOBNAME                      CHARACTER VARYING
 - BUILDNUMBER                  SMALLINT
 - CONFIG                       CHARACTER VARYING
 - AR                           CHARACTER VARYING
 - ARFILE                       CHARACTER VARYING
 - DEPLOYDUR                    SMALLINT
 - WARMUPDUR                    SMALLINT
 - SCALE                        SMALLINT
* OUTPUT
 - CRUNCHCOUNTER                BIGINT
 - JOBNAME                      CHARACTER VARYING
 - BUILDNUMBER                  CHARACTER VARYING
 - CONFIG                       CHARACTER VARYING
 - AR                           CHARACTER VARYING
 - ARFILE                       CHARACTER VARYING
 - DEPLOYDUR                    CHARACTER VARYING
 - WARMUPDUR                    CHARACTER VARYING
 - SCALE                        CHARACTER VARYING
 - WARMUPSLOWER                 CHARACTER VARYING

Starts to look like a HSQLDB bug.

Solution

Figured out. The problem is that HSQLDB expands the * in SELECT 1 AS foo, * FROM myTable into foo, foo, myTable.col1, ....

That is IMO a bug. Edit: Not a bug, but HSQLDB could give a better error message.

The workaround: Use qualified wildcard.

INSERT INTO output (SELECT NULL AS crunchCounter, 
        apollo_session_occurrence.* 
   FROM apollo_session_occurrence ...)