Re: I can't believe this is a bug

rkusenet wrote: > IDS 9.21.UC4 on Solaris 2.6 > > Look at this code:- > > select pnr_locator > from ftravel_tr4@ifmx_flx:pnr_info > where pnr_locator not in > (select pnr from rpt_table where dep_date > TODAY-331) > and pnr_locator not in > (select pnr_locator from pnr_status where pnrstat = 'A') > ^^^^^^^^^^^> > this is a typo. There is no such field pnr_locator in the table > pnr_status. The correct column name is pnr. Sadly, it's not a bug, it's a feature. Of SQL not Informix. What makes you think that the indicated pnr_locator will be searched for only in pnr_status? You clearly have a pnr_locator in pnr_info which is the table on the top-level select. Correlated sub-queries can only work because the parser considers the "scope" of the columns. Inside the subselect, all the listed tables are available for matching a column! This is why I ALWAYS, I mean, ALWAYS put table.column for EVERY column name. If you had written pnr_status.pnr_locator in the nested select then you would have discovered the problem immediately. Unfortunately, although your accidental query is virtually useless, it's not possible for the parser to discover and report bad SQLs - even if this one was detected and reported, there are so many ways to unintentionally screw up an SQL that it's not really reasonable to demand sensibility checks.