/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.BooleanValuesTest

   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to you under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

     http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

 */

package org.apache.derbyTesting.functionTests.tests.lang;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import junit.framework.Test;
import org.apache.derby.iapi.types.HarmonySerialBlob;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;
import org.apache.derbyTesting.junit.XML;

/**
 * <p>
 * Test Derby's expanding support for BOOLEAN values.
 * </p>
 */
public class BooleanValuesTest  extends GeneratedColumnsHelper
{
    ///////////////////////////////////////////////////////////////////////////////////
    //
    // CONSTANTS
    //
    ///////////////////////////////////////////////////////////////////////////////////

    private static final String ILLEGAL_GET = "22005";
    private static final String ILLEGAL_XML_SELECTION = "42Z71";
    private static final String BAD_CAST = "22018";
    private static final String NOT_UNION_COMPATIBLE = "42X61";
    private static final String BAD_CONVERSION = "42846";
    private static final String ILLEGAL_INSERT = "42821";
    private static final String BAD_DEFAULT = "42894";
    private static final String ILLEGAL_UPDATE = "XCL12";
    private static final String NON_BOOLEAN_OPERAND = "42Y94";

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // STATE
    //
    ///////////////////////////////////////////////////////////////////////////////////

    private boolean _supportsXML;

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // CONSTRUCTOR
    //
    ///////////////////////////////////////////////////////////////////////////////////


    /**
     * Create a new instance.
     */

    public BooleanValuesTest(String name)
    {
        super(name);

        _supportsXML = XML.classpathMeetsXMLReqs();
    }

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // JUnit BEHAVIOR
    //
    ///////////////////////////////////////////////////////////////////////////////////


    /**
     * Construct top level suite in this JUnit test
     */
    public static Test suite()
    {
        Test result = (BaseTestSuite)TestConfiguration.defaultSuite(
            BooleanValuesTest.class);

        return result;
    }

    protected void    setUp()
        throws Exception
    {
        super.setUp();

        Connection conn = getConnection();

        if ( !routineExists( conn, "MAKESIMPLEBLOB" ) )
        {
            goodStatement
                (
                 conn,
                 "create function makeSimpleBlob( ) returns blob\n" +
                 "language java parameter style java no sql deterministic\n" +
                 "external name 'org.apache.derbyTesting.functionTests.tests.lang.BooleanValuesTest.makeSimpleBlob'\n"
                 );
        }
        if ( !tableExists( conn, "ALL_TYPES" ) )
        {
            StringBuffer buffer;

            //
            // create table
            //
            buffer = new StringBuffer();
            buffer.append
                (
                 "create table all_types\n" +
                 "(\n" +
                 "    key_col int,\n" +
                 "    bigint_col  BIGINT,\n" +
                 "    blob_col  BLOB(2147483647),\n" +
                 "    char_col  CHAR(10),\n" +
                 "    char_for_bit_data_col  CHAR (10) FOR BIT DATA,\n" +
                 "    clob_col  CLOB(2147483647),\n" +
                 "    date_col  DATE,\n" +
                 "    decimal_col  DECIMAL(5,2),\n" +
                 "    real_col  REAL,\n" +
                 "    double_col  DOUBLE,\n" +
                 "    int_col  INTEGER,\n" +
                 "    long_varchar_col  LONG VARCHAR,\n" +
                 "    long_varchar_for_bit_data_col  LONG VARCHAR FOR BIT DATA,\n" +
                 "    numeric_col  NUMERIC(5,2), \n" +
                 "    smallint_col  SMALLINT,\n" +
                 "    time_col  TIME,\n" +
                 "    timestamp_col  TIMESTAMP,\n" +
                 "    varchar_col  VARCHAR(10),\n" +
                "    varchar_for_bit_data_col  VARCHAR (10) FOR BIT DATA\n"
                 );
            if ( _supportsXML )
            {
                buffer.append( "    ,xml_col  XML\n" );
            }
            buffer.append( ")\n" );
            goodStatement( conn, buffer.toString() );

            //
            // populate table
            //
            buffer = new StringBuffer();
            buffer.append
                (
                 "insert into all_types\n" +
                 "(\n" +
                 "    key_col,\n" +
                 "    bigint_col,\n" +
                 "    blob_col,\n" +
                 "    char_col,\n" +
                 "    char_for_bit_data_col,\n" +
                 "    clob_col,\n" +
                 "    date_col,\n" +
                 "    decimal_col,\n" +
                 "    real_col,\n" +
                 "    double_col,\n" +
                 "    int_col,\n" +
                 "    long_varchar_col,\n" +
                 "    long_varchar_for_bit_data_col,\n" +
                 "    numeric_col, \n" +
                 "    smallint_col,\n" +
                 "    time_col,\n" +
                 "    timestamp_col,\n" +
                 "    varchar_col,\n" +
                 "    varchar_for_bit_data_col\n"
                 );
            if ( _supportsXML )
            {
                buffer.append( "    ,xml_col\n" );
            }
            buffer.append
                (
                 ")\n" +
                 "values\n" +
                 "(\n" +
                 "    0,\n" +
                 "    0,\n" +
                 "    makeSimpleBlob(),\n" +
                 "    '0',\n" +
                 "    X'DE',\n" +
                 "    '0',\n" +
                 "    date('1994-02-23'),\n" +
                 "    0.00,\n" +
                 "    0.0,\n" +
                 "    0.0,\n" +
                 "    0,\n" +
                 "    '0',\n" +
                 "    X'DE',\n" +
                 "    0.00, \n" +
                 "    0,\n" +
                 "    time('15:09:02'),\n" +
                 "    timestamp('1962-09-23 03:23:34.234'),\n" +
                 "    '0',\n" +
                 "    X'DE'\n"
                 );
            if ( _supportsXML )
            {
                buffer.append( "    , xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html/>' preserve whitespace )\n" );
            }
            buffer.append
                (
                 "),\n" +
                 "(\n" +
                 "    1,\n" +
                 "    1,\n" +
                 "    makeSimpleBlob(),\n" +
                 "    '1',\n" +
                 "    X'DE',\n" +
                 "    '1',\n" +
                 "    date('1994-02-23'),\n" +
                 "    1.00,\n" +
                 "    1.0,\n" +
                 "    1.0,\n" +
                 "    1,\n" +
                 "    '1',\n" +
                 "    X'DE',\n" +
                 "    1.00, \n" +
                 "    1,\n" +
                 "    time('15:09:02'),\n" +
                 "    timestamp('1962-09-23 03:23:34.234'),\n" +
                 "    '1',\n" +
                 "    X'DE'\n"
                 );
            if ( _supportsXML )
            {
                buffer.append( "    , xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html/>' preserve whitespace )\n"  );
            }
            buffer.append
                 (
                 "),\n" +
                 "(\n" +
                 "    2,\n" +
                 "    2,\n" +
                 "    makeSimpleBlob(),\n" +
                 "    '2',\n" +
                 "    X'DE',\n" +
                 "    '2',\n" +
                 "    date('1994-02-23'),\n" +
                 "    2.00,\n" +
                 "    2.0,\n" +
                 "    2.0,\n" +
                 "    2,\n" +
                 "    '2',\n" +
                 "    X'DE',\n" +
                 "    2.00, \n" +
                 "    2,\n" +
                 "    time('15:09:02'),\n" +
                 "    timestamp('1962-09-23 03:23:34.234'),\n" +
                 "    '2',\n" +
                 "    X'DE'\n"
                  );
            if ( _supportsXML )
            {
                buffer.append( "    , xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html/>' preserve whitespace )\n" );
            }
            buffer.append
                (
                 "),\n" +
                 "(\n" +
                 "    3,\n" +
                 "    3,\n" +
                 "    makeSimpleBlob(),\n" +
                 "    'baffle',\n" +
                 "    X'DE',\n" +
                 "    'baffle',\n" +
                 "    date('1994-02-23'),\n" +
                 "    3.00,\n" +
                 "    3.0,\n" +
                 "    3.0,\n" +
                 "    3,\n" +
                 "    'baffle',\n" +
                 "    X'DE',\n" +
                 "    3.00, \n" +
                 "    3,\n" +
                 "    time('15:09:02'),\n" +
                 "    timestamp('1962-09-23 03:23:34.234'),\n" +
                 "    'baffle',\n" +
                 "    X'DE'\n"
                 );
            if ( _supportsXML )
            {
                buffer.append( "    , xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html/>' preserve whitespace )\n" );
            }
            buffer.append
                (
                 ")\n"
                 );
            goodStatement( conn, buffer.toString() );
        }
        
        if ( !tableExists( conn, "BOOLEANSTRING" ) )
        {
            //
            // create table
            //
            goodStatement( conn, "create table booleanString( keyCol int, stringCol varchar( 20 ) )" );

            //
            // populate it
            //
            goodStatement
                (
                 conn,
                 "insert into booleanString( keyCol, stringCol )\n" +
                 "values ( 0, 'false' ), ( 1, 'true' ), ( 2, null ), ( 3, 'unknown' ), ( 10, ' false ' ), ( 11, ' true ' ), ( 12, null ), ( 13, ' unknown ' )\n"
                 );
        }

        if ( !tableExists( conn, "T_4704" ) )
        {
            //
            // create table
            //
            goodStatement( conn, "create table t_4704( keyCol int, stringCol varchar( 20 ) not null )" );

            //
            // populate it
            //
            goodStatement
                (
                 conn,
                 "insert into t_4704( keyCol, stringCol )\n" +
                 "values ( 0, 'false' ), ( 1, 'true' ), ( 2, 'unknown' )\n"
                 );
        }

        if ( !tableExists( conn, "STRING_TYPES" ) )
        {
            //
            // create table
            //
            goodStatement
                (
                 conn,
                 "create table string_types\n" +
                 "(\n" +
                 "    key_col int,\n" +
                 "    char_col  CHAR(10),\n" +
                 "    clob_col  CLOB(2147483647),\n" +
                 "    long_varchar_col  LONG VARCHAR,\n" +
                 "    varchar_col  VARCHAR(10)\n" +
                 ")\n"
                 );

            //
            // populate it
            //
            goodStatement
                (
                 conn,
                 "insert into string_types\n" +
                 "( key_col, char_col, clob_col, long_varchar_col, varchar_col )\n" +
                 "values\n" +
                 "( 0, 'false', 'false', 'false', 'false' ),\n" +
                 "( 1, 'true', 'true', 'true', 'true' ),\n" +
                 "( 2, 'unknown', 'unknown', 'unknown', 'unknown' ),\n" +
                 "( 3, null, null, null, null ),\n" +
                 "( 4, ' false ', ' false ', ' false ', ' false ' ),\n" +
                 "( 5, ' true ', ' true ', ' true ', ' true ' ),\n" +
                 "( 6, ' unknown ', ' unknown ', ' unknown ', ' unknown ' ),\n" +
                 "( 7, null, null, null, null ),\n" +
                 "( 10, 'FALSE', 'FALSE', 'FALSE', 'FALSE' ),\n" +
                 "( 11, 'TRUE', 'TRUE', 'TRUE', 'TRUE' ),\n" +
                 "( 12, 'UNKNOWN', 'UNKNOWN', 'UNKNOWN', 'UNKNOWN' ),\n" +
                 "( 13, NULL, NULL, NULL, NULL ),\n" +
                 "( 14, ' FALSE ', ' FALSE ', ' FALSE ', ' FALSE ' ),\n" +
                 "( 15, ' TRUE ', ' TRUE ', ' TRUE ', ' TRUE ' ),\n" +
                 "( 16, ' UNKNOWN ', ' UNKNOWN ', ' UNKNOWN ', ' UNKNOWN ' ),\n" +
                 "( 17, NULL, NULL, NULL, NULL ),\n" +
                 "( 20, 'arg', 'arg', 'arg', 'arg' ),\n" +
                 "( 21, '0', '0', '0', '0' ),\n" +
                 "( 22, '1', '1', '1', '1' ),\n" +
                 "( 23, '2', '2', '2', '2' )\n"
                 );
        }
        
        if ( !routineExists( conn, "BOOLEANVALUE" ) )
        {
            //
            // create function
            //
            goodStatement
                (
                 conn,
                 "create function booleanValue( b boolean )\n" +
                 "returns varchar( 100 ) language java parameter style java no sql\n" +
                 "external name 'org.apache.derbyTesting.functionTests.tests.lang.BooleanValuesTest.booleanValue'\n"
                 );
        }
        
        if ( !tableExists( conn, "BOOLEAN_TABLE" ) )
        {
            //
            // create table
            //
            goodStatement
                (
                 conn,
                 "create table boolean_table\n" +
                 "(\n" +
                 "    key_col int,\n" +
                 "    boolean_col  boolean\n" +
                 ")\n"
                 );
        }
        
        if ( !tableExists( conn, "T_4889" ) )
        {
            //
            // create table
            //
            goodStatement
                (
                 conn,
                 "create table t_4889\n" +
                 "(\n" +
                 "    key_col int,\n" +
                 "    setter_col varchar( 20 ),\n" +
                 "    boolean_col  boolean\n" +
                 ")\n"
                 );
        }
    }


