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

Quantified comparison predicates (SOME, ANY, ALL)

    Details

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

      Description

      Support quantified comparison predicates (SOME, ANY, ALL), per the SQL standard.

      <comp op> ::=
      <equals operator>
      | <not equals operator>
      | <less than operator>
      | <greater than operator>
      | <less than or equals operator>
      | <greater than or equals operator>

      <quantifier> ::=
      <all>
      | <some>

      <all> ::= ALL

      <some> ::=
      SOME
      | ANY

      The result of “R <comp op> <quantifier> T” is derived by the application of the implied <comparison predicate> “R <comp op> RT” to every row RT in T.

      Case:

      • a) If T is empty or if the implied <comparison predicate> is True for every row RT in T, then “R <comp op> <all> T” is True.
      • b) If the implied <comparison predicate> is False for at least one row RT in T, then “R <comp op> <all> T” is False.
      • c) If the implied <comparison predicate> is True for at least one row RT in T, then “R <comp op> <some> T” is True.
      • d) If T is empty or if the implied <comparison predicate> is False for every row RT in T, then “R <comp op> <some> T” is False.
      • e) If “R <comp op> <quantifier> T” is neither True nor False, then it is Unknown.

        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
          julianhyde Julian Hyde added a comment -

          Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/f4746523.

          The fix is null-safe (i.e. returns unknown, as required by the standard, in certain situations where the sub-query returns null values) and could be made a bit more efficient if certain columns have NOT NULL constraints (we could make do with one count rather than two, because the counts will always be the same).

          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/f4746523 . The fix is null-safe (i.e. returns unknown, as required by the standard, in certain situations where the sub-query returns null values) and could be made a bit more efficient if certain columns have NOT NULL constraints (we could make do with one count rather than two, because the counts will always be the same).

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development