Can you write a working SQL statement without using any whitespace?

I read this post by Laurent Schneider yesterday. In the comment section Tom Kyte already explained what the issue was about, but I’ll expand this explanation a little.

The question was why should the apparently invalid statement below work? I mean there is no such column nor number as “1.x”), yet the statement works ok:

SQL> select 1.x from dual;

         X
----------
         1

The column header gives a good hint what happened above. Oracle has treated the X as the column alias.

Let’s remove the “x” and see:

SQL> select 1. from dual;

        1.
----------
         1

Now all works as expected, “1.” is treated as number ( 1. = 1.0 )

But the question remains, how come Oracle splits up the string “1.x” and decides that the “x” is the column and “1.” is the number part – considering that there’s no whitespace between the 1. and x?

The answer is that apparently the string tokenizer used by Oracle SQL parser is smart enough that it doesn’t rely only on whitespace for recognizing token delimiters. It is also able to use character class analysis for understanding where a literal ends and the next token (like column alias) starts.

So, it is possible to write valid SQL statements without using any whitespace at all. For example:

SQL> select*from"DUAL";

D
-
X

SQL> select"DUMMY"from"DUAL";

D
-
X

Of course you could always use comments for replacing whitespace below…

SQL> alter/**/system/**/switch/**/logfile;

System altered.

All the info above has quite limited use (unless your keyboard’s space bar is broken), but nevertheless, it illustrates some internal workings and flexibility of Oracle’s SQL text parser…

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.

