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

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

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 <kleptog (AT) svana (DOT) org> http://svana.org/kleptog/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBemr/Y5Twig3Ge+YRAlIYAKC6DjO0oikssfd+WdC0LIQTlngvxQCeMn tm
jtYktqJ/FuAfqLShKD7Sga0=
=xKg9
-----END PGP SIGNATURE-----

Hi,

Martijn van Oosterhout wrote:
actually, I've overseen it. But then, my assumption in my mail was
correct anyway.

This is what I thought.

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 majordomo (AT) postgresql (DOT) org

On Sat, Oct 23, 2004 at 02:35:20PM +0000, Philip Hofstetter wrote:
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 <kleptog (AT) svana (DOT) org> http://svana.org/kleptog/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBem2yY5Twig3Ge+YRAqrsAJ0Vt8H67qHEBFreXltZCM xGUB0W2ACgyLCj
i6U+ec/FqTlIqGPoFPaQ32E=
=4GA1
-----END PGP SIGNATURE-----

On Sat, 23 Oct 2004, Philip Hofstetter wrote:

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Sat, 2004-10-23 at 07:35, Philip Hofstetter wrote:
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

Stephan Szabo wrote:

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 majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

* Thomas Hallgren <thhal (AT) mailblocks (DOT) com> [2004-10-25 15:52:20 +0200]:

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, (503) 667-4564

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

On Mon, 25 Oct 2004, Thomas Hallgren wrote:

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_from
(although "from" lists would be better).

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

Steven,


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 majordomo (AT) postgresql (DOT) org