Informix - Problem description
Problem IT30915 | Status: Closed |
RUNNING BUILTIN UDR EXPLAIN_SQL CAN ASSERT IN VARIOUS XML ROUTINES WITH LARGER WHERE IN CLAUSE | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
The problem involves queries that are passed to the builtin routine, explain_sql(), that have larger WHERE IN clauses (see example below). Running the query after executing SET EXPLAIN ON does not cause an assertion failure. This defect causes stack corruption and the server will typically be unable to unwind the stack and only report a couple functions. Examples from linux x86_64 include: 0x0000000001bbb9af (oninit) afstack 0x0000000001bb9a47 (oninit) afhandler 0x0000000001bb8c17 (oninit) affail_interface 0x0000000001bbe76e (oninit) mt_ex_throw_sig 0x0000000001bc657e (oninit) afsig_handler 0x0000003ddfa0e4c0 (Linux) 0x0000000000b10999 (oninit) xml_strcopy 0x0000000000af762e (oninit) printkeyfirstfilter_xml_pre 0x000000000141f989 (oninit) afstack 0x0000000001422961 (oninit) afhandler 0x0000000001423142 (oninit) affail_interface 0x00000000014236d1 (oninit) mt_ex_throw_sig 0x000000000142e343 (oninit) afsig_handler 0x0000003ddfa0e4c0 (Linux) 0x0000000000974789 (oninit) printtablescan_xml An example from sol64: 0x10062b234 oninit :: expl_add_data + 0x4 sp=0x10c4635e0(0x30393436202c2031, 0x37202c2031313832 , 0x37202c2031313132, 0x10d0d4128, 0x0, 0x101400) 0x100619744 oninit :: printnode_xml + 0xb54 sp=0x10c463690(0x3036202c20323335, 0x36202c2031313830, 0x37202c2031313732, 0x30202c2031313730, 0x37202c2031313731, 0x36202c2032333537) Example of query problematic query SELECT SKIP 0 FIRST 20 w.c1, COUNT(*) OVER() AS TR, a.c12 FROM t0 w INNER JOIN t1 AS a ON(a.c14=w.c1) INNER JOIN t2 AS e ON(e.c2=a.c2) INNER JOIN t3 AS g ON(g.c4=e.c2) INNER JOIN t4 AS i ON(i.c5=g.c5) INNER JOIN t5 AS k ON(k.c3=a.c3) INNER JOIN t6 AS o ON(o.c1=w.c1) INNER JOIN t7 AS p ON(p.c6=o.c6) INNER JOIN t8 AS r ON(r.c7=p.c7) INNER JOIN cos AS s ON(s.cos_id=r.c8) INNER JOIN t0 AS x ON(x.c16=w.c1) LEFT JOIN t1 AS b ON(b.c14=x.c1) LEFT JOIN t2 AS y ON(y.c2=b.c2) LEFT JOIN t3 AS h ON(h.c4=y.c2) LEFT JOIN t4 AS j ON(j.c5=h.c5) LEFT JOIN t5 AS l ON(l.c3=b.c3) INNER JOIN t10 AS v ON(v.c1=x.c1) INNER JOIN t1 AS c ON(c.c14=v.c10) INNER JOIN t2 AS f ON(f.c2=c.c2) INNER JOIN t5 AS m ON(m.c3=c.c3) INNER JOIN t11 AS u ON(u.c11=v.c11) INNER JOIN t1 AS d ON(d.c14=u.c11) INNER JOIN t5 AS n ON(n.c3=d.c3) LEFT JOIN t6 AS uc ON(uc.c1=x.c1) LEFT JOIN t7 AS q ON( q.c6=uc.c6) LEFT JOIN t8 AS rec ON(rec.c7=q.c7) LEFT JOIN cos AS t ON(t.cos_id=rec.c8) WHERE w.c15 IS NULL AND b.c3 != 510 AND b.c3 NOT IN('594','512','490','11565','2349') AND a.c3 NOT IN('594','512','490','11565','2349') AND a.c2 IN( '41504','41825','41520','41524','41634','41583','41585','41578', '40949','41523','41698','41522','41521','41870','41906','41854', '31504','31825','31520','31524','31634','31583','31585','31578', '30949','31523','31698','31522','31521','31870','31906','31854', '21504','21825','21520','21524','21634','21583','21585','21578', '20949','21523','21698','21522','21521','21870','21906','21854', '11504','11825','11520','11524','11634','11583','11585','11578', '10949','11523','11698','11522','11521','11870','11906','11854', '11519','11579','11628','2246','11865','11823','11648','11641', '11646','11647','2244','2245','2274','11632','10947','2214', '10946','12007','2354','2355','11945','11943','11944','12006', '2356','11807','11720','11707','11716','2357','2358','11898', '11702','2336','2335','11929','2334','11621','11873','11872', '11871','11651','11703','11709','11649','11808','11721','11706', '11717','11653','2270','11899','11713') AND f.c13 IN('DPL') AND d.c3 IN('11568') AND c.c3 NOT IN('594','512','490','11565','2349') AND ((1=1 And h.c5 IN('2') AND rec.c8 IN('S')) OR (1=1 AND g.c5 IN('2') AND r.c8 IN('S'))) GROUP BY w.c1, a.c12 ORDER BY a.c12 ASC, w.c1; | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC14 and 14.10.xC3. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Update to Informix Server 12.10.xC14 or 14.10.xC3. * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC14 and 14.10.xC3. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Update to Informix Server 12.10.xC14 or 14.10.xC3. * **************************************************************** | |
Comment | |
Update to Informix Server 12.10.xC14 or 14.10.xC3. | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 12.11.2019 27.02.2020 27.02.2020 |
Problem solved at the following versions (IBM BugInfos) | |
12.10.xC14, 14.10.xC3 | |
Problem solved according to the fixlist(s) of the following version(s) |