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.


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 *