Thread: Re: [SQL] aliases break my query
Peter Eisentraut <e99re41@DoCS.UU.SE> writes:
> Once again, I think that we *really* need to discuss whether implicit
> range table entries in SELECT are a good idea. We invariably get a
> question like this every week and invariably the answer is "if you give a
> table an alias you *must* refer to it by that alias". (I'm sure Tom has
> this reply automated by now.)
No, this one was actually a pretty original way of shooting oneself in
the foot ;-). I thought the interesting point was the confusion between
whether variables in the inner select were supposed to be local to the
inner select or references to the outer select. I'm not sure getting
rid of implicit rangetable entries would've helped prevent that.
> I claim the only thing that buys is
> confusion for very little convenience at the other end.
>
> Stop the madness! :)
I doubt that it's worth breaking a lot of existing applications for.
At one time Bruce had made some patches to emit informative notice
messages about implicit FROM entries, but that got turned off again
for reasons that I forget...
regards, tom lane
"Zeugswetter Andreas" <andreas.zeugswetter@telecom.at> writes:
> I think we could get agreement to not allow implicit from entries
> if there is a from clause in the statement, but allow them if a from clause
> is missing altogether. The patch did not distinguish the two cases.
Hmm, that's a thought. Taking it a little further, how about this:
"Emit a notice [or error if you insist] when an implicit FROM item is
added that refers to the same underlying table as any existing FROM
item."
95% of the complaints I can remember seeing were from people who got
confused by the behavior of "FROM table alias" combined with a reference
like "table.column". Seems to me the above rule would catch this case
without being obtrusive in the useful cases. Comments?
regards, tom lane
Tom Lane writes: > "Zeugswetter Andreas" <andreas.zeugswetter@telecom.at> writes: > > I think we could get agreement to not allow implicit from entries > > if there is a from clause in the statement, but allow them if a from clause > > is missing altogether. That's what I had in mind. > "Emit a notice [or error if you insist] when an implicit FROM item is > added that refers to the same underlying table as any existing FROM > item." That's a step in the right direction, but I'd still like to catch SELECT a.a1, b.b1 FROM a; SELECT a.a1 FROM a WHERE a.a2 = b.b1; both of which are more or less obviously incorrect and easily fixed. > 95% of the complaints I can remember seeing were from people who got > confused by the behavior of "FROM table alias" combined with a reference > like "table.column". Seems to me the above rule would catch this case > without being obtrusive in the useful cases. Comments? -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut <peter_e@gmx.net> writes:
>> "Emit a notice [or error if you insist] when an implicit FROM item is
>> added that refers to the same underlying table as any existing FROM
>> item."
> That's a step in the right direction, but I'd still like to catch
> SELECT a.a1, b.b1 FROM a;
> SELECT a.a1 FROM a WHERE a.a2 = b.b1;
> both of which are more or less obviously incorrect and easily fixed.
More or less obviously nonstandard, you mean. It's unlikely that
either of those examples are incorrect in the sense of not doing what
the user expected them to.
If we were working in a green field then I'd agree that we ought to be
100% SQL-spec-compliant on this point. But as is, we are talking about
rejecting an extension that Postgres has always had and a lot of people
find useful. I'm not eager to do that; I think it'd be putting pedantry
ahead of usefulness and backwards-compatibility. What I want to see is
the minimum restriction that will catch likely errors, not an "I'll
annoy you until you change your queries to meet the letter of the spec"
kind of message.
regards, tom lane
> "Zeugswetter Andreas" <andreas.zeugswetter@telecom.at> writes: > > I think we could get agreement to not allow implicit from entries > > if there is a from clause in the statement, but allow them if a from clause > > is missing altogether. The patch did not distinguish the two cases. > > Hmm, that's a thought. Taking it a little further, how about this: > > "Emit a notice [or error if you insist] when an implicit FROM item is > added that refers to the same underlying table as any existing FROM > item." > > 95% of the complaints I can remember seeing were from people who got > confused by the behavior of "FROM table alias" combined with a reference > like "table.column". Seems to me the above rule would catch this case > without being obtrusive in the useful cases. Comments? Yes, I even added a define called FROM_WARN. It was disabled, and never enabled. When can we enable it? -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > > > "Zeugswetter Andreas" <andreas.zeugswetter@telecom.at> writes: > > > I think we could get agreement to not allow implicit from entries > > > if there is a from clause in the statement, but allow them if a from clause > > > is missing altogether. The patch did not distinguish the two cases. > > > > Hmm, that's a thought. Taking it a little further, how about this: > > > > "Emit a notice [or error if you insist] when an implicit FROM item is > > added that refers to the same underlying table as any existing FROM > > item." > > > > 95% of the complaints I can remember seeing were from people who got > > confused by the behavior of "FROM table alias" combined with a reference > > like "table.column". Seems to me the above rule would catch this case > > without being obtrusive in the useful cases. Comments? > > Yes, I even added a define called FROM_WARN. It was disabled, and never > enabled. When can we enable it? How about a SET variable which allows PostgreSQL to reject any queries which are not entirely within the specificaton; kind of like -ansi -pedantic with gcc? Perhaps that's quite a bit of work, but it seems quite valuable for developing portable applications...Of course dependency on PostgreSQL extensions isn't a bad thing either ;-) Mike Mascari
Mike Mascari <mascarm@mascari.com> writes:
> How about a SET variable which allows PostgreSQL to reject any
> queries which are not entirely within the specificaton; kind of
> like -ansi -pedantic with gcc? Perhaps that's quite a bit of
> work, but it seems quite valuable for developing portable
> applications...Of course dependency on PostgreSQL extensions
> isn't a bad thing either ;-)
Hmm. Some aspects of that seem fairly straightforward, like rejecting
the table-not-in-FROM extension being discussed here. On the other
hand, it'd be painful to check for uses of datatypes or functions not
present in the standard.
In any case, I think the general reaction will be "good idea but a huge
amount of work compared to the reward". Unless someone steps forward
who's willing to do the work, I'd bet this won't happen...
regards, tom lane
> > I claim the only thing that buys is > > confusion for very little convenience at the other end. > > > > Stop the madness! :) > > I doubt that it's worth breaking a lot of existing applications for. > > At one time Bruce had made some patches to emit informative notice > messages about implicit FROM entries, but that got turned off again > for reasons that I forget... I think we could get agreement to not allow implicit from entries if there is a from clause in the statement, but allow them if a from clause is missing altogether. The patch did not distinguish the two cases. Andreas
On Fri, 26 May 2000, Tom Lane wrote: > "Zeugswetter Andreas" <andreas.zeugswetter@telecom.at> writes: > > I think we could get agreement to not allow implicit from entries > > if there is a from clause in the statement, but allow them if a from clause > > is missing altogether. The patch did not distinguish the two cases. > > Hmm, that's a thought. Taking it a little further, how about this: > > "Emit a notice [or error if you insist] when an implicit FROM item is > added that refers to the same underlying table as any existing FROM > item." > > 95% of the complaints I can remember seeing were from people who got > confused by the behavior of "FROM table alias" combined with a reference > like "table.column". Seems to me the above rule would catch this case > without being obtrusive in the useful cases. Comments? I guess I would be more strict on the reason, that people playing with implicit from entries usually know what they are doing, and thus know how to avoid a from clause if they want that behavior. I don't see a reason to have one table in the from clause but not another. This is too misleading for me. Andreas
> At one time Bruce had made some patches to emit informative notice
> messages about implicit FROM entries, but that got turned off again
> for reasons that I forget...
It was triggered with common cases from the "outer join" syntax. It took
a while to track down since it was introduced while I was working on the
syntax feature :(
If it *really* needs to be put back in, then we should do so with a flag
so we can disable the warning at compile time, run time, and/or in the
outer join parser area. But imho sprinkling the parser with warnings for
allowed syntax is heading the wrong direction. If it is legal, allow it.
If it is illegal, disallow it. If it is confusing for some, but works
fine for others, it shouldn't become "sort of legal" with a warning.
- Thomas