Errors in subselects

#1

Interestingly 8.0.2.4301 executes this statement:

select PersonID from xxxcand where xxxcand.consno in (select consno from
xxxconsxref where region = 'M') and personid is not null;

without error even though there is no consno field in xxxconsxref. (there is
one in xxxcand, but its not a correlated subquery). If you execute

select consno from xxxconsxref where region = 'M'

by itself you get the appropiate error message. The query behaves as though
the xxxcand.consno in (select consno from xxxconsxref where region = 'M')
clause was not present!

Justin Willey

#2

Its reproducable here. We logged bug QTS 341445 for this problem.

--
Hartmut Branz
International and Sustaining Engineering
iAnywhere Solutions

Justin Willey wrote:

Quote:

Interestingly 8.0.2.4301 executes this statement:

select PersonID from xxxcand where xxxcand.consno in (select consno from
xxxconsxref where region = 'M') and personid is not null;

without error even though there is no consno field in xxxconsxref. (there is
one in xxxcand, but its not a correlated subquery). If you execute

select consno from xxxconsxref where region = 'M'

by itself you get the appropiate error message. The query behaves as though
the xxxcand.consno in (select consno from xxxconsxref where region = 'M')
clause was not present!

Justin Willey

#3

Many thanks, I had an 'interesting' half hour trying to work out where I was
going wrong! Its always the obvious ones .....

Justin Willey

"Hartmut" <hbranz (AT) sybase (DOT) com> wrote

Quote:

Its reproducable here. We logged bug QTS 341445 for this problem.

--
Hartmut Branz
International and Sustaining Engineering
iAnywhere Solutions

Justin Willey wrote:

Interestingly 8.0.2.4301 executes this statement:

select PersonID from xxxcand where xxxcand.consno in (select consno from
xxxconsxref where region = 'M') and personid is not null;

without error even though there is no consno field in xxxconsxref.
(there is
one in xxxcand, but its not a correlated subquery). If you execute

select consno from xxxconsxref where region = 'M'

by itself you get the appropiate error message. The query behaves as
though
the xxxcand.consno in (select consno from xxxconsxref where region =
'M')
clause was not present!

Justin Willey

#4

I don't see what the problem is here. It is perfectly valid to place the
unqualified column name "consno" in the select list of the subquery
(which does make the subquery correlated). The subquery predicate will
return true as long there exists at least one row in xxconsxref where
region = 'M'. If there is no such row, the subquery will consist of the
empty set, so that the predicate xxxcand.consno in ( <subquery> ) will
evaluate to UNKNOWN which will then be interpreted as FALSE.

Glenn

Justin Willey wrote:

Quote:

Interestingly 8.0.2.4301 executes this statement:

select PersonID from xxxcand where xxxcand.consno in (select consno from
xxxconsxref where region = 'M') and personid is not null;

without error even though there is no consno field in xxxconsxref. (there is
one in xxxcand, but its not a correlated subquery). If you execute

select consno from xxxconsxref where region = 'M'

by itself you get the appropiate error message. The query behaves as though
the xxxcand.consno in (select consno from xxxconsxref where region = 'M')
clause was not present!

Justin Willey

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

#5

Ohhhhhh, man, that is so *evil*... I, too, would have bet good money
it was a bug. But nooooooo, it's standard ANSI SQL, isn't it?

This would be a good time to reaffirm the rule, "always qualify column
names."

Here's a script that demonstrates what's going on...

CREATE TABLE t1 (
pkey INTEGER NOT NULL PRIMARY KEY,
c1 INTEGER NOT NULL );

CREATE TABLE t2 (
pkey INTEGER NOT NULL PRIMARY KEY,
c2 INTEGER NOT NULL );

INSERT t1 VALUES ( 1, 1 );
INSERT t1 VALUES ( 2, 2 );

INSERT t2 VALUES ( 2, 2 );
COMMIT;

-- Wrong column name, no qualification, no error, unintended result.
SELECT pkey, c1
FROM t1
WHERE c1 IN ( SELECT c1
FROM t2 );

-- Wrong column name, error produced because it's qualified.
-- That's what you want, to catch your coding errors.
SELECT t1.pkey, t1.c1
FROM t1
WHERE t1.c1 IN ( SELECT t2.c1
FROM t2 );

-- Correct column name, desired result.
SELECT t1.pkey, t1.c1
FROM t1
WHERE t1.c1 IN ( SELECT t2.c2
FROM t2 );

Breck

On 5 Dec 2003 07:08:00 -0800, Glenn Paulley <paulley (AT) ianywhere (DOT) com>
wrote:

Quote:

I don't see what the problem is here. It is perfectly valid to place the
unqualified column name "consno" in the select list of the subquery
(which does make the subquery correlated). The subquery predicate will
return true as long there exists at least one row in xxconsxref where
region = 'M'. If there is no such row, the subquery will consist of the
empty set, so that the predicate xxxcand.consno in ( <subquery> ) will
evaluate to UNKNOWN which will then be interpreted as FALSE.

Glenn

Justin Willey wrote:
Interestingly 8.0.2.4301 executes this statement:

select PersonID from xxxcand where xxxcand.consno in (select consno from
xxxconsxref where region = 'M') and personid is not null;

