Re: SQL query brings database server down

June Tong wrote: > > Jonathan Leffler wrote: > > > So, just out of curiosity, I ran an adapted version of the query on my > > desktop Sparc 20 running Solaris 2.6 and OnLine 7.24.UC1 against the stores7 > > database, with the query: > > > > SELECT fname, lname, address1 > > FROM customer > > WHERE "John" IN (fname);> > > > and was not able to detect any problems... It suggests that the bug > > was fixed between the 7.1x and 7.2x families. I've no idea which > > version fixed it. And the fix was not to generate a syntax error but > > to accept the query and generate no output... This might need to be > > reported as a bug, still, but at least the engine doesn't crash. > > Oh, I don't think the fix was "to generate no output." There aren't any customers > named "John" in the stores7 database (incredible, isn't it?) I tried > SELECT * FROM customer > WHERE "Ludwig" IN (fname) > and got back Ludwig Pauli's record. This was on IDS-PE, which appears to be 7.22 on> NT. Similarly, using WHERE "Ludwig" IN ("Ludwig") returns all rows, just as 1=1 does. > > June > -- > june_t@hotmail.com > Grounded in Palo Alto, living on Twix bars June is correct, in that the syntax is valid, however the problem points out a bug in the system. My "guess" is that there is a NULL value in one of the first_name fields (assuming that the column allows NULL values), and that it ends up being mishandled by the server causing a segmentation fault (NULL pointer access possibly, or non-terminated char buffer). Definitely a problem to report to Informix, but first verify if there are any NULL values in the first_name column. BTW, the syntax IS valid, just as one may want to execute the following SQL: select * from customers where "Bozo" in (first_name, last_name); This is semantically identical to: select * from customers where last_name = "Bozo" or first_name ="Bozo"; However, the first example will always force a full table scan, whereas the second will utilize indices if available and appropriate - the optimizer would determine that. -Bill Boyle