Functional Spec For Boolean Datatype

• Overview
• Terms
• Incompatibilities
• New SQL Syntax
• Casting Behavior
  • Explicit Casts
  • Implicit Casts
  • ODBC Metadata Queries
• DatabaseMetaData
• ResultSetMetaData
• ParameterMetaData
• JDBC getXXX() Methods
• JDBC setXXX() Methods
• Upgrade
  • Soft Upgrade
  • Hard Upgrade
• Documentation
• Release Notes
• Appendix A: Old Casting Details
• Appendix B: Old DatabaseMetaData Behavior
• Appendix C: Old ResultSetMetaData Behavior
• Appendix D: Old ParameterMetaData Behavior
• Appendix E: Old getXXX() Behavior
• Appendix F: Old setXXX() Behavior

Overview

This spec describes the introduction of the ANSI BOOLEAN datatype. Derby 10.3 already partially supports this datatype:

• System tables - Some of the system tables have BOOLEAN columns, which users can SELECT.
• Qualification - The WHERE clause evaluates to a BOOLEAN value.
• Casts - BOOLEAN values can be cast explicitly to other datatypes.
• ResultSet - getXXX() methods can be called on BOOLEAN columns in JDBC ResultSets.

In Target Release, Derby behavior changes as follows:

• CREATE TABLE/FUNCTION/PROCEDURE - Users can create tables with BOOLEAN columns. Users can create functions with BOOLEAN parameters and/or return values. Users can create procedures with BOOLEAN arguments.
• Casts - In Target Release, BOOLEANs obey the ANSI casting rules.
• ResultSet - The behavior of some getXXX() methods changes.

Terms

For the remainder of this spec, we use the following terms:

• Target Release - The release which carries these changes. This would be 10.4 or later.

Incompatibilities

The following changes create incompatibilities with previous Derby releases. The community needs to decide whether these incompatibilities warrant bumping the release identifier's major number.

• Casts - Casts to and from BOOLEAN change.
• JDBC getXXX() Methods - The behavior of some getXXX() methods change.

New SQL Syntax

We introduce new SQL syntax:

• CREATE TABLE - Users can create tables with BOOLEAN columns.
• CREATE INDEX - Users can include BOOLEAN columns in indexes.
• CREATE FUNCTION - Users can create functions with BOOLEAN parameters and return values.
• CREATE PROCEDURE - Users can create procedures with BOOLEAN arguments.
• New Literals - SQL statements can contain 3 new BOOLEAN literals:
  • true
  • false
  • unknown - This evaluates to a BOOLEAN null.
• Sorts - Users can include BOOLEAN typed values in GROUP BY and ORDER BY clauses. Null sorts before false, which sorts before true.

So, for instance, the following script is supported:

CREATE TABLE sample( boolCol BOOLEAN );

CREATE INDEX boolIndex on sample( boolCol );

INSERT INTO SAMPLE( boolCol ) VALUES
( true ),
( false ),
( null ),
( unknown );

select * from sample order by boolCol;

Casting Behavior

In Target Release, Derby implements ANSI casting behavior with respect to BOOLEANs. This is a change from Derby's behavior in release 10.3. The ANSI casting behavior is described in the SQL Standard, part 2, section 6.12 (<cast specification>). For details on the discrepancy between Derby 10.3 and the ANSI spec, see Appendix A.

Explicit Casts

Derby 10.3 lets you cast BOOLEAN to most other datatypes. However, ANSI only allows you to cast BOOLEANs to the string types CHAR, VARCHAR, LONG VARCHAR, and CLOB.

According to the SQL Standard, the result of casting a BOOLEAN to a string type is an uppercase string value "TRUE" or "FALSE". For fixed-length string types, this value is then padded with trailing spaces. A truncation error is raised if the fixed-length string type is too small. Derby 10.3 correctly pads and raises truncation errors. However, Derby 10.3 casts BOOLEAN to lowercase "true" and "false".

In Target Release, Derby behavior changes to conform to the SQL Standard:

• BOOLEAN values may only be cast to BOOLEAN and to the string types CHAR, VARCHAR, LONG VARCHAR, and CLOB. All other casts raise syntax errors.
• The result of casting a BOOLEAN value to a string type is uppercase 'TRUE" and "FALSE" rather than lowercase "true" and "false".

