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

Implicitly convert character values in comparisons

    Details

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

      Description

      In relational DB such as Postgres, this query works fine.
      "select ... from ... where column (INT type) between '10' and '11'"

      Calcite blocks this query early by the fact that data types "char" & "integer" are not directly compatible. However, this is very common for people to filter columns with date types. For example,

      "...where date between '1911-01-01' and '1911-01-02' "

      To relax this type check when comparing with literals can help improve usability.

        Issue Links

          Activity

          Hide
          julianhyde Julian Hyde added a comment -

          It makes sense to solve these use cases, especially those involving DATE, TIME, TIMESTAMP. I'd do it by making BETWEEN (and other operators such as <) more flexible in the types of argument they support, and then introducing an implicit cast.

          We need to be careful converting numerics. Consider what MySQL does:

          mysql> select '10' || '0' > 900;
          +-------------------+
          | '10' || '0' > 900 |
          +-------------------+
          |                 1 |
          +-------------------+
          1 row in set (0.00 sec)
          
          mysql> select '100' > 900;
          +-------------+
          | '100' > 900 |
          +-------------+
          |           0 |
          +-------------+
          1 row in set (0.00 sec)
          

          Do we really want that mess? I don't think so.

          Show
          julianhyde Julian Hyde added a comment - It makes sense to solve these use cases, especially those involving DATE, TIME, TIMESTAMP. I'd do it by making BETWEEN (and other operators such as <) more flexible in the types of argument they support, and then introducing an implicit cast. We need to be careful converting numerics. Consider what MySQL does: mysql> select '10' || '0' > 900; +-------------------+ | '10' || '0' > 900 | +-------------------+ | 1 | +-------------------+ 1 row in set (0.00 sec) mysql> select '100' > 900; +-------------+ | '100' > 900 | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) Do we really want that mess? I don't think so.
          Hide
          seanhychu Sean Hsuan-Yi Chu added a comment -

          There seems to be some confusion in the first test case.
          (Different from Postgres) in MySql, "||" is interpreted as OR.
          ----------------------------------------------------------------------------------------------------
          So if we type
          select concat('10', '0') > 900;

          We will get zero, which is consistent with

          select '100' > 900;

          Show
          seanhychu Sean Hsuan-Yi Chu added a comment - There seems to be some confusion in the first test case. (Different from Postgres) in MySql, "||" is interpreted as OR. ---------------------------------------------------------------------------------------------------- So if we type select concat('10', '0') > 900; We will get zero, which is consistent with select '100' > 900;
          Hide
          julianhyde Julian Hyde added a comment -

          Ah yes. I had forgotten about MySQL needed to use concat.

          As part of this task, we should decide and document which implicit type conversions are allowed. It would seem bad if we treat literals differently than other expressions, or worse if we treat literals differently based on their content (e.g. "this string looks like a number, so let's implicitly convert it to a number")

          Oracle seems to implicitly convert strings to dates and numerics (see http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements002.htm#g195937 Table 2-10 Implicit Type Conversion Matrix), which makes me happier that it is reasonable for Calcite to do so. One of their examples:

          SELECT last_name
              FROM employees 
              WHERE hire_date = '03-MAR-97';

          We should also track https://issues.apache.org/jira/browse/DRILL-2443, which describes Drill's conversion rules (less applicable, because these rules are applied dynamically not statically).

          Show
          julianhyde Julian Hyde added a comment - Ah yes. I had forgotten about MySQL needed to use concat. As part of this task, we should decide and document which implicit type conversions are allowed. It would seem bad if we treat literals differently than other expressions, or worse if we treat literals differently based on their content (e.g. "this string looks like a number, so let's implicitly convert it to a number") Oracle seems to implicitly convert strings to dates and numerics (see http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements002.htm#g195937 Table 2-10 Implicit Type Conversion Matrix), which makes me happier that it is reasonable for Calcite to do so. One of their examples: SELECT last_name FROM employees WHERE hire_date = '03-MAR-97'; We should also track https://issues.apache.org/jira/browse/DRILL-2443 , which describes Drill's conversion rules (less applicable, because these rules are applied dynamically not statically).
          Hide
          julianhyde Julian Hyde added a comment -

          I have pushed a fix to this issue to https://github.com/julianhyde/incubator-calcite/tree/613. Sean Hsuan-Yi Chu, Please review & test before I commit to master.

          Show
          julianhyde Julian Hyde added a comment - I have pushed a fix to this issue to https://github.com/julianhyde/incubator-calcite/tree/613 . Sean Hsuan-Yi Chu , Please review & test before I commit to master.
          Hide
          seanhychu Sean Hsuan-Yi Chu added a comment -

          Will finish testing & reviewing by tomorrow evening!

          Show
          seanhychu Sean Hsuan-Yi Chu added a comment - Will finish testing & reviewing by tomorrow evening!
          Hide
          seanhychu Sean Hsuan-Yi Chu added a comment -

          The patch looks good.
          Also, I tried a few queries, comparing date columns with literals. They all worked fine.

          Show
          seanhychu Sean Hsuan-Yi Chu added a comment - The patch looks good. Also, I tried a few queries, comparing date columns with literals. They all worked fine.
          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/272e6040 .
          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.2.0-incubating (2015-04-16)

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.2.0-incubating (2015-04-16)

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              seanhychu Sean Hsuan-Yi Chu
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development