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

[Steven Klassen]

* Thomas Hallgren <th***@mailbloc ks.com> [2004-10-25 19:06:40 +0200]:

I don't see how that makes a difference really.

/me notes the timestamp on his post and vows never to post before 8am
again.

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Thomas Hallgren]

Stephan,

In general, when we add a backwards compatibility option, we give
a couple of versions before the default is changed.
Perhaps the 8.0 would be a perfect time since it's a change of the major
number.
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).
Still, if the query is incorrect, I want to know about it. I don't ever
want an incorrect behavior as a result of some behind the scenes magic.
For me, there's no exception to that rule and my guess is that very few
people would disagree if they think about it more in depth. This option
helps no one. It only adds to the confusion.
I think that many people do, even if they don't realize it.

If people write incorrect SQL because "this looks like the natural way
of doing it", don't you think it's fair if they find out about the error
ASAP? Catching errors early in the development process is generally
considered a good thing. When this option is enabled, errors might be
hidden (you get the notification that not everyone will pay attention
to, or even see). I consider that a very *bad* thing.

It's perhaps OK that the option exists so that old legacy system can
keep on running, but to have it enabled by default is not good at all.

Regards,
Thomas Hallgren
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Mike Mascari]

Thomas Hallgren wrote:

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.

This particular error may be less than obvious even during crude testing
because the number of tuples in the relation in question may be zero or
one, so the cross-product wouldn't produce anything unusual.

Mike Mascari

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

[Stephan Szabo]

On Mon, 25 Oct 2004, Thomas Hallgren wrote:

Stephan,

> In general, when we add a backwards compatibility option, we give
> a couple of versions before the default is changed.
>

Perhaps the 8.0 would be a perfect time since it's a change of the major
number.

Maybe, but I think it'll be a hard sell without a replacement for the
delete form that works when it's off.

> 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).
>

Still, if the query is incorrect, I want to know about it. I don't ever

But, is the query incorrect? It does what PostgreSQL says it will.
That's not what the spec says it'll do, but the same is true of most of
the extensions, and I don't think people generally consider queries using
those as incorrect.

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

http://archives.postgresql.org

[Martijn van Oosterhout]

On Tue, Oct 26, 2004 at 05:25:57PM +0200, Thomas Hallgren wrote:

If the WHERE clause that defines the criteria for deletion involves more
than one table, then you'd use a sub select and that has a FROM clause
of its own.
Sure, that's what you could do, but it makes the query rather more
complex than it needs to be. For example, consider this statement:

DELETE FROM x WHERE x.a = table.a and x.b > table.b and table.c = 4;

Look, "table" is not declared anywhere, but I can't think of a way to
rewrite this in strict SQL. Maybe:

DELETE FROM x WHERE x.id IN
(SELECT x.id FROM x, table where x.a = table.a and x.b > table.b and table.c = 4);

Seems like a lot of extra work for no gain. Hope id is never NULL.
Maybe EXISTS would work better?
I haven't seen any other extension that, when enabled, attempts to
"improve" badly written SQL in a way that potentially gives incorrect
query results. As I said in another post, this is like having a compiler
that instead of complaining about a misspelled variable, adds a new one.
transform_equal s_null comes to mind. It's a hack to make 'x = NULL'
work the way people coming from Oracle expect. It "fixes" it to be 'x
IS NULL'.

That is arguably something that could cause unexpected results.
So all your tests run fine. You ship to your customers. The customers
starts adding data to tables and finds some strange behavior. It turns
out that everything is caused by tables being added to the FROM clause.
You didn't see the problem in your test because there, the added table
had less than 2 tuples in it.
It has to be exactly one tuple. If there are zero tuples you get zero
output. Cross-joining with an empty table produces no output. You're
shipping a product where people expect to be able to add more rows to a
table, but you never test that?
As I said before, I don't object to the presence of this "option" so
that people that really knows _why_ they enable it can do so, but I
strongly object to having this option enabled by default. I suggest that:

1. Have this option disabled by default.
2. Print WARNING's rather than notifications when tables are added.
If you're not seeing NOTICEs now, what makes you think you'll see
WARNINGs? Every DB interface I've used so far displays the notices
where I can see them. This notice is one of the less useful, there
are other more useful warnings which are much more handy to see...
--
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

