Derby
  1. Derby
  2. DERBY-4583

TRUE by itself is not accepted in WHERE

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.5.3.0
    • Fix Version/s: 10.7.1.1
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      n/a
    • Bug behavior facts:
      Deviation from standard

      Description

      A SELECT query with a literal boolean value in its WHERE clause results in a syntax error.

      How to reproduce:

      CREATE TABLE list (value VARCHAR(10));

      Given this table, the following queries result in a syntax error.

      SELECT * FROM list WHERE TRUE;
      SELECT * FROM list WHERE FALSE;
      SELECT * FROM list WHERE value='A' OR TRUE;

      Why is TRUE/FALSE not a boolean expression?

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Derby does not support the BOOLEAN data type. See DERBY-499.

          Show
          Knut Anders Hatlen added a comment - Derby does not support the BOOLEAN data type. See DERBY-499 .
          Hide
          Rick Hillegas added a comment -

          Linking this issue to DERBY-499. The TRUE and FALSE literals were removed along with the rest of the BOOLEAN datatype before Cloudscape was open-sourced as Derby. The literals would be re-enabled as part of re-enabling BOOLEAN. It is hard to re-enable these literals without doing a lot of the work described on DERBY-499.

          Show
          Rick Hillegas added a comment - Linking this issue to DERBY-499 . The TRUE and FALSE literals were removed along with the rest of the BOOLEAN datatype before Cloudscape was open-sourced as Derby. The literals would be re-enabled as part of re-enabling BOOLEAN. It is hard to re-enable these literals without doing a lot of the work described on DERBY-499 .
          Hide
          Wouter added a comment -

          Fair enough. But in this particular case Derby actually demands a boolean expression.

          ij(CONNECTION1)> SELECT * FROM list WHERE 1;
          ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.

          Which is rather confusing if there is no support for booleans...

          Show
          Wouter added a comment - Fair enough. But in this particular case Derby actually demands a boolean expression. ij(CONNECTION1)> SELECT * FROM list WHERE 1; ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression. Which is rather confusing if there is no support for booleans...
          Hide
          Kathey Marsden added a comment -

          You should be able to use SELECT * FROM list WHERE 1=1;

          Show
          Kathey Marsden added a comment - You should be able to use SELECT * FROM list WHERE 1=1;
          Hide
          Bryan Pendleton added a comment -

          Or just use SELECT * FROM list. The WHERE clause is optional.

          Show
          Bryan Pendleton added a comment - Or just use SELECT * FROM list. The WHERE clause is optional.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-4583-01-ac-booleanLiterals.diff. This patch re-enables the boolean literals TRUE and FALSE. The regression tests passed for me after applying this patch.

          Touches the following files:

          ---------

          M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj

          Small edit to enable the literals.

          ---------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java

          New test case to verify that the literals work again.

          ---------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
          M java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
          M java/testing/org/apache/derbyTesting/functionTests/master/implicitConversions.out

          Tweaks to other regression tests to reflect the new behavior.

          Show
          Rick Hillegas added a comment - Attaching derby-4583-01-ac-booleanLiterals.diff. This patch re-enables the boolean literals TRUE and FALSE. The regression tests passed for me after applying this patch. Touches the following files: --------- M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Small edit to enable the literals. --------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java New test case to verify that the literals work again. --------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql M java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out M java/testing/org/apache/derbyTesting/functionTests/master/implicitConversions.out Tweaks to other regression tests to reflect the new behavior.
          Hide
          Knut Anders Hatlen added a comment -

          The fix looks good to me, but I was a little puzzled by the update of the master for implicitConversion.sql.

          ij> values true, 'a';
          1


          true
          ERROR 22018: Invalid character string format for type BOOLEAN.
          ij> values 'a', true;
          1


          ERROR 22018: Invalid character string format for type BOOLEAN.

          I would have expected that these statements had failed at compile-time, just like this similar statement:

          ij> values 'a', 1.1;
          ERROR 42X61: Types 'CHAR' and 'DECIMAL' are not UNION compatible.

          Do you know what causes this difference?

          Show
          Knut Anders Hatlen added a comment - The fix looks good to me, but I was a little puzzled by the update of the master for implicitConversion.sql. ij> values true, 'a'; 1 true ERROR 22018: Invalid character string format for type BOOLEAN. ij> values 'a', true; 1 ERROR 22018: Invalid character string format for type BOOLEAN. I would have expected that these statements had failed at compile-time, just like this similar statement: ij> values 'a', 1.1; ERROR 42X61: Types 'CHAR' and 'DECIMAL' are not UNION compatible. Do you know what causes this difference?
          Hide
          Rick Hillegas added a comment -

          Hi Knut,

          Thanks for taking a look at the patch. The short answer is that the compiler merely relies on type information to raise that error. The compiler knows that a numeric can never be cast to a BOOLEAN and so can reject the latter query early on. In contrast, some strings can be cast to BOOLEAN (e.g., 'TRUE' and 'FALSE'). Derby waits until execution-time to check this. It's true that the compiler could be smart enough to run the execution-time checks on string constants. Derby just isn't that smart today.

          Show
          Rick Hillegas added a comment - Hi Knut, Thanks for taking a look at the patch. The short answer is that the compiler merely relies on type information to raise that error. The compiler knows that a numeric can never be cast to a BOOLEAN and so can reject the latter query early on. In contrast, some strings can be cast to BOOLEAN (e.g., 'TRUE' and 'FALSE'). Derby waits until execution-time to check this. It's true that the compiler could be smart enough to run the execution-time checks on string constants. Derby just isn't that smart today.
          Hide
          Knut Anders Hatlen added a comment -

          I see. Thanks for explaining. So the statement below is valid and should return two booleans?

          VALUES 'TRUE', TRUE

          If so, the behaviour makes sense. +1 from me.

          Show
          Knut Anders Hatlen added a comment - I see. Thanks for explaining. So the statement below is valid and should return two booleans? VALUES 'TRUE', TRUE If so, the behaviour makes sense. +1 from me.
          Hide
          Rick Hillegas added a comment -

          Hi Knut,

          I have looked at your question again and I see that the second query does not involve BOOLEANs at all. I have looked at the SQL Standard again and I think that you are right, the UNION between a character type and a boolean type should not work at all and can be rejected at compile-time. That is, Derby should not be performing implicit casts in the query mentioned in DERBY-4684. This is my reasoning:

          1) The rules for determining whether two datatypes are union compatible are stated in the SQL Standard in part 2, section 7.3 (<query expression>), syntax rule 20.b.ii.

          2) That, in turn, refers the reader to section 9.3 (Result of data type combinations).

          3) Section 9.3, syntax rule 3.g says that if either of two values to be merged is BOOLEAN, then both must be BOOLEAN.

          Show
          Rick Hillegas added a comment - Hi Knut, I have looked at your question again and I see that the second query does not involve BOOLEANs at all. I have looked at the SQL Standard again and I think that you are right, the UNION between a character type and a boolean type should not work at all and can be rejected at compile-time. That is, Derby should not be performing implicit casts in the query mentioned in DERBY-4684 . This is my reasoning: 1) The rules for determining whether two datatypes are union compatible are stated in the SQL Standard in part 2, section 7.3 (<query expression>), syntax rule 20.b.ii. 2) That, in turn, refers the reader to section 9.3 (Result of data type combinations). 3) Section 9.3, syntax rule 3.g says that if either of two values to be merged is BOOLEAN, then both must be BOOLEAN.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-4583-01-ad-booleanLiterals.diff. This updates the patch to reflect the changes introduced by DERBY-4692: The implicitConversions test now shows that CHAR and BOOLEAN types are not UNION compatible.

          I plan to commit this patch if the regression tests pass cleanly.

          Show
          Rick Hillegas added a comment - Attaching derby-4583-01-ad-booleanLiterals.diff. This updates the patch to reflect the changes introduced by DERBY-4692 : The implicitConversions test now shows that CHAR and BOOLEAN types are not UNION compatible. I plan to commit this patch if the regression tests pass cleanly.
          Hide
          Rick Hillegas added a comment -

          Committed derby-4583-01-ad-booleanLiterals.diff at subversion revision 952348. The tests ran cleanly for me except for the following error in NetworkServerMBeanTest. The test passed when I re-ran it standalone. I believe that the error is a timing-related heisenbug in that test and is not related to the changes in this patch.

          There was 1 error:
          1) testAttributeAccumulatedConnectionCount(org.apache.derbyTesting.functionTests.tests.management.NetworkServerMBeanTest)java.security.PrivilegedActionException: javax.management.InstanceNotFoundException: org.apache.derby:type=NetworkServer,system=c013800d-0129-1374-c722-ffffe1d7aa3e
          at java.security.AccessController.doPrivileged(Native Method)
          at org.apache.derbyTesting.functionTests.tests.management.MBeanTest.getAttribute(MBeanTest.java:379)
          at org.apache.derbyTesting.functionTests.tests.management.NetworkServerMBeanTest.testAttributeAccumulatedConnectionCount(NetworkServerMBeanTest.java:93)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          Caused by: javax.management.InstanceNotFoundException: org.apache.derby:type=NetworkServer,system=c013800d-0129-1374-c722-ffffe1d7aa3e
          at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getMBean(DefaultMBeanServerInterceptor.java:1010)
          at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getAttribute(DefaultMBeanServerInterceptor.java:627)
          at com.sun.jmx.mbeanserver.JmxMBeanServer.getAttribute(JmxMBeanServer.java:659)
          at org.apache.derbyTesting.functionTests.tests.management.MBeanTest$4.run(MBeanTest.java:382)
          ... 41 more

          Show
          Rick Hillegas added a comment - Committed derby-4583-01-ad-booleanLiterals.diff at subversion revision 952348. The tests ran cleanly for me except for the following error in NetworkServerMBeanTest. The test passed when I re-ran it standalone. I believe that the error is a timing-related heisenbug in that test and is not related to the changes in this patch. There was 1 error: 1) testAttributeAccumulatedConnectionCount(org.apache.derbyTesting.functionTests.tests.management.NetworkServerMBeanTest)java.security.PrivilegedActionException: javax.management.InstanceNotFoundException: org.apache.derby:type=NetworkServer,system=c013800d-0129-1374-c722-ffffe1d7aa3e at java.security.AccessController.doPrivileged(Native Method) at org.apache.derbyTesting.functionTests.tests.management.MBeanTest.getAttribute(MBeanTest.java:379) at org.apache.derbyTesting.functionTests.tests.management.NetworkServerMBeanTest.testAttributeAccumulatedConnectionCount(NetworkServerMBeanTest.java:93) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) Caused by: javax.management.InstanceNotFoundException: org.apache.derby:type=NetworkServer,system=c013800d-0129-1374-c722-ffffe1d7aa3e at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getMBean(DefaultMBeanServerInterceptor.java:1010) at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getAttribute(DefaultMBeanServerInterceptor.java:627) at com.sun.jmx.mbeanserver.JmxMBeanServer.getAttribute(JmxMBeanServer.java:659) at org.apache.derbyTesting.functionTests.tests.management.MBeanTest$4.run(MBeanTest.java:382) ... 41 more
          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Wouter
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development