RE: Wrong Results from Select on sysindexes Subquery

I am including Jonathan's entire message at the end of this one but quoting the relevant portions and responding at the top. > 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. If this is true, I may have to beat my self senseless with the three-volume SQL Guide, in which I found nothing to cover this scenario. My understanding of correlated subqueries is to expect them to be evaluated each row, and that is indeed what happens on physical tables. It is just on views where it seems to happen that I get screwy data. I have unloaded the data from sysindexes and loaded it into a true table and this does not occur. Also, the same query against sysindexes in 7.31.UD7 on Solaris 8 does not show the same behaviour. So, if this is intended behaviour: 1. The behaviour for views differs than that for tables; why? 2. This needs to be documented. However, there is more evidence which would justify calling this a bug. If I create a second table with indexes, its results are another wrong subset. The sub select is not being executed once per query or once per row, but once per tabid (first where clause). I can send more test case data if anyone wants to see it. > The screwball result is the 'case' value - which > changes from null to 3 when we process the first multi-column index. Actually, I originally stuck the 'case' in because I wanted to handle 'part2 = 0' without losing those indexes. The behaviour is rather odd though, so I left it in. The 'case' is not necessary for reproduction. > Is the use of a view a key part of the reproduction? > Is the use of sysindexes a key part of the reproduction? I used sysindexes because it is a common view, and I wanted a common test case. However, sysindexes has proven to be far more interesting than any other test case I have developed, by any means. The view is definitely a critical element, as is a sufficiently complex join. I do not have IDS 10 to test on, but I do not mind looking at the v10 doc so long as I can confirm expected behaviour has not changed. Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc. -----Original Message----- From: Jonathan Leffler [mailto:jleffler@earthlink.net] Sent: Wednesday, December 14, 2005 11:36 PM To: informix-list@iiug.org Subject: Re: Wrong Results from Select on sysindexes Subquery 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