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
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
Comment