Is this valid SQL syntax? :-)

I’m talking about this:

select-1from from dual;

Looks like invalid, right? Well, let’s run it:

SQL> select-1from from dual;


This is because:

  1. Oracle doesn’t need whitespace for tokenizing the SQL statement (differences in character classes will do – as I’ve explained here)
  2. The first from “keyword” in the above statement is broken down to two tokens as an “F” right after a digit means that the preceding number is a FLOAT (and “D” means DOUBLE) and the tokenizer stops right there, knowing that whatever comes after this character (“ROM”) is a next token, which according to the Oracle SQL syntax rules will be assigned as the output column alias

The following funky-looking SQL statements are also valid:

SQL> select.1e2ffrom dual;


SQL> select.1e2fas"."from dual;


In the upper example, the “.1e2f” means number .1 * 10^2 (scientific notation) represented as a FLOAT internally and in the lower one I’ve just added a column alias with “AS” keyword just to make the SQL look a bit crazier.


NB! Dates updated: After a 1.5 year break, this year’s only Advanced Oracle Troubleshooting training class (updated with Oracle 12c content) takes place on 14-18 December 2015 and 11-15 January 2016 (I had to reschedule the start from November to December). So sign up now if you want to learn new cool stuff!

This entry was posted in Cool stuff, Oracle and tagged . Bookmark the permalink.

18 Responses to Is this valid SQL syntax? :-)

  1. funny syntax with join and misuse of aliases
    SQL> select count(*) from emp full natural join dept;


    SQL> select count(*) from emp natural full join dept;


    happy new year

  2. Tanel Poder says:

    @Laurent Schneider
    hehe nice :-)

    Some more commands with wrong syntax which won’t probably do what intended:

    Index created.
    Table created.
  3. Indeed pretty tricky, this is all the fun with the keywords that could not possibly become reserved words without breaking old code :)

  4. SQL*developer used to break if you had a table called recyclebin.

    In version 3, it does not break (but it does not work correctly neither). Probably a huge hack à la EXCEPTION WHEN OTHERS THEN NULL

  5. Tanel Poder says:

    @Laurent Schneider

    Yeah, another gem was a table called SYS which was created in an Oracle 9.0 database during an application migration from Ingres – we ended up with ORA-600’s all over the place and couldn’t drop that SYS table anymore :)

  6. Gary says:

    @Laurent Schneider
    Well anyone who uses NATURAL JOIN deserves whatever they get.

  7. Tanel Poder says:

    Here’s a few more funny looking SQLs without spaces in them:

    SQL> select-null-user@!from"DUAL";
    SQL> select*from"DUAL";
  8. Tanel Poder says:

    This is pretty cool too:

    0 rows deleted.
  9. Gary says:

    Just to add, an important aspect of these oddities is if someone is using some form of parsing to try to prevent SQL injection. These space-less SQLs might sneak through such a barrier, which is why such an approach is not particularly effective.

  10. Tanel Poder says:

    Yeah, check your regexes :)

  11. Walter says:

    TP – Even the geeks look at you and say “Such a geek” :-)

  12. -null-user, how could this work?

    same here :

    select*from dual where exists(select 1/0 from dual)

  13. null+user works, but user+null does not, is not the addition supposed to be commutative ;-)

  14. Tanel Poder says:

    @Laurent Schneider
    Laurent, -null is negative null (not subtraction), but there’s no such thing as a negative “user” :)

  15. Tanel Poder says:

    Walter, I’ll take this as a compliment ;-)

  16. Walter says:

    What can i say other than “You da man!”

  17. Tanel Poder says:

    Here’s another funky one in PL/SQL:

    SQL> EXEC FOR i IN.1..10LOOP:v:=1;END LOOP;

    PL/SQL procedure successfully completed.

Leave a Reply

Your email address will not be published. Required fields are marked *