    ///////////////////////////////////////////////////////////////////////////////////
    //
    // TESTS
    //
    ///////////////////////////////////////////////////////////////////////////////////

    /**
     * <p>
     * Verify the number of datatypes supported by Derby. If this number changes,
     * then you need to add a new datatype to this test:
     * </p>
     *
     * <ul>
     * <li>Add a new column to ALL_TYPES and corresponding rows (see setUp())</li>
     * <li>Add the new datatype to one of the tests below</li>
     * <li>Add a new bad union case to test_06_unions()</li>
     * <li>Add a new bad explicit cast to test_09_explicitCasts()</li>
     * </ul>
     */
    public void test_01_datatypeCount() throws Exception
    {
        Connection conn = getConnection();

        ResultSet rs = conn.getMetaData().getTypeInfo();
        int actualTypeCount = 0;
        while ( rs.next() ) { actualTypeCount++; }
        rs.close();

        assertEquals( 22, actualTypeCount );
    }

    /**
     * <p>
     * Test that ResultSet.getBoolean() behaves well for the datatypes for
     * which the JDBC spec defines correct values: CHAR, VARCHAR, TINYINT,
     * SMALLINT, INTEGER, BIGINT.
     * </p>
     */
    public void test_02_defined() throws Exception
    {
        Connection conn = getConnection();

        vet_getBoolean( conn, "BIGINT_COL" );
        vet_getBoolean( conn, "CHAR_COL" );
        vet_getBoolean( conn, "INT_COL" );
        vet_getBoolean( conn, "SMALLINT_COL" );
        vet_getBoolean( conn, "VARCHAR_COL" );
    }
    /**
     * <p>
     * Verifies Derby's behavior is the same for embedded and client
     * drivers on datatypes for which the JDBC spec does not define results
     * but which Derby handles.
     * </p>
     */
    public void test_03_undefinedButLegal() throws Exception
    {
        Connection conn = getConnection();

        vet_getBoolean( conn, "DECIMAL_COL" );
        vet_getBoolean( conn, "REAL_COL" );
        vet_getBoolean( conn, "DOUBLE_COL" );
        vet_getBoolean( conn, "LONG_VARCHAR_COL" );
        vet_getBoolean( conn, "NUMERIC_COL" );
    }
    /**
     * <p>
     * Verify Derby's behavior is the same for embedded and client
     * drivers on datatypes for which the JDBC spec does not define results
     * and which Derby does not handle.
     * </p>
     */
    public void test_04_undefinedAndIllegal() throws Exception
    {
        Connection conn = getConnection();

        vet_getBooleanIsIllegal( conn, "BLOB_COL" );
        vet_getBooleanIsIllegal( conn, "CHAR_FOR_BIT_DATA_COL" );
        vet_getBooleanIsIllegal( conn, "CLOB_COL" );
        vet_getBooleanIsIllegal( conn, "DATE_COL" );
        vet_getBooleanIsIllegal( conn, "LONG_VARCHAR_FOR_BIT_DATA_COL" );
        vet_getBooleanIsIllegal( conn, "TIME_COL" );
        vet_getBooleanIsIllegal( conn, "TIMESTAMP_COL" );
        vet_getBooleanIsIllegal( conn, "VARCHAR_FOR_BIT_DATA_COL" );
    }
    
    /**
     * <p>
     * Regression tests for outliers. If this behavior changes,
     * hopefully the new behavior will fit into one of the categories above.
     * </p>
     */
    public void test_05_undefinedIllegalOutliers() throws Exception
    {
        //
        // We don't test the XML datatype on JDK 1.4 because the Xalan
        // packages are in the wrong location there.
        //
        if ( _supportsXML )
        {
            expectCompilationError
                (
                 ILLEGAL_XML_SELECTION,
                 makeQuery( "XML_COL" )
                 );
        }
    }