iD8DBQFBfnK5Y5T wig3Ge+YRAg+zAK DPKCFYbgT8lRqsE 9jlg+Uwu48iGQCf bL3l
p9CACUptj0Olemr PzOYnYko=
=wiqT
-----END PGP SIGNATURE-----

[Martijn van Oosterhout]

On Tue, Oct 26, 2004 at 06:21:23PM +0200, Thomas Hallgren wrote:

Do you consider this overly complex? Compare:

DELETE FROM x WHERE EXISTS (SELECT * FROM table WHERE x.a = table.a and
x.b > table.b and table.c = 4)

to:

DELETE FROM x, table WHERE x.a = table.a and x.b > table.b and table.c = 4

In the latter, what is it you are deleting? Is it x or table? I'm not at
all in favor of listing several tables in the FROM clause of a DELETE
statement (that includes implicitly adding them).
The problem is that in DELETE, there is no FROM clause in the sense
there is with the other commands, the FROM keyword is used for a
different purpose. The FROM clause the tables are automatically added
to does not have an equivalent in the original SQL statement.

I'm in favour of the status quo, exactly the current default behaviour.
That second example you give is confusing and should be disallowed. But
no-one has come up with anything better. Do you have a better
suggestion, other than forbidding the currently allowed syntax?

Every DB interface I've used so far displays the notices
where I can see them. This notice is one of the less useful, there
are other more useful warnings which are much more handy to see...

Right. Useful "warnings"! Seems you agree that this should be a warning,
not a notice.

Hmm, I consider a notice to be a warning anyway, something you should
always read. The default log level is notice anyway, so if you're
seeing warnings, you'll see the notices too...

Anyway, I think the reasoning so far is, the default stays as it is
until someone comes up with a non-confusing way of adding a real FROM
clause to DELETEs. Requiring people upgrading to add missing tables in
the FROM for SELECT and UPDATE is one thing. Asking them to rewrite
every DELETE query as a subselect is a bit too far. It would be nice
also because then you could then also use aliases.

Have a nice day,
--
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

iD8DBQFBfq9gY5T wig3Ge+YRAmrWAK CmehRYQOhHFGirs OIyeUgfuMajggCb BpBh
IhFROXg6iuqbt1m YSB5Py5c=
=j/CR
-----END PGP SIGNATURE-----

[Thomas Hallgren]

Martijn,

Do you have a better
suggestion, other than forbidding the currently allowed syntax?
Yes I do.

We agree that my second example should be disallowed since the semantics
of the FROM clause is different for a DELETE so the "add_missing_fr om"
is actually not adding to a FROM clause, it is guessing the scope for
the predicate. I assume the same is true for an UPDATE where there is no
FROM at all.

My suggestion is that we rename the add_missing_fro m to:

update_delete_a utoscope

and that this option has no effect on SELECT clauses. It would be more
or less harmless to have it enabled by default.

Perhaps the add_missing_fro m should remain but then only affect the
SELECT and as disabled by default.
Anyway, I think the reasoning so far is, the default stays as it is
until someone comes up with a non-confusing way of adding a real FROM
clause to DELETEs.

SQL already defines a stright forward way to do that. Consider the
following PostgreSQL syntax:

DELETE FROM first_table
WHERE first_table.id = second_table.xi d AND second_table.fo o > 4

in standard SQL this would be:

DELETE FROM first_table x
WHERE x.id IN (SELECT y.xid FROM second_table y WHERE y.foo > 4)

The number of characters is almost the same in both statements even for
a very simple WHERE clause thanks to aliasing. The benefits of aliasing
increases as the WHERE clause gets more complicated.

For composite keys or other non key based relationships, the EXISTS
clause can be used.

Why confuse people with yet another syntax?

Regards,
Thomas Hallgren

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

[Martijn van Oosterhout]

On Wed, Oct 27, 2004 at 12:15:10AM +0200, Thomas Hallgren wrote:

Martijn,

Do you have a better
suggestion, other than forbidding the currently allowed syntax?
Yes I do.

We agree that my second example should be disallowed since the semantics
of the FROM clause is different for a DELETE so the "add_missing_fr om"
is actually not adding to a FROM clause, it is guessing the scope for
the predicate. I assume the same is true for an UPDATE where there is no
FROM at all.

Not true, UPDATE in PostgreSQL does allow a from clause. Observe:

# \h update
Command: UPDATE
Description: update rows of a table
Syntax:
UPDATE [ ONLY ] table SET col = expression [, ...]
[ FROM fromlist ]
[ WHERE condition ]

