Apache Drill
  1. Apache Drill
  2. DRILL-416

Make Drill work with SELECT without FROM

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 0.4.0
    • Fix Version/s: Future
    • Component/s: None
    • Labels:
      None

      Description

      This works with postgres:

      [root@qa-node120 ~]# sudo -u postgres psql foodmart
      foodmart=# select 1+1.1;
      ?column?
      ----------
      2.1
      (1 row)

      But does not work with Drill:

      0: jdbc:drill:> select 1+1.1;
      Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure while running query.[error_id: "100f4d4c-1ee1-495e-9c2f-547aae75473d"
      endpoint

      { address: "qa-node118.qa.lab" user_port: 31010 control_port: 31011 data_port: 31012 }

      error_type: 0
      message: "Failure while parsing sql. < SqlParseException:[ Encountered \"<EOF>\" at line 1, column 12.\nWas expecting one of:....

        Activity

        Chun Chang created issue -
        Hide
        Julian Hyde added a comment -

        It's that missing VALUES operator again. ("select 1 + 1.1" is the same as "values 1 + 1.1".) See DRILL-57.

        Show
        Julian Hyde added a comment - It's that missing VALUES operator again. ("select 1 + 1.1" is the same as "values 1 + 1.1".) See DRILL-57 .
        Hide
        Jacques Nadeau added a comment -

        Chun, can you provide the full stack trace from the error log? The little bit I see here looks like it might be a parsing problem and not the values operator.

        Show
        Jacques Nadeau added a comment - Chun, can you provide the full stack trace from the error log? The little bit I see here looks like it might be a parsing problem and not the values operator.
        Hide
        Chun Chang added a comment -

        11:09:49.542 [WorkManager-2] ERROR o.a.drill.exec.work.foreman.Foreman - Error e2946b36-dd24-429b-b8a3-84d04a7ecc65: Failure while parsing sql.
        org.eigenbase.sql.parser.impl.ParseException: Encountered "<EOF>" at line 1, column 10.
        Was expecting one of:
        "FROM" ...
        "," ...
        "AS" ...
        <IDENTIFIER> ...
        <QUOTED_IDENTIFIER> ...
        <BACK_QUOTED_IDENTIFIER> ...
        <BRACKET_QUOTED_IDENTIFIER> ...
        <UNICODE_QUOTED_IDENTIFIER> ...
        "NOT" ...
        "IN" ...
        "BETWEEN" ...
        "LIKE" ...
        "SIMILAR" ...
        "=" ...
        ">" ...
        "<" ...
        "<=" ...
        ">=" ...
        "<>" ...
        "+" ...
        "-" ...
        "*" ...
        "/" ...
        "||" ...
        "AND" ...
        "OR" ...
        "IS" ...
        "MEMBER" ...
        "SUBMULTISET" ...
        "MULTISET" ...
        "[" ...

        at org.eigenbase.sql.parser.impl.SqlParserImpl.generateParseException(SqlParserImpl.java:15736) ~[optiq-core-0.4.18.jar:na]
        at org.eigenbase.sql.parser.impl.SqlParserImpl.jj_consume_token(SqlParserImpl.java:15565) ~[optiq-core-0.4.18.jar:na]
        at org.eigenbase.sql.parser.impl.SqlParserImpl.SqlSelect(SqlParserImpl.java:704) ~[optiq-core-0.4.18.jar:na]
        at org.eigenbase.sql.parser.impl.SqlParserImpl.LeafQuery(SqlParserImpl.java:471) ~[optiq-core-0.4.18.jar:na]
        at org.eigenbase.sql.parser.impl.SqlParserImpl.LeafQueryOrExpr(SqlParserImpl.java:2120) ~[optiq-core-0.4.18.jar:na]
        at org.eigenbase.sql.parser.impl.SqlParserImpl.QueryOrExpr(SqlParserImpl.java:2082) ~[optiq-core-0.4.18.jar:na]
        at org.eigenbase.sql.parser.impl.SqlParserImpl.OrderedQueryOrExpr(SqlParserImpl.java:389) ~[optiq-core-0.4.18.jar:na]
        at org.eigenbase.sql.parser.impl.SqlParserImpl.SqlStmt(SqlParserImpl.java:645) ~[optiq-core-0.4.18.jar:na]
        at org.eigenbase.sql.parser.impl.SqlParserImpl.SqlStmtEof(SqlParserImpl.java:671) ~[optiq-core-0.4.18.jar:na]
        at org.eigenbase.sql.parser.SqlParser.parseStmt(SqlParser.java:126) ~[optiq-core-0.4.18.jar:na]
        at net.hydromatic.optiq.prepare.PlannerImpl.parse(PlannerImpl.java:142) ~[optiq-core-0.4.18.jar:na]
        at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:57) ~[drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]
        at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:350) [drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]
        at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:175) [drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_45]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_45]
        at java.lang.Thread.run(Thread.java:744) [na:1.7.0_45]
        [root@qa-node118 drill]#

        Show
        Chun Chang added a comment - 11:09:49.542 [WorkManager-2] ERROR o.a.drill.exec.work.foreman.Foreman - Error e2946b36-dd24-429b-b8a3-84d04a7ecc65: Failure while parsing sql. org.eigenbase.sql.parser.impl.ParseException: Encountered "<EOF>" at line 1, column 10. Was expecting one of: "FROM" ... "," ... "AS" ... <IDENTIFIER> ... <QUOTED_IDENTIFIER> ... <BACK_QUOTED_IDENTIFIER> ... <BRACKET_QUOTED_IDENTIFIER> ... <UNICODE_QUOTED_IDENTIFIER> ... "NOT" ... "IN" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "=" ... ">" ... "<" ... "<=" ... ">=" ... "<>" ... "+" ... "-" ... "*" ... "/" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "MULTISET" ... "[" ... at org.eigenbase.sql.parser.impl.SqlParserImpl.generateParseException(SqlParserImpl.java:15736) ~ [optiq-core-0.4.18.jar:na] at org.eigenbase.sql.parser.impl.SqlParserImpl.jj_consume_token(SqlParserImpl.java:15565) ~ [optiq-core-0.4.18.jar:na] at org.eigenbase.sql.parser.impl.SqlParserImpl.SqlSelect(SqlParserImpl.java:704) ~ [optiq-core-0.4.18.jar:na] at org.eigenbase.sql.parser.impl.SqlParserImpl.LeafQuery(SqlParserImpl.java:471) ~ [optiq-core-0.4.18.jar:na] at org.eigenbase.sql.parser.impl.SqlParserImpl.LeafQueryOrExpr(SqlParserImpl.java:2120) ~ [optiq-core-0.4.18.jar:na] at org.eigenbase.sql.parser.impl.SqlParserImpl.QueryOrExpr(SqlParserImpl.java:2082) ~ [optiq-core-0.4.18.jar:na] at org.eigenbase.sql.parser.impl.SqlParserImpl.OrderedQueryOrExpr(SqlParserImpl.java:389) ~ [optiq-core-0.4.18.jar:na] at org.eigenbase.sql.parser.impl.SqlParserImpl.SqlStmt(SqlParserImpl.java:645) ~ [optiq-core-0.4.18.jar:na] at org.eigenbase.sql.parser.impl.SqlParserImpl.SqlStmtEof(SqlParserImpl.java:671) ~ [optiq-core-0.4.18.jar:na] at org.eigenbase.sql.parser.SqlParser.parseStmt(SqlParser.java:126) ~ [optiq-core-0.4.18.jar:na] at net.hydromatic.optiq.prepare.PlannerImpl.parse(PlannerImpl.java:142) ~ [optiq-core-0.4.18.jar:na] at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:57) ~ [drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT] at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:350) [drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT] at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:175) [drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_45] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_45] at java.lang.Thread.run(Thread.java:744) [na:1.7.0_45] [root@qa-node118 drill] #
        Hide
        Xiao Meng added a comment -

        It seems that "FROM tablename" is required. It works fine if you add any tablename here.

        0: jdbc:drill:zk=local> select 1+1.1 from INFORMATION_SCHEMA.CATALOGS;
        +------------+
        |   EXPR$0   |
        +------------+
        | 2          |
        +------------+
        1 row selected (0.211 seconds)
        
        Show
        Xiao Meng added a comment - It seems that "FROM tablename" is required. It works fine if you add any tablename here. 0: jdbc:drill:zk=local> select 1+1.1 from INFORMATION_SCHEMA.CATALOGS; +------------+ | EXPR$0 | +------------+ | 2 | +------------+ 1 row selected (0.211 seconds)
        Hide
        Xiao Meng added a comment - - edited

        Oops , it does not exactly work. The expression will be converted to a 32-bit singed integer.

        0: jdbc:drill:zk=local> select 2147483647.9 from INFORMATION_SCHEMA.CATALOGS;
        +------------+
        |   EXPR$0   |
        +------------+
        | 2147483647 |
        +------------+
        1 row selected (0.173 seconds)
        0: jdbc:drill:zk=local> select 2147483648 from INFORMATION_SCHEMA.CATALOGS;
        +------------+
        |   EXPR$0   |
        +------------+
        | -2147483648 |
        +------------+
        1 row selected (0.162 seconds)
        
        Show
        Xiao Meng added a comment - - edited Oops , it does not exactly work. The expression will be converted to a 32-bit singed integer. 0: jdbc:drill:zk=local> select 2147483647.9 from INFORMATION_SCHEMA.CATALOGS; +------------+ | EXPR$0 | +------------+ | 2147483647 | +------------+ 1 row selected (0.173 seconds) 0: jdbc:drill:zk=local> select 2147483648 from INFORMATION_SCHEMA.CATALOGS; +------------+ | EXPR$0 | +------------+ | -2147483648 | +------------+ 1 row selected (0.162 seconds)
        Jake Farrell made changes -
        Field Original Value New Value
        Workflow no-reopen-closed, patch-avail [ 12848254 ] no-reopen-closed, patch-avail, testing [ 12860191 ]
        Chun Chang made changes -
        Priority Major [ 3 ] Blocker [ 1 ]
        Hide
        Chun Chang added a comment -

        Make this a blocker since most decimal test cases are written without the 'from' clause, such as the following:

        select cast('999999999' as decimal(9,0)) + cast('12345678901234.56789123456789' as decimal(28,14));

        Show
        Chun Chang added a comment - Make this a blocker since most decimal test cases are written without the 'from' clause, such as the following: select cast('999999999' as decimal(9,0)) + cast('12345678901234.56789123456789' as decimal(28,14));
        Chun Chang made changes -
        Priority Blocker [ 1 ] Major [ 3 ]
        Hide
        Chun Chang added a comment -

        had a discussion on this. as a workaround, i will modify my query to make it work. lower the priority to major.

        Show
        Chun Chang added a comment - had a discussion on this. as a workaround, i will modify my query to make it work. lower the priority to major.
        Jacques Nadeau made changes -
        Fix Version/s Future [ 12326743 ]
        Tony Stevenson made changes -
        Workflow no-reopen-closed, patch-avail, testing [ 12860191 ] Drill workflow [ 12933842 ]

          People

          • Assignee:
            Unassigned
            Reporter:
            Chun Chang
          • Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:

              Development