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

Case-insensitive matching of sub-query columns fails

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.0.0-incubating
    • Component/s: None
    • Labels:
      None

      Description

      Currently, the default LEX in Calcite is LEX.ORACLE, and most unit test cases are using LEX.ORACLE. However, when LEX is set to be MYSQL/SQL_SERVER etc, Calcite would complain "field/column not found error" in subquery, while the MYSQL/SQL_SERVER policy should allow such query.

      For example, given the following query, with LEX.MYSQL.

      select DID 
      from (select deptid as did 
               FROM
                  ( values (1), (2) ) as T1(deptid) 
               ) 
      

      Calcite would raise the following error:

      Caused by: java.lang.AssertionError: Internal error: Type 'RecordType(INTEGER did)' has no field ‘DID'
      

      According to LEX.MYSQL, the unquoted sql identifier should remain unchanged, and matched with case-insensitive, hence the query is valid.

       /** Lexical policy similar to MySQL. (To be precise: MySQL on Windows;
         * MySQL on Linux uses case-sensitive matching, like the Linux file system.)
         * The case of identifiers is preserved whether or not they quoted;
         * after which, identifiers are matched case-insensitively.
         * Back-ticks allow identifiers to contain non-alphanumeric characters. */
        MYSQL(Quoting.BACK_TICK, Casing.UNCHANGED, Casing.UNCHANGED, false),
      

        Activity

        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/0caa1c54 .
        Hide
        vladimirsitnikov Vladimir Sitnikov added a comment -

        Julian Hyde,
        Do you know

        +    CalciteAssert.that()
        +        .with(CalciteAssert.Config.REGULAR)
        +        .with(ImmutableMap.of("lex", "SQL_SERVER"))
        

        is not performing as one might read it?
        See relevant CALCITE-489.

        The second with(ImmutableMap call creates brand-new assert object and the .with(CalciteAssert.Config.REGULAR) part is not used.

        Show
        vladimirsitnikov Vladimir Sitnikov added a comment - Julian Hyde , Do you know + CalciteAssert.that() + .with(CalciteAssert.Config.REGULAR) + .with(ImmutableMap.of( "lex" , "SQL_SERVER" )) is not performing as one might read it? See relevant CALCITE-489 . The second with(ImmutableMap call creates brand-new assert object and the .with(CalciteAssert.Config.REGULAR) part is not used.
        Hide
        julianhyde Julian Hyde added a comment -

        Ah, good catch. I don't think it affects this case - the default config is REGULAR anyway - but CALCITE-489 is a hole in our testing strategy and I will fix today.

        Show
        julianhyde Julian Hyde added a comment - Ah, good catch. I don't think it affects this case - the default config is REGULAR anyway - but CALCITE-489 is a hole in our testing strategy and I will fix today.
        Hide
        julianhyde Julian Hyde added a comment -

        Closing now that 1.0.0-incubating has been released.

        Show
        julianhyde Julian Hyde added a comment - Closing now that 1.0.0-incubating has been released.

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            jni Jinfeng Ni
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development