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_equals_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 <kleptog@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.