    /**
     * <p>
     * Test that ResultSet.getBoolean() returns  the correct value. Expects to
     * select a result set with the following rows:
     * </p>
     *
     * <pre>
     * 0       0
     * 1       1
     * 2       2
     * 3       for string columns, something other than a number
     * </pre>
     */
    private void vet_getBoolean( Connection conn, String columnName ) throws Exception
    {
        PreparedStatement ps = chattyPrepare( conn, makeQuery( columnName ) );
        ResultSet rs = ps.executeQuery();

        // 0 => getBoolean() = false
        rs.next();
        assertFalse( rs.getBoolean( columnName ) );

        // 1 => getBoolean() = true
        rs.next();
        assertTrue( rs.getBoolean( columnName ) );

        // 2 => getBoolean() = true
        //
        // If the value is not 0 or 1, the JDBC spec leaves it up to the implementation.
        // For Derby, the answer is  ( value != 0 )
        rs.next();
        assertTrue( rs.getBoolean( columnName ) );

        // non-number => getBoolean() = true
        //
        // If the value is not 0 or 1, the JDBC spec leaves it up to the implementation.
        // For Derby, the answer is  ( value != 0 )
        rs.next();
        assertTrue( rs.getBoolean( columnName ) );

        rs.close();
        ps.close();
    }
    private String makeQuery( String columnName )
    {
        return "select key_col, " + columnName + " from all_types order by key_col";
    }
    /**
     * <p>
     * Test that ResultSet.getBoolean() raises an error on datatypes which
     * Derby doesn't handle.
     * </p>
     */
    private void vet_getBooleanIsIllegal( Connection conn, String columnName ) throws Exception
    {
        vet_getBooleanException( conn, columnName, ILLEGAL_GET );
    }
    /**
     * <p>
     * Test that ResultSet.getBoolean() raises the expected error.
     * </p>
     */
    private void vet_getBooleanException( Connection conn, String columnName, String sqlstate ) throws Exception
    {
        PreparedStatement ps = chattyPrepare( conn, makeQuery( columnName ) );
        ResultSet rs = ps.executeQuery();

        rs.next();

        try {
            rs.getBoolean( columnName );
            fail( "getBoolean() on " + columnName + " should not have succeeded." );
        }
        catch (SQLException se)
        {
            assertSQLState( "getBoolean() on " + columnName, sqlstate, se );
        }

    }
    
    /**
     * <p>
     * Test that unions fail if one but not both sides of the union is BOOLEAN. The rules for union
     * compatibility are found in the SQL Standard, part 2, section 7.3 (<query expression>),
     * syntax rule 20.b.ii. That in turn, refers you to section 9.3 (Result of data type combinations).
     * See, for instance, <a href="https://issues.apache.org/jira/browse/DERBY-4692">DERBY-4692</a>.This enforces the rules found 
     * </p>
     */
    public void test_06_unions() throws Exception
    {
        Connection conn = getConnection();

        vetBadImplicitCasts( conn, "bigint_col" );
        vetBadImplicitCasts( conn, "blob_col" );
        vetBadImplicitCasts( conn, "char_col" );
        vetBadImplicitCasts( conn, "char_for_bit_data_col" );
        vetBadImplicitCasts( conn, "clob_col" );
        vetBadImplicitCasts( conn, "date_col" );
        vetBadImplicitCasts( conn, "decimal_col" );
        vetBadImplicitCasts( conn, "real_col" );
        vetBadImplicitCasts( conn, "double_col" );
        vetBadImplicitCasts( conn, "int_col" );
        vetBadImplicitCasts( conn, "long_varchar_col" );
        vetBadImplicitCasts( conn, "long_varchar_for_bit_data_col" );
        vetBadImplicitCasts( conn, "numeric_col" );
        vetBadImplicitCasts( conn, "smallint_col" );
        vetBadImplicitCasts( conn, "time_col" );
        vetBadImplicitCasts( conn, "timestamp_col" );
        vetBadImplicitCasts( conn, "varchar_col" );
        vetBadImplicitCasts( conn, "varchar_for_bit_data_col" );
        if ( _supportsXML ) { vetBadImplicitCasts( conn, "xml_col" ); }
    }
    private void vetBadImplicitCasts( Connection conn, String columnName ) throws Exception
    {
        vetBadImplicitCastToBoolean( conn, columnName );
        vetBadImplicitCastFromBoolean( conn, columnName );
    }
    private void vetBadImplicitCastToBoolean( Connection conn, String columnName ) throws Exception
    {
        String query =
            "select isindex from sys.sysconglomerates where conglomeratename = 'foo'\n" +
            "union\n" +
            "select " + columnName + " from all_types\n";
        
        expectCompilationError( NOT_UNION_COMPATIBLE, query );
    }
    private void vetBadImplicitCastFromBoolean( Connection conn, String columnName ) throws Exception
    {
        String query =
            "select " + columnName + " from all_types\n" +
            "union\n" +
            "select isindex from sys.sysconglomerates\n";
        
        expectCompilationError( NOT_UNION_COMPATIBLE, query );
    }

    /**
     * <p>
     * Verify that boolean literals work.
     * </p>
     */
    public void test_07_booleanLiterals() throws Exception
    {
        Connection conn = getConnection();

        assertResults
            (
             conn,
             "values ( true )",
             new String[][]
             {
                 { "true" },
             },
             false
             );
        assertResults
            (
             conn,
             "values ( false )",
             new String[][]
             {
                 { "false" },
             },
             false
             );
    }
    
    /**
     * <p>
     * Verify that explicit casts to and from string work as expected.
     * </p>
     */
    public void test_08_stringCasts() throws Exception
    {
        Connection conn = getConnection();

        assertResults( conn, "values ( cast( 'true' as boolean ) )", new String[][] { { "true" } }, false );
        assertResults( conn, "values ( cast( 'false' as boolean ) )", new String[][] { { "false" } }, false );
        assertResults( conn, "values ( cast( null as boolean ) )", new String[][] { { null } }, false );
        assertResults( conn, "values ( cast( 'unknown' as boolean ) )", new String[][] { { null } }, false );
        assertResults( conn, "values ( cast( ' true ' as boolean ) )", new String[][] { { "true" } }, false );
        assertResults( conn, "values ( cast( ' false ' as boolean ) )", new String[][] { { "false" } }, false );
        assertResults( conn, "values ( cast( ' unknown ' as boolean ) )", new String[][] { { null } }, false );

        expectCompilationError( BAD_CAST, "values ( cast( 'arglebargle' as boolean ) )" );
        expectCompilationError( BAD_CAST, "values ( cast( '1' as boolean ) )" );
        expectCompilationError( BAD_CAST, "values ( cast( '0' as boolean ) )" );
        expectCompilationError( BAD_CAST, "values ( cast( '2' as boolean ) )" );

        assertResults
            (
             conn,
             "select keyCol, cast( stringCol as boolean) from booleanString order by keyCol",
             new String[][]
             {
                 { "0", "false" },
                 { "1", "true" },
                 { "2", null },
                 { "3", null },
                 { "10", "false" },
                 { "11", "true" },
                 { "12", null },
                 { "13", null },
             },
             false
             );
        
        goodStatement( conn, "update booleanString set stringCol = 'arglebargle'" );
        expectExecutionError( conn, BAD_CAST, "select keyCol, cast( stringCol as boolean) from booleanString order by keyCol" );

        goodStatement( conn, "update booleanString set stringCol = '0'" );
        expectExecutionError( conn, BAD_CAST, "select keyCol, cast( stringCol as boolean) from booleanString order by keyCol" );

        goodStatement( conn, "update booleanString set stringCol = '1'" );
        expectExecutionError( conn, BAD_CAST, "select keyCol, cast( stringCol as boolean) from booleanString order by keyCol" );

        goodStatement( conn, "update booleanString set stringCol = '2'" );
        expectExecutionError( conn, BAD_CAST, "select keyCol, cast( stringCol as boolean) from booleanString order by keyCol" );

    }
    
