Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1120

Support SELECT without FROM syntax (similar to MySQL)

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.4.0-incubating
    • Fix Version/s: 1.8.0
    • Component/s: core
    • Labels:
    • Environment:

      1.4.0-incubating

      Description

      Calcite is unable to parse "select 2+2;"

      when i try to parse the below query

      select 2+2;
      

      Me getting error as below

      org.apache.calcite.sql.parser.impl.ParseException: Encountered "<EOF>" at line 1, column 22.
      Was expecting one of:
          "FROM" ...
          "," ...
          "AS" ...
          <IDENTIFIER> ...
          <QUOTED_IDENTIFIER> ...
          <BACK_QUOTED_IDENTIFIER> ...
          <BRACKET_QUOTED_IDENTIFIER> ...
          <UNICODE_QUOTED_IDENTIFIER> ...
          "A" ...
          "ABSOLUTE" ...
          "ACTION" ...
          "ADA" ...
          "ADD" ...
          "ADMIN" ...
          "AFTER" ...
          "ALWAYS" ...
          "ASC" ...
          "ASSERTION" ...
          "ASSIGNMENT" ...
          "ATTRIBUTE" ...
          "ATTRIBUTES" ...
          "BEFORE" ...
          "BERNOULLI" ...
          "BREADTH" ...
          "C" ...
          "CASCADE" ...
          "CATALOG" ...
          "CATALOG_NAME" ...
          "CHAIN" ...
          "CHARACTER_SET_CATALOG" ...
          "CHARACTER_SET_NAME" ...
          "CHARACTER_SET_SCHEMA" ...
          "CHARACTERISTICTS" ...
          "CHARACTERS" ...
          "CLASS_ORIGIN" ...
          "COBOL" ...
          "COLLATION" ...
          "COLLATION_CATALOG" ...
          "COLLATION_NAME" ...
          "COLLATION_SCHEMA" ...
          "COLUMN_NAME" ...
          "COMMAND_FUNCTION" ...
          "COMMAND_FUNCTION_CODE" ...
          "COMMITTED" ...
          "CONDITION_NUMBER" ...
          "CONNECTION" ...
          "CONNECTION_NAME" ...
          "CONSTRAINT_CATALOG" ...
          "CONSTRAINT_NAME" ...
          "CONSTRAINT_SCHEMA" ...
          "CONSTRAINTS" ...
          "CONSTRUCTOR" ...
          "CONTAINS" ...
          "CONTINUE" ...
          "CURSOR_NAME" ...
          "DATA" ...
          "DATETIME_INTERVAL_CODE" ...
          "DATETIME_INTERVAL_PRECISION" ...
          "DEFAULTS" ...
          "DEFERRABLE" ...
          "DEFERRED" ...
          "DEFINED" ...
          "DEFINER" ...
          "DEGREE" ...
          "DEPTH" ...
          "DERIVED" ...
          "DESC" ...
          "DESCRIPTION" ...
          "DESCRIPTOR" ...
          "DIAGNOSTICS" ...
          "DISPATCH" ...
          "DOMAIN" ...
          "DYNAMIC_FUNCTION" ...
          "DYNAMIC_FUNCTION_CODE" ...
          "EQUALS" ...
          "EXCEPTION" ...
          "EXCLUDE" ...
          "EXCLUDING" ...
          "FINAL" ...
          "FIRST" ...
          "FOLLOWING" ...
          "FORTRAN" ...
          "FOUND" ...
          "G" ...
          "GENERAL" ...
          "GENERATED" ...
          "GO" ...
          "GOTO" ...
          "GRANTED" ...
          "HIERARCHY" ...
          "IMMEDIATE" ...
          "IMPLEMENTATION" ...
          "INCLUDING" ...
          "INCREMENT" ...
          "INITIALLY" ...
          "INPUT" ...
          "INSTANCE" ...
          "INSTANTIABLE" ...
          "INVOKER" ...
          "ISOLATION" ...
          "JAVA" ...
          "K" ...
          "KEY" ...
          "KEY_MEMBER" ...
          "KEY_TYPE" ...
          "LABEL" ...
          "LAST" ...
          "LENGTH" ...
          "LEVEL" ...
          "LIBRARY" ...
          "LOCATOR" ...
          "M" ...
          "MAP" ...
          "MATCHED" ...
          "MAXVALUE" ...
          "MESSAGE_LENGTH" ...
          "MESSAGE_OCTET_LENGTH" ...
          "MESSAGE_TEXT" ...
          "MINVALUE" ...
          "MORE" ...
          "MUMPS" ...
          "NAME" ...
          "NAMES" ...
          "NESTING" ...
          "NORMALIZED" ...
          "NULLABLE" ...
          "NULLS" ...
          "NUMBER" ...
          "OBJECT" ...
          "OCTETS" ...
          "OPTION" ...
          "OPTIONS" ...
          "ORDERING" ...
          "ORDINALITY" ...
          "OTHERS" ...
          "OUTPUT" ...
          "OVERRIDING" ...
          "PAD" ...
          "PARAMETER_MODE" ...
          "PARAMETER_NAME" ...
          "PARAMETER_ORDINAL_POSITION" ...
          "PARAMETER_SPECIFIC_CATALOG" ...
          "PARAMETER_SPECIFIC_NAME" ...
          "PARAMETER_SPECIFIC_SCHEMA" ...
          "PARTIAL" ...
          "PASCAL" ...
          "PASSTHROUGH" ...
          "PATH" ...
          "PLACING" ...
          "PLAN" ...
          "PLI" ...
          "PRECEDING" ...
          "PRESERVE" ...
          "PRIOR" ...
          "PRIVILEGES" ...
          "PUBLIC" ...
          "READ" ...
          "RELATIVE" ...
          "REPEATABLE" ...
          "RESTART" ...
          "RESTRICT" ...
          "RETURNED_CARDINALITY" ...
          "RETURNED_LENGTH" ...
          "RETURNED_OCTET_LENGTH" ...
          "RETURNED_SQLSTATE" ...
          "ROLE" ...
          "ROUTINE" ...
          "ROUTINE_CATALOG" ...
          "ROUTINE_NAME" ...
          "ROUTINE_SCHEMA" ...
          "ROW_COUNT" ...
          "SCALE" ...
          "SCHEMA" ...
          "SCHEMA_NAME" ...
          "SCOPE_CATALOGS" ...
          "SCOPE_NAME" ...
          "SCOPE_SCHEMA" ...
          "SECTION" ...
          "SECURITY" ...
          "SELF" ...
          "SEQUENCE" ...
          "SERIALIZABLE" ...
          "SERVER" ...
          "SERVER_NAME" ...
          "SESSION" ...
          "SETS" ...
          "SIMPLE" ...
          "SIZE" ...
          "SOURCE" ...
          "SPACE" ...
          "SPECIFIC_NAME" ...
          "STATE" ...
          "STATEMENT" ...
          "STRUCTURE" ...
          "STYLE" ...
          "SUBCLASS_ORIGIN" ...
          "SUBSTITUTE" ...
          "TABLE_NAME" ...
          "TEMPORARY" ...
          "TIES" ...
          "TOP_LEVEL_COUNT" ...
          "TRANSACTION" ...
          "TRANSACTIONS_ACTIVE" ...
          "TRANSACTIONS_COMMITTED" ...
          "TRANSACTIONS_ROLLED_BACK" ...
          "TRANSFORM" ...
          "TRANSFORMS" ...
          "TRIGGER_CATALOG" ...
          "TRIGGER_NAME" ...
          "TRIGGER_SCHEMA" ...
          "TYPE" ...
          "UNBOUNDED" ...
          "UNCOMMITTED" ...
          "UNDER" ...
          "UNNAMED" ...
          "USAGE" ...
          "USER_DEFINED_TYPE_CATALOG" ...
          "USER_DEFINED_TYPE_CODE" ...
          "USER_DEFINED_TYPE_NAME" ...
          "USER_DEFINED_TYPE_SCHEMA" ...
          "VERSION" ...
          "VIEW" ...
          "WRAPPER" ...
          "WORK" ...
          "WRITE" ...
          "XML" ...
          "ZONE" ...
          "NOT" ...
          "IN" ...
          "BETWEEN" ...
          "LIKE" ...
          "SIMILAR" ...
          "=" ...
          ">" ...
          "<" ...
          "<=" ...
          ">=" ...
          "<>" ...
          "+" ...
          "-" ...
          "*" ...
          "/" ...
          "||" ...
          "AND" ...
          "OR" ...
          "IS" ...
          "MEMBER" ...
          "SUBMULTISET" ...
          "MULTISET" ...
          "[" ...
      

        Issue Links

          Activity

          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.8.0 (2016-06-13).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.8.0 (2016-06-13).
          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/40576e71 , with fix up by me in http://git-wip-us.apache.org/repos/asf/calcite/commit/f8f2af3e . Jimmy Xiang , thanks for the PR!
          Hide
          jxiang Jimmy Xiang added a comment -

          Julian Hyde, sure, please take it. Thanks. Your fix-ups are great.

          Show
          jxiang Jimmy Xiang added a comment - Julian Hyde , sure, please take it. Thanks. Your fix-ups are great.
          Hide
          julianhyde Julian Hyde added a comment -

          Please review my working branch in https://github.com/julianhyde/calcite/tree/1120-select-without-from. It has your change plus some fix-ups.

          Show
          julianhyde Julian Hyde added a comment - Please review my working branch in https://github.com/julianhyde/calcite/tree/1120-select-without-from . It has your change plus some fix-ups.
          Hide
          jnadeau Jacques Nadeau added a comment -

          Great to see this being added. Thanks Jimmy Xiang!

          Show
          jnadeau Jacques Nadeau added a comment - Great to see this being added. Thanks Jimmy Xiang !
          Hide
          julianhyde Julian Hyde added a comment -

          I notice that MySQL allows DISTINCT, AS, multiple SELECT-items, ORDER BY; but disallows WHERE, GROUP BY, HAVING. Seems reasonable to me, and I plan to implement this.

          What does PostgreSQL do?

          Show
          julianhyde Julian Hyde added a comment - I notice that MySQL allows DISTINCT, AS, multiple SELECT-items, ORDER BY; but disallows WHERE, GROUP BY, HAVING. Seems reasonable to me, and I plan to implement this. What does PostgreSQL do?
          Hide
          julianhyde Julian Hyde added a comment -

          I had a quick look and it looks excellent. Can I take it from here? I think I might add a connect string parameter "compliance=strict", "compliance=pragmatic_2003" etc. but it looks like you have it working except for loose ends, and I'm happy to tidy up the loose ends.

          Show
          julianhyde Julian Hyde added a comment - I had a quick look and it looks excellent. Can I take it from here? I think I might add a connect string parameter "compliance=strict", "compliance=pragmatic_2003" etc. but it looks like you have it working except for loose ends, and I'm happy to tidy up the loose ends.
          Hide
          jxiang Jimmy Xiang added a comment -

          Julian Hyde, I fixed the sql-to-rel converter and more tests as suggested, and updated the PR. I also add some config to CalciteConnectionProperty. However, I have not fully hooked the conformance with the connect-string property. Could you please take a look? Thanks.

          Show
          jxiang Jimmy Xiang added a comment - Julian Hyde , I fixed the sql-to-rel converter and more tests as suggested, and updated the PR. I also add some config to CalciteConnectionProperty. However, I have not fully hooked the conformance with the connect-string property. Could you please take a look? Thanks.
          Hide
          jxiang Jimmy Xiang added a comment -

          Thanks a lot for the review. Let me fix the issues you pointed out.

          Show
          jxiang Jimmy Xiang added a comment - Thanks a lot for the review. Let me fix the issues you pointed out.
          Hide
          julianhyde Julian Hyde added a comment -

          The PR looks good as far as it goes. But you've only changed the parser, so I think it would fall over in the sql-to-rel converter and we would not be able to evaluate these queries. We'd need a test in SqlToRelConverterTest and another test in JdbcTest (similar to JdbcTest.testValues) for this to be complete.

          We also need a property to enable/disable this feature, since it's not standard SQL. Add a SqlValidatorTest that calls SqlTester.withConformance(SqlConformance.STRICT_2003) and it should fail, and another that calls SqlTester.withConformance(SqlConformance.PRAGMATIC_2003) and should succeed. After you've done that I'll add a CalciteConnectionProperty value that exposes conformance as a connect-string property.

          Show
          julianhyde Julian Hyde added a comment - The PR looks good as far as it goes. But you've only changed the parser, so I think it would fall over in the sql-to-rel converter and we would not be able to evaluate these queries. We'd need a test in SqlToRelConverterTest and another test in JdbcTest (similar to JdbcTest.testValues) for this to be complete. We also need a property to enable/disable this feature, since it's not standard SQL. Add a SqlValidatorTest that calls SqlTester.withConformance(SqlConformance.STRICT_2003) and it should fail, and another that calls SqlTester.withConformance(SqlConformance.PRAGMATIC_2003) and should succeed. After you've done that I'll add a CalciteConnectionProperty value that exposes conformance as a connect-string property.
          Hide
          jxiang Jimmy Xiang added a comment -

          I created a PR and added the link. Julian Hyde, can you take a ook?

          Show
          jxiang Jimmy Xiang added a comment - I created a PR and added the link. Julian Hyde , can you take a ook?
          Hide
          julianhyde Julian Hyde added a comment -

          SELECT without a FROM clause is not standard SQL. Calcite supports the standard syntax

          VALUES 2 + 2

          We could consider adding support for SELECT <expression-list>, which we would allow in MySQL and Postgres compatibility mode.

          We could also allow SELECT 2 + 2 FROM dual in Oracle compatibility mode.

          Show
          julianhyde Julian Hyde added a comment - SELECT without a FROM clause is not standard SQL. Calcite supports the standard syntax VALUES 2 + 2 We could consider adding support for SELECT <expression-list> , which we would allow in MySQL and Postgres compatibility mode. We could also allow SELECT 2 + 2 FROM dual in Oracle compatibility mode.

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              ShyamP Shyam
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development