select clause not according to SQL standard

Started by Klaus Itaabout 21 years ago4 messagesbugs

postgres@stro.at

about 21 years ago

hi all!

sorry if you are recieving this 3 times, i am too stupid to file a simple
bugreport (4rd!!! try already) . btw the bugreport webinterface is
broken.

i am using version 7.4.7/Debian of postgres.

as far as i understand the standard, the following situation should not
be possible. neither is it stated in the manual that the select is not
conforming to the standard.

i have the following data:

table L:
LNR ORT LCODE MENGE
L1 Graz A 1000
L2 Wien C 500
L3 Wien C 1500
L4 Linz B 1000
L5 Graz B 300

table P:
PNR PNAME ORT PREIS
P1 Alpha Wien 50.00
P2 Delta Linz 95.00
P3 Sigma Linz 75.00
P4 Omega Wien 40.00

now the problem:

select l.ort;
gives:

ORT
Graz
Wien
Wien
Linz
Graz

select l.ort from p;
gives:

ORT
Graz
Wien
Wien
Linz
Graz
Graz
Wien
Wien
Linz
Graz
Graz
Wien
Wien
Linz
Graz
Graz
Wien
Wien
Linz
Graz

i know what it is doing, but still i feel this should be considered a
bug as this "feature" is very inviting to bugs in scripts. i asked
around and everybody seems very astounished by the results one gets here
from postgres.
please let me know if i am mistaken
i thank you 1000000 times for your excellent work! -> i am trying to
replace oracle with postgres at our university and the more
standard-compliant postgresql is, the better are my chances :-)

regs,
klaus

----- End forwarded message -----

Richard Huxton

dev@archonet.com

about 21 years ago

In reply to: Klaus Ita (#1)

Re: select clause not according to SQL standard

Klaus Ita wrote:

i have the following data:

table L:
LNR ORT LCODE MENGE
L1 Graz A 1000
L2 Wien C 500
L3 Wien C 1500
L4 Linz B 1000
L5 Graz B 300

table P:
PNR PNAME ORT PREIS
P1 Alpha Wien 50.00
P2 Delta Linz 95.00
P3 Sigma Linz 75.00
P4 Omega Wien 40.00

now the problem:

select l.ort;
gives:

ORT
Graz
Wien
Wien
Linz
Graz

select l.ort from p;
gives:

ORT
Graz
Wien
Wien
Linz
Graz
Graz
Wien

etc

Does it still do it if you disable "add_missing_from" in postgresql.conf?
--
Richard Huxton
Archonet Ltd

Klaus Ita

postgres@stro.at

about 21 years ago

In reply to: Richard Huxton (#2)

Re: select clause not according to SQL standard

Hi Richard!

burn me, damn me! thank you. it is now working and i will sit back and
read some more of the conf-options.

you just gave me a huge argument for the db and so i will probably win
my fight against a stubborn professor, that wants to stick to his big
fat o....e 'cause it is the most conforming db.

thx,
klaus

* Richard Huxton <dev@archonet.com> [2005-02-25 15:43:

Show quoted text

From: Richard Huxton <dev@archonet.com>
Subject: Re: [BUGS] select clause not according to SQL standard
To: Klaus Ita <postgres@stro.at>
Cc: pgsql-bugs@postgresql.org
X-Virus-Scanned: by Amavis (ClamAV) at stro.at

Klaus Ita wrote:

i have the following data:

table L:
LNR ORT LCODE MENGE
L1 Graz A 1000
L2 Wien C 500
L3 Wien C 1500
L4 Linz B 1000
L5 Graz B 300

table P:
PNR PNAME ORT PREIS
P1 Alpha Wien 50.00
P2 Delta Linz 95.00
P3 Sigma Linz 75.00
P4 Omega Wien 40.00

now the problem:

select l.ort;
gives:

ORT
Graz
Wien
Wien
Linz
Graz

select l.ort from p;
gives:

ORT
Graz
Wien
Wien
Linz
Graz
Graz
Wien

etc

Does it still do it if you disable "add_missing_from" in postgresql.conf?
--
Richard Huxton
Archonet Ltd

Richard Huxton

dev@archonet.com

about 21 years ago

In reply to: Klaus Ita (#3)

Re: select clause not according to SQL standard

Klaus Ita wrote:

Hi Richard!

burn me, damn me! thank you. it is now working and i will sit back and
read some more of the conf-options.

It catches lots of people out. Helpful sometimes, but mostly I spend
time puzzling just like you did.

you just gave me a huge argument for the db and so i will probably win
my fight against a stubborn professor, that wants to stick to his big
fat o....e 'cause it is the most conforming db.

What, with their use of nulls?

--
Richard Huxton
Archonet Ltd