    /**
     * <p>
     * Verify that most explicit casts to and from boolean are not allowed.
     * </p>
     */
    public void test_09_explicitCasts() throws Exception
    {
        Connection conn = getConnection();

        vetBadExplicitCasts( conn, "bigint_col", "BIGINT", "0" );
        vetBadExplicitCasts( conn, "blob_col", "BLOB(2147483647)", "makeSimpleBlob()" );
        // CHAR ok
        vetBadExplicitCasts( conn, "char_for_bit_data_col", "CHAR (10) FOR BIT DATA", "X'DE'" );
        // CLOB ok
        vetBadExplicitCasts( conn, "date_col", "DATE", "date('1994-02-23')" );
        vetBadExplicitCasts( conn, "decimal_col", "DECIMAL(5,2)", "0.00" );
        vetBadExplicitCasts( conn, "real_col", "REAL", "0.0" );
        vetBadExplicitCasts( conn, "double_col", "DOUBLE", "0.0" );
        vetBadExplicitCasts( conn, "int_col", "INTEGER", "0" );
        // LONG VARCHAR ok
        vetBadExplicitCasts( conn, "long_varchar_for_bit_data_col", "LONG VARCHAR FOR BIT DATA", "X'DE'" );
        vetBadExplicitCasts( conn, "numeric_col", "NUMERIC(5,2)", "0.00" );
        vetBadExplicitCasts( conn, "smallint_col", "SMALLINT", "0" );
        vetBadExplicitCasts( conn, "time_col", "TIME", "time('15:09:02')" );
        vetBadExplicitCasts( conn, "timestamp_col", "TIMESTAMP", "timestamp('1962-09-23 03:23:34.234')" );
        // VARCHAR ok
        vetBadExplicitCasts( conn, "varchar_for_bit_data_col", "VARCHAR (10) FOR BIT DATA", "X'DE'" );
        if ( _supportsXML ) { vetBadExplicitCasts( conn, "xml_col", "XML", "xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html/>' preserve whitespace )" ); }

        assertResults
            (
             conn,
             "select key_col, cast( char_col as boolean ), cast( clob_col as boolean ), cast( long_varchar_col as boolean ), cast( varchar_col as boolean )\n" +
             "from string_types where key_col < 18 order by key_col\n",
             new String[][]
             {
                 { "0", "false", "false", "false", "false", },
                 { "1", "true", "true", "true", "true", },
                 { "2", null, null, null, null, },
                 { "3", null, null, null, null, },
                 { "4", "false", "false", "false", "false", },
                 { "5", "true", "true", "true", "true", },
                 { "6", null, null, null, null, },
                 { "7", null, null, null, null, },
                 { "10", "false", "false", "false", "false", },
                 { "11", "true", "true", "true", "true", },
                 { "12", null, null, null, null, },
                 { "13", null, null, null, null, },
                 { "14", "false", "false", "false", "false", },
                 { "15", "true", "true", "true", "true", },
                 { "16", null, null, null, null, },
                 { "17", null, null, null, null, },
             },
             false
             );

        assertResults
            (
             conn,
             "values ( cast( true as boolean) )\n",
             new String[][]
             {
                 { "true" },
             },
             false
             );

        vetBadStringCast( conn, "char_col" );
        vetBadStringCast( conn, "clob_col" );
        vetBadStringCast( conn, "long_varchar_col" );
        vetBadStringCast( conn, "varchar_col" );

        vet4704();
    }
    private void vetBadStringCast( Connection conn, String columnName ) throws Exception
    {
        for ( int i = 20; i < 24; i++ )
        {
            expectExecutionError( conn, BAD_CAST, "select cast( " + columnName + " as boolean ) from string_types where key_col = " + i );
        }
    }
    private void vetBadExplicitCasts( Connection conn, String columnName, String dataType, String literal ) throws Exception
    {
        vetBadExplicitCastToBoolean( conn, columnName, literal );
        vetBadExplicitCastFromBoolean( conn, dataType );
    }
    private void vetBadExplicitCastToBoolean( Connection conn, String columnName, String literal ) throws Exception
    {
        expectCompilationError( BAD_CONVERSION, "values ( cast( " + literal + " as boolean ) )" );
        expectCompilationError( BAD_CONVERSION, "select cast( " + columnName + " as boolean ) from all_types\n" );
    }
    private void vetBadExplicitCastFromBoolean( Connection conn, String dataType ) throws Exception
    {
        expectCompilationError( BAD_CONVERSION, "values ( cast( true as " + dataType + " ) )" );
        expectCompilationError( BAD_CONVERSION, "select cast( isindex as " + dataType + " ) from sys.sysconglomerates" );
    }

    /**
     * Regression test case for DERBY-4704. When casting non-nullable VARCHAR
     * columns to BOOLEAN, the result column was marked as non-nullable, even
     * though the VARCHAR could contain the value 'UNKNOWN', in which case
     * the cast should return NULL.
     */
    public void vet4704()
            throws SQLException
    {
        Statement s = createStatement();

        ResultSet rs = s.executeQuery( "select keyCol, stringCol from t_4704 order by keyCol" );
        JDBC.assertNullability(rs, new boolean[] { true, false });

        rs = s.executeQuery( "select keyCol, cast(stringCol as boolean) from t_4704 order by keyCol" );
        JDBC.assertNullability(rs, new boolean[] { true, true });
        JDBC.assertFullResultSet
            (
             rs,
             new String[][]
             {
                 { "0", "false" },
                 { "1", "true" },
                 { "2", null },
             }
             );
    }

    public void test_10_nullabilityOfCastFromLiteral() throws SQLException {
        Statement s = createStatement();

        ResultSet rs = s.executeQuery("values cast('true' as boolean)");
        JDBC.assertNullability(rs, new boolean[] { false });
        JDBC.assertSingleValueResultSet(rs, "true");

        rs = s.executeQuery("values cast('false' as boolean)");
        JDBC.assertNullability(rs, new boolean[] { false });
        JDBC.assertSingleValueResultSet(rs, "false");

        rs = s.executeQuery("values cast('unknown' as boolean)");
        JDBC.assertNullability(rs, new boolean[] { true });
        JDBC.assertSingleValueResultSet(rs, null);

        rs = s.executeQuery(
                "values (cast('true' as boolean),\n" +
                "        cast('false' as boolean),\n" +
                "        cast('unknown' as boolean))");
        JDBC.assertNullability(rs, new boolean[] { false, false, true });
        JDBC.assertFullResultSet(
                rs, new String[][] {{ "true", "false", null }});

        rs = s.executeQuery(
                "values (cast ('true' as boolean)),\n" +
                "       (cast ('false' as boolean)),\n" +
                "       (cast ('unknown' as boolean))");
        JDBC.assertNullability(rs, new boolean[] { true });
        JDBC.assertFullResultSet(
                rs, new String[][] { {"true"}, {"false"}, {null} });
    }

    /**
     * <p>
     * Verify that boolean function parameters behave well.
     * </p>
     */
    public void test_11_booleanFunctionParameters() throws Exception
    {
        Connection conn = getConnection();

        assertResults
            (
             conn,
             "values ( booleanValue( true ) ), ( booleanValue( false ) ), ( booleanValue( null ) )",
             new String[][]
             {
                 { "True value", },
                 { "False value", },
                 { "Null value", },
             },
             false
             );

        PreparedStatement ps = chattyPrepare( conn, "values ( booleanValue( ? ) )" );

        ps.setBoolean( 1, true );
        assertScalarResult( ps, "True value" );
        
        ps.setBoolean( 1, false );
        assertScalarResult( ps, "False value" );
        
        ps.setNull( 1, Types.BOOLEAN );
        assertScalarResult( ps, "Null value" );

        ps.close();
    }
    private void assertScalarResult( PreparedStatement ps, String result ) throws Exception
    {
        ResultSet rs = ps.executeQuery();
        assertResults( rs, new String[][] {{ result } }, false );
        rs.close();        
    }
                                                                                
    /**
     * <p>
     * Verify that tables with boolean columns behave well.
     * </p>
     */
    public void test_12_booleanColumns() throws Exception
    {
        Connection conn = getConnection();

        goodStatement( conn, "insert into boolean_table( key_col, boolean_col ) values ( 0, true ), ( 1, false ), ( 2, null )" );
        assertResults
            (
             conn,
             "select * from boolean_table order by boolean_col",
             new String[][]
             {
                 { "1", "false" },
                 { "0", "true" },
                 { "2", null },
             },
             false
             );
        goodStatement( conn, "delete from boolean_table" );

        vetBadInsert( "bigint_col" );
        vetBadInsert( "blob_col" );
        vetBadInsert( "char_for_bit_data_col" );
        vetBadInsert( "date_col" );
        vetBadInsert( "decimal_col" );
        vetBadInsert( "real_col" );
        vetBadInsert( "double_col" );
        vetBadInsert( "int_col" );
        vetBadInsert( "long_varchar_for_bit_data_col" );
        vetBadInsert( "numeric_col" );
        vetBadInsert( "smallint_col" );
        vetBadInsert( "time_col" );
        vetBadInsert( "timestamp_col" );
        vetBadInsert( "varchar_for_bit_data_col" );

        vetStringInsert( conn, "char_col" );
        vetStringInsert( conn, "clob_col" );
        vetStringInsert( conn, "long_varchar_col" );
        vetStringInsert( conn, "varchar_col" );
        
    }
    private void vetBadInsert( String columnName ) throws Exception
    {
        expectCompilationError
            (
             ILLEGAL_INSERT,
             "insert into boolean_table( key_col, boolean_col ) select key_col, " + columnName + " from all_types"
             );
    }
    private void vetStringInsert( Connection conn, String columnName ) throws Exception
    {
        goodStatement( conn, "insert into boolean_table( key_col, boolean_col ) select key_col, " + columnName + " from string_types where key_col < 18" );
        assertResults
            (
             conn,
             "select * from boolean_table order by key_col",
             new String[][]
             {
                 { "0", "false" },
                 { "1", "true" },
                 { "2", null },
                 { "3", null },
                 { "4", "false" },
                 { "5", "true" },
                 { "6", null },
                 { "7", null },
                 { "10", "false" },
                 { "11", "true" },
                 { "12", null },
                 { "13", null },
                 { "14", "false" },
                 { "15", "true" },
                 { "16", null },
                 { "17", null },
             },
             false
             );
        
        for ( int i = 20; i < 24; i++ )
        {
            expectExecutionError
                ( conn, BAD_CAST, "insert into boolean_table( key_col, boolean_col ) select key_col, " + columnName + " from string_types where key_col = " + i );
        }

        goodStatement( conn, "delete from boolean_table" );
    }
    
