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

Add option StructKind.PEEK_FIELDS_NO_EXPAND, similar to PEEK_FIELDS but is not expanded in "SELECT *"

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.14.0
    • Component/s: core
    • Labels:
      None

      Description

      In Calcite, StructKind.PEEK_FIELDS allow see its fields without qualifying them with the name of this field. For example, if address is labeled PEEK_FIELDS, one could write zipcode as shorthand for address.zipcode, i.e., the following SQL is allowed:

      Select zipcode from T
      

      StructKind.FULLY_QUALIFIED is the normal SQL behavior, where each field must be referenced explicitly, i.e. the above SQL will fail.

      But currently in calcite, a side effect of using PEEK_FIELDS is that "select *" will recursively flatten the PEEK_FIELDS RecordType. We want to add an option call PEEK_FIELDS_NO_FLATTENING to disable the behavior.

      With the new option, if the table structure T is as follows:

      VARCHAR K0,
      VARCHAR C1,
      RecordType:peek_no_flattening(INTEGER C0, INTEGER C1) F0,
      RecordType:peek_no_flattening(INTEGER C0, INTEGER C2) F1
      
      

      The following query

      Select * from T
      

      will return (K0, C1, F0, F1) instead of (K0, C1, F0.C0, F0.C1, F1.C0, F1.C1)

        Issue Links

          Activity

          Hide
          michaelmior Michael Mior added a comment -

          Resolved in release 1.14.0 (2017-10-01)

          Show
          michaelmior Michael Mior added a comment - Resolved in release 1.14.0 (2017-10-01)
          Hide
          vladimirsitnikov Vladimir Sitnikov added a comment -

          The sad thing is Avatica does not support Struct/Array types.
          It errors out on the RecordType:peek_no_flattening.

          java.lang.RuntimeException: not implemented
                  at org.apache.calcite.avatica.AvaticaSite.notImplemented(AvaticaSite.java:420)
                  at org.apache.calcite.avatica.AvaticaSite.get(AvaticaSite.java:302)
                  at org.apache.calcite.avatica.AvaticaResultSet.getObject(AvaticaResultSet.java:409)
                  at com.github.vlsi.mat.tests.calcite.QueryTest.execute(QueryTest.java:210)
          switch (targetSqlType) {
              case Types.CLOB:
              case Types.DATALINK:
              case Types.NCLOB:
              case Types.REF:
              case Types.SQLXML:
              case Types.STRUCT:
                throw notImplemented();
          
          Show
          vladimirsitnikov Vladimir Sitnikov added a comment - The sad thing is Avatica does not support Struct/Array types. It errors out on the RecordType:peek_no_flattening. java.lang.RuntimeException: not implemented at org.apache.calcite.avatica.AvaticaSite.notImplemented(AvaticaSite.java:420) at org.apache.calcite.avatica.AvaticaSite.get(AvaticaSite.java:302) at org.apache.calcite.avatica.AvaticaResultSet.getObject(AvaticaResultSet.java:409) at com.github.vlsi.mat.tests.calcite.QueryTest.execute(QueryTest.java:210) switch (targetSqlType) { case Types.CLOB: case Types.DATALINK: case Types.NCLOB: case Types.REF: case Types.SQLXML: case Types.STRUCT: throw notImplemented();
          Hide
          vladimirsitnikov Vladimir Sitnikov added a comment -

          select * projects RecordType:peek_no_flattening fields as well, however I do not need them. I just need some "available yet invisible to select * fields". Is it possible?

          Show
          vladimirsitnikov Vladimir Sitnikov added a comment - select * projects RecordType:peek_no_flattening fields as well, however I do not need them. I just need some "available yet invisible to select * fields". Is it possible?
          Hide
          julianhyde Julian Hyde added a comment -

          Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/c0307571. Thanks for the PR, Shuyi Chen! (It won't make it into 1.13; 1.13 was released on Monday.)

          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/c0307571 . Thanks for the PR, Shuyi Chen ! (It won't make it into 1.13; 1.13 was released on Monday.)
          Hide
          suez1224 Shuyi Chen added a comment -

          Julian Hyde Examples included. Can this change get into 1.13 release?

          Show
          suez1224 Shuyi Chen added a comment - Julian Hyde Examples included. Can this change get into 1.13 release?
          Hide
          julianhyde Julian Hyde added a comment -

          Shuyi Chen, Please include those examples. I can't mark this bug fixed until I have them.

          Show
          julianhyde Julian Hyde added a comment - Shuyi Chen , Please include those examples. I can't mark this bug fixed until I have them.
          Hide
          julianhyde Julian Hyde added a comment -

          Can you also include examples that describe how PEEK_FIELDS_NO_EXPAND is different from StructKind.FULLY_QUALIFIED, which is the normal option for SQL.

          Show
          julianhyde Julian Hyde added a comment - Can you also include examples that describe how PEEK_FIELDS_NO_EXPAND is different from StructKind.FULLY_QUALIFIED, which is the normal option for SQL.
          Hide
          suez1224 Shuyi Chen added a comment -

          That sounds good. SQL example added in the description.

          Show
          suez1224 Shuyi Chen added a comment - That sounds good. SQL example added in the description.
          Hide
          julianhyde Julian Hyde added a comment -

          Could you please give an example on how "it makes the names of intervening record-typed fields disappear"?

          If the STORE table has a field COORD which is a record type (X, Y) and COORD is marked PEEK, then you can omit COORD. Both SELECT COORD.X FROM STORE and SELECT X FROM STORE are valid.

          Please add SQL examples.

          Show
          julianhyde Julian Hyde added a comment - Could you please give an example on how "it makes the names of intervening record-typed fields disappear"? If the STORE table has a field COORD which is a record type (X, Y) and COORD is marked PEEK, then you can omit COORD. Both SELECT COORD.X FROM STORE and SELECT X FROM STORE are valid. Please add SQL examples.
          Hide
          suez1224 Shuyi Chen added a comment -

          PEEK_FIELDS_NO_EXPAND looks good to me.

          Show
          suez1224 Shuyi Chen added a comment - PEEK_FIELDS_NO_EXPAND looks good to me.
          Hide
          suez1224 Shuyi Chen added a comment -

          Julian Hyde Thanks a lot for reviewing the PR.

          Could you please give an example on how "it makes the names of intervening record-typed fields disappear"?

          Show
          suez1224 Shuyi Chen added a comment - Julian Hyde Thanks a lot for reviewing the PR. Could you please give an example on how "it makes the names of intervening record-typed fields disappear"?
          Hide
          julianhyde Julian Hyde added a comment -

          I reviewed the PR, looks good, and I will commit after 1.13 is released.

          I wonder whether a better name might be PEEK_FIELDS_NO_EXPAND or PEEK_FIELDS_NOGLOB. (Your option still "flattens" to some extent, because it makes the names of intervening record-typed fields disappear.) If you agree, I will make the change as I commit.

          Can you please add a couple of SQL examples to this JIRA case, to serve as documentation.

          Show
          julianhyde Julian Hyde added a comment - I reviewed the PR, looks good, and I will commit after 1.13 is released. I wonder whether a better name might be PEEK_FIELDS_NO_EXPAND or PEEK_FIELDS_NOGLOB. (Your option still "flattens" to some extent, because it makes the names of intervening record-typed fields disappear.) If you agree, I will make the change as I commit. Can you please add a couple of SQL examples to this JIRA case, to serve as documentation.
          Hide
          suez1224 Shuyi Chen added a comment -
          Show
          suez1224 Shuyi Chen added a comment - PR is here: https://github.com/apache/calcite/pull/479

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              suez1224 Shuyi Chen
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development