Informix 15 - Index on a temp table issue.
Hello everyone.
We are in the process of migrating a user from 14 to 15.0.1.0. While testing we found a strange issue. A query runs fine on version 14 (returns rows) but does not return anything on 15:
DROP TABLE IF EXISTS tmp_srteink;
CREATE TEMP TABLE tmp_srteink(val NCHAR(8), srteink NCHAR(8)) WITH NO LOG;
CREATE INDEX tmp_srteink_i ON tmp_srteink(val);
LOAD FROM "./bin600/tmp_srteink.unl" INSERT INTO tmp_srteink;
select vbhreyf.rowid,vbgerd.eink,tmp.srteink,vbhreyf.dags,vbhreyf.tilvisun[1,10],
(select sum(upph) from vbhreyfsl where hreyf=vbhreyf.hreyf),
(select sum(upphvidsk) from vbhreyfsl where hreyf=vbhreyf.hreyf),vbhreyf.tegund,vbhreyf.stada,vbhreyf.leidrett,vbhreyf.utansjod,vbhreyf.hreyf,vbhreyf.tilvisun
from vbhreyf,verdbref,outer vbgerd,vidskipti,tmp_srteink tmp
where verdbref.sjodnr='Z000'
and verdbref.vbref=vbhreyf.vbref
and vbgerd.gerd=verdbref.gerd
and vidskipti.vidsk=vbhreyf.vidsk
and vbhreyf.vidsk = 96619835
and tmp.val=verdbref.eink
order by vbgerd.eink,srteink,vbhreyf.tegund,vbhreyf.dags,vbhreyf.hreyf
This query should return 2 rows but returns none on 15. However, if you omit the CREATE INDEX statement on the temp table, it does return the rows! I've also tried with CHAR(8) instead of NCHAR(8) with the same result. EXPLAIN tells me that 14 does not use the temp table index in the query (does a seq. scan) but 15 does. Forcing 14 to use the index returns the rows nevertheless.
I'm working on a stand-alone test case, but I post this here in the meantime if someone has encountered anything similar.
Cheers,
-Snorri
------------------------------
Snorri Bergmann
------------------------------
Snorri: Definitely something to open a support case for. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Hi Snorri, how many rows, roughly, are there in tmp_srteink, and what page size does it use? What actually matters is what's the maximum expected rowid in this table? Also are you sure you first created the index and then loaded the temp table, or maybe the other way round (as you'd normally do with a larger table)? Asking against the backdrop of a problem we recently fixed with (SMALL) rowids > 0x80000000. Should this apply in your case, trying a larger page size for the temp table might avoid the problem. HTH, Andreas ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------