    /**
     * <p>
     * Verify that boolean nulls sort at the end with or without an index,
     * the behavior of other datatypes.
     * </p>
     */
    public void test_13_sortOrder() throws Exception
    {
        Connection conn = getConnection();

        goodStatement( conn, "create table booleanUnindexed( a boolean )" );
        goodStatement( conn, "create table booleanIndexed( a boolean )" );
        goodStatement( conn, "create index bi on booleanIndexed( a )" );

        goodStatement( conn, "insert into booleanUnindexed( a ) values ( true ), ( null ), ( false )" );
        goodStatement( conn, "insert into booleanIndexed( a ) values ( true ), ( null ), ( false )" );

        String[][] expectedResults = new String[][]
        {
            { "false" },
            { "true" },
            { null },
        };
        
        assertResults
            (
             conn,
             "select * from booleanUnindexed order by a",
             expectedResults,
             false
             );
        assertResults
            (
             conn,
             "select * from booleanIndexed order by a",
             expectedResults,
             false
             );
        
        goodStatement( conn, "drop table booleanUnindexed" );
        goodStatement( conn, "drop table booleanIndexed" );
    }

    /**
     * <p>
     * Verify that you can declare literal defaults for boolean columns.
     * </p>
     */
    public void test_14_defaults() throws Exception
    {
        Connection conn = getConnection();

        goodStatement( conn, "create table booleanDefaults( a int, b boolean default true, c boolean default false, d boolean default null )" );
        goodStatement( conn, "insert into booleanDefaults( a ) values ( 0 )" );
        assertResults
            (
             conn,
             "select * from booleanDefaults order by a",
             new String[][]
             {
                 { "0", "true", "false", null }
             },
             false
             );
        
        goodStatement( conn, "alter table booleanDefaults add column e boolean default true" );
        assertResults
            (
             conn,
             "select * from booleanDefaults order by a",
             new String[][]
             {
                 { "0", "true", "false", null, "true" }
             },
             false
             );

        goodStatement( conn, "drop table booleanDefaults" );

        expectCompilationError( BAD_DEFAULT, "create table badDefault( a int, b boolean default 0 )" );
        expectCompilationError( BAD_DEFAULT, "create table badDefault( a int, b boolean default 9.99 )" );
        expectCompilationError( BAD_DEFAULT, "create table badDefault( a int, b boolean default 5e1 )" );
        expectCompilationError( BAD_DEFAULT, "create table badDefault( a int, b boolean default 'false' )" );
        expectCompilationError( BAD_DEFAULT, "create table badDefault( a int, b boolean default X'DE' )" );
    }
    
    /**
     * <p>
     * Verify that you can have boolean expressions in the SELECT list.
     * </p>
     */
    public void test_15_selectList() throws Exception
    {
        Connection conn = getConnection();

        assertResults
            (
             conn,
             "select columnnumber > 0 from sys.syscolumns where columnname = 'TABLENAME'",
             new String[][]
             {
                 { "true" }
             },
             false
             );
        
        assertResults
            (
             conn,
             "values 3 > 10",
             new String[][]
             {
                 { "false" }
             },
             false
             );
    }

    /**
     * <p>
     * Verify that you can alter a table and add boolean columns.
     * </p>
     */
    public void test_16_alterTable() throws Exception
    {
        Connection conn = getConnection();

        goodStatement( conn, "create table booleanAlter( a int )" );
        goodStatement( conn, "insert into booleanAlter( a ) values ( 0 ), ( 1 ), ( 2 )" );
        goodStatement( conn, "alter table booleanAlter add column b boolean" );
        goodStatement( conn, "alter table booleanAlter add column c boolean default true" );
        goodStatement( conn, "alter table booleanAlter add column d boolean default false" );
        goodStatement( conn, "alter table booleanAlter add column e boolean default null" );        
        assertResults
            (
             conn,
             "select * from booleanAlter order by a",
             new String[][]
             {
                 { "0", null, "true", "false", null },
                 { "1", null, "true", "false", null },
                 { "2", null, "true", "false", null },
             },
             false
             );
        
        goodStatement( conn, "alter table booleanAlter drop column b" );        
        assertResults
            (
             conn,
             "select * from booleanAlter order by a",
             new String[][]
             {
                 { "0", "true", "false", null },
                 { "1", "true", "false", null },
                 { "2", "true", "false", null },
             },
             false
             );

        goodStatement( conn, "drop table booleanAlter" );
    }

    /**
     * <p>
     * Verify that you can create boolean-valued generated columns.
     * </p>
     */
    public void test_17_generatedColumns() throws Exception
    {
        Connection conn = getConnection();

        goodStatement
            (
             conn,
             "create table tree\n" +
             "(\n" +
             "    treeID int primary key generated always as identity,\n" +
             "    latinName varchar( 100 ),\n" +
             "    commonName varchar( 100 ),\n" +
             "    waterPerWeek int,\n" +
             "    minimumSunNeeded int,\n" +
             "    maximumSunTolerated int,\n" +
             "    windTolerated int,\n" +
             "    isHardy boolean generated always as( (waterPerWeek < 10) and (minimumSunNeeded < 5) and (maximumSunTolerated > 90) and (windTolerated > 100) )\n" +
             ")\n"
             );
        generatedMinion( conn );

        // repeat the experiment with an implicitly typed generated column
        goodStatement
            (
             conn,
             "create table tree\n" +
             "(\n" +
             "    treeID int primary key generated always as identity,\n" +
             "    latinName varchar( 100 ),\n" +
             "    commonName varchar( 100 ),\n" +
             "    waterPerWeek int,\n" +
             "    minimumSunNeeded int,\n" +
             "    maximumSunTolerated int,\n" +
             "    windTolerated int,\n" +
             "    isHardy generated always as( (waterPerWeek < 10) and (minimumSunNeeded < 5) and (maximumSunTolerated > 90) and (windTolerated > 100) )\n" +
             ")\n"
             );
        generatedMinion( conn );
    }
    private void generatedMinion( Connection conn ) throws Exception
    {
        goodStatement( conn, "create index hardyTrees on tree( treeID, isHardy )" );
        goodStatement
            (
             conn,
             "insert into tree( latinName, commonName, waterPerWeek, minimumSunNeeded, maximumSunTolerated, windTolerated )\n" +
             "values\n" +
             "( 'tristania conferta', 'brisbane boxwood', 7, 4, 95, 120 ),\n" +
             "( 'acer rubrum', 'red maple', 20, 20, 95, 120 )\n"
             );
        
        assertResults
            (
             conn,
             "select * from tree order by treeID",
             new String[][]
             {
                 { "1", "tristania conferta", "brisbane boxwood", "7", "4", "95", "120", "true" },
                 { "2", "acer rubrum", "red maple", "20", "20", "95", "120", "false" },
             },
             false
             );
        assertResults
            (
             conn,
             "select * from tree where isHardy order by treeID",
             new String[][]
             {
                 { "1", "tristania conferta", "brisbane boxwood", "7", "4", "95", "120", "true" },
             },
             false
             );
        
        goodStatement( conn, "drop table tree" );
    }

    /**
     * <p>
     * Verify views with boolean columns.
     * </p>
     */
    public void test_18_views() throws Exception
    {
        Connection conn = getConnection();

        goodStatement
            (
             conn,
             "create table item\n" +
             "(\n" +
             "    itemID int primary key generated always as identity,\n" +
             "    itemName varchar( 100 ),\n" +
             "    quantityOnHand int,\n" +
             "    quantityOrdered int\n" +
             ")\n"
             );
        goodStatement
            (
             conn,
             "create view orderView( itemID, needsRestocking )\n" +
             "    as select itemID, quantityOrdered > quantityOnHand from item\n"
             );
        goodStatement
            (
             conn,
             "insert into item( itemName, quantityOnHand, quantityOrdered )\n" +
             "values\n" +
             "( 'Bracelet', 100, 200 ),\n" +
             "( 'Glasses', 200, 100 )\n"
             );

        assertResults
            (
             conn,
             "select * from orderView order by itemID",
             new String[][]
             {
                 { "1", "true" },
                 { "2", "false" },
             },
             false
             );
        
        goodStatement( conn, "drop view orderView" );
        goodStatement( conn, "drop table item" );
    }
    
    /**
     * <p>
     * Verify booleans in foreign keys.
     * </p>
     */
    public void test_19_foreignKeys() throws Exception
    {
        Connection conn = getConnection();

        goodStatement
            (
             conn,
             "create table keys\n" +
             "(\n" +
             "    userName varchar( 100 ),\n" +
             "    publicVersion boolean,\n" +
             "    keyValue varchar( 1000 ),\n" +
             "    primary key( publicVersion, userName )\n" +
             ")\n"
            );
        goodStatement
            (
             conn,
             "insert into keys( userName, publicVersion, keyValue )\n" +
             "values\n" +
             "( 'Robert', true, 'abcdefghij' ),\n" +
             "( 'Robert', false, 'mnopqrstuv' )\n"
            );
        goodStatement
            (
             conn,
             "create table messages\n" +
             "(\n" +
             "    messageID int primary key generated always as identity,\n" +
             "    userName varchar( 100 ),\n" +
             "    publicVersion boolean,\n" +
             "    messageText clob,\n" +
             "    foreign key ( publicVersion, userName ) references keys( publicVersion, userName )\n" +
             ")\n"
             );
        goodStatement
            (
             conn,
             "insert into messages( userName, publicVersion, messageText )\n" +
             "values\n" +
             "( 'Robert', false, 'Twas brillig' )\n"
             );

        assertResults
            (
             conn,
             "select * from messages order by messageID",
             new String[][]
             {
                 { "1", "Robert", "false", "Twas brillig" },
             },
             false
             );
        
        goodStatement( conn, "drop table messages" );
        goodStatement( conn, "drop table keys" );
    }
    
