Expected behaviour? (Well, obviously)
Vastly simplified example of one of our developers' typos:
create table tab1(col1 integer);
create table tab2(col2 integer);
insert into tab1 <a few thousand rows>;
insert into tab2 values <several more thousand rows>;
update tab1 set col1 = (select col1 from tab2)
(Using dbaccess under IDS9.30 on HP-UX11i)
NOTE the subselect is selecting a column that ISN'T in tab2. Rather
than failing with a 'no such column' type error, it updates all rows
in tab1 as many times as there are rows in tab2 - it's obviously doing
a correlated subquery where a join isn't being made.
We can cause the error with subselect "SELECT tab2.col1 from tab2",
but the point my developers have made is that it isn't immediately
obvious that it is/isn't a syntax problem. Is this just an artifact of
the way IDS does a subquery correlation? Any way it could have been
trapped? Or should I just send my developers to SQL101(remedial)
again?
Cheers
Malc
Malc P wrote:
> Vastly simplified example of one of our developers' typos:
>
> create table tab1(col1 integer);
> create table tab2(col2 integer);
> insert into tab1 <a few thousand rows>;
> insert into tab2 values <several more thousand rows>;
> update tab1 set col1 = (select col1 from tab2)>
> (Using dbaccess under IDS9.30 on HP-UX11i)
> NOTE the subselect is selecting a column that ISN'T in tab2. Rather
> than failing with a 'no such column' type error, it updates all rows
> in tab1 as many times as there are rows in tab2 - it's obviously doing
> a correlated subquery where a join isn't being made.
From an SQL Standard point of view it should raise an error stating that
a scalar subquery returned more than one row. But that may be an
artifact of your simplification.
> We can cause the error with subselect "SELECT tab2.col1 from tab2",
> but the point my developers have made is that it isn't immediately
> obvious that it is/isn't a syntax problem. Is this just an artifact of
> the way IDS does a subquery correlation? Any way it could have been
> trapped? Or should I just send my developers to SQL101(remedial)
> again?
If an SQL column identifier cannot be resolved within the immediate
context (tab2) then SQL name resolution requires the DBMS to start
looking up. The next outer context is tab1. So IDS is behaving compliant
by not raising a syntaxt error. It should raise a runtime error as I
indicated above for the simplified example you gave.
Send your developers to SQL 101...
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Serge Rielau wrote:
> Malc P wrote:
>
>> Vastly simplified example of one of our developers' typos:
>>
>> create table tab1(col1 integer);
>> create table tab2(col2 integer);
>> insert into tab1 <a few thousand rows>;
>> insert into tab2 values <several more thousand rows>;
>> update tab1 set col1 = (select col1 from tab2)>>
>> (Using dbaccess under IDS9.30 on HP-UX11i)
>> NOTE the subselect is selecting a column that ISN'T in tab2. Rather
>> than failing with a 'no such column' type error,
This periodically crops up as a question, either in a pure SELECT
statement or in an UPDATE or (occasionally) a DELETE statement. As
Serge says, syntactically - and even semantically - the operation is
being correctly interpreted.
>> it updates all rows
>> in tab1 as many times as there are rows in tab2 - it's obviously doing
>> a correlated subquery where a join isn't being made.
>
> From an SQL Standard point of view it should raise an error stating that
> a scalar subquery returned more than one row. But that may be an
> artifact of your simplification.
And as Serge says here, too, there should, I think, be a cardinality
error here.
>> We can cause the error with subselect "SELECT tab2.col1 from tab2",
>> but the point my developers have made is that it isn't immediately
>> obvious that it is/isn't a syntax problem. Is this just an artifact of
>> the way IDS does a subquery correlation? Any way it could have been
>> trapped? Or should I just send my developers to SQL101(remedial)
>> again?
>
> If an SQL column identifier cannot be resolved within the immediate
> context (tab2) then SQL name resolution requires the DBMS to start
> looking up. The next outer context is tab1. So IDS is behaving compliant
> by not raising a syntaxt error. It should raise a runtime error as I
> indicated above for the simplified example you gave.
> Send your developers to SQL 101...
I'm not sure it's SQL 101 - it is not trivial. But it is certainly
standard.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/