MoronSQL

jplindstrom on 2007-02-28T21:34:36

Ok, rant on.

Consider this setup:

create table first (id int);
insert into first values (1);

create table second (id int, first_id int);
insert into second values (10, 1);

I alias the tables for easy reference in the query.

select
    f.*,
    s.*
from
    first f,
    second s
where f.id = s.first_id
;

+------+------+----------+
| id   | id   | first_id |
+------+------+----------+
|    1 |   10 |        1 |
+------+------+----------+

But I want to refer to the table names in the result list, not the aliases (that part is autogenerated stuff), not rocket science you'd think:

select
    first.*,
    second.*
from
    first f,
    second s
where f.id = s.first_id
;

But, wait, what happens here?

ERROR 1051 (42S02): Unknown table 'first'

The aliasing removes the original table name?!

What th...

HOW %%&*&*"£! STUPID IS THAT!?!?!?!

This goes for MySQL 4.*, it may have changed in 5 (any takers?)

I don't know... The more I use MySQL, the more it seems like the biggest hack wrapped around a cheap shortcut on top of a bad design decision since... I don't even know when. Again and again, it refuses to behave like what you'd expect from an actual database.

Post-comment update:

The fact that I may be wrong doesn't change the fact I am annoyed :)

I can't really see why disambiguation shouldn't be completely orthogonal to aliasing. Really, what rationale would you use to combine and confuse those two concepts?

The fact that I encountered this while fiddling with MySQL just made it so much easier to start fuming.

---

Not moronic at all

Aristotle on 2007-03-01T01:18:21

SELECT
    second.*
FROM
    second p,
    second k
WHERE
    p.id = k.first_id

Which row in second is the SELECT clause referring to?

Re:Not moronic at all

jplindstrom on 2007-03-01T11:00:53

Yeah, when you have ambiguities, you need to disambiguate. So? That's not the case in my example.

And that's not what it complains about. It complains that it doesn't know of the table at all, not that it doesn't know which table I refer to.

Re:Not moronic at all

Aristotle on 2007-03-01T12:55:44

Hmm, I don’t have PostgreSQL handy here to check; but I know that SQLite at least behaves just like MySQL. I’d be surprised, actually, if any RDBMS let you do what you were after.

We can quibble about the wording of the error message; I’ll agree that it could be more plausible.

I also won’t disagree at all that MySQL is a cardhouse of hack upon hack.

But… I dunno, that behaviour there seems sensible to me.

Re:Not moronic at all

stu42j on 2007-03-01T23:43:54

Here's what Postgres says:

ERROR: invalid reference to FROM-clause entry for table "first"
HINT: Perhaps you meant to reference the table alias "f".


May be standard

VSarkiss on 2007-03-01T15:03:36

I know that neither SQL Server nor Oracle will support that syntax. Once you alias a table name in a query, you must use the alias within the scope of that query.

It may be part of the SQL standard. I'm not sure, and the document is a bear to grep thru.

I agree the error message could be clearer.

Re:May be standard

avik on 2007-04-06T15:38:43

I stumbled upon it too just the other day.
But it makes sense, doesn't it?

Imagine this
select F.*, B.*
from SERVER1.DB1.owner1.FOOFOOFOOFOOFOO_2005 F
join SERVER2.DB2.owner2.BARBARBARBARBAR_2005 B
on F.first_field = B.first_field
and F.second_field = B.second_field
and F.third_field = B.third_field
and F.fourth_field = B.fourth_field
and F.you_get_the_idea = 1
where f.something = 'something'
    and b.another_thing != 'something'
order by 1,2,3

If address for table changes for some reason (and it happens) all you need to worry about is "FROM" clause. However if we had a choice whether to use an alias or a real name of table that could be a disaster to debug, IMHO.