    /**
     * <p>
     * Verify booleans in check constraints.
     * </p>
     */
    public void test_20_checkConstraints() throws Exception
    {
        Connection conn = getConnection();

        goodStatement
            (
             conn,
             "create table remes\n" +
             "(\n" +
             "    remesID int primary key generated always as identity,\n" +
             "    name varchar( 100 ),\n" +
             "    isInsect boolean,\n" +
             "    isArachnid boolean,\n" +
             "    check ( isInsect or isArachnid )\n" +
             ")\n"
            );
        goodStatement
            (
             conn,
             "insert into remes( name, isInsect, isArachnid )\n" +
             "values\n" +
             "( 'black widow', false, true ),\n" +
             "( 'house fly', true, false )\n"
            );
        
        assertResults
            (
             conn,
             "select * from remes order by remesID",
             new String[][]
             {
                 { "1", "black widow", "false", "true" },
                 { "2", "house fly", "true", "false" },
             },
             false
             );
        
        goodStatement( conn, "drop table remes" );
    }

    /**
     * <p>
     * Verify triggers on boolean columns.
     * </p>
     */
    public void test_21_triggers() throws Exception
    {
        Connection conn = getConnection();

        goodStatement
            (
             conn,
             "create table alumnus\n" +
             "(\n" +
             "    alumnusID int primary key generated always as identity,\n" +
             "    name varchar( 100 ),\n" +
             "    living boolean\n" +
             ")\n"
            );
        goodStatement
            (
             conn,
             "create table livenessChange\n" +
             "(\n" +
             "    eventID int primary key generated always as identity,\n" +
             "    alumnusID int, \n" +
             "    living boolean\n" +
             ")\n"
            );
        goodStatement
            (
             conn,
             "create trigger trig_after_update_row_trigger\n" +
             "after update of living\n" +
             "on alumnus\n" +
             "referencing new as newRow\n" +
             "for each row\n" +
             "insert into livenessChange( alumnusID, living ) values ( newRow.alumnusID, newRow.living )\n"
            );
        goodStatement( conn, "insert into alumnus( name, living ) values ( 'Fred', true ), ( 'Monica', true )" );
        goodStatement( conn, "update alumnus set living = false where name = 'Fred'" );

        
        assertResults
            (
             conn,
             "select * from livenessChange order by eventID",
             new String[][]
             {
                 { "1", "1", "false" },
             },
             false
             );

        goodStatement( conn, "drop table alumnus" );
        goodStatement( conn, "drop table livenessChange" );
    }

    /**
     * Test that the binary operators return the expected values for all
     * possible combinations of operands.
     */
    public void test_22_binary_operators() throws SQLException {
        setAutoCommit(false);

        // Create a table with two columns containing all combinations of
        // legal boolean values.
        Statement s = createStatement();
        s.execute("create table combos(b1 boolean, b2 boolean)");
        assertUpdateCount(s, 9,
                "insert into combos select * from "
                + "(values true, false, cast(null as boolean)) v1, "
                + "(values true, false, cast(null as boolean)) v2");

        // Equals operator (=)
        JDBC.assertFullResultSet(
                s.executeQuery(
                    "select b1, b2, b1 = b2 from combos order by 1, 2"),
                new String[][] {
                    { "false", "false", "true"  },
                    { "false", "true" , "false" },
                    { "false",  null  ,  null   },
                    { "true" , "false", "false" },
                    { "true" , "true" , "true"  },
                    { "true" ,  null  ,  null   },
                    {  null  , "false",  null   },
                    {  null  , "true" ,  null   },
                    {  null  ,  null  ,  null   },
                });

        // Not equals operator (<>)
        JDBC.assertFullResultSet(
                s.executeQuery(
                    "select b1, b2, b1 <> b2 from combos order by 1, 2"),
                new String[][] {
                    { "false", "false", "false" },
                    { "false", "true" , "true"  },
                    { "false",  null  ,  null   },
                    { "true" , "false", "true"  },
                    { "true" , "true" , "false" },
                    { "true" ,  null  ,  null   },
                    {  null  , "false",  null   },
                    {  null  , "true" ,  null   },
                    {  null  ,  null  ,  null   },
                });

        // Less than operator (<)
        JDBC.assertFullResultSet(
                s.executeQuery(
                    "select b1, b2, b1 < b2 from combos order by 1, 2"),
                new String[][] {
                    { "false", "false", "false" },
                    { "false", "true" , "true"  },
                    { "false",  null  ,  null   },
                    { "true" , "false", "false" },
                    { "true" , "true" , "false" },
                    { "true" ,  null  ,  null   },
                    {  null  , "false",  null   },
                    {  null  , "true" ,  null   },
                    {  null  ,  null  ,  null   },
                });

        // Greater than operator (>)
        JDBC.assertFullResultSet(
                s.executeQuery(
                    "select b1, b2, b1 > b2 from combos order by 1, 2"),
                new String[][] {
                    { "false", "false", "false" },
                    { "false", "true" , "false" },
                    { "false",  null  ,  null   },
                    { "true" , "false", "true"  },
                    { "true" , "true" , "false" },
                    { "true" ,  null  ,  null   },
                    {  null  , "false",  null   },
                    {  null  , "true" ,  null   },
                    {  null  ,  null  ,  null   },
                });

        // Less than or equals operator (<=)
        JDBC.assertFullResultSet(
                s.executeQuery(
                    "select b1, b2, b1 <= b2 from combos order by 1, 2"),
                new String[][] {
                    { "false", "false", "true"  },
                    { "false", "true" , "true"  },
                    { "false",  null  ,  null   },
                    { "true" , "false", "false" },
                    { "true" , "true" , "true"  },
                    { "true" ,  null  ,  null   },
                    {  null  , "false",  null   },
                    {  null  , "true" ,  null   },
                    {  null  ,  null  ,  null   },
                });

        // Greater than or equals operator (>=)
        JDBC.assertFullResultSet(
                s.executeQuery(
                    "select b1, b2, b1 >= b2 from combos order by 1, 2"),
                new String[][] {
                    { "false", "false", "true"  },
                    { "false", "true" , "false" },
                    { "false",  null  ,  null   },
                    { "true" , "false", "true"  },
                    { "true" , "true" , "true"  },
                    { "true" ,  null  ,  null   },
                    {  null  , "false",  null   },
                    {  null  , "true" ,  null   },
                    {  null  ,  null  ,  null   },
                });

        // AND operator
        JDBC.assertFullResultSet(
                s.executeQuery(
                    "select b1, b2, b1 and b2 from combos order by 1, 2"),
                new String[][] {
                    { "false", "false", "false" },
                    { "false", "true" , "false" },
                    { "false",  null  , "false" },
                    { "true" , "false", "false" },
                    { "true" , "true" , "true"  },
                    { "true" ,  null  ,  null   },
                    {  null  , "false", "false" },
                    {  null  , "true" ,  null   },
                    {  null  ,  null  ,  null   },
                });

        // OR operator
        JDBC.assertFullResultSet(
                s.executeQuery(
                    "select b1, b2, (b1 or b2) from combos order by 1, 2"),
                new String[][] {
                    { "false", "false", "false" },
                    { "false", "true" , "true"  },
                    { "false",  null  ,  null   },
                    { "true" , "false", "true"  },
                    { "true" , "true" , "true"  },
                    { "true" ,  null  , "true"  },
                    {  null  , "false",  null   },
                    {  null  , "true" , "true"  },
                    {  null  ,  null  ,  null   },
                });
    }

    /**
     * Verify that SELECT DISTINCT works as expected on boolean columns.
     */
    public void test_23_select_distinct() throws SQLException {
        setAutoCommit(false);

        Statement s = createStatement();
        assertUpdateCount(s, 6, "insert into boolean_table(boolean_col) "
                + "values true, true, false, false, null, null");

        JDBC.assertUnorderedResultSet(
            s.executeQuery("select distinct boolean_col from boolean_table"),
            new String[][] { {"true"}, {"false"}, {null} });
    }
    
    /**
     * <p>
     * Verify fix for DERBY-4890.
     * </p>
     */
    public void test_4890() throws Exception
    {
        Connection conn = getConnection();

        goodStatement( conn, "delete from boolean_table" );

        PreparedStatement ps = chattyPrepare( conn, "values ( cast ( ? as boolean ), cast ( ? as boolean ) )" );
        ps.setString( 1, "true" );
        ps.setString( 2, "false" );
        ResultSet rs = ps.executeQuery();

        rs.next();
        assertTrue( rs.getBoolean( 1 ) );
        assertFalse( rs.getBoolean( 2 ) );

        rs.close();
        ps.close();

        ps = chattyPrepare
            (
             conn,
             "insert into boolean_table( key_col, boolean_col ) values ( 1, ? ), ( 2, ? )"
             );
        ps.setString( 1, "true" );
        ps.setString( 2, "false" );
        ps.execute();

        assertResults
            (
             conn,
             "select * from boolean_table order by key_col",
             new String[][]
             {
                 { "1",  "true" },
                 { "2",  "false" },
             },
             false
             );

        goodStatement( conn, "delete from boolean_table" );
    }
    
