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:
- Oracle doesn’t need whitespace for tokenizing the SQL statement (differences in character classes will do – as I’ve explained here)
- 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.
:-)
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
@Laurent Schneider
hehe nice :-)
Some more commands with wrong syntax which won’t probably do what intended:
Indeed pretty tricky, this is all the fun with the keywords that could not possibly become reserved words without breaking old code :)
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
@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 :)
@Laurent Schneider
Well anyone who uses NATURAL JOIN deserves whatever they get.
Here’s a few more funny looking SQLs without spaces in them:
This is pretty cool too:
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.
@Gary
Yeah, check your regexes :)
TP – Even the geeks look at you and say “Such a geek” :-)
-null-user, how could this work?
same here :
select*from dual where exists(select 1/0 from dual)
null+user works, but user+null does not, is not the addition supposed to be commutative ;-)
@Laurent Schneider
Laurent, -null is negative null (not subtraction), but there’s no such thing as a negative “user” :)
@Walter
Walter, I’ll take this as a compliment ;-)
What can i say other than “You da man!”
I’ve run into another kind of valid syntax problems – see http://cedet.blogspot.com/2010/12/valid-or-not.html
Here’s another funky one in PL/SQL:
SQL> EXEC FOR i IN.1..10LOOP:v:=1;END LOOP;
PL/SQL procedure successfully completed.