Обсуждение: select clause not according to SQL standard
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 -----
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
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: > 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
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