select 1.x from t1

I made a funny typo today 🙂
SQL> select * from t1;

X
0

SQL> select 1.x from t1;

X
1

Should not I get ORA-904 invalid identifier 😕 ?

Published
Categorized as Blogroll, sql

By Laurent Schneider

Oracle Certified Master

15 comments

  1. No, for the same reason that

    select ‘a’x from dual

    would not fail.

    select 1.
    from dual;

    works – 1. is a valid literal.

    select ‘literal’name
    from dual

    is valid (whitespace is many times optional)… hence

    select 1.x from dual

    is.

    ops$tkyte%ORA10GR2> select*from dual;

    D

    X

  2. So, what does
    SQL > select 1.dummy from “DUAL””1”;
    select 1.dummy from “DUAL””1”
    *
    ERROR at line 1:
    ORA-03001: unimplemented feature

    tells us ?

  3. Can you guys explain why the following statement works?

    select SYSDATE@! from dual;

    SYSDATE@!
    ———
    14-JAN-08

    SQL> select SYSDATE@!-4 from dual;

    SYSDATE@!
    ———
    10-JAN-08

  4. @matthias
    Probably in Oracle 17, “DUAL””1” will be a table called DUAL”1, in Oracle 11 and before, it is not possible to have a double quote in a table name

    @ed
    No idea !

  5. For what it’s worth

    SQL> select sysdate from dual;

    SYSDATE
    ———
    15-JAN-08

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

    SQL> select sysdate@! from dual;

    SYSDATE@!
    ———
    15-JAN-08

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

    And I don’t understand anything any more!

  6. Currently I don’t have a database at hand, so I can really test it, but regarding:

    select 1.dummy from “DUAL””1″;

    Counting that the use of double quotas is a standard thing while for instance looking into the syntax of a dumpfile, generated by “exp”. I would expect that “1” is regarded as a database link alias or such. With SQL*Net V1 this was the place to use the connection descriptor starting with the protocol (t for tcpip, d for dec, n for names – if I remember correctly). Therefore maybe the ORA-03001: unimplemented feature.

    Laurent are you sure you can’t use double quotes in a table name? Didn’t we wander these paths before…?

  7. Another little oddity. If you’re setting event 10053 and run

    select sysdate@! from dual;

    All you get is an empty tracefile (on 10.2.0.3) — seems the optimizer is choking on it ?

    Perhaps the “!” character has a special meaning (other than executing commands in your shell on *nix systems) ?

    Stefan

  8. Maybe we should have a separate discussion on sysdate@! thing 😉
    It was found when sysdate was used with db link.
    I.e. SQL> select sysdate from dual@db_link;
    produces the following SQL in remote database:
    SELECT SYSDATE@! FROM “DUAL” “A1”

    Ed

  9. Sure there is an entertaining (and time consuming) explanation for each and every oddity, but how many developers really write a query without whitespace? I’m not aware of any modern programming language where whitespace is not a fundamental part of the syntax. Ditto for identifiers in quotes. Those should be deprecated (although was there a case when anything has been deprecated in SQL?)

  10. Come on, Mikito Harakiri, wasting our time is not only a side effect but the *sense* of dealing with SQL, programming languages and all the rest, isn’t t ?

Leave a comment

Your email address will not be published.