Bug or stupidity

[Philip Hofstetter]

Hello,

I think, I found a bug, but maybe it's just my stupidity. Granted: What
I did was an error on my part, but I still think, PostgreSQL should not
do what it does.

I've already created a simple testcase:
popscan_light=> create table a (id serial, name varchar(10), primary
key(id)) without oids;
NOTICE: CREATE TABLE will create implicit sequence "a_id_seq" for
"serial" column "a.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey"
for table "a"
CREATE TABLE
popscan_light=> create table b (id int4 references a (id) on delete
cascade, name2 varchar(15), primary key (id)) without oids;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey"
for table "b"
CREATE TABLE
popscan_light=> insert into a (name) values ('gnegg');
INSERT 0 1
popscan_light=> insert into a (name) values ('blepp');
INSERT 0 1
popscan_light=> insert into b values (1, 'gnegglink');
INSERT 0 1
popscan_light=> insert into b values (2, 'blepplink');
INSERT 0 1
popscan_light=> select a.name, b.name2 from a left join b using (id)
order by b.name2;
name | name2
-------+-----------
blepp | blepplink
gnegg | gnegglink
(2 rows)

popscan_light=> select aliasa.name, aliasb.name2 from a aliasa left join
b aliasb using (id) order by b.name2;
NOTICE: adding missing FROM-clause entry for table "b"
name | name2
-------+-----------
gnegg | gnegglink
blepp | blepplink
gnegg | gnegglink
blepp | blepplink
(4 rows)

popscan_light=> select aliasa.name, aliasb.name2 from a aliasa left join
b aliasb using (id) order by aliasb.name2;
name | name2
-------+-----------
blepp | blepplink
gnegg | gnegglink
(2 rows)

popscan_light=> \q
fangorn ~ $ psql --version
psql (PostgreSQL) 7.4.3
contains support for command-line editing

In the second "SELECT"-Query I've ordered the result set by the
name-column of the second table, but I have not used the alias "aliasb"
I created, but I used the full table name. I know this is not really
correct, but I'd still like to know why Postgres throws 4 results at me.

If I use the correct column in the order by clause, I get the correctly
joined result.

Looking at my second query, I think the false "order by" seems to pull
in another copy of table b joining it without a proper condition. I
don't think, this is the right thing to do.

Or ist it?

Anyone?

Philip

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bruno Wolff III]

On Wed, Oct 27, 2004 at 22:10:05 +0200,

2. Let's change so that "add_missing_fr om" is disabled by default and
doesn't affect the DELETE statement at all.

That is supposed to happen. My memory was that 8.0 was the release that
the default was going to change, but if not then it should be 8.1.

I don't see any great reason to change the name at this point. That is
going to just cause more problems.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Bruno Wolff III <br***@wolff.to > writes:

On Wed, Oct 27, 2004 at 22:10:05 +0200,

2. Let's change so that "add_missing_fr om" is disabled by default and
doesn't affect the DELETE statement at all.

That is supposed to happen. My memory was that 8.0 was the release that
the default was going to change, but if not then it should be 8.1.

add_missing_fro m was only added in 7.4; the default behavior goes all
the way back because we inherited it from PostQUEL. It's probably
premature to flip the factory default after only one release cycle,
especially given the DELETE deficiency. A reasonable position is to
flip the default one release cycle after we fix the DELETE syntax.

It is interesting that SQL2003 allows an alias on the UPDATE or DELETE
target table; that was definitely not there in SQL99 or earlier. We'll
want to add that, for sure, but it is just a notational convenience.

There are several threads in the archives about how to fix the DELETE
syntax, but I don't think we ever really got consensus on what keyword
to use to introduce the auxiliary FROM clause.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html