错误 683：从视图中选择时"Specified STEP expression will not traverse RANGE"

Question

我在Informix 12.10 (Linux x86_64)中有一个这样定义的视图。

create view "dbo".perinexv
(
ac0,   ri0,   wf0,   up0,   di0,   dp0,   if0,   iu0,   ts0,   dv0,   dc0,   ca0,   m0,   ax0,   ce0,   al0,   tg0,   ti0,   tf0,   tn0,   pu0,   tr0,   fm0,   pq0,   av0,   ad0,   ab0,   af0,   an0,   ct0,   et0,   ft0,   fn0,   nb0,   pi0,   hb0,   rx0,   rz0,   az0,   as0,    dr0,   ii0,   xr0,   le0,   sk0,   sl0,   wd0
)
as
select
('SK' || x1.kat_c ) ::varchar(8),
'PE' ::char(2),
CASE WHEN ((x0.dat_zru < CURRENT year to month ) OR ((x0.dat_zru IS NULL ) AND (((mod(x0.vestzru,100 ) - YEAR (TODAY ) ) < 0. ) OR ((mod(x0.vestzru,100 ) - YEAR (TODAY ) ) > 20. ) ) ) ) THEN '.' ELSE ' ' END ::char(1),
CASE WHEN (x0.dat_zru < (CURRENT year to month - interval( 1) month(9) to month ) ) THEN 'H' WHEN (x0.dat_zru < CURRENT year to month ) THEN 'W' WHEN (x1.datzv < (CURRENT year to month - interval( 1) month(9) to month ) ) THEN ('U' || CASE WHEN EXISTS (select x5.datzv from "dbo".evuknor x5 where ((((x5.datzv < CURRENT year to month ) AND (x5.zmop != '1' ) ) AND (x5.identif = x0.identif ) ) AND (x5.rokvyd = x0.rokvyd ) ) ) THEN '*M' ELSE '  ' END ) ELSE ('N' || CASE WHEN EXISTS (select x6.datzv from "dbo".evuknor x6 where ((((x6.datzv < CURRENT year to month ) AND (x6.zmop != '1' ) ) AND (x6.identif = x0.identif ) ) AND (x6.rokvyd = x0.rokvyd ) ) ) THEN '*M' ELSE '  ' END ) END ::char(3),
TRIM ( BOTH ' ' FROM x0.identif ) ::varchar(50),
x1.datzv ::date,
CASE WHEN (x0.identif NOT MATCHES ((((('*' || LPAD (x0.cistn,2,'0' )) || ' ' ) || x0.cisskn ) || LPAD (x0.cisnsk,2,'0' )) || '*' ) ) THEN (((LPAD (x0.cistn,2,'0' )|| ' ' ) || x0.cisskn ) || LPAD (x0.cisnsk,2,'0' )) END ::char(7),
'Normalization office' ::varchar(128),
'ST*N' ::char(4),
x1.datuc ::date,
x1.datsch ::date,
(select informix.concagr(TRIM ( BOTH ' ' FROM x7.ics ),'*' ) ::varchar(64) from "dbo".charcsn x7 where ((x7.identif = x0.identif ) AND (x7.rokvyd = x0.rokvyd ) ) ) ::varchar(64),
((('TK ' || x2.cistnk ) || ' ' ) || x2.nametnk ::varchar(255) ),
NULL ::date,
NULL ::char(1),
x1.jazyk ::char(2),
NULL ::char(1),
REPLACE (x3.nazang,'|','/' )::char(440),
NULL ::char(1),
REPLACE (x0.nazevn,'|','/' )::char(440),
'UNMS SR' ::varchar(24),
NULL ::char(1),
NVL ((x1.str_celk || CASE WHEN (x1.format != 'A4' ) THEN ('/' || x1.format ) ELSE '' END ),'not applicable' )::varchar(14),
(x4.cena_kc || ' EUR' ::varchar(13) ),
NULL ::char(1),
NULL ::char(1),
NULL ::char(1),
NULL ::char(1),
rmnl(REPLACE (x3.abstr,'|','/' ))::lvarchar(2046),
NULL ::char(1),
(select informix.concagr(TRIM ( BOTH ' ' FROM x9.heslo ),'*' ) ::lvarchar(2046) from "dbo".deskrcsn x8,"dbo".klicslo x9 where (((((x8.identif = x0.identif ) AND (x8.rokvyd = x0.rokvyd ) ) AND (x8.isoroot = x9.isoroot ) ) AND (x9.jazyk = 'en' ) ) AND (x9.heslo > '' ) ) ) ::lvarchar(2046),
NULL ::char(1),
(select informix.concagr(TRIM ( BOTH ' ' FROM x11.heslo ),'*' ) ::lvarchar(2046) from "dbo".deskrcsn x10,"dbo".klicslo x11 where (((((x10.identif = x0.identif ) AND (x10.rokvyd = x0.rokvyd ) ) AND (x10.isoroot = x11.isoroot ) ) AND (x11.jazyk = 'sk' ) ) AND (x11.heslo > '' ) ) ) ::lvarchar(2046),
rmnl(REPLACE (x0.pozncsn,'|','/' ))::varchar(255),
NULL ::char(1),
NULL ::char(1),
(select informix.concagr((((TRIM ( BOTH ' ' FROM x13.identif ) || '(' ) || REPLACE (LPAD (x13.rokvyd ::varchar(7),7,'0' ),'.','-' )) || ')' ),'*' ) ::lvarchar(2046) from "dbo".zmena x12,"dbo".evuknor x13 where (((((x12.kat_c = x13.kat_c ) AND (x12.identif = x0.identif ) ) AND (x12.rokvyd = x0.rokvyd ) ) AND (x12.zmzr IN ('0','S' )) ) AND (x12.rokvyd > 1900.00 ) ) ) ::lvarchar(2046),
(select informix.concagrl((((TRIM ( BOTH ' ' FROM x14.identif ) || '(' ) || REPLACE (LPAD (x14.rokvyd ::varchar(7),7,'0' ),'.','-' )) || ')' ),'*' ) ::lvarchar(4094) from "dbo".zmena x14 where (((x14.kat_c = x1.kat_c ) AND (x14.zmzr IN ('0','S' )) ) AND (x14.rokvyd > 1900.00 ) ) ) ::lvarchar(4094),
(select informix.concagr((((x15.zmop || ' ' ) || TRIM ( BOTH ' ' FROM x15.idzm ) ) || NVL ((' ' || LPAD (REPLACE ((x15.vestnik / 100.0000000000000000 ) ::decimal(4,2),'.','/' ),5,'0' )),'' )),'*' ) from "dbo".evuknor x15 where ((((x15.identif = x0.identif ) AND (x15.rokvyd = x0.rokvyd ) ) AND (x15.zmop != '1' ) ) AND (x15.datzv < CURRENT year to month ) ) ) ::varchar(255),
NULL ::char(1),
NULL ::char(1),
(select informix.concagr(((((CASE WHEN (x16.stupshod = 'E' ) THEN 'eqv' WHEN (x16.stupshod = 'H' ) THEN 'har' WHEN (x16.stupshod = 'I' ) THEN 'idt' WHEN (x16.stupshod = 'M' ) THEN 'mod' WHEN (x16.stupshod = 'N' ) THEN 'neq' WHEN (x16.stupshod = 'O' ) THEN 'idt' WHEN (x16.stupshod = 'R' ) THEN 'rel' WHEN (x16.stupshod = 'Z' ) THEN 'zap' ELSE NVL (x16.stupshod ::char(3),'   ' ) END || ' ' ) || TRIM ( BOTH ' ' FROM x17.identzn ) ) || ':' ) || x17.rokvzn ),'*' ) from "dbo".prevzeti x16,"dbo".meznorm x17 where ((x16.kat_c = x1.kat_c ) AND (x16.xmezn = x17.xmezn ) ) ) ::varchar(255),
NULL ::char(1),
NULL ::char(1),
(CASE WHEN (x0.dat_zru < CURRENT year to month ) THEN 'Z' ELSE 'A' END || sortkey(x0.identif )::varchar(50) ),
NULL ::char(1),
x0.dat_zru ::date
from "dbo".csn x0,
"dbo".evuknor x1,
outer("dbo".tnk x2 ),
outer("dbo".csntext x3 ),
outer("dbo".censkup x4 )
where
(
(
(
(
(
(
(
((x0.rokvyd > 1900.00 ) AND (x1.zmop = '1' ) )
AND (x1.datzv < CURRENT year to month )
)
AND (x1.identif = x0.identif )
)
AND (x1.rokvyd = x0.rokvyd )
)
AND (x0.cistnk = x2.cistnk )
)
AND (x3.identif = x0.identif )
)
AND (x3.rokvyd = x0.rokvyd )
)
AND (x4.censk = x1.censk )
)
;

当我运行命令select count(ac0) from perinexv时，它给了我正确的结果

> select count(ac0) from perinexv;

(count)
71476
1 row(s) retrieved.

但是当我尝试运行select count(*) from perinexv时，我收到错误683。

> select count(*) from perinexv;
683: Specified STEP expression will not traverse RANGE.
Error in line 1
Near character position 28

如果我运行select ac0 from perinexv，输出是正确的

> select ac0 from perinexv;

ac0
SK68386
SK68387
...
SK136374
SK136375
71476 row(s) retrieved.

对于每个单独的列也是正确的。不返回任何错误。错误683表示:

683指定的STEP表达式不能遍历RANGE.
FOR语句中STEP表达式的求值将导致无限循环。
错误示例:
FOR i = 10 TO 20;- error…END FOR
纠正:纠正范围或步进表达式，使增量值在范围内

我在视图定义中没有看到任何阶跃函数，而且，当我尝试分别选择每列时，它没有错误。

当我限制返回的行数时，例如select first 100 * from perinexv，它可以工作到大约20000，但是，当我添加排序(order by ca0)时，它会因上面的错误而失败。
当我运行select * from perinexv时，它总是以相同的ac0值结束，所以这似乎是数据中的一个问题。有什么方法可以找到结果的下一行吗?
你知道是什么原因导致这个问题吗?

Answer 1

问题已确定。这是数据的问题。

在视图perinexv中有sk0列，其定义如下:

(CASE WHEN (x0.dat_zru < CURRENT year to month ) THEN 'Z' ELSE 'A' END || sortkey(x0.identif )::varchar(50) )

有一个函数sortkey()和参数x0.identif(x0在本例中是表evuknor)。在sortkey()的定义中可以找到以下FOR循环:

LET txt = TRIM(txt);
FOR posa=1 TO LENGTH(txt) STEP 1
IF SUBSTR(txt,posa,1)=' ' THEN
LET txt = SUBSTR(txt,posa);
EXIT FOR;
END IF
END FOR

可以看到，当LENGTH(txt)(其中txt由x0.identif的值组成)等于0(空字符串)时，就会出现我提到的错误。

不幸的是，源表中存在空字符串值，这导致了这个问题。

解决方案是将缺失的值添加到源表中，并更改列定义以不允许空字符串。