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

Support operator "!=" as an alternative to "<>"

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.10.0
    • Component/s: None
    • Labels:

      Issue Links

        Activity

        Hide
        maryannxue Maryann Xue added a comment - - edited

        As I was adding some new tests and verifying existing tests, I found the following code comment in SqlOperatorBaseTest.java. Is or was there any reason that we would not like to allow "!=" as an alternative to "<>", Julian Hyde?

          @Test public void testNotEqualsOperatorIntervals() {
            tester.checkBoolean(
                "interval '2' day <> interval '1' day",
                Boolean.TRUE);
            tester.checkBoolean(
                "interval '2' day <> interval '2' day",
                Boolean.FALSE);
            tester.checkBoolean(
                "interval '2:2:2' hour to second <> interval '2' hour",
                Boolean.TRUE);
            tester.checkNull(
                "cast(null as interval hour) <> interval '2' minute");
        
            // "!=" is not an acceptable alternative to "<>"
            tester.checkFails(
                "1 ^!^= 1",
                "(?s).*Encountered: \"!\" \\(33\\).*",
                false);
          }
        
        Show
        maryannxue Maryann Xue added a comment - - edited As I was adding some new tests and verifying existing tests, I found the following code comment in SqlOperatorBaseTest.java. Is or was there any reason that we would not like to allow "!=" as an alternative to "<>", Julian Hyde ? @Test public void testNotEqualsOperatorIntervals() { tester.checkBoolean( "interval '2' day <> interval '1' day" , Boolean .TRUE); tester.checkBoolean( "interval '2' day <> interval '2' day" , Boolean .FALSE); tester.checkBoolean( "interval '2:2:2' hour to second <> interval '2' hour" , Boolean .TRUE); tester.checkNull( " cast ( null as interval hour) <> interval '2' minute" ); // "!=" is not an acceptable alternative to "<>" tester.checkFails( "1 ^!^= 1" , "(?s).*Encountered: \" !\ " \\(33\\).*" , false ); }
        Hide
        julianhyde Julian Hyde added a comment -

        See also SqlParserTest, which has an even stronger opinion:

          @Test public void testBangEqualIsBad() {
            // Quoth www.ocelot.ca:
            //   "Other relators besides '=' are what you'd expect if
            //   you've used any programming language: > and >= and < and <=. The
            //   only potential point of confusion is that the operator for 'not
            //   equals' is <> as in BASIC. There are many texts which will tell
            //   you that != is SQL's not-equals operator; those texts are false;
            //   it's one of those unstampoutable urban myths."
            checkFails("'abc'^!^=123",
                "Lexical error at line 1, column 6\\.  Encountered: \"!\" \\(33\\), after : \"\"");
          }
        

        I believe we should support "!=", but not under the strict standard conformance level. See CALCITE-1120 for more examples of conformance and how to test it.

        Show
        julianhyde Julian Hyde added a comment - See also SqlParserTest , which has an even stronger opinion: @Test public void testBangEqualIsBad() { // Quoth www.ocelot.ca: // "Other relators besides '=' are what you'd expect if // you've used any programming language: > and >= and < and <=. The // only potential point of confusion is that the operator for 'not // equals' is <> as in BASIC. There are many texts which will tell // you that != is SQL's not-equals operator ; those texts are false ; // it's one of those unstampoutable urban myths." checkFails( "'abc'^!^=123" , "Lexical error at line 1, column 6\\. Encountered: \" !\ " \\(33\\), after : \" \""); } I believe we should support "!=", but not under the strict standard conformance level. See CALCITE-1120 for more examples of conformance and how to test it.
        Hide
        maryannxue Maryann Xue added a comment -

        I made this option into the ORACLE_10 conformance mode. Could you please review the patch, Julian Hyde?

        Show
        maryannxue Maryann Xue added a comment - I made this option into the ORACLE_10 conformance mode. Could you please review the patch, Julian Hyde ?
        Hide
        julianhyde Julian Hyde added a comment -

        Will do. In future, can you submit a PR or (since you're a committer) a link to a github branch? It's easier on this end.

        Show
        julianhyde Julian Hyde added a comment - Will do. In future, can you submit a PR or (since you're a committer) a link to a github branch? It's easier on this end.
        Hide
        julianhyde Julian Hyde added a comment -

        +1 when you've done these.

        Show
        julianhyde Julian Hyde added a comment - Can you add to http://calcite.apache.org/docs/reference.html#comparison-operators , noting that it is only available at some conformance levels; and also in the precedence table. There are errors when you run mvn javadoc:javadoc javadoc:test-javadoc under JDK 1.8. Please fix them. (Please also try under JDK 1.7.) +1 when you've done these.
        Hide
        maryannxue Maryann Xue added a comment -

        Fixed in https://git1-us-west.apache.org/repos/asf?p=calcite.git;a=commit;h=c5bd9d2. Thank you very much for the review and for find the issues, Julian Hyde! Javadoc with JDK 1.8 fixed and JDK 1.7 verified. Also updated the SQL reference page.

        Show
        maryannxue Maryann Xue added a comment - Fixed in https://git1-us-west.apache.org/repos/asf?p=calcite.git;a=commit;h=c5bd9d2 . Thank you very much for the review and for find the issues, Julian Hyde ! Javadoc with JDK 1.8 fixed and JDK 1.7 verified. Also updated the SQL reference page.
        Hide
        jcamachorodriguez Jesus Camacho Rodriguez added a comment -

        Resolved in release 1.10.0 (2016-10-12).

        Show
        jcamachorodriguez Jesus Camacho Rodriguez added a comment - Resolved in release 1.10.0 (2016-10-12).

          People

          • Assignee:
            maryannxue Maryann Xue
            Reporter:
            maryannxue Maryann Xue
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development