    /**
     * <p>
     * Verify fix for DERBY-4890.
     * </p>
     */
    public void test_4889() throws Exception
    {
        Connection conn = getConnection();

        minion_4889( conn, 0, false );
        minion_4889( conn, 1, true );
        minion_4889( conn, 2, true );
    }
    private void minion_4889( Connection conn, int value, boolean expectedBooleanResult )
        throws Exception
    {
        goodStatement( conn, "delete from t_4889" );
        
        PreparedStatement ps = chattyPrepare
            (
             conn,
             "insert into t_4889( key_col, setter_col, boolean_col ) values ( ?, ?, ? )"
             );

        ps.setInt( 1, 1 );
        ps.setString( 2, "setByte" );
        ps.setByte( 3, (byte) value );
        ps.execute();

        ps.setInt( 1, 2 );
        ps.setString( 2, "setShort" );
        ps.setShort( 3, (short) value );
        ps.execute();

        ps.setInt( 1, 3 );
        ps.setString( 2, "setInt" );
        ps.setInt( 3, value );
        ps.execute();

        ps.setInt( 1, 4 );
        ps.setString( 2, "setLong" );
        ps.setLong( 3, (long) value );
        ps.execute();

        ps.setInt( 1, 5 );
        ps.setString( 2, "setObject( Byte )" );
        ps.setObject( 3, (byte) value );
        ps.execute();

        ps.setInt( 1, 6 );
        ps.setString( 2, "setObject( Short )" );
        ps.setObject( 3, (short) value );
        ps.execute();

        ps.setInt( 1, 7 );
        ps.setString( 2, "setObject( Integer )" );
        ps.setObject( 3, value );
        ps.execute();

        ps.setInt( 1, 8 );
        ps.setString( 2, "setObject( Long )" );
        ps.setObject( 3, (long) value );
        ps.execute();

        String stringValue = Boolean.toString( (value != 0) );
        assertResults
            (
             conn,
             "select * from t_4889 order by key_col",
             new String[][]
             {
                 { "1",  "setByte", stringValue },
                 { "2",  "setShort", stringValue },
                 { "3",  "setInt", stringValue },
                 { "4",  "setLong", stringValue },
                 { "5",  "setObject( Byte )", stringValue },
                 { "6",  "setObject( Short )", stringValue },
                 { "7",  "setObject( Integer )", stringValue },
                 { "8",  "setObject( Long )", stringValue },
             },
             false
             );

    }

    /**
     * Verify fix for DERBY-4964 - failure to convert string to boolean with
     * the client driver.
     */
    public void test_4964() throws SQLException {
        PreparedStatement ps = prepareStatement("values cast(? as boolean)");

        // These should work
        test_4964_minion(ps, null, null);
        test_4964_minion(ps, "unknown", null);
        test_4964_minion(ps, "UNKNOWN", null);
        test_4964_minion(ps, "unKnoWn", null);
        test_4964_minion(ps, "  unknown  ", null);
        test_4964_minion(ps, "true", Boolean.TRUE);
        test_4964_minion(ps, "TRUE", Boolean.TRUE);
        test_4964_minion(ps, "TrUe", Boolean.TRUE);
        test_4964_minion(ps, "  true  ", Boolean.TRUE);
        test_4964_minion(ps, "false", Boolean.FALSE);
        test_4964_minion(ps, "FALSE", Boolean.FALSE);
        test_4964_minion(ps, "FaLsE", Boolean.FALSE);
        test_4964_minion(ps, "FaLsE", Boolean.FALSE);
        test_4964_minion(ps, "  false  ", Boolean.FALSE);

        // These should fail
        test_4964_minion(ps, "0", BAD_CAST);
        test_4964_minion(ps, "1", BAD_CAST);
        test_4964_minion(ps, "2", BAD_CAST);
        test_4964_minion(ps, "null", BAD_CAST);
        test_4964_minion(ps, "true true", BAD_CAST);
        test_4964_minion(ps, "false false", BAD_CAST);
    }

    /**
     * Set a boolean parameter using a string value and verify that we get
     * the expected result.
     *
     * @param ps a prepared statement that takes a boolean parameter
     * @param input input string for the parameter
     * @param expectedValue the expected result; either the expected Boolean return
     * value if the operation is expected to succeed, or the SQLState of the
     * exception if it is expected to fail
     */
    private void test_4964_minion(
            PreparedStatement ps, String input, Object expectedValue)
        throws SQLException
    {
        // If the expected value is a string, it denotes the SQLState of an
        // expected failure
        boolean shouldFail = expectedValue instanceof String;

        Object[][] rows = { { expectedValue } };

        // test setString(int, String)
        try {
            ps.setString(1, input);
            JDBC.assertFullResultSet(ps.executeQuery(), rows, false);
            assertFalse(shouldFail);
        } catch (SQLException sqle) {
            if (shouldFail) {
                assertSQLState((String) expectedValue, sqle);
            } else {
                throw sqle;
            }
        }

        // test setObject(int, Object)
        try {
            ps.setObject(1, input);
            JDBC.assertFullResultSet(ps.executeQuery(), rows, false);
            assertFalse(shouldFail);
        } catch (SQLException sqle) {
            if (shouldFail) {
                assertSQLState((String) expectedValue, sqle);
            } else {
                throw sqle;
            }
        }

        // test setObject(int, Object, int) with various target types
        int[] types = { Types.BIT, Types.BOOLEAN, Types.CHAR, Types.VARCHAR };
        for (int i = 0; i < types.length; i++) {
            try {
                ps.setObject(1, input, types[i]);
                JDBC.assertFullResultSet(ps.executeQuery(), rows, false);
                assertFalse(shouldFail);
            } catch (SQLException sqle) {
                if (shouldFail) {
                    assertSQLState((String) expectedValue, sqle);
                } else {
                    throw sqle;
                }
            }
        }
    }

    /**
     * Verify fix for DERBY-4965 - conversion from boolean to char results
     * in 1/0 instead of true/false.
     */
    public void test_4965() throws SQLException {
        String[] stringTypes = { "CHAR(10)", "VARCHAR(10)", "LONG VARCHAR" };
        for (int i = 0; i < stringTypes.length; i++) {
            PreparedStatement ps = prepareStatement(
                    "values cast(? as " + stringTypes[i] + ")");

            // Test setBoolean()
            ps.setBoolean(1, true);
            JDBC.assertSingleValueResultSet(ps.executeQuery(), "true");
            ps.setBoolean(1, false);
            JDBC.assertSingleValueResultSet(ps.executeQuery(), "false");

            // Test setObject(int, Object)
            ps.setObject(1, Boolean.TRUE);
            JDBC.assertSingleValueResultSet(ps.executeQuery(), "true");
            ps.setObject(1, Boolean.FALSE);
            JDBC.assertSingleValueResultSet(ps.executeQuery(), "false");

            // Test setObject(int, Object, int)
            int[] targetTypes = {
                Types.BIT, Types.BOOLEAN,
                Types.CHAR, Types.VARCHAR, Types.LONGVARCHAR,
            };
            for (int j = 0; j < targetTypes.length; j++) {
                ps.setObject(1, Boolean.TRUE, targetTypes[j]);
                JDBC.assertSingleValueResultSet(ps.executeQuery(), "true");
                ps.setObject(1, Boolean.FALSE, targetTypes[j]);
                JDBC.assertSingleValueResultSet(ps.executeQuery(), "false");
            }
        }

        String[] intTypes = { "SMALLINT", "INTEGER", "BIGINT" };
        for (int i = 0; i < intTypes.length; i++) {
            PreparedStatement ps = prepareStatement(
                    "values cast(? as " + intTypes[i] + ")");

            // Test setBoolean()
            ps.setBoolean(1, true);
            JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
            ps.setBoolean(1, false);
            JDBC.assertSingleValueResultSet(ps.executeQuery(), "0");

            // Test setObject(int, Object)
            ps.setObject(1, Boolean.TRUE);
            JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
            ps.setObject(1, Boolean.FALSE);
            JDBC.assertSingleValueResultSet(ps.executeQuery(), "0");

            // Test setObject(int, Object, int)
            int[] targetTypes = {
                Types.BIT, Types.BOOLEAN,
                Types.SMALLINT, Types.INTEGER, Types.BIGINT,
            };
            for (int j = 0; j < targetTypes.length; j++) {
                ps.setObject(1, Boolean.TRUE, targetTypes[j]);
                JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
                ps.setObject(1, Boolean.FALSE, targetTypes[j]);
                JDBC.assertSingleValueResultSet(ps.executeQuery(), "0");
            }
        }
    }

