Informix crashes with EXCEPTION_ACCESS_VIOLATION when using ROW predicates in JOIN .. ON clauses

[Lukas Eder]

(This is actually a bug report. Not sure if this is the right place to report it)

When running the following query on an Informix database, the database crashes fatally with an EXCEPTION_ACCESS_VIOLATION.

select
trim(systables.owner) owner,
trim(systables.tabname) tabname,
trim(sysconstraints.constrname) constrname,
sysindexes.part1, sysindexes.part2,
sysindexes.part3, sysindexes.part4,
sysindexes.part5, sysindexes.part6,
sysindexes.part7, sysindexes.part8,
sysindexes.part9, sysindexes.part10,
sysindexes.part11, sysindexes.part12,
sysindexes.part13, sysindexes.part14,
sysindexes.part15, sysindexes.part16
from
sysconstraints
join systables
on sysconstraints.tabid = systables.tabid
join sysindexes
on row (sysconstraints.owner, sysconstraints.idxname)
= row (sysindexes.owner, sysindexes.idxname)
order by
sysconstraints.owner asc,
systables.tabname asc,
sysconstraints.constrname asc

The database I'm using is a Developer Edition of Informix 12.10 on Windows.

I could track down the issue to be strictly related to the usage of row() predicates in the join .. on clause:

-- causes issues
from
sysconstraints
join systables
on sysconstraints.tabid = systables.tabid
join sysindexes
on row (sysconstraints.owner, sysconstraints.idxname)
= row (sysindexes.owner, sysindexes.idxname)
Here's an equivalent on clause that works just fine:

-- causes no issues
from
sysconstraints
join systables
on sysconstraints.tabid = systables.tabid
join sysindexes
on sysconstraints.owner = sysindexes.owner
and sysconstraints.idxname = sysindexes.idxname
And here's an equivalent query that uses the row() predicates in the where clause, which also works:

-- causes no issues
from
sysconstraints
join systables
on sysconstraints.tabid = systables.tabid
cross join sysindexes
where row (sysconstraints.owner, sysconstraints.idxname)
= row (sysindexes.owner, sysindexes.idxname)

Conclusion: do NOT use ROW() predicates in the Informix JOIN .. ON clause. It will crash your database

Cross-posted here:
http://stackoverflow.com/q/25035770/521799

[Marco Greco]

Post by Lukas Eder
(This is actually a bug report. Not sure if this is the right place to report it)
When running the following query on an Informix database, the database crashes fatally with an EXCEPTION_ACCESS_VIOLATION.
select
trim(systables.owner) owner,
trim(systables.tabname) tabname,
trim(sysconstraints.constrname) constrname,
sysindexes.part1, sysindexes.part2,
sysindexes.part3, sysindexes.part4,
sysindexes.part5, sysindexes.part6,
sysindexes.part7, sysindexes.part8,
sysindexes.part9, sysindexes.part10,
sysindexes.part11, sysindexes.part12,
sysindexes.part13, sysindexes.part14,
sysindexes.part15, sysindexes.part16
from
sysconstraints
join systables
on sysconstraints.tabid = systables.tabid
join sysindexes
on row (sysconstraints.owner, sysconstraints.idxname)
= row (sysindexes.owner, sysindexes.idxname)
order by
sysconstraints.owner asc,
systables.tabname asc,
sysconstraints.constrname asc
The database I'm using is a Developer Edition of Informix 12.10 on Windows.
-- causes issues
from
sysconstraints
join systables
on sysconstraints.tabid = systables.tabid
join sysindexes
on row (sysconstraints.owner, sysconstraints.idxname)
= row (sysindexes.owner, sysindexes.idxname)
-- causes no issues
from
sysconstraints
join systables
on sysconstraints.tabid = systables.tabid
join sysindexes
on sysconstraints.owner = sysindexes.owner
and sysconstraints.idxname = sysindexes.idxname
-- causes no issues
from
sysconstraints
join systables
on sysconstraints.tabid = systables.tabid
cross join sysindexes
where row (sysconstraints.owner, sysconstraints.idxname)
= row (sysindexes.owner, sysindexes.idxname)
Conclusion: do NOT use ROW() predicates in the Informix JOIN .. ON clause. It will crash your database
http://stackoverflow.com/q/25035770/521799

you don't give version information nor stack

[Lukas Eder]

The version is 12.10 on Windows.

I have uploaded the contents of
"C:\Program Files\IBM Informix Software Bundle\tmp\af.d3c009a"

... to here:
http://www.jooq.org/af.d3c009a

[Marco Greco]

Post by Lukas Eder
The version is 12.10 on Windows.
I have uploaded the contents of
"C:\Program Files\IBM Informix Software Bundle\tmp\af.d3c009a"
http://www.jooq.org/af.d3c009a
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list

The version is 12.10.FC2DE.
Not a known issue, still reproes.