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;

       ROM
----------
 -1.0E+000

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;

     .1E2F
----------
  1.0E+001

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

         .
----------
  1.0E+001

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.

:-)

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

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

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

  1. Pingback: Tweets that mention Is this valid SQL syntax? :-) | Tanel Poder's blog: IT & Mobile for Geeks and Pros -- Topsy.com

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

    COUNT(*)
    ----------
    14

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

    COUNT(*)
    ----------
    15

    happy new year

  3. Tanel Poder says:

    @Laurent Schneider
    hehe nice :-)

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

    
    SQL> CREATE INDEX i ON t NOLOGGING (a);
    
    Index created.
    
    SQL> CREATE TABLE t2 AS SELECT * FROM t NOLOGGING;
    
    Table created.
    
    
  4. Indeed pretty tricky, this is all the fun with the keywords that could not possibly become reserved words without breaking old code :)

  5. 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

    http://forums.oracle.com/forums/thread.jspa?messageID=1458730

  6. 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 :)

  7. Gary says:

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

  8. Tanel Poder says:

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

    
    SQL> select-null-user@!from"DUAL";
    
    -NULL-USER@!
    ------------
    
    
    SQL> select*from"DUAL";
    
    D
    -
    X
    
    
  9. Tanel Poder says:

    This is pretty cool too:

    
    SQL> DELETE"T";
    
    0 rows deleted.
    
    
  10. 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.

  11. Tanel Poder says:

    @Gary
    Yeah, check your regexes :)

  12. Walter says:

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

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

    same here :

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

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

  15. Tanel Poder says:

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

  16. Tanel Poder says:

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

  17. Walter says:

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

  18. Pingback: State of Data Last Week – #31 « Dr Data's Blog

  19. 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.

  20. Pingback: Slightly Weird Oracle Stuff | Rants & Raves – The Blog!

  21. Pingback: Oracle

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>