Wrong Results from Select on sysindexes Subquery

When doing a subquery on a view like sysindexes, the wrong results are returned. It appears that the first results are cached and returned for subsequent rows. This does not occur if the data is unloaded and loaded into a true table with the same columns as the view. My suspicion is that this is a bug, so I have notified support, but I wondered if anyone here might have another explanation and/or workaround. Especially a workaround. This affects in more than the query below which is just for test case purposes. CREATE TABLE index_test ( col1 SERIAL, col2 INT, col3 INT, col4 INT ); CREATE INDEX index_test_desc1 ON index_test (col1 DESC); CREATE INDEX index_test_asc2 ON index_test (col2); CREATE INDEX index_test_asc23 ON index_test (col2, col3); CREATE INDEX index_test_desc34 ON index_test (col3, col4 DESC); -- Select from sysindexes view SELECT st.tabname, si.tabid, si.idxname, si.part1, (SELECT colno FROM syscolumns sc WHERE si.tabid = sc.tabid and ABS(si.part1) = sc.colno), part2, CASE WHEN part2 = 0 THEN NULL ELSE (SELECT colno FROM syscolumns sc WHERE si.tabid = sc.tabid AND ABS(si.part2) = sc.colno) END CASE FROM systables st, sysindexes si WHERE st.tabid = si.tabid AND st.tabid > 99 AND st.tabname = 'index_test' ; -- Results -- tabname index_test -- tabid 100 -- idxname index_test_desc1 -- part1 -1 -- (expression) 1 -- part2 0 -- case -- -- tabname index_test -- tabid 100 -- idxname index_test_asc2 -- part1 2 -- (expression) 1 -- part2 0 -- case -- -- tabname index_test -- tabid 100 -- idxname index_test_asc23 -- part1 2 -- (expression) 1 -- part2 3 -- case 3 -- -- tabname index_test -- tabid 100 -- idxname index_test_desc34 -- part1 3 -- (expression) 1 -- part2 -4 -- case 3 -- Wrong column numbers returned. Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc. sending to informix-list

Oi! Shame on you! And from a Steering Commitee President for a Usergroup as well... Which IDS Version? Which OS Version?

Christopher Coleman wrote: > When doing a subquery on a view like sysindexes, the wrong results are returned. It appears that the first results are cached and returned for subsequent rows. This does not occur if the data is unloaded and loaded into a true table with the same columns as the view. > > My suspicion is that this is a bug, so I have notified support, but I wondered if anyone here might have another explanation and/or workaround. Especially a workaround. This affects in more than the query below which is just for test case purposes. > > CREATE TABLE index_test ( > col1 SERIAL, > col2 INT, > col3 INT, > col4 INT > );> > CREATE INDEX index_test_desc1 ON index_test (col1 DESC); > CREATE INDEX index_test_asc2 ON index_test (col2); > CREATE INDEX index_test_asc23 ON index_test (col2, col3); > CREATE INDEX index_test_desc34 ON index_test (col3, col4 DESC);> > -- Select from sysindexes view > SELECT st.tabname, si.tabid, si.idxname, > si.part1, > (SELECT colno FROM syscolumns sc > WHERE si.tabid = sc.tabid and ABS(si.part1) = sc.colno), > part2, > CASE > WHEN part2 = 0 THEN NULL > ELSE (SELECT colno FROM syscolumns sc > WHERE si.tabid = sc.tabid > AND ABS(si.part2) = sc.colno) > END CASE > FROM systables st, sysindexes si > WHERE st.tabid = si.tabid > AND st.tabid > 99 > AND st.tabname = 'index_test' > ;> > -- Results > -- tabname index_test > -- tabid 100 > -- idxname index_test_desc1 > -- part1 -1 > -- (expression) 1 > -- part2 0 > -- case > -- > -- tabname index_test > -- tabid 100 > -- idxname index_test_asc2 > -- part1 2 > -- (expression) 1 > -- part2 0 > -- case > -- > -- tabname index_test > -- tabid 100 > -- idxname index_test_asc23 > -- part1 2 > -- (expression) 1 > -- part2 3 > -- case 3 > -- > -- tabname index_test > -- tabid 100 > -- idxname index_test_desc34 > -- part1 3 > -- (expression) 1 > -- part2 -4 > -- case 3 > -- Wrong column numbers returned. Ignoring the issue of version of IDS (miscellaneous 9.40 versions) and platform (Solaris and Windows), I'm not wholly convinced that the behaviour is a bug - though it is not wholly intuitive either (but then, lots of other parts of SQL, let alone IDS's dialect of SQL, are not wholly intuitive). The problem is that I believe sub-select statements in the select-list are executed just once, not per row. If so, then your 'cached result' is correct. What's more, I think that's the intended behaviour. Now, given that these ones are correlated sub-queries, it might be more reasonable to expect them to be evaluated each row, but I'm still not sure that's what happens, and your empirical evidence mostly supports that hypothesis. The screwball result is the 'case' value - which changes from null to 3 when we process the first multi-column index. Not nice - and maybe will get your observations the label 'bug'. Do I have to go chasing this in the manuals for you? I was afraid you'd say yes. Using the 10.00.UC1 SQL Syntax manual (25122840.pdf), p2-569 covers some of this. I note that it restricts the use of ORDER BY; odd that it doesn't also list INTO TEMP (Hi Tom - yes, I know there's a more recent manual for UC3 or UC4 or both; I'm just not looking at it). Between pages 2-569 and 2-574, there are discussions of a number of forms of expression - but not one on (subquery) appearing in the select list. Scrutiny of the global index (25124420.pdf - nice feature) shows that the SQL Tutorial mentions sub-queries in the select-list on p5-24. The example there is: SELECT customer.customer_num, (SELECT SUM(ship_charge) FROM orders WHERE customer.customer_num = orders.customer_num) AS total_ship_chg FROM customer The results shown there dispute my suggestion - the subquery is re-evaluated for each customer num. So, we've found a hole in the SQL Syntax documentation (no discussion of the semantics of sub-queries in the select-list). And we may have found a genuine problem in the SQL statement quoted. Is the use of a view a key part of the reproduction? Is the use of sysindexes a key part of the reproduction? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/