without error even though there is no consno field in xxxconsxref. (there is
one in xxxcand, but its not a correlated subquery). If you execute

select consno from xxxconsxref where region = 'M'

by itself you get the appropiate error message. The query behaves as though
the xxxcand.consno in (select consno from xxxconsxref where region = 'M')
clause was not present!

Justin Willey

--
bcarter (AT) risingroad (DOT) com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com

#6

I see your point about the sub query in fact being correlated and therefore
if there is no consno column in xxxconsxref the engine assumes you're
talking about xxxcand.cosno. Interestingly though if you add the column
consno to xxxconsxref the query executes and you don't get a 'Ambiguous
column name error'. This suggests that there is an order in which the engine
looks for the columns in the tables in the queries, which could presumably
change depending on how the optimiser restructures the query.

Justin Willey

"Glenn Paulley" <paulley (AT) ianywhere (DOT) com> wrote

Quote:

I don't see what the problem is here. It is perfectly valid to place the
unqualified column name "consno" in the select list of the subquery
(which does make the subquery correlated). The subquery predicate will
return true as long there exists at least one row in xxconsxref where
region = 'M'. If there is no such row, the subquery will consist of the
empty set, so that the predicate xxxcand.consno in ( <subquery> ) will
evaluate to UNKNOWN which will then be interpreted as FALSE.

Glenn

Justin Willey wrote:
Interestingly 8.0.2.4301 executes this statement:

select PersonID from xxxcand where xxxcand.consno in (select consno from
xxxconsxref where region = 'M') and personid is not null;

without error even though there is no consno field in xxxconsxref.
(there is
one in xxxcand, but its not a correlated subquery). If you execute

select consno from xxxconsxref where region = 'M'

by itself you get the appropiate error message. The query behaves as
though
the xxxcand.consno in (select consno from xxxconsxref where region =
'M')
clause was not present!

Justin Willey

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
iAnywhere
Developer Community at www.ianywhere.com/developer

#7

I suppose that means that's is going to be the pedants rather than the meek
who inherit the earth!

J

"Breck Carter [TeamSybase]" <NOSPAM__bcarter (AT) risingroad (DOT) com> wrote in
message news:qf71tvcc0od49r7gfcvn6of4cv6r69ts74 (AT) 4ax (DOT) com...

Quote:

Ohhhhhh, man, that is so *evil*... I, too, would have bet good money
it was a bug. But nooooooo, it's standard ANSI SQL, isn't it?

This would be a good time to reaffirm the rule, "always qualify column
names."

#8

The name space search rules apply to the original SQL statement. Any
rewritings that may be made to the statement during optimization are
performed only after all of the columns have been resolved.

You don't get an ambiguous column reference error in this example
because if the name can be resolved unambiguously in any one SELECT
block, then the column reference is well-defined, and stems from the
(one) local table in that block (or any expression in any outer query
block above it). You'll get an ambiguous reference error if two or more
tables in the same block have the same column names and the column
reference is unqualified.

Glenn

Justin Willey wrote:

Quote:

I see your point about the sub query in fact being correlated and therefore
if there is no consno column in xxxconsxref the engine assumes you're
talking about xxxcand.cosno. Interestingly though if you add the column
consno to xxxconsxref the query executes and you don't get a 'Ambiguous
column name error'. This suggests that there is an order in which the engine
looks for the columns in the tables in the queries, which could presumably
change depending on how the optimiser restructures the query.

Justin Willey

"Glenn Paulley" <paulley (AT) ianywhere (DOT) com> wrote in message
news:3fd09f50$1 (AT) forums-1-dub (DOT) ..

I don't see what the problem is here. It is perfectly valid to place the
unqualified column name "consno" in the select list of the subquery
(which does make the subquery correlated). The subquery predicate will
return true as long there exists at least one row in xxconsxref where
region = 'M'. If there is no such row, the subquery will consist of the
empty set, so that the predicate xxxcand.consno in ( <subquery> ) will
evaluate to UNKNOWN which will then be interpreted as FALSE.

Glenn

Justin Willey wrote:

Interestingly 8.0.2.4301 executes this statement:

select PersonID from xxxcand where xxxcand.consno in (select consno from
xxxconsxref where region = 'M') and personid is not null;

without error even though there is no consno field in xxxconsxref.

(there is

one in xxxcand, but its not a correlated subquery). If you execute

select consno from xxxconsxref where region = 'M'

by itself you get the appropiate error message. The query behaves as

though

the xxxcand.consno in (select consno from xxxconsxref where region =

'M')

clause was not present!

Justin Willey

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the

iAnywhere

Developer Community at www.ianywhere.com/developer

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Errors in subselects

sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general

Errors in subselects - 12-05-2003 , 07:03 AM

Re: Errors in subselects - 12-05-2003 , 08:04 AM

Re: Errors in subselects - 12-05-2003 , 08:47 AM

Re: Errors in subselects - 12-05-2003 , 09:08 AM

Re: Errors in subselects - 12-05-2003 , 09:27 AM

Re: Errors in subselects - 12-10-2003 , 05:37 AM

Re: Errors in subselects - 12-10-2003 , 05:38 AM

Re: Errors in subselects - 12-10-2003 , 09:31 AM