Обсуждение: wrong query result
Hi
I just want to let you know one thing.
By the way, I'm running Postgres 8.3.1 on windows
I was making a query using pgadmin:
select clau, lecact-lecant as m3, m3any, ta, tc, 3 as mesos, persones, true as clavegueram
from fac_abonats
where clau in (select clau from tmp_claus_prova)
order by clau
clau is the primary key of the table fac_abonats
the thing is that the result of the query returns the same number of rows as the table fac_abonats (thousands).
select clau from tmp_claus_prova return about one hundred rows, and all the values in the column exists
in fac_abonats. So maybe something wrong is happening in the where clause.
At the end I've realized that there is no column named 'clau'. It has another name.
So the subselect query is wrong, if I execute it alone, an error appears
ERROR: no existe la columna «clau»
LINE 6: select clau from tmp_claus_prova
^
********** Error **********
ERROR: no existe la columna «clau»
Estado SQL:42703
Caracter: 188
The question is:
Is it a normal behaviour? Because I think that if the query is wrong, wouldn't be better to raise an error?
Regards,
Josep Porres
I just want to let you know one thing.
By the way, I'm running Postgres 8.3.1 on windows
I was making a query using pgadmin:
select clau, lecact-lecant as m3, m3any, ta, tc, 3 as mesos, persones, true as clavegueram
from fac_abonats
where clau in (select clau from tmp_claus_prova)
order by clau
clau is the primary key of the table fac_abonats
the thing is that the result of the query returns the same number of rows as the table fac_abonats (thousands).
select clau from tmp_claus_prova return about one hundred rows, and all the values in the column exists
in fac_abonats. So maybe something wrong is happening in the where clause.
At the end I've realized that there is no column named 'clau'. It has another name.
So the subselect query is wrong, if I execute it alone, an error appears
ERROR: no existe la columna «clau»
LINE 6: select clau from tmp_claus_prova
^
********** Error **********
ERROR: no existe la columna «clau»
Estado SQL:42703
Caracter: 188
The question is:
Is it a normal behaviour? Because I think that if the query is wrong, wouldn't be better to raise an error?
Regards,
Josep Porres
josep porres wrote: > select clau, lecact-lecant as m3, m3any, ta, tc, 3 as mesos, persones, true > as clavegueram > from fac_abonats > where clau in (select clau from tmp_claus_prova) > order by clau > At the end I've realized that there is no column named 'clau'. It has > another name. > The question is: > Is it a normal behaviour? Because I think that if the query is wrong, > wouldn't be better to raise an error? It's not an error, you need to be able to refer to columns in the outer query so you can do: ...(SELECT x FROM tmp_claus_prova WHERE y = clau) It *is* confusing where you have a typo though. It gets everyone from time to time. -- Richard Huxton Archonet Ltd
On Wed, Apr 09, 2008 at 10:20:18AM +0200, josep porres wrote: > At the end I've realized that there is no column named 'clau'. It has > another name. > So the subselect query is wrong, if I execute it alone, an error appears <snip> > The question is: > Is it a normal behaviour? Because I think that if the query is wrong, > wouldn't be better to raise an error? Google for "correlated subquery". Short answer: It's supposed to work that way. Have a nce day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Вложения
josep porres wrote: > select clau, lecact-lecant as m3, m3any, ta, tc, 3 as mesos, persones, true as clavegueram > from fac_abonats > where clau in (select clau from tmp_claus_prova) > order by clau > > the thing is that the result of the query returns the same > number of rows as the table fac_abonats (thousands). [...] > At the end I've realized that there is no column named > 'clau'. It has another name. > So the subselect query is wrong, if I execute it alone, an > error appears > > ERROR: no existe la columna «clau» > LINE 6: select clau from tmp_claus_prova > ^ > ********** Error ********** > > ERROR: no existe la columna «clau» > Estado SQL:42703 > Caracter: 188 > > The question is: > Is it a normal behaviour? Because I think that if the query > is wrong, wouldn't be better to raise an error? Yes, this is normal behaviour. The query is syntactically correct, it just doesn't do what you intended. It will do the same as: select clau, lecact-lecant as m3, m3any, ta, tc, 3 as mesos, persones, true as clavegueram from fac_abonats where 42 in (select 42 from tmp_claus_prova) order by clau except probably less efficiently. Yours, Laurenz Albe