14 Responses to Can you write a working SQL statement without using any whitespace?

  1. chris says:

    I also read Tom Kyte’s comment but still consider it a bug and not “flexibility” because the behaviour is even stranger. No self-respecting parser should do this:

    SQL> select dummy from dual;

    D
    -
    X

    1 row selected.

    SQL> select 1.dummy from dual;

    UMMY
    ———-
    1.0E+000

    1 row selected.

  2. Chris Antognini says:

    Ciao Tanel

    I just had a look to section 5.2 ( and ) of the ISO/IEC 9075-2:2003 standard.

    To me it seams that Oracle’s parser is just following the standard… In fact in that section you can read, among other things:

    - ::= { | }

    - Any may be followed by a . A shall be followed by a or a .

    Of course they fully describe what a and is… And it matches your (and Laurent’s) observation.

    Cheers,
    Chris

  3. Chris Antognini says:

    Dammit! Lot’s of interesting information is disappeared… All those nice greater-than and lesser-than. Anyway, if somebody is interested, simple go to section 5.2 of part 2 of the SQL standard ;-)

  4. tanelp says:

    Hi Chris,

    I narrowed down the issue further, the “.” is not needed either, Oracle “recognizes” the next token when character class changes from a digit to letter.

    SQL> select 1a from dual;

    A
    ----------
    1

    However the “d” and “f” letters are special (and belong to number character class in literal parser) as they indicate binary_Double and binary_Float datatypes in the literal text:

    SQL> select 1d from dual;

    1D
    ----------
    1.0E+000

    SQL> select 1f from dual;

    1F
    ----------
    1.0E+000

    Therefore:

    SQL> create table t as select 1dummy from dual;

    Table created.

    SQL> desc t
    Name Null? Type
    ------------------------------- -------- --------------
    1 UMMY BINARY_DOUBLE

    Yes, this is getting weird, so I will downgrade my opinion on this from “flexibility” to a “feature” which should be fixed in order to alert users who happen to do typos or use wrong syntax like this…

  5. Chris Antognini says:

    Just to avoid confusion… I’m not the Chris that posted the first comment…

    I agree that the last test you posted in the comment is quite “interesting”.

  6. tanelp says:

    Chris A: Yep I noticed :)

    I quickly skimmed through the standard too (the BNF notation) and didn’t see anything which would enforce use of whitespace.

    So, it may be that Oracle’s just following the standard on this.. (What about the “D” and “F” notation in literal values – is that specified in ANSI too?)

    Anyway, after seeing a case where a NUMBER column DUMMY is silently converted to a BINARY_DOUBLE column UMMY I think there should be a safeguard against this issue. For example parser erroring out with “ORA-xyz: ambiguous expression or literal value” or something like that…

  7. Chris Antognini says:

    > What about the “D” and “F” notation in literal values – is that specified in ANSI too?

    I was not able to find information about that.

  8. chris neumueller says:

    Hi, here’s the “dummy” chris again.

    Good analysis, I assumed it had something to do with number types but didn’t investigate further.

    Btw, here’s another gem, which confirms my view that such a “relaxed” parser is no good idea at all:

    SQL> select 1from dual;
    SQL> select 1ffrom dual;

  9. Brian Tkatch says:

    There seems to be a difference in version. I tried this using SQL Plus 10.2.0.1.0, but with two version of Oracle:

    Oracle 10XE 10.2.0.1.0

    SQL> select 1. from dual;

    1.
    ———-
    1

    SQL> select*from”DUAL”;

    D
    -
    X

    SQL> select”DUMMY”from”DUAL”;

    D
    -
    X

    SQL> select/**/DUMMY/**/from/**/DUAL;

    D
    -
    X

    SQL> select 1.dummy from dual;

    UMMY
    ———-
    1.0E+000

    SQL> select 1a from dual;

    A
    ———-
    1

    SQL> select 1d from dual;

    1D
    ———-
    1.0E+000

    SQL> select 1f from dual;

    1F
    ———-
    1.0E+000

    SQL> select 1from dual;
    select 1from dual
    *
    ERROR at line 1:
    ORA-00923: FROM keyword not found where expected

    SQL> select 1ffrom dual;

    1F
    ———-
    1.0E+000

    Oracle 9iEE 9.2.0.8.0

    SQL> select 1. from dual;

    1.
    ———-
    1

    SQL> select*from”DUAL”;

    D
    -
    X

    SQL> select”DUMMY”from”DUAL”;

    D
    -
    X

    SQL> select/**/DUMMY/**/from/**/DUAL;

    D
    -
    X

    SQL> select 1.dummy from dual;

    DUMMY
    ———-
    1

    SQL> select 1a from dual;

    A
    ———-
    1

    SQL> select 1d from dual;

    D
    ———-
    1

    SQL> select 1f from dual;

    F
    ———-
    1

    SQL> select 1from dual;

    1
    ———-
    1

    SQL> select 1ffrom dual;
    select 1ffrom dual
    *
    ERROR at line 1:
    ORA-00923: FROM keyword not found where expected

    So, this seems that the behavior changed between 9i and 10, in that 10 treat the “d” and “f” as special identifiers. The final line for 9i is also interesting.

    I also though maybe the “D” was special because it was the first letter in “DUMMY”, and “F” the first in “FROM”:

    10:

    SQL> CREATE TABLE A(A INT);

    Table created.

    SQL> SELECT 1D FROM A;

    no rows selected

    SQL> INSERT INTO A VALUES(2);

    1 row created.

    SQL> SELECT 1D FROM A;

    1D
    ———-
    1.0E+000

    SQL> SELECT 1A FROM A;

    A
    ———-
    1

    9:

    SQL> CREATE TABLE A(A INT);

    Table created.

    SQL> SELECT 1D FROM A;

    no rows selected

    SQL> INSERT INTO A VALUES(2);

    1 row created.

    SQL> SELECT 1D FROM A;

    D
    ———-
    1

    SQL> SELECT 1A FROM A;

    A
    ———-
    1

    So, that’s not it.

  10. tanelp says:

    It makes sense as the BINARY_FLOAT and BINARY_DOUBLE datatypes (what the “F” and “D” stand for) were introduced in 10g!

  11. Pingback: Blog » Blog Archive » Oracle, white spaces and unexpected behaviour

  12. Brian Tkatch says:

    Tanel, thanx. I did not know they were added in 10g.

  13. select’abc’from”DUAL”;

  14. Pingback: Is this valid SQL syntax? :-) | Tanel Poder's blog: IT & Mobile for Geeks and Pros

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>