312349 - Oracle SQL doesn't allow "AS" keyword before table aliases in FROM clause

Status: RESOLVED WONTFIX

(Bugzilla :: Database, enhancement)

Description

[Lance Larsh]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3) Gecko/20041119
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3) Gecko/20041119

Bugzilla uses frequently uses queries like:

   SELECT a.text, b.text
     FROM table1 AS a
     JOIN table2 AS b
       ON a.pk = b.fk;

However, Oracle SQL doesn't allow the "AS" keyword in this usage, and it appears
to be optional for MySQL and PostgreSQL.  The following syntax should work on
all three databases:

   SELECT a.text, b.text
     FROM table1 a
     JOIN table2 b
       ON a.pk = b.fk;

Note that the AS keyword is allowed for column aliases, as in:

   SELECT a.text AS name1, b.text AS name2
     FROM table1 a JOIN table2 b ON a.pk = b.fk;

Reproducible: Always

Steps to Reproduce:
1. CREATE TABLE table1 ( pk INTEGER, text VARCHAR(32) );
2. CREATE TABLE table2 ( fk INTEGER, text VARCHAR(32) );

3. SELECT a.text, b.text FROM table1 AS a JOIN table2 AS b ON a.pk = b.fk;
   (Succeeds on MySQL; returns error ORA-00933 on Oracle)

4. SELECT a.text, b.text FROM table1 a JOIN table2 b ON a.pk = b.fk;
   (Succeeds on both MySQL and Oracle).

Comment 1

[Max Kanat-Alexander]

Yes, OK, this is fine. ANSI SQL-99 says that AS is optional:

http://savage.net.au/SQL/sql-99.bnf.html#xref-table%20primary

Comment 2

[Max Kanat-Alexander]

Oh, that link should have actually been:

http://savage.net.au/SQL/sql-99.bnf.html#table%20primary

Comment 3

[Lance Larsh]

Attachment: Patch to remove "AS" from FROM clauses

I did a pretty thorough job of searching, so this patch should catch all the
places that existed as of yesterday.

Comment 4

[Max Kanat-Alexander]

Comment on attachment 199595 [details] [diff] [review]
Patch to remove "AS" from FROM clauses

Ah, I remember the problem with this, now.

There's a regex in Search.pm that requires that "AS" exists. It's around where
we build the orderby and groupby statements. You'll have to fix that regex,
somehow. (I didn't see a fix for it here.)

Comment 5

[Max Kanat-Alexander]

Comment on attachment 199595 [details] [diff] [review]
Patch to remove "AS" from FROM clauses

Oh wait, my AS problem is only for the SELECT statement. Oracle's OK with that,
right? So there shouldn't be a problem with the tables.

OK, so I'll have to still take a look at this.

Comment 6

[Lance Larsh]

(In reply to comment #5)
> (From update of attachment 199595 [details] [diff] [review] [edit])
> Oh wait, my AS problem is only for the SELECT statement. Oracle's OK with that,
> right? So there shouldn't be a problem with the tables.

Exactly: the two "AS" regex's in Search.pm are both relating to SELECT field
aliases, not table aliases.  Oracle does indeed allow that, so no problem.

Comment 7

[Lance Larsh]

Attachment: Updated patch

This is an updated version of the previous patch (Attachment 199595 [details] [diff]), which fixes rejects the occur due to recent code changes.

Comment 8

[Max Kanat-Alexander]

Comment on attachment 201016 [details] [diff] [review]
Updated patch

r=mkanat by inspection. There may have been some bitrot since you've posted the patch, you'll want to check.

Comment 9

[Max Kanat-Alexander]

Unfortunately this isn't going to make 2.22, so you'll probably just want to wait a while (since it's going to have to be un-bitrotted).

Comment 10

[Frédéric Buclin]

Guess what? This patch doesn't apply cleanly anymore. Good luck to unbitrot it.

Comment 11

[Frédéric Buclin]

This bug is retargetted to Bugzilla 3.2 for one of the following reasons:

- it has no assignee (except the default one)
- we don't expect someone to fix it in the next two weeks (i.e. before we freeze the trunk to prepare Bugzilla 3.0 RC1)
- it's not a blocker

If you are working on this bug and you think you will be able to submit a patch in the next two weeks, retarget this bug to 3.0.

If this bug is something you would like to see implemented in 3.0 but you are not a developer or you don't think you will be able to fix this bug yourself in the next two weeks, please *do not* retarget this bug.

If you think this bug should absolutely be fixed before we release 3.0, either ask on IRC or use the "blocking3.0 flag".

Comment 12

[Max Kanat-Alexander]

We fixed this in the Oracle driver by making Bugzilla parse the SQL and remove the AS statements.

We do have to check that that still works with subselects, though.