In addition, in Target Release, only the string types and BOOLEAN itself may be cast to BOOLEAN. The reverse casts are also allowed and those are the only legal casts from BOOLEAN.

Implicit Casts

Derby 10.3 allows comparisons between BOOLEAN values and numbers. In these comparisons, the number is implicitly cast to a BOOLEAN: 0 becomes false and all other values become true. The SQL Standard does not allow these comparisons because the underlying cast is illegal.

In Target Release, Derby behavior changes to conform to the SQL Standard:

• Derby raises a syntax error when asked to compare a number to a BOOLEAN.

ODBC Metadata Queries

In Derby 10.3, the ODBC metadata queries depend on the ability to perform a non-ANSI cast from a BOOLEAN to an INT. These queries are rewritten in Target Release to use a CASE clause instead. E.g.:

select
case
  when boolcol then 1
  when not boolcol then 0
  else null
  end
...
;

DatabaseMetaData

Several DatabaseMetaData methods return datatype information:

• getColumns() - This method returns a ResultSet describing columns in tables.
• getProcedureColumns() - This method returns a ResultSet describing the parameters and return values of database procedures. This ResultSet holds one row per parameter and one row per column in ResultSets returned by database procedures.
• getFunctionColumns() - This method returns a ResultSet describing the parameters and return values of functions. This ResultSet holds one row per parameter and one row per return value.

All of these ResultSets have a column, DATA_TYPE, which contains the type ID of the described column/parameter/result. Another field in this row, TYPE_NAME, contains the server's name for the datatype of the described column/parameter/result. In Target Release, these columns contain the following values for BOOLEAN columns/parameters/results:

Appendix B describes the old behavior of these DatabaseMetaData methods.

ResultSetMetaData

Two ResultSetMetaData methods describe type information for the columns returned by queries:

• getColumnType() - This returns a JDBC type ID.
• getColumnTypeName() - This returns the corresponding name for the type.

These methods return the following values for BOOLEAN columns when either the client and server are at Derby Target Release or higher:

Appendix C describes legacy behavior. Note that jdk 1.3 is not a supported platform for Target Release.

ParameterMetaData

JDK 1.4 introduced the ParameterMetaData interface with the following methods which describe type information for ? query parameters:

• getParameterType() - This returns a JDBC type ID.
• getParameterTypeName() - This returns the corresponding name for the type.

These methods return the following values for BOOLEAN parameters:

See Appendix D for a description of how the ParameterMetaData methods behave in earlier Derby releases.

JDBC getXXX() Methods

Appendix E describes the old behavior of getXXX() methods on BOOLEAN columns in ResultSets. Note that embedded and client/server behavior differ for two methods: getString() and getObject(). The getXXX() methods of ResultSet and CallableStatement continue to behave as described in Appendix E UNLESS both the client and server are at Target Release or higher. In that case, the old embedded behavior prevails and getString() and getObject() behave as follows:

JDBC setXXX() Methods

Appendix F describes the old behavior of setXXX() methods on BOOLEAN columns in PreparedStatements. If both the client and server are at Target Release, then setXXX() methods on PreparedStatements and CallableStatements conform to the embedded behavior described in Appendix F. Otherwise, these methods behave according to the Derby client and DB2JCC behavior described in Appendix F.

Upgrade

Soft Upgrade

After a soft-upgrade to Target Release, you see the following changes introduced by this spec:

Hard Upgrade

After a hard-upgrade to Target Release, you also see the following behavior:

Documentation

This new behavior will require changes to the user guides:

• Reference Guide
  • Data types - This section needs a sub-section on the BOOLEAN datatype.
  • Data type assignments and comparison, sorting, and ordering - We need to add BOOLEAN to the tables in this section, listing the legal casts.

Release Notes

We need a release note for this feature. The release note should describe:

• Casts - Changes to casting behavior.
• JDBC - Changes to JDBC behavior.

Appendix A: Old Casting Details

The following script demonstrates the BOOLEAN casting behavior in Derby 10.3:

--
-- supported
--
select
  cast (systemalias as SMALLINT),
  cast (systemalias as INTEGER),
  cast (systemalias as BIGINT),
  cast (systemalias as DECIMAL),
  cast (systemalias as NUMERIC),
  cast (systemalias as REAL),
  cast (systemalias as DOUBLE),
  cast (systemalias as FLOAT),
  cast (systemalias as CHAR(1)),
  cast (systemalias as VARCHAR(1)),
  cast (systemalias as LONG VARCHAR),
  cast (systemalias as CLOB),

  cast (systemalias as char(1) for bit data),
  cast (systemalias as varchar(1) for bit data),
  cast (systemalias as long varchar for bit data),
  xmlserialize( cast (systemalias as xml) as clob),
  cast (systemalias as BLOB)
from sys.sysaliases
where 0=1;


--
-- NOT supported
--
select
  cast (systemalias as date)
from sys.sysaliases
where 0=1;

select
  cast (systemalias as time)
from sys.sysaliases
where 0=1;

select
  cast (systemalias as timestamp)
from sys.sysaliases
where 0=1;

--
-- Result of casting booleans to character types
-- is a lowercase value although ANSI specifies uppercase.
-- Fixed length character types are correctly padded with
-- trailing spaces.
--
select
  cast (systemalias as CHAR(8)) || 'foo',
  cast (systemalias as VARCHAR(8)) || 'foo',
  cast (systemalias as LONG VARCHAR),
  cast (systemalias as CLOB)
from sys.sysaliases
where alias='SYSCS_IMPORT_TABLE';

--
-- Correctly raises truncation exceptions.
--
select
  cast (systemalias as CHAR(3))
from sys.sysaliases
where alias='SYSCS_IMPORT_TABLE';

select
  cast (systemalias as VARCHAR(3))
from sys.sysaliases
where alias='SYSCS_IMPORT_TABLE';

--
-- Comparing booleans to numbers. Should raise
-- a syntax error but Derby tolerates this syntax.
-- The number is implicitly cast to a boolean: 0 becomes
-- false and all other values become true.
--
select systemalias
from sys.sysaliases
where systemalias = 1;

select systemalias
from sys.sysaliases
where systemalias < -1;

The following table describes casts from BOOLEAN to target datatypes. Note the discrepancy between the ANSI casting behavior and the casting behavior observed in Derby 10.3.

The following table describes casts from source datatypes to BOOLEAN. Note the discrepancy between the ANSI casting behavior and the casting behavior observed in Derby 10.3.

Appendix B: Old DatabaseMetaData Behavior

The following table describes the values of the DATA_TYPE and TYPE_NAME fields returned by DatabaseMetaData.getColumns() for SYS.SYSALIASES.SYSTEMALIAS in various combinations of Derby clients, servers, and VMs. These are the results for Derby 10.2 and earlier.

Appendix C: Old ResultSetMetaData Behavior

The following table describes the values of the getColumnType() and getColumnTypeName() methods of the ResultSetMetaData returned for the query "select systemalias from sys.sysaliases" in various combinations of Derby clients, servers, and VMs. These are the results for Derby 10.2 and earlier.

Appendix D: Old ParameterMetaData Behavior

I do not know of any legal way for an old version of Derby to compile a SQL statement with a parameter which has type BOOLEAN. However, as of the writing of this spec, bug DERBY-2605 allows you to create user tables with BOOLEAN columns in the mainline. The following table describes the values of the getParameterType() and getParameterTypeName() methods of the ParameterMetaData returned for the statement "insert into foo( boolcol ) values ( ? )" thanks to this bug:

Appendix E: Old getXXX() Behavior

Here is how ResultSet getXXX() methods behave on BOOLEAN columns in Derby 10.3 and earlier. Note that the embedded and client/server behavior is identical except for getString() and getObject().

Appendix F: Old setXXX() Behavior

I do not know of any legal way for an old version of Derby to compile a SQL statement which inserts into a BOOLEAN column. However, as of the writing of this spec, bug DERBY-2605 allows you to create user tables with BOOLEAN columns in the mainline. The following table describes the behavior of various PreparedStatement setXXX() methods for the statement "insert into foo( boolcol ) values ( ? )", thanks to this bug. Derby embedded and client behavior mostly agree. DB2JCC behavior, however, diverges. Differences from the embedded behavior are marked in gold.