matt_lists <[EMAIL PROTECTED]> wrote on 02/02/2005 08:50:16 AM: > Jay Blanchard wrote: > > >[snip] > > > > > >>>No, it isn't ignored...it just returns a FALSE for the IN statement > >>> > >>> > >[/snip] > > > >More info .... > > > >"The word IN is an alias for = ANY. Thus these two statements are the > >same: > > > >SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2); > >SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2); > > > >However, NOT IN is not an alias for <> ANY, but for <> ALL. See Section > >13.1.8.4, "Subqueries with ALL". " > > > >>From http://dev.mysql.com/doc/mysql/en/any-in-some-subqueries.html > > > > > > > I can tell you the sql works fine when I alias the column > > the original sql should not work at all, the column does not exist in > the table, yet it returns no query error >
I don't think the column alias is what made the query work. You should be able to call the column in your subquery 'moo_cow' and it should still work. The "fieldname IN (subquery)" construct works because the subquery returns a single column that acts as a list of values (even if that list is possibly empty). What I think you two are cross-communicating about is that for an INVALID subquery, MySQL threw no errors and no warnings. What I have heard discussed was that the clause "fieldname IN (failed subquery)" should evaluate as FALSE for the IN clause. Then I heard that the "AND fieldname IN (failed subquery)" term didn't restrict results, effectively saying that it evaluated as TRUE. Both cannot be correct. What I believe is that error was probably just ignored (that the processing of that branch of the WHERE tree failed silently) when it should have thrown an error for the entire statement. An invalid subquery should cause the entire enclosing statement to fail, but it didn't. It shouldn't matter whether the results of the subquery *could* be ignored, the syntax error (invalid column name) should have been raised and it should have cancelled the query. IMHO, that's the problem. I suggest that the OP report it as a bug. Shawn Green Database Administrator Unimin Corporation - Spruce Pine I disagree that the error should be evaluated as FALSE
