ReferenceManualTenTwo

Section Name

Reviewers

Status

SQL Language Reference

Yip, Manjula

Completed(Yip)

SQL Language Reference

Myrna

Completed

SQL Reserved Words

Yip

Completed

Derby support for SQL-92 features

Yip

Completed

Derby System Tables

Yip

Completed

Derby exception messages and SQL States (DERBY-1566)

jta, Yip

Completed

JDBC Reference

Mamta, Kristian

Setting attributes for the database connection URL

Mamta

J2EE Compliance: Java Transaction API and javax.sql Extensions

Susan

Completed

Derby API

Mamta

Derby limitations

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

DECLARE GLOBAL TEMPORARY TABLE statement

rrefdeclaretemptable.html

Yip

Under Restrictions on Declared Global Temporary Tables, GRANT and REVOKE should be added to that restriction list.

Fixed (Laura)

CREATE SCHEMA statement

rrefsqlj31580.html

Yip

CREATE SCHEMA syntax needs to be updated. The following are also allowed: CREATE SCHEMA schema-name AUTHORIZATION user-name and CREATE SCHEMA AUTHORIZATION user-name. Addtional examples to use these new syntax would be nice.

Fixed (Laura)

RENAME TABLE statement

rrefsqljrenametablestatement.html

Yip

RENAME TABLE needs to address that one can only perform renaming of a table if the user is the database owner or the user have the authorization to the schema of the table to be renamed.

Fixed (Laura)

LOCK TABLE statement

rrefsqlj40506.html

Yip

LOCK TABLE needs to address that one can only perform locking of a table if the user is the database owner or the user have the proper privilege to execute such an action. This is not covered in the design spec.

Fixed (Laura)

CREATE TRIGGER statement

rrefsqlj43125.html

Yip

The statement "You can create a trigger in any schema except one that starts with SYS." is no longer true when sql authorization is enabled. Also, under the syntax section, { INSERT | DELETE | UPDATE } [OF column-name [, column-name]* ] should be { INSERT | DELETE | UPDATE [ OF column-name [, column-name]* ] }

Fixed (Laura)

Built-in Functions

ref/toc.html

Susan

The table of contents was edited to include the new Math Functions (see DERBY-1549) ACOS, ASIN, ATAN, etc. The word "function" was added to each of these functions in the toc, whereas the existing functions do not contain this word. For consistency it would be better if this word was removed.

This has not been fixed as of the 10.2.1.6 RC. Derby-1920 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1920

ATAN Function

rreffuncatan.html

Susan

According to DERBY-1548, Derby does not support negative zero, so this statement 'If the specified number is zero (0), the result of this function is zero with the same sign as the specified number. ' should be changed to this 'If the specified number is zero (0), the result of this function is zero.'

Fixed (Laura)

CEIL Function

rreffuncceil.html

Susan

According to DERBY-1548, Derby does not support negative zero, so this statement 'If the specified number is less than zero but greater than -1.0, then the result of these functions is negative zero.' should be changed to this 'If the specified number is less than zero but greater than -1.0, then the result of these functions is zero.'

Fixed (Laura)

JDBC Escape Syntax - PI Function

rrefjdbc88908.html

Susan

I think this is a typo - 'Returns an value that is closer than any other value to pi.' I believe this is correct, 'Returns a value that is closer than any other value to pi.'

Fixed (Laura)

List of datatypes

derv/ref

Dan D

In the list of data types the XML type is listed as 'XML data type', remove the words 'data type', all of the other types are listed just as the type name. Same is true of the page that describes the XML type, it says 'XML data type', other types just have the name of the type, e.g. BLOB

Derby-1920 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1920

XQUERY operator

rreffuncxmlquery.html

Dan D

This description: The result of the XMLQUERY operator is another Derby XML value. Unlike other XML values, the result might represent an empty sequence of XML nodes or values. Atomic values, such as strings, can be part of the result sequence. The result of an XMLQUERY operator is not guaranteed to represent a valid XML document and it might not be possible to insert the result of an XMLQUERY operator into a Derby XML column should be replaced with: The result of the XMLQUERY operator is a value of type XML. The result represents a sequence of XML nodes or values. Atomic values, such as strings, can be part of the result sequence. The result of an XMLQUERY operator is not guaranteed to represent a well formed XML document and it might not be possible to insert the result of an XMLQUERY operator into an XML column - Notes on the changes (bold), a) no need to use Derby b) The old wording had the use of 'might' and 'empty sequence', I think it wanted to describe a sequence and the result is always a sequence, not that it might be. c) Use of 'well-formed' is better than 'valid'.

Fixed (Laura)

Date function

ref/rrefdatefunc.html

jta

The number listed in the description "a positive number less than or equal to 3,652,059" gets an error, the max number that works is 2,932,897 (posted to derby-dev, added to DERBY-1803)

Fixed (Laura)

XML data type

rrefsqljtypexml.html

Army

See following thread: xmlTypeThread
^(KW)Missing dot between class and methodname. Change PreparedStatementsetCharacterStream to PreparedStatement.setCharacterStream.

Fixed (Laura)

XML operators

rrefsqlj29026.html

Army

See following thread: xmlOpsThread

Fixed (Laura)

XML Operators

various

Dan D

The XML operators and data type include text about requiring Xalan etc. When trying out XML I felt lost at this point because there was no information on what I was meant to do. It was the feeling I was being told "You need to do something with Xalan to get this to work, but you are going to have to figure that out yourself". It stopped me from evaluating Mustang with XML. Can there be information in the developers' guide about this, and a link to that information from these Usage notes?

Derby-1923 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1923

XMLSERIAIZE operator

rreffuncxmlserialize.html

Susan

To retain consistencey in all of the XML examples the example table name should remain x_table. When used in the XMLSERIALIZE example it was changed to xtable.

Fixed (Laura)

CAST function

rrefsqlj33562.html

Susan

Several of the values listed in the supported CAST conversion table are incorrect when casting to VARCHAR and TIMESTAMP. See DERBY-1804

Fixed (Laura)

SECOND, SMALLINT, SUBSTR, TIMESTAMP, UCASE functions

See the following thread on derby-dev

Susan

There are some incorrect statements or errors for all of these functions.

Patch submitted (Laura) for SMALLINT, SUBSTR, TIMESTAMP, AND UCASE functions. Some updates to SECOND. Derby-1927 and Derby-908 have been opened to address the remaining issues for SECOND and the general comment about built-in functions. https://issues.apache.org/jira/browse/DERBY-1924 https://issues.apache.org/jira/browse/DERBY-908

DECLARE GLOBAL TEMPORARY TABLE

rrefdeclaretemptable.html

Myrna

