pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedaliases break my query
15+ messages / 9 participants
[nested] [flat]
* aliases break my query
@ 2000-05-26 03:11 Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Joseph Shraibman @ 2000-05-26 03:11 UTC (permalink / raw)
To: pgsql-sql
These two queries are exactly alike. The first one uses aliases except
for the order by. The second uses aliases also for the order by. The
third uses whole names. The third has the behavior I want.
Someone please tell me what I am doing wrong. I don't want to have to
use whole names for my query.
The data for the tables are at the end.
playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
ta.a) from tablea ta, tableb tb order by tablea.a;
a|b|c|?column?
-+-+-+--------
1|2| | 0
2|3|4| 1
3|4|5| 0
4|5|4| 0
1|2| | 0
2|3|4| 1
3|4|5| 0
4|5|4| 0
1|2| | 0
2|3|4| 0
3|4|5| 1
4|5|4| 0
1|2| | 0
2|3|4| 0
3|4|5| 0
4|5|4| 1
1|2| | 0
2|3|4| 0
3|4|5| 0
4|5|4| 0
1|2| | 0
2|3|4| 1
3|4|5| 0
4|5|4| 0
1|2| | 0
2|3|4| 1
3|4|5| 0
4|5|4| 0
1|2| | 0
2|3|4| 0
3|4|5| 1
4|5|4| 0
1|2| | 0
2|3|4| 0
3|4|5| 0
4|5|4| 1
1|2| | 0
2|3|4| 0
3|4|5| 0
4|5|4| 0
1|2| | 0
2|3|4| 1
3|4|5| 0
4|5|4| 0
1|2| | 0
2|3|4| 1
3|4|5| 0
4|5|4| 0
1|2| | 0
2|3|4| 0
3|4|5| 1
4|5|4| 0
1|2| | 0
2|3|4| 0
3|4|5| 0
4|5|4| 1
1|2| | 0
2|3|4| 0
3|4|5| 0
4|5|4| 0
1|2| | 0
2|3|4| 1
3|4|5| 0
4|5|4| 0
1|2| | 0
2|3|4| 1
3|4|5| 0
4|5|4| 0
1|2| | 0
2|3|4| 0
3|4|5| 1
4|5|4| 0
1|2| | 0
2|3|4| 0
3|4|5| 0
4|5|4| 1
1|2| | 0
2|3|4| 0
3|4|5| 0
4|5|4| 0
(80 rows)
playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
ta.a) from tablea ta, tableb tb order by ta.a;
a|b|c|?column?
-+-+-+--------
1|2| | 0
1|2| | 0
1|2| | 0
1|2| | 0
1|2| | 0
2|3|4| 1
2|3|4| 1
2|3|4| 0
2|3|4| 0
2|3|4| 0
3|4|5| 0
3|4|5| 0
3|4|5| 1
3|4|5| 0
3|4|5| 0
4|5|4| 0
4|5|4| 0
4|5|4| 0
4|5|4| 1
4|5|4| 0
(20 rows)
playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz)
where tableb.yy = tablea.a) order by tablea.a;
a|b|c|?column?
-+-+-+--------
1|2| | 0
2|3|4| 2
3|4|5| 1
4|5|4| 1
(4 rows)
playpen=>
playpen=> select * from tablea;
a|b|c
-+-+-
1|2|
2|3|4
3|4|5
4|5|4
(4 rows)
playpen=> select * from tableb;
yy|zz
--+--
2| 4
2| 5
3| 9
4|14
5|15
(5 rows)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
@ 2000-05-26 04:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 13:02 ` Re: aliases break my query Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-05-26 18:13 ` Re: aliases break my query Joseph Shraibman <jks@selectacast.net>
0 siblings, 2 replies; 15+ messages in thread
From: Tom Lane @ 2000-05-26 04:35 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql
Joseph Shraibman <jks@selectacast.net> writes:
> These two queries are exactly alike. The first one uses aliases except
> for the order by. The second uses aliases also for the order by. The
> third uses whole names. The third has the behavior I want.
I think you are confusing yourself by leaving out FROM clauses.
In particular, with no FROM for the inner SELECT it's not real clear
what should happen there. I can tell you what *is* happening, but
who's to say if it's right or wrong?
> playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> ta.a) from tablea ta, tableb tb order by tablea.a;
[ produces 80 rows ]
> playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> ta.a) from tablea ta, tableb tb order by ta.a;
[ produces 20 rows ]
The difference between these two is that by explicitly specifying
"tablea" in the order-by clause, you've created a three-way join,
as if you had written "from tablea ta, tableb tb, tablea tablea".
Once you write an alias in a from-clause entry, you must refer to
that from-clause entry by its alias, not by its true table name.
Meanwhile, what of the inner select? It has no FROM clause *and*
no valid table names. The only way to interpret the names in it
is as references to the outer select. So, on any given iteration
of the outer select, the inner select collapses to constants.
It looks like "SELECT count(constant1) WHERE constant2 = constant3"
and so you get either 0 or 1 depending on whether tb.yy and ta.a
from the outer scan are different or equal.
> playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz)
> where tableb.yy = tablea.a) order by tablea.a;
[ produces 4 rows ]
Here the outer select is not a join at all --- it mentions only tablea,
so you are going to get one output for each tablea row. The inner
select looks like "select count (zz) FROM tableb WHERE yy = <constant>",
so you get an actual scan of tableb for each iteration of the outer
scan.
It's not very clear from these examples what you actually wanted to have
happen, but I suggest that you will have better luck if you specify
explicit FROM lists in both the inner and outer selects, and be careful
that each variable you use clearly refers to exactly one of the
FROM-list entries.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-05-26 13:02 ` Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-05-26 16:26 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 17:47 ` Re: aliases break my query Joseph Shraibman <jks@selectacast.net>
1 sibling, 2 replies; 15+ messages in thread
From: Peter Eisentraut @ 2000-05-26 13:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joseph Shraibman <jks@selectacast.net>; pgsql-sql; pgsql-hackers@postgresql.org
> > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> > ta.a) from tablea ta, tableb tb order by tablea.a;
> [ produces 80 rows ]
> > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> > ta.a) from tablea ta, tableb tb order by ta.a;
> [ produces 20 rows ]
> > playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz)
> > where tableb.yy = tablea.a) order by tablea.a;
> [ produces 4 rows ]
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.) I claim the only thing that buys is
confusion for very little convenience at the other end.
Stop the madness! :)
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 13:02 ` Re: aliases break my query Peter Eisentraut <e99re41@DoCS.UU.SE>
@ 2000-05-26 16:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 19:47 ` Re: Re: [SQL] aliases break my query Zeugswetter Andreas <andreas.zeugswetter@telecom.at>
2000-05-31 02:04 ` Re: [HACKERS] Re: aliases break my query Thomas Lockhart <lockhart@alumni.caltech.edu>
1 sibling, 2 replies; 15+ messages in thread
From: Tom Lane @ 2000-05-26 16:26 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Joseph Shraibman <jks@selectacast.net>; pgsql-sql; pgsql-hackers@postgreSQL.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Re: [SQL] aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 13:02 ` Re: aliases break my query Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-05-26 16:26 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-05-26 19:47 ` Zeugswetter Andreas <andreas.zeugswetter@telecom.at>
2000-05-26 21:34 ` Re: Re: [SQL] aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 15+ messages in thread
From: Zeugswetter Andreas @ 2000-05-26 19:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org
> > 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Re: [SQL] aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 13:02 ` Re: aliases break my query Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-05-26 16:26 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 19:47 ` Re: Re: [SQL] aliases break my query Zeugswetter Andreas <andreas.zeugswetter@telecom.at>
@ 2000-05-26 21:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 22:30 ` Re: Re: [SQL] aliases break my query Peter Eisentraut <peter_e@gmx.net>
2000-05-26 23:43 ` Re: Re: [SQL] aliases break my query Bruce Momjian <pgman@candle.pha.pa.us>
2000-05-28 07:34 ` Re: Re: [SQL] aliases break my query Andreas Zeugswetter <andreas.zeugswetter@telecom.at>
0 siblings, 3 replies; 15+ messages in thread
From: Tom Lane @ 2000-05-26 21:34 UTC (permalink / raw)
To: Zeugswetter Andreas <andreas.zeugswetter@telecom.at>; +Cc: pgsql-hackers@postgresql.org
"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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Re: [SQL] aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 13:02 ` Re: aliases break my query Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-05-26 16:26 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 19:47 ` Re: Re: [SQL] aliases break my query Zeugswetter Andreas <andreas.zeugswetter@telecom.at>
2000-05-26 21:34 ` Re: Re: [SQL] aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-05-26 22:30 ` Peter Eisentraut <peter_e@gmx.net>
2000-05-26 22:42 ` Re: Re: [SQL] aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 15+ messages in thread
From: Peter Eisentraut @ 2000-05-26 22:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Zeugswetter Andreas <andreas.zeugswetter@telecom.at>; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Re: [SQL] aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 13:02 ` Re: aliases break my query Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-05-26 16:26 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 19:47 ` Re: Re: [SQL] aliases break my query Zeugswetter Andreas <andreas.zeugswetter@telecom.at>
2000-05-26 21:34 ` Re: Re: [SQL] aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 22:30 ` Re: Re: [SQL] aliases break my query Peter Eisentraut <peter_e@gmx.net>
@ 2000-05-26 22:42 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2000-05-26 22:42 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Zeugswetter Andreas <andreas.zeugswetter@telecom.at>; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Re: [SQL] aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 13:02 ` Re: aliases break my query Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-05-26 16:26 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 19:47 ` Re: Re: [SQL] aliases break my query Zeugswetter Andreas <andreas.zeugswetter@telecom.at>
2000-05-26 21:34 ` Re: Re: [SQL] aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-05-26 23:43 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-05-27 01:45 ` Re: Re: [SQL] aliases break my query Mike Mascari <mascarm@mascari.com>
2 siblings, 1 reply; 15+ messages in thread
From: Bruce Momjian @ 2000-05-26 23:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Zeugswetter Andreas <andreas.zeugswetter@telecom.at>; pgsql-hackers@postgresql.org
> "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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Re: [SQL] aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 13:02 ` Re: aliases break my query Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-05-26 16:26 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 19:47 ` Re: Re: [SQL] aliases break my query Zeugswetter Andreas <andreas.zeugswetter@telecom.at>
2000-05-26 21:34 ` Re: Re: [SQL] aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 23:43 ` Re: Re: [SQL] aliases break my query Bruce Momjian <pgman@candle.pha.pa.us>
@ 2000-05-27 01:45 ` Mike Mascari <mascarm@mascari.com>
2000-05-27 04:06 ` Re: Re: [SQL] aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Mike Mascari @ 2000-05-27 01:45 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Re: [SQL] aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 13:02 ` Re: aliases break my query Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-05-26 16:26 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 19:47 ` Re: Re: [SQL] aliases break my query Zeugswetter Andreas <andreas.zeugswetter@telecom.at>
2000-05-26 21:34 ` Re: Re: [SQL] aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 23:43 ` Re: Re: [SQL] aliases break my query Bruce Momjian <pgman@candle.pha.pa.us>
2000-05-27 01:45 ` Re: Re: [SQL] aliases break my query Mike Mascari <mascarm@mascari.com>
@ 2000-05-27 04:06 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2000-05-27 04:06 UTC (permalink / raw)
To: Mike Mascari <mascarm@mascari.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Re: [SQL] aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 13:02 ` Re: aliases break my query Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-05-26 16:26 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 19:47 ` Re: Re: [SQL] aliases break my query Zeugswetter Andreas <andreas.zeugswetter@telecom.at>
2000-05-26 21:34 ` Re: Re: [SQL] aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-05-28 07:34 ` Andreas Zeugswetter <andreas.zeugswetter@telecom.at>
2 siblings, 0 replies; 15+ messages in thread
From: Andreas Zeugswetter @ 2000-05-28 07:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [HACKERS] Re: aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 13:02 ` Re: aliases break my query Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-05-26 16:26 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-05-31 02:04 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1 sibling, 0 replies; 15+ messages in thread
From: Thomas Lockhart @ 2000-05-31 02:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Joseph Shraibman <jks@selectacast.net>; pgsql-sql; pgsql-hackers@postgreSQL.org
> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 13:02 ` Re: aliases break my query Peter Eisentraut <e99re41@DoCS.UU.SE>
@ 2000-05-26 17:47 ` Joseph Shraibman <jks@selectacast.net>
1 sibling, 0 replies; 15+ messages in thread
From: Joseph Shraibman @ 2000-05-26 17:47 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-hackers@postgresql.org
Peter Eisentraut wrote:
>
> > > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> > > ta.a) from tablea ta, tableb tb order by tablea.a;
> > [ produces 80 rows ]
>
> > > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> > > ta.a) from tablea ta, tableb tb order by ta.a;
> > [ produces 20 rows ]
>
> > > playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz)
> > > where tableb.yy = tablea.a) order by tablea.a;
> > [ produces 4 rows ]
>
> Once again, I think that we *really* need to discuss whether implicit
> range table entries in SELECT are a good idea.
What is an "implicit range table entry"?
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".
Hey, I *did* do that in the second query, and that still produced extra
results. I tried putting the aliases in the inner select too but that
didn't help. In fact the inner select always is 4 in that case. Unless I
only alias tableb in the inner query, and let it get the definition of
tablea from the outer query.
(I'm sure Tom has
> this reply automated by now.) I claim the only thing that buys is
> confusion for very little convenience at the other end.
>
> Stop the madness! :)
>
> --
> Peter Eisentraut Sernanders väg 10:115
> peter_e@gmx.net 75262 Uppsala
> http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: aliases break my query
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Re: aliases break my query Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-05-26 18:13 ` Joseph Shraibman <jks@selectacast.net>
1 sibling, 0 replies; 15+ messages in thread
From: Joseph Shraibman @ 2000-05-26 18:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane wrote:
>
> Joseph Shraibman <jks@selectacast.net> writes:
> > These two queries are exactly alike. The first one uses aliases except
> > for the order by. The second uses aliases also for the order by. The
> > third uses whole names. The third has the behavior I want.
>
> I think you are confusing yourself by leaving out FROM clauses.
> In particular, with no FROM for the inner SELECT it's not real clear
> what should happen there. I can tell you what *is* happening, but
> who's to say if it's right or wrong?
>
Well I assumed that the aliases would be inerited from the outer query.
> > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> > ta.a) from tablea ta, tableb tb order by tablea.a;
> [ produces 80 rows ]
>
> > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> > ta.a) from tablea ta, tableb tb order by ta.a;
> [ produces 20 rows ]
>
> The difference between these two is that by explicitly specifying
> "tablea" in the order-by clause, you've created a three-way join,
> as if you had written "from tablea ta, tableb tb, tablea tablea".
> Once you write an alias in a from-clause entry, you must refer to
> that from-clause entry by its alias, not by its true table name.
I guess I made the mistake of assuming that SQL is logical. I don't know
what I was thinking. ;)
>
> Meanwhile, what of the inner select? It has no FROM clause *and*
> no valid table names. The only way to interpret the names in it
> is as references to the outer select. So, on any given iteration
> of the outer select, the inner select collapses to constants.
> It looks like "SELECT count(constant1) WHERE constant2 = constant3"
> and so you get either 0 or 1 depending on whether tb.yy and ta.a
> from the outer scan are different or equal.
OK that sorta makes sense to be. What I want is the behavior I got with
the third query (below). I want the values in table a, and then a count
of how many entries in tableb have the yy field of tableb that matches
that entry in tablea's a field.
playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) from tableb tb
where tb.yy = ta.a) from tablea ta, tableb tb group by ta.a, ta.b, ta.c
order by ta.a;
a|b|c|?column?
-+-+-+--------
1|2| | 0
2|3|4| 2
3|4|5| 1
4|5|4| 1
(4 rows)
... which is what I want. Thanks.
>
> > playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz)
> > where tableb.yy = tablea.a) order by tablea.a;
> [ produces 4 rows ]
>
> Here the outer select is not a join at all --- it mentions only tablea,
> so you are going to get one output for each tablea row. The inner
> select looks like "select count (zz) FROM tableb WHERE yy = <constant>",
> so you get an actual scan of tableb for each iteration of the outer
> scan.
>
> It's not very clear from these examples what you actually wanted to have
> happen, but I suggest that you will have better luck if you specify
> explicit FROM lists in both the inner and outer selects, and be careful
> that each variable you use clearly refers to exactly one of the
> FROM-list entries.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2000-05-31 02:04 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-26 03:11 aliases break my query Joseph Shraibman <jks@selectacast.net>
2000-05-26 04:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 13:02 ` Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-05-26 16:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 19:47 ` Zeugswetter Andreas <andreas.zeugswetter@telecom.at>
2000-05-26 21:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 22:30 ` Peter Eisentraut <peter_e@gmx.net>
2000-05-26 22:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-26 23:43 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-05-27 01:45 ` Mike Mascari <mascarm@mascari.com>
2000-05-27 04:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-28 07:34 ` Andreas Zeugswetter <andreas.zeugswetter@telecom.at>
2000-05-31 02:04 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2000-05-26 17:47 ` Joseph Shraibman <jks@selectacast.net>
2000-05-26 18:13 ` Joseph Shraibman <jks@selectacast.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox