代码之家 › 专栏 › 技术社区 › Ondra Å½iÅ¾ka David Lilljegren

HSQLDB:INSERT INTO…(选择NULL,*FROM)导致“列名重复”

hsqldb sql

Ondra Å½iÅ¾ka David Lilljegren · 技术社区 · 6 年前

TEXT 表,映射到CSV文件。参见项目 CSV Cruncher

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

我正在尝试向这个“input”表中添加一列,以便每个表的结果都有一个唯一的ID。

所以这看起来很符合逻辑:加上列,定义为一个序列,

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

然后使用null让DB选择值:

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

但是,如果失败:

SQLSyntaxErrorException duplicate column name in derived table

* 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

我认为在MySQL中这是可行的,但是还没有测试过。

crunchCounter 就这样做 INSERT INTO output (SELECT * FROM apollo_session_occurrence)

理论上我可以用 NEXT VALUE FOR crunchCounter

发生什么事?而且,

我该怎么填 output 具有唯一列的表?

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

1 回复 | 直到 6 年前

Ondra Å½iÅ¾ka David Lilljegren 6 年前

我明白了。问题是HSQLDB扩展了 * SELECT 1 AS foo, * FROM myTable 进入之内 foo, foo, myTable.col1, ... .

那是个虫子。 Edit:不是bug,但是HSQLDB可以提供更好的错误消息。

使用限定通配符。

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