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

[Martijn van Oosterhout]

On Sat, Oct 23, 2004 at 02:17:16PM +0000, Philip Hofstetter wrote:

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.
.... snip ...
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)
See that NOTIVCE? It's telling you that it's converted your query to:

select aliasa.name, aliasb.name2 from b, a aliasa left join
b aliasb using (id) order by b.name2;

Since you now have an unconstrained join on the B table, you get twice
as many rows.

It basically comes down to, if you make an alias, you have to use the
alias. You can't use the original table name *and* the alias. The
reference to the original table is becomes another copy of the same
table.

As for what's SQL standard, I think by a strict definition your query
shouldn't be allowed at all, referencing an undefined table.

Hope this helps,
--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBemr/Y5Twig3Ge+YRAlI YAKC6DjO0oikssf d+WdC0LIQTlngvx QCeMntm
jtYktqJ/FuAfqLShKD7Sga0 =
=xKg9
-----END PGP SIGNATURE-----

[Philip Hofstetter]

Hi,

Martijn van Oosterhout wrote:

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)

See that NOTIVCE? It's telling you that it's converted your query to:

actually, I've overseen it. But then, my assumption in my mail was
correct anyway.
select aliasa.name, aliasb.name2 from b, a aliasa left join
b aliasb using (id) order by b.name2; Since you now have an unconstrained join on the B table, you get twice
as many rows.
This is what I thought.
As for what's SQL standard, I think by a strict definition your query
shouldn't be allowed at all, referencing an undefined table.

This is exactly what I think too. I mean: I know I made an error in my
query. It would just have been easier to find if PostgreSQL actually had
told me so (I'm not getting those NOTICEs from PHP...).

If it's wrong, it should be disallowed, not made worse by assuming a
completely wrong thing.

Thanks for your fast response anyway.

Philip

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Martijn van Oosterhout]

On Sat, Oct 23, 2004 at 02:35:20PM +0000, Philip Hofstetter wrote:

As for what's SQL standard, I think by a strict definition your query
shouldn't be allowed at all, referencing an undefined table.
This is exactly what I think too. I mean: I know I made an error in my
query. It would just have been easier to find if PostgreSQL actually had
told me so (I'm not getting those NOTICEs from PHP...).

If it's wrong, it should be disallowed, not made worse by assuming a
completely wrong thing.

Maybe, ofcourse, this exact same construct is used heavily in DELETEs.
Look at the syntax of the delete command:

DELETE FROM [ ONLY ] table [ WHERE condition ]

You can't declare extra tables or define aliases. Every other table
used in the query is by strict definitions "undefined" . Should they all
be declared illegal too?

Perhaps you could argue that using undeclared tables is allowed for
DELETEs but not for SELECTs. But why make a distiction if you don't
need to.

Hope this helps,

--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBem2yY5T wig3Ge+YRAqrsAJ 0Vt8H67qHEBFreX ltZCMxGUB0W2ACg yLCj
i6U+ec/FqTlIqGPoFPaQ32 E=
=4GA1
-----END PGP SIGNATURE-----

[Stephan Szabo]

On Sat, 23 Oct 2004, Philip Hofstetter wrote:

As for what's SQL standard, I think by a strict definition your query
shouldn't be allowed at all, referencing an undefined table.

This is exactly what I think too. I mean: I know I made an error in my
query. It would just have been easier to find if PostgreSQL actually had
told me so (I'm not getting those NOTICEs from PHP...).

If it's wrong, it should be disallowed, not made worse by assuming a
completely wrong thing.

It's enabled in large part for backwards compatibility. There's a runtime
option that controls the behavior (add_missing_fr om).
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Karim Nassar]

On Sat, 2004-10-23 at 07:35, Philip Hofstetter wrote:

<snip> It would just have been easier to find if PostgreSQL actually had
told me so (I'm not getting those NOTICEs from PHP...).

As far as I can tell, Apache or PHP snarfs up all the messages that
postgres generates before they can get to the postgres log.

In order to see them, these are my entries from php.ini:

1. error_reporting = E_ALL
2. display_errors = Off
3. log_errors = On
4. log_errors_max_ len = 2048

In english:

1. Every freakin message you see
2. don't put em on the web page
3. just my log file
4. and show me all my long queries.

On my system, everything ends up in apache's error_log.

HTH,
\<.
---------------------------(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

[Thomas Hallgren]

Stephan Szabo wrote:

It's enabled in large part for backwards compatibility. There's a runtime option that controls the behavior (add_missing_fr om).

IMHO, it would be a more natural choice to have the add_missing_fro m
disabled by default. Why would anyone *ever* want faulty SQL being
magically "patched up" by the dbms?

Ok, so some older installations might break when this is changed but the
option is still there. Let applications that depend on this somewhat
magical behavior enable it rather than have everyone else potentially
run into the same problem as Philip.

Regards,

Thomas Hallgren
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Steven Klassen]

* Thomas Hallgren <th***@mailbloc ks.com> [2004-10-25 15:52:20 +0200]:

IMHO, it would be a more natural choice to have the add_missing_fro m
disabled by default. Why would anyone *ever* want faulty SQL being
magically "patched up" by the dbms?

That assumes that developers will implement queries in their code
without testing them. Unfortunately, that's probably not too far from
reality. I've thought of it as a nice "debugging" feature while I'm
trying to hammer out a complicated query for the first time.

--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, [phone]

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

On Mon, 25 Oct 2004, Thomas Hallgren wrote:

Stephan Szabo wrote:

> It's enabled in large part for backwards compatibility. There's a runtime

> option that controls the behavior (add_missing_fr om).
>

IMHO, it would be a more natural choice to have the add_missing_fro m
disabled by default. Why would anyone *ever* want faulty SQL being

In general, when we add a backwards compatibility option, we give a couple
of versions before the default is changed. In addition, until we have a
form of delete which allows a "from" list, there are some queries which
are really more naturally written in a form similar to add_missing_fro m
(although "from" lists would be better).
magically "patched up" by the dbms?

I think that many people do, even if they don't realize it. Pretty much
almost any extension to the spec is faulty SQL, from != and use of column
aliases in some places they technically aren't allowed to DISTINCT ON and
UPDATE FROM.

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

[Thomas Hallgren]

Steven,

That assumes that developers will implement queries in their code
without testing them. Unfortunately, that's probably not too far from
reality. I've thought of it as a nice "debugging" feature while I'm
trying to hammer out a complicated query for the first time.

I don't see how that makes a difference really. As a developer, I'd
rather prefer if I get an explanatory error result rather than a notice
(often invisible) and an incorrect result when testing. If I don't test
at all (God forbid) I want the same thing to happen the first time the
code is deployed. Anything else is really scary. I don't see how it can
be the dbms responsibility to correct erroneous SQL ever. It's
comparable to having a compiler that magically adds undeclared (or
misspelled) variables in your code. Shrug...

Is the variable settable in a session? If so, that would be good for the
purpose you mention.

Regards,
Thomas Hallgren
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org