    /**
     * Verify fix for DERBY-5042, where updateBoolean() and updateObject()
     * would fail on a BOOLEAN column when using the client driver.
     */
    public void test_5042_updateBoolean() throws SQLException {
        setAutoCommit(false);

        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                      ResultSet.CONCUR_UPDATABLE);
        s.execute("create table derby5042(b boolean, i int, c char(10))");

        ResultSet rs = s.executeQuery("select * from derby5042");

        // Test updateBoolean() on various column types
        rs.moveToInsertRow();
        rs.updateBoolean("B", true); // Used to fail with client driver
        rs.updateBoolean("I", true);
        rs.updateBoolean("C", true);
        rs.insertRow();

        // Test updateObject() with a java.lang.Boolean on various column types
        rs.moveToInsertRow();
        rs.updateObject("B", Boolean.FALSE); // Used to fail with client driver
        rs.updateObject("I", Boolean.FALSE);
        rs.updateObject("C", Boolean.FALSE);
        rs.insertRow();

        rs.close();

        JDBC.assertFullResultSet(
                s.executeQuery("select * from derby5042 order by 1,2,3"),
                new String[][]{
                    {"false", "0", "false"},
                    {"true", "1", "true"}});
    }

    /**
     * Verify fix for DERBY-5063 - updateBytes() should fail when invoked
     * on boolean columns.
     */
    public void test_5063_updateBytes() throws SQLException {
        setAutoCommit(false);

        Statement s = createStatement();
        s.execute("create table derby5063(b boolean)");

        PreparedStatement ps = prepareStatement("select b from derby5063",
                ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

        byte[] bytes = "abc".getBytes();

        // Test updateBytes()
        ResultSet rs = ps.executeQuery();
        rs.moveToInsertRow();
        try {
            rs.updateBytes(1, bytes);
            fail("updateBytes should fail");
        } catch (SQLException sqle) {
            assertSQLState(ILLEGAL_UPDATE, sqle);
        }
        rs.close();

        // setObject() should also fail when the argument is a byte array
        rs = ps.executeQuery();
        rs.moveToInsertRow();
        try {
            rs.updateObject(1, bytes);
            fail("updateObject should fail");
        } catch (SQLException sqle) {
            assertSQLState(ILLEGAL_UPDATE, sqle);
        }
        rs.close();
    }

    /**
     * Verify that you can use CREATE TABLE AS SELECT to create
     * empty tables with BOOLEAN columns.
     */
    public void test_5918() throws Exception
    {
        setAutoCommit(false);

        Statement s = createStatement();
        s.execute("create table derby5918_1(b boolean)");
        s.execute("create table derby5918_2 as select * from derby5918_1 with no data");
        s.executeUpdate("insert into derby5918_2 values ( true )");
        
        assertResults
            (
             getConnection(),
             "select * from derby5918_2",
             new String[][]
             {
                 { "true" },
             },
             false
             );
    }

    /**
     * Verify that you can use AND and OR expressions as BOOLEAN values.
     * Regression test case for DERBY-5972.
     */
    public void test_5972() throws SQLException {
        // Disable auto-commit so that tearDown() can roll back any changes.
        setAutoCommit(false);

        Statement s = createStatement();

        // Test boolean expressions in select list. Used to fail with syntax
        // errors with OR expressions.
        JDBC.assertSingleValueResultSet(
                s.executeQuery("select true and false from sysibm.sysdummy1"),
                "false");
        JDBC.assertSingleValueResultSet(
                s.executeQuery("select true or false from sysibm.sysdummy1"),
                "true");
        assertCompileError(NON_BOOLEAN_OPERAND,
                "select 1 and 2 from sysibm.sysdummy1");
        assertCompileError(NON_BOOLEAN_OPERAND,
                "select 1 or 2 from sysibm.sysdummy1");

        // Test boolean expressions in VALUES statements. Used to fail with
        // syntax errors with OR expressions.
        JDBC.assertSingleValueResultSet(
                s.executeQuery("values true and false"), "false");
        JDBC.assertSingleValueResultSet(
                s.executeQuery("values true or false"), "true");
        assertCompileError(NON_BOOLEAN_OPERAND, "values 1 and 2");
        assertCompileError(NON_BOOLEAN_OPERAND, "values 1 or 2");

        // Test boolean expressions as parameters in function calls. Used to
        // result in syntax errors with OR expressions.
        JDBC.assertSingleValueResultSet(s.executeQuery(
                "values booleanValue(true and false)"), "False value");
        JDBC.assertSingleValueResultSet(s.executeQuery(
                "values booleanValue(true or false)"), "True value");
        assertCompileError(NON_BOOLEAN_OPERAND, "values booleanValue(1 and 2)");
        assertCompileError(NON_BOOLEAN_OPERAND, "values booleanValue(1 or 2)");

        // Test boolean expressions in UPDATE statements. Used to fail with
        // syntax errors with OR expressions.
        s.execute("create table d5972(b boolean)");
        assertUpdateCount(s, 0, "update d5972 set b = true and false");
        assertUpdateCount(s, 0, "update d5972 set b = true or false");
        assertCompileError(NON_BOOLEAN_OPERAND, "update d5972 set b = 1 and 2");
        assertCompileError(NON_BOOLEAN_OPERAND, "update d5972 set b = 1 or 2");

        // Used to work correctly. Verify for completeness.
        JDBC.assertSingleValueResultSet(s.executeQuery(
                "values case when true and false then 1 else 0 end"), "0");
        JDBC.assertSingleValueResultSet(s.executeQuery(
                "values case when true or false then 1 else 0 end"), "1");
        assertCompileError(NON_BOOLEAN_OPERAND,
                "values case when 1 and 2 then 1 else 0 end");
        assertCompileError(NON_BOOLEAN_OPERAND,
                "values case when 1 or 2 then 1 else 0 end");
    }
    
    /**
     * Some BOOLEAN expressions used to be transformed to non-equivalent
     * IN lists. Verify that they now return the correct results.
     * Regression test case for DERBY-6363.
     */
    public void test_6363() throws SQLException {
        Statement s = createStatement();
        s.execute("create table d6363(a int, b char)");
        s.execute("insert into d6363 values (1, 'a'), (2, 'b'), (3, 'a'), "
                + "(4, 'b'), (5, 'a'), (6, 'b')");

        JDBC.assertFullResultSet(s.executeQuery(
            "select a, ((b = 'a' or b = 'b') and a < 4), "
                    + "((b = 'a' or b = 'c' or b = 'b') and a < 4), "
                    + "((b = 'a' or (b = 'c' or b = 'b')) and a < 4), "
                    + "((b = 'a' or b in ('c', 'b')) and a < 4), "
                    + "(a < 4 and (b = 'a' or b = 'b')) "
                    + "from d6363 order by a"),
            new String[][] {
                { "1", "true", "true", "true", "true", "true" },
                { "2", "true", "true", "true", "true", "true" },
                { "3", "true", "true", "true", "true", "true" },
                { "4", "false", "false", "false", "false", "false" },
                { "5", "false", "false", "false", "false", "false" },
                { "6", "false", "false", "false", "false", "false" },
            });

        JDBC.assertFullResultSet(s.executeQuery(
            "select a, b, "
            + "case when ((b = 'a' or b = 'b') and a < 4) "
            + "then 'x' else '-' end, "
            + "case when (a < 4 and (b = 'a' or b = 'b')) "
            + "then 'y' else '-' end "
            + "from d6363 order by a"),
            new String[][] {
                { "1", "a", "x", "y" },
                { "2", "b", "x", "y" },
                { "3", "a", "x", "y" },
                { "4", "b", "-", "-" },
                { "5", "a", "-", "-" },
                { "6", "b", "-", "-" },
            });
    }

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // SQL ROUTINES
    //
    ///////////////////////////////////////////////////////////////////////////////////

    public static Blob makeSimpleBlob() throws SQLException
    {
        return new HarmonySerialBlob( new byte[] { 1 } );
    }
    
    public static String booleanValue( Boolean b )
    {
        if ( b == null ) { return "Null value"; }
        else if ( b.booleanValue() ) { return "True value"; }
        else { return "False value"; }
    }

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // MINIONS
    //
    ///////////////////////////////////////////////////////////////////////////////////

    /** Return true if the SQL routine exists */
    private boolean routineExists( Connection conn, String functionName ) throws Exception
    {
        PreparedStatement ps = chattyPrepare( conn, "select count (*) from sys.sysaliases where alias = ?" );
        ps.setString( 1, functionName );

        ResultSet rs = ps.executeQuery();
        rs.next();

        boolean retval = rs.getInt( 1 ) > 0 ? true : false;

        rs.close();
        ps.close();

        return retval;
    }

    /** Return true if the table exists */
    private boolean tableExists( Connection conn, String tableName ) throws Exception
    {
        PreparedStatement ps = chattyPrepare( conn, "select count (*) from sys.systables where tablename = ?" );
        ps.setString( 1, tableName );

        ResultSet rs = ps.executeQuery();
        rs.next();

        boolean retval = rs.getInt( 1 ) > 0 ? true : false;

        rs.close();
        ps.close();

        return retval;
    }

}