1. DOUBLE is missing from list of supported Data-types (although it's a synonym of DOUBLE PRECISION which is there, so minor, minor). 2. Following are missing from 'the following data types cannot be used with Declared...': CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA. 3. The section 'Restrictions specific to Derby' needs to be integrated into the section 'Restrictions on Declared Global Temporary Tables'. Some items are in both places, for instance, 'Temporary tables cannot be specified in the following statements' lists 'LOCK' and the other section 'LOCK TABLE'. On the other hand, CREATE INDEX is not in the list of unsupported statements, but the 'Restrictions specific to Derby' shows that 'Indexes' are not supported.

Fixed (Laura)

TableExpression

rreftableexpression.html

Myrna

Link to and list with 1 item TableOrViewExpression is placed oddly. I think TableOrViewExpression should be the main item, and TableExpression should be a subitem. Maybe we can just get rid of the page for TableExpression altogether and only have TableOrViewExpression instead with all the info currently on the TableExpression page - except for combining the syntax.

Derby-1924 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1924

Grant Statement

rrefsqlgrant.html

Manjula

For the update previlege we should mention that to update a row using a statement which has a where clause one should have select permission on that particular column.

Fixed (Laura)

Grant Statement

rrefsqlgrant.html

Manjula

Please add an example about the RESTRICT clause on REVOKE statements for routines .

Fixed (Laura)

Grant statement

derby-dev

Dan D

Incorrect information for GRANT

Fixed (Laura)

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

SQL reserved words

http://db.apache.org/derby/docs/10.2/ref/rrefkeywords29722.html

Yip

Add XMLQUERY as reserved word.

Patch submitted (Laura)

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

Table 12. Support for SQL-92 Features: Basic schema manipulation

http://db.apache.org/derby/docs/10.2/ref/rrefsql9241891.html

Yip

Column DERBY for GRANT/REVOKE row should mark as partial instead of no.

Derby-1928 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1928

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

SYSTABLEPERMS

http://db.apache.org/derby/docs/10.2/ref/rrefsistabssystableperms.html

Yip

In the column information for the SYSTABLEPERMS table's SELECTPRIV,DELETEPRIV,INSERTPRIV,UPDATEPRIV, REFERENCEPRIV and TRIGGERPRIV column. Their contents column should be: The valid values are 'y'(non-grantable privilege),'Y'(grantable privilege) and 'N'(no privilege).

Derby-1929 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1929

SYSCOLPERMS

http://db.apache.org/derby/docs/10.2/ref/rrefsistabssyscolperms.html

Yip

In the column information for the SYSCOLPERMS table's TYPE column. Its content column should be: If the privilege is non-grantable, the valid values are 's' for SELECT, 'u' for UPDATE and 'r' for REFERENCES. If the privilege is grantable, the valid values are 'S' for SELECT, 'U' for UPDATE and 'R' for REFERENCES.

Derby-1929 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1929

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

Autogen of sqlstates

DERBY-1566

jta

See the Jira issue

Fixed (Jean)

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

java.sql.Blob and java.sql.Clob

http://db.apache.org/derby/docs/10.2/ref/rrefjdbc96386.html

Mamta

This section has a table titled Table 1. JDBC 2.0 java.sql.Blob Methods Supported. The first row, 3rd column of this table shows up empty in the html page but has an ' in the pdf format. The pdf format should also show the column empty. I think this happens for all the tables with no data in the pdf format

Derby-1932 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1932

DatabaseMetaData functionality not supported

http://db.apache.org/derby/docs/10.2/ref/ref-single.html#N2E3A9

Mamta

This section says that getColumnPrivileges and getTablePrivileges are not supported. But checkin r381109 has changes to metadata.properties for these 2 functions. So, looks like these methods are probably now supported with grant revoke functionality.

Derby-1932 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1932

Data streaming

⁽¹⁾http://db.apache.org/derby/docs/dev/ref/rrefjavsqlprst.html
⁽²⁾http://db.apache.org/derby/docs/dev/ref/rrefjavsqlrssc.html
⁽³⁾http://db.apache.org/derby/docs/dev/ref/crefjavbclobnot.html
⁽⁴⁾http://db.apache.org/derby/docs/dev/ref/rrefjdbc4_0summary.html

Kristian

(1): Rewrite section to reflect a newer JDBC version than 1.2? Comment that getUnicodeStream is deprecated and no longer implemented in Derby. Add description of getCharacterStream. Could also add info about setNCharacterStream, which is JDBC 4 only and not supported. The note under the table is no longer accurate; it only applies when using the client driver (and also only for the client?).
(2): OK
(3): Update list of methods (mark as deprecated, add getCharacterStream).
(4): OK.

Derby-1932 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1932

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

Attributes of the Derby database connection URL

http://db.apache.org/derby/docs/10.2/ref/ref-single.html#N2D6D9

Mamta

This section has 3 examples of setting attributes on database connection URL using Java. One minor suggestion is we should use Java commenting scheme ie /*..*/ instead of -- in this sample code.

Derby-1933 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1933

encryptionKey section under Setting attributes for the database connection URL

http://db.apache.org/derby/docs/10.2/ref/rrefattribencryptkey.html

Mamta

The pdf page for encryptionKey has incomplete example. The pdf page only shows jdbc:derby:newDB;create=true;dataEncryption=true;encryptionAlgorithm=DES/CBC/NoPadding; The corresponding html page shows the correct example which looks as follows jdbc:derby:newDB;create=true;dataEncryption=true;encryptionAlgorithm=DES/CBC/NoPadding;encryptionKey=6162636465666768

Derby-1933 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1933

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

javax.sql:JDBC Extensions

HTML Url

Susan

This URL no longer exists: (For more details about these extensions, see http://java.sun.com/products/jdbc/jdbc20.stdext.javadoc/javax/sql/package-summary.html). The page that has this information, although you have to browse to the section called JDBC 2.0 Optional Package API is http://java.sun.com/products/jdbc/download.html

Derby-1934 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1934

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

DATE, TIME and TIMESTAMP

rrefdttlimits.html

Susan

The largest TIMESTAMP value is incorrect, DERBY-1803

Fixed (Laura)

Numeric limitations

rrefnumericlimits.html

Susan

The largest decimal precision value is incorrect, it should be 31 instead of 31,255

Derby-1935 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1935