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

Functions case sensitive when using Lex.MYSQL

    Details

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

      Description

      When I try to write select sum(a) from tbl, Optiq states that sum(TYPE) doesn't exist. However, if I write the same query as select SUM(a) from tbl, Optiq correctly parses the statement. While I expected Lex.MYSQL to not touch/change table and column identifiers, I expected function resolution to still work for functions even if a user uses lower case.

      ---------------- Imported from GitHub ----------------
      Url: https://github.com/julianhyde/optiq/issues/218
      Created by: jacques-n
      Labels:
      Created at: Mon Mar 31 02:57:01 CEST 2014
      State: closed

        Activity

        Hide
        github-import GitHub Import added a comment -

        [Date: Mon Mar 31 19:49:16 CEST 2014, Author: julianhyde]

        By the way, I'm having serious misgivings about naming the lexical convention `MYSQL`. I wanted a convention that stored names as is, then matched them case-insensitively. But here is the <a href="http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html">MySQL doc</a> on tables:

        > [T]he case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case sensitive in Windows, but are case sensitive in most varieties of Unix. One notable exception is Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix.

        On table aliases:

        > By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X. The following statement would not work on Unix, because it refers to the alias both as a and as A:

        ```sql
        SELECT col_name FROM tbl_name AS a
        WHERE a.col_name = 1 OR A.col_name = 2;
        ```

        On column, index, routine and event names:

        > Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases.

        On logfile groups:

        > However, names of logfile groups are case sensitive. This differs from standard SQL.

        On routines (functions and procedures) and others:

        > Object names may be considered duplicates if their uppercase forms are equal according to a binary collation. That is true for names of cursors, conditions, procedures, functions, savepoints, stored routine parameters, stored program local variables, and plugins. It is not true for names of columns, constraints, databases, partitions, statements prepared with PREPARE, tables, triggers, users, and user-defined variables.

        I have no intention of implementing such a baroque set of rules. (I'm sure the MySQL developers would love to get away from them, if they could.)

        We'll either make it clear that Optiq's Lex.MYSQL is not intended to have such a complex set of rules, or if that proves confusing, rename the convention.

        Show
        github-import GitHub Import added a comment - [Date: Mon Mar 31 19:49:16 CEST 2014, Author: julianhyde ] By the way, I'm having serious misgivings about naming the lexical convention `MYSQL`. I wanted a convention that stored names as is, then matched them case-insensitively. But here is the <a href="http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html">MySQL doc</a> on tables: > [T] he case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case sensitive in Windows, but are case sensitive in most varieties of Unix. One notable exception is Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix. On table aliases: > By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X. The following statement would not work on Unix, because it refers to the alias both as a and as A: ```sql SELECT col_name FROM tbl_name AS a WHERE a.col_name = 1 OR A.col_name = 2; ``` On column, index, routine and event names: > Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases. On logfile groups: > However, names of logfile groups are case sensitive. This differs from standard SQL. On routines (functions and procedures) and others: > Object names may be considered duplicates if their uppercase forms are equal according to a binary collation. That is true for names of cursors, conditions, procedures, functions, savepoints, stored routine parameters, stored program local variables, and plugins. It is not true for names of columns, constraints, databases, partitions, statements prepared with PREPARE, tables, triggers, users, and user-defined variables. I have no intention of implementing such a baroque set of rules. (I'm sure the MySQL developers would love to get away from them, if they could.) We'll either make it clear that Optiq's Lex.MYSQL is not intended to have such a complex set of rules, or if that proves confusing, rename the convention.

          People

          • Assignee:
            Unassigned
            Reporter:
            github-import GitHub Import
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development