当使用带引号的表标识符时，从Informix中的CONNECT BY查询引发的一般语法错误

Question

在Informix数据库上运行以下查询时，数据库报告一个一般语法错误(没有任何关于导致问题的原因的指示)。同样的查询可以在CUBRID或Oracle数据库上完美运行，这两种数据库都支持CONNECT BY语法:

select 
  lower(connect_by_root "t_directory"."name"), 
  connect_by_isleaf, 
  connect_by_iscycle, 
  substr(
    sys_connect_by_path(lower("t_directory"."name"), '/'), 
    2) "dir"
from "t_directory"
start with "t_directory"."parent_id" is null
connect by nocycle prior "t_directory"."id" = "t_directory"."parent_id"
order siblings by lower("t_directory"."name") asc

我使用的数据库是Windows上的Informix 12.10的开发人员版。我使用以下连接URL(允许带引号的表标识符)从JDBC驱动程序运行查询:

jdbc:informix-sqli://localhost:9092/test:INFORMIXSERVER=ol_informix;DELIMIDENT=y

Answer 1

这里确切的问题是prior不接受带引号的表标识符，尽管带引号的列标识符似乎很好。这个查询运行得非常好:

select 
  lower(connect_by_root "t_directory"."name"), 
  connect_by_isleaf, 
  connect_by_iscycle, 
  substr(
    sys_connect_by_path(lower("t_directory"."name"), '/'), 
    2) "dir"
from "t_directory"
start with "t_directory"."parent_id" is null
connect by nocycle prior t_directory."id" = "t_directory"."parent_id"
order siblings by lower("t_directory"."name") asc

…不同之处在于:

-- Bad:
connect by nocycle prior "t_directory"."id" = "t_directory"."parent_id"
-- Good:
connect by nocycle prior t_directory."id" = "t_directory"."parent_id"