Perfectly reasonable addition, but not strictly SQL standard. Also, the
scope is not guessed, it's totally unambiguous. I avoid the issue
entirly by either never using aliases, or always using aliases, hence
the issue doesn't come up, but that's me.

Anyway, I think there's a confusion in the phrase "from clause". From
the server's point of view, it's the list of tables the query is
working with and this applies to all kinds of queries, DELETE, SELECT
and UPDATE alike. Internally all those queries are processed the same,
it's just what happens to the selected rows that changes. SELECT and
UPDATE allow you to explicitly list tables, DELETE doesn't. The bit
after FROM in a DELETE query is *not* the from clause by this
definition.

But I guess it comes down to to how strictly you want to follow the SQL
standard.
My suggestion is that we rename the add_missing_fro m to:

update_delete_a utoscope

and that this option has no effect on SELECT clauses. It would be more
or less harmless to have it enabled by default.
As pointed out above, it's not needed to update. And add_missing_fro m
currently has no effect on delete, so your suggested option appears to
be merely the inverse of what is already there.
DELETE FROM first_table x
WHERE x.id IN (SELECT y.xid FROM second_table y WHERE y.foo > 4)

The number of characters is almost the same in both statements even for
a very simple WHERE clause thanks to aliasing. The benefits of aliasing
increases as the WHERE clause gets more complicated.
The SQL standard (what I can find on the web anyway) doesn't allow an
alias there, and neither does PostgreSQL. Incidently, MS SQL server
allows the following syntax:

DELETE FROM Table1 FROM Table1 INNER JOIN Table2 ON ....

The UPDATE syntax extension I mentioned above is also in MS SQL as far
as I can tell (I've never personally used it). Would adding support for
a from clause there make a difference to you?

Ref: http://www.mvps.org/access/queries/qry0022.htm
Why confuse people with yet another syntax?
Why confuse people by changing a perfectly usable syntax, that's been
present for years (since the beginning I beleive) and generates NOTICEs
already. The difference between NOTICEs and WARNINGs is that NOTICEs
are expected, a direct consequence of the query, whereas warnings are
unexpected, change each time you run the query. By that definition it
clearly should be a NOTICE.

Anyway, this isn't going anywhere. Neither of us is going to make any
changes to the server. And the core has decided to leave it as is for
the time being. Maybe after 8.0 is released it can be revisited.

Have a nice day,
--
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

iD8DBQFBf1vOY5T wig3Ge+YRApc1AK CXAZOLCv5KgFkAP hPpMHB9oSRT2ACd F9l2
/ivsywktmWqRPOdH z4fStzA=
=h5JH
-----END PGP SIGNATURE-----

[Sim Zacks]

I didn't see that join syntax in the documentation for delete, thanks
for pointing it out.

MS SQL Server syntax for a delete is a little less confusing, IMHO.

instead of DELETE FROM x WHERE x.a = table.a and x.b > table.b and table.c = 4;
they have DELETE x FROM x join table on x.a = table.a and x.b > table.b and table.c = 4

the table being deleted from is listed separately, but you can still
have full join syntax (including outer joins) to help with the
deletion.

This is similar to the current PostGreSQL update syntax, except that
the table being updated is not part of the from and therefore can only
be connected through an inner join, not an outer join.

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

_______________ _______________ _______________ _______________ _______________ _____

On Tue, Oct 26, 2004 at 06:21:23PM +0200, Thomas Hallgren wrote:

Do you consider this overly complex? Compare:

DELETE FROM x WHERE EXISTS (SELECT * FROM table WHERE x.a = table.a and
x.b > table.b and table.c = 4)

to:

DELETE FROM x, table WHERE x.a = table.a and x.b > table.b and table.c = 4

In the latter, what is it you are deleting? Is it x or table? I'm not at
all in favor of listing several tables in the FROM clause of a DELETE
statement (that includes implicitly adding them).
The problem is that in DELETE, there is no FROM clause in the sense
there is with the other commands, the FROM keyword is used for a
different purpose. The FROM clause the tables are automatically added
to does not have an equivalent in the original SQL statement.

I'm in favour of the status quo, exactly the current default behaviour.
That second example you give is confusing and should be disallowed. But
no-one has come up with anything better. Do you have a better
suggestion, other than forbidding the currently allowed syntax?

Every DB interface I've used so far displays the notices
where I can see them. This notice is one of the less useful, there
are other more useful warnings which are much more handy to see...

Right. Useful "warnings"! Seems you agree that this should be a warning,
not a notice.

Hmm, I consider a notice to be a warning anyway, something you should
always read. The default log level is notice anyway, so if you're
seeing warnings, you'll see the notices too...

Anyway, I think the reasoning so far is, the default stays as it is
until someone comes up with a non-confusing way of adding a real FROM
clause to DELETEs. Requiring people upgrading to add missing tables in
the FROM for SELECT and UPDATE is one thing. Asking them to rewrite
every DELETE query as a subselect is a bit too far. It would be nice
also because then you could then also use aliases.

Have a nice day,
--
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.

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

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

[Thomas Hallgren]

Martijn,
I realize that the change I'm proposing might be too complex to be added
in the upcoming 8.0 release. I do find this discussion interesting
though, so please bear with me while I try to tie up some loose ends.

UPDATE [ ONLY ] table SET col = expression [, ...]
[ FROM fromlist ]
[ WHERE condition ]

Perfectly reasonable addition, but not strictly SQL standard. Also, the
scope is not guessed, it's totally unambiguous.
Ok, bad choice of words. It's not guessed, and I agree, this is
perfectly reasonable.
Anyway, I think there's a confusion in the phrase "from clause".
There's no confusion. I fully understand the differences. That's why
think that the term 'add_missing_fr om' is misleading. From a strict
syntax point of view it implies expansion to the statement we both
agreed should be disallowed. The fact that it doesn't actually add a
missing from but rather expands the scope for the predicate is somewhat
confusing. Hence my suggestion that the variable is renamed.
But I guess it comes down to to how strictly you want to follow the SQL
standard.

I think it's OK to deviate from the standard and add features. My whole
argument in this thread is based on the fact that PostgreSQL adds tables
to the FROM clause of a SELECT which may produce incorrect results and
that this "magic" is performed by default.

My suggestion is that we rename the add_missing_fro m to:

update_delete _autoscope

and that this option has no effect on SELECT clauses. It would be more
or less harmless to have it enabled by default.

As pointed out above, it's not needed to update. And add_missing_fro m
currently has no effect on delete, so your suggested option appears to
be merely the inverse of what is already there.

What I was trying to say is that: a) since the 'add_missing_fr om'
affects the predicate scope for DELETE's, UPDATE's, and SELECT's, and
since those statements have different ways of expressing this scope, the
current choice of name is a bit confusing and b) it would be nice if the
variable affected DELETE and UPDATE scopes only. Now you point out that
an UPDATE can have a FROM clause, so let me revise my suggestion and
instead say:

1. Let's add a variable named "autoscope_for_ delete" that is enabled by
default and only affects the scope of a DELETE predicate. We do this to
maintain backward compatibility.
2. Let's change so that "add_missing_fr om" is disabled by default and
doesn't affect the DELETE statement at all.
3. The "autoscope_for_ delete" will use generate notices and
"add_missing_fr om" will generate warnings.

DELETE FROM first_table x
WHERE x.id IN (SELECT y.xid FROM second_table y WHERE y.foo > 4)

The SQL standard (what I can find on the web anyway) doesn't allow an
alias there, and neither does PostgreSQL.

The SQL 2003 draft I have states:

<delete statement: searched> ::=
DELETE FROM <target table> [ [ AS ] <correlation name> ]
[ WHERE <search condition> ]

whereas SQL 3 is more elaborated:

<table reference> ::=
<table name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <joined table>

<delete statement: searched> ::=
DELETE FROM <table reference>
[ WHERE <search condition> ]

Perhaps PostgreSQL should adopt this?
Incidently, MS SQL server allows the following syntax:

DELETE FROM Table1 FROM Table1 INNER JOIN Table2 ON ....

The UPDATE syntax extension I mentioned above is also in MS SQL as far
as I can tell (I've never personally used it). Would adding support for
a from clause there make a difference to you?

I'm happy as long as the 'add_missing_fr om' is disabled or changed so
that it doesn't affect SELECT. And yes, this extension looks good.
Perhaps consider changing the second FROM to USING (mimicking MySQL
instead of MS SQL server). I think it would lessen the risk of
introducing ambiguities in the parser (and it looks better than repeated
FROM's).

Regards,
Thomas Hallgren

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

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