<html><head><META http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>Chapter 2. SQL Issues</title><link href="guide.css" rel="stylesheet" type="text/css"><meta content="DocBook XSL Stylesheets V1.65.1" name="generator"><meta name="keywords" content="Hsqldb, SQL"><meta name="keywords" content="Hsqldb, Hypersonic, Database, JDBC, Java"><link rel="home" href="index.html" title="Hsqldb User Guide"><link rel="up" href="index.html" title="Hsqldb User Guide"><link rel="previous" href="ch01.html" title="Chapter 1. Running and Using Hsqldb"><link rel="next" href="ch03.html" title="Chapter 3. UNIX Quick Start"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table summary="Navigation header" width="100%"><tr><th align="center" colspan="3">Chapter 2. SQL Issues</th></tr><tr><td align="left" width="20%"><a accesskey="p" href="ch01.html"><img src="navicons/prev.gif" alt="Prev"></a> </td><th align="center" width="60%"> </th><td align="right" width="20%"> <a accesskey="n" href="ch03.html"><img src="navicons/next.gif" alt="Next"></a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="sql_issues-chapter"></a>Chapter 2. SQL Issues</h2></div><div><div class="authorgroup"><div class="author"><h3 class="author"><span class="firstname">Fred</span> <span class="surname">Toussi</span></h3><div class="affiliation"><span class="orgname">HSQLDB Development Group<br></span></div><tt class="email"><<a href="mailto:ft@cluedup.com">ft@cluedup.com</a>></tt></div></div></div><div><div class="legalnotice"><p>Copyright 2002-2005 Fred Toussi. Permission is granted to
distribute this document without any alteration under the terms of the
HSQLDB license. Additional permission is granted to the HSQLDB
Development Group to distribute this document with or without
alterations under the terms of the HSQLDB license.</p></div></div><div><p class="pubdate">$Date: 2005/05/27 15:37:31 $</p></div></div><div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="ch02.html#N102AE">Purpose</a></span></dt><dt><span class="section"><a href="ch02.html#N102B3">SQL Standard Support</a></span></dt><dt><span class="section"><a href="ch02.html#N102D3">Constraints and Indexes</a></span></dt><dd><dl><dt><span class="section"><a href="ch02.html#N102D6">Primary Key Constraints</a></span></dt><dt><span class="section"><a href="ch02.html#N102DF">Unique Constraints</a></span></dt><dt><span class="section"><a href="ch02.html#N1030E">Unique Indexes</a></span></dt><dt><span class="section"><a href="ch02.html#N10318">FOREIGN KEYS</a></span></dt><dt><span class="section"><a href="ch02.html#N1033B">Indexes and Query Speed</a></span></dt><dt><span class="section"><a href="ch02.html#N10372">Where Condition or Join</a></span></dt><dt><span class="section"><a href="ch02.html#N103A6">Subqueries and Joins</a></span></dt></dl></dd><dt><span class="section"><a href="ch02.html#N103BF">Types and Arithmetic Operations</a></span></dt><dd><dl><dt><span class="section"><a href="ch02.html#N103D6">Integral Types</a></span></dt><dt><span class="section"><a href="ch02.html#N10426">Other Numeric Types</a></span></dt><dt><span class="section"><a href="ch02.html#N1045F">Bit and Boolean Types</a></span></dt><dt><span class="section"><a href="ch02.html#N1047A">Storage and Handling of Java Objects</a></span></dt><dt><span class="section"><a href="ch02.html#N10499">Type Size, Precision and Scale</a></span></dt></dl></dd><dt><span class="section"><a href="ch02.html#N104AE">Sequences and Identity</a></span></dt><dd><dl><dt><span class="section"><a href="ch02.html#N104B3">Identity Auto-Increment Columns</a></span></dt><dt><span class="section"><a href="ch02.html#N104E1">Sequences</a></span></dt></dl></dd><dt><span class="section"><a href="ch02.html#N104FC">Issues with Transactions</a></span></dt><dt><span class="section"><a href="ch02.html#N10521">New Features and Changes</a></span></dt></dl></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N102AE"></a>Purpose</h2></div></div><div></div></div><p>Many questions repeatedly asked in Forums and mailing lists are
answered in this guide. If you want to use HSQLDB with your application,
you should read this guide.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N102B3"></a>SQL Standard Support</h2></div></div><div></div></div><p>HSQLDB 1.8.0 supports the dialect of SQL defined by SQL standards
92, 99 and 2003. This means where a feature of the standard is supported,
e.g. left outer join, the syntax is that specified by the standard text.
Many features of SQL92 and 99 up to Advanced Level are supported and there
is support for most of SQL 2003 Foundation and several optional features
of this standard. However, certain features of the Standards are not
supported so no claim is made for full support of any level of the
standards.</p><p>The SQL Syntax chapter of this guide <a href="ch09.html" title="Chapter 9. SQL Syntax">SQL Syntax</a> lists all the keywords and syntax that is
supported. When writing or converting existing SQL DDL (Data Definition
Language) and DML (Data Manipulation Language) statements for HSQLDB, you
should consult the supported syntax and modify the statements
accordingly.</p><p>Several words are reserved by the standard and cannot be used as
table or column names. For example, the word POSITION is reserved as it is
a function defined by the Standards with a similar role as
String.indexOf() in Java. HSQLDB does not currently prevent you from using
a reserved word if it does not support its use or can distinguish it. For
example BEGIN is a reserved words that is not currently supported by
HSQLDB and is allowed as a table or column name. You should avoid the use
of such words as future versions of HSQLDB are likely to support the words
and will reject your table definitions or queries. The full list of SQL
reserved words is in the source of the
<tt class="classname">org.hsqldb.Token</tt> class.</p><p>HSQLDB also supports some keywords and expressions that are not part
of the SQL standard as enhancements. Expressions such as <tt class="literal">SELECT
TOP 5 FROM ..</tt>, <tt class="literal">SELECT LIMIT 0 10 FROM ...</tt> or
<tt class="literal">DROP TABLE mytable IF EXISTS</tt> are among such
constructs.</p><p>All keywords, can be used for database objects if they are double
quoted.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N102D3"></a>Constraints and Indexes</h2></div></div><div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N102D6"></a>Primary Key Constraints</h3></div></div><div></div></div><p>Before 1.7.0, a<tt class="literal"> CONSTRAINT <name> PRIMARY
KEY</tt> was translated internally to a unique index and, in
addition, a hidden column was added to the table with an extra unique
index. From 1.7.0 both single-column and multi-column PRIMARY KEY
constraints are supported. They are supported by a unique index on the
primary key column(s) specified and no extra hidden column is maintained
for these indexes.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N102DF"></a>Unique Constraints</h3></div></div><div></div></div><p>According to the SQL standards, a unique constraint on a single
column means no two values are equal unless one of them is NULL. This
means you can have one or more rows where the column value is
NULL.</p><p>A unique constraint on multiple columns (c1, c2, c3, ..) means
that no two sets of values for the columns are equal unless at lease one
of them is NULL. Each single column taken by itself can have repeat
values. The following example satisfies a UNIQUE constraint on the two
columns:</p><div class="example"><a name="N102E6"></a><p class="title"><b>Example 2.1. Column values which satisfy a 2-column UNIQUE
constraint</b></p><table summary="Simple list" border="0" class="simplelist"><tr><td>1,</td><td>2</td></tr><tr><td>2,</td><td>1</td></tr><tr><td>2,</td><td>2</td></tr><tr><td>NULL,</td><td>1</td></tr><tr><td>NULL,</td><td>1</td></tr><tr><td>1,</td><td>NULL</td></tr><tr><td>NULL,</td><td>NULL</td></tr><tr><td>NULL,</td><td>NULL</td></tr></table></div><p>Since version 1.7.2 the behaviour of UNIQUE constraints and
indexes with respect to NULL values has changed to conform to SQL
standards. A row, in which the value for any of the UNIQUE constraint
columns is NULL, can always be added to the table. So multiple rows can
contain the same values for the UNIQUE columns if one of the values is
NULL.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N1030E"></a>Unique Indexes</h3></div></div><div></div></div><p>In 1.8.0, user defined UNIQUE indexes can still be declared but
they are deprecated. You should use a UNIQUE constraint instead.</p><p><tt class="literal">CONSTRAINT <name> UNIQUE</tt> always creates
internally a unique index on the columns, as with previous versions, so
it has exactly the same effect as the deprecated UNIQUE index
declaration.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N10318"></a>FOREIGN KEYS</h3></div></div><div></div></div><p>From version 1.7.0, HSQLDB features single and multiple column
foreign keys. A foreign key can also be specified to reference a target
table without naming the target column(s). In this case the primary key
column(s) of the target table is used as the referenced column(s). Each
pair of referencing and referenced columns in any foreign key should be
of identical type. When a foreign key is declared, a unique constraint
(or primary key) must exist on the referenced columns in the primary key
table. A non-unique index is automatically created on the referencing
columns. For example:</p><div class="informalexample"><pre class="programlisting">
CREATE TABLE child(c1 INTEGER, c2 VARCHAR, FOREIGN KEY (c1, c2) REFERENCES parent(p1, p2));</pre></div><p>There must be a UNIQUE constraint on columns
<tt class="literal">(p1,p2)</tt> in the table named "parent". A non-unique
index is automatically created on columns <tt class="literal">(c1, c2)</tt> in
the table named "child". Columns <tt class="literal">p1</tt> and
<tt class="literal">c1</tt> must be of the same type (INTEGER). Columns
<tt class="literal">p2</tt> and <tt class="literal">c2</tt> must be of the same type
(VARCHAR).</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N1033B"></a>Indexes and Query Speed</h3></div></div><div></div></div><p>HSQLDB does not use indexes to improve sorting of query results.
But indexes have a crucial role in improving query speed. If no index is
used in a query on a single table, such as a DELETE query, then all the
rows of the table must be examined. With an index on one of the columns
that is in the WHERE clause, it is often possible to start directly from
the first candidate row and reduce the number of rows that are
examined.</p><p>Indexes are even more important in joins between multiple tables.
<tt class="literal">SELECT ... FROM t1 JOIN t2 ON t1.c1 = t2.c2 </tt> is
performed by taking rows of t1 one by one and finding a matching row in
t2. If there is no index index on t2.c2 then for each row of t1, all the
rows of t2 must be checked. Whereas with an index, a matching row can be
found in a fraction of the time. If the query also has a condition on
t1, e.g., <tt class="literal">SELECT ... FROM t1 JOIN t2 ON t1.c1 = t2.c2 WHERE
t1.c3 = 4</tt> then an index on t1.c3 would eliminate the need for
checking all the rows of t1 one by one, and will reduce query time to
less than a millisecond per returned row. So if t1 and t2 each contain
10,000 rows, the query without indexes involves checking 100,000,000 row
combinations. With an index on t2.c2, this is reduced to 10,000 row
checks and index lookups. With the additional index on t2.c2, only about
4 rows are checked to get the first result row.</p><p>Indexes are automatically created for primary key and unique
columns. Otherwise you should define an index using the CREATE INDEX
command.</p><p>Note that in HSQLDB a unique index on multiple columns can be used
internally as a non-unique index on the first column in the list. For
example: <tt class="literal">CONSTRAINT name1 UNIQUE (c1, c2, c3); </tt> means
there is the equivalent of <tt class="literal">CREATE INDEX name2 ON
atable(c1);</tt>. So you do not need to specify an extra index if
you require one on the first column of the list.</p><p>In 1.8.0, a multi-column index will speed up queries that contain
joins or values on ALL the columns. You need NOT declare additional
individual indexes on those columns unless you use queries that search
only on a subset of the columns. For example, rows of a table that has a
PRIMARY KEY or UNIQUE constraint on three columns or simply an ordinary
index on those columns can be found efficiently when values for all
three columns are specified in the WHERE clause. For example,
<tt class="literal">SELECT ... FROM t1 WHERE t1.c1 = 4 AND t1.c2 = 6 AND t1.c3 = 8
</tt>will use an index on <tt class="literal">t1(c1,c2,c3)</tt> if it
exists.</p><p>As a result of the improvements to multiple key indexes, the order
of declared columns of the index or constraint has less affect on the
speed of searches than before. If the column that contains more diverse
values appears first, the searches will be slightly faster.</p><p>A multi-column index will not speed up queries on the second or
third column only. The first column must be specified in the JOIN .. ON
or WHERE conditions.</p><p>Query speed depends a lot on the order of the tables in the JOIN
.. ON or FROM clauses. For example the second query below should be
faster with large tables (provided there is an index on
<tt class="literal">TB.COL3</tt>). The reason is that TB.COL3 can be evaluated
very quickly if it applies to the first table (and there is an index on
TB.COL3):</p><div class="informalexample"><pre class="programlisting">
(TB is a very large table with only a few rows where TB.COL3 = 4)
SELECT * FROM TA JOIN TB ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;
SELECT * FROM TB JOIN TA ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;</pre></div><p>The general rule is to put first the table that has a narrowing
condition on one of its columns.</p><p>1.7.3 features automatic, on-the-fly indexes for views and
subselects that are used in a query. An index is added to a view when it
is joined to a table or another view.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N10372"></a>Where Condition or Join</h3></div></div><div></div></div><p>Using <tt class="literal">WHERE</tt> conditions to join tables is likely
to reduce execution speed. For example the following query will
generally be slow, even with indexes:</p><pre class="programlisting">
SELECT ... FROM TA, TB, TC WHERE TC.COL3 = TA.COL1 AND TC.COL3=TB.COL2 AND TC.COL4 = 1</pre><p>The query implies <tt class="literal">TA.COL1 = TB.COL2</tt> but does
not explicitly set this condition. If TA and TB each contain 100 rows,
10000 combinations will be joined with TC to apply the column
conditions, even though there may be indexes on the joined columns. With
the JOIN keyword, the <tt class="literal">TA.COL1 = TB.COL2</tt> condition has
to be explicit and will narrow down the combination of TA and TB rows
before they are joined with TC, resulting in much faster execution with
larger tables:</p><pre class="programlisting">
SELECT ... FROM TA JOIN TB ON TA.COL1 = TB.COL2 JOIN TC ON TB.COL2 = TC.COL3 WHERE TC.COL4 = 1</pre><p>The query can be speeded up a lot more if the order of tables in
joins are changed, so that <tt class="literal">TC.COL1 = 1</tt> is applied
first and a smaller set of rows are joined together:</p><pre class="programlisting">
SELECT ... FROM TC JOIN TB ON TC.COL3 = TB.COL2 JOIN TA ON TC.COL3 = TA.COL1 WHERE TC.COL4 = 1</pre><p>In the above example the engine automatically applies
<tt class="literal">TC.COL4 = 1</tt> to TC and joins only the set of rows that
satisfy this condition with other tables. Indexes on
<tt class="literal">TC.COL4</tt>, <tt class="literal">TB.COL2</tt> and
<tt class="literal">TA.COL1</tt> will be used if present and will speed up the
query.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N103A6"></a>Subqueries and Joins</h3></div></div><div></div></div><p>Using joins and setting up the order of tables for maximum
performance applies to all areas. For example, the second query below
should generally be much faster if there are indexes on TA.COL1 and
TB.COL3:</p><div class="example"><a name="N103AB"></a><p class="title"><b>Example 2.2. Query comparison</b></p><pre class="programlisting">
SELECT ... FROM TA WHERE TA.COL1 = (SELECT MAX(TB.COL2) FROM TB WHERE TB.COL3 = 4)
SELECT ... FROM (SELECT MAX(TB.COL2) C1 FROM TB WHERE TB.COL3 = 4) T2 JOIN TA ON TA.COL1 = T2.C1</pre></div><p>The second query turns <tt class="literal">MAX(TB.COL2)</tt> into a
single row table then joins it with TA. With an index on
<tt class="literal">TA.COL1</tt>, this will be very fast. The first query will
test each row in TA and evaluate <tt class="literal">MAX(TB.COL2)</tt> again
and again.</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N103BF"></a>Types and Arithmetic Operations</h2></div></div><div></div></div><p>Table columns of all types supported by HSQLDB can be indexed and
can feature in comparisons. Types can be explicitly converted using the
CONVERT() library function, but in most cases they are converted
automatically. It is recommended not to use indexes on LONGVARBINARY,
LONGVARCHAR and OTHER columns, as these indexes will probably not be
allowed in future versions.</p><p>Previous versions of HSQLDB featured poor handling of arithmetic
operations. For example, it was not possible to insert
<tt class="literal">10/2.5</tt> into any DOUBLE or DECIMAL column. Since 1.7.0,
full operations are possible with the following rules:</p><p>TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL (without a
decimal point) are supported integral types and map to byte, short, int,
long and BigDecimal in Java. The SQL type dictates the maximum and minimum
values that can be held in a field of each type. For example the value
range for TINYINT is -128 to +127, although the actual Java type used for
handling TINYINT is <tt class="classname">java.lang.Integer</tt>.</p><p>REAL, FLOAT, DOUBLE are all mapped to double in Java.</p><p>DECIMAL and NUMERIC are mapped to
<tt class="classname">java.math.BigDecimal</tt> and can have very large
numbers of digits.</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N103D6"></a>Integral Types</h3></div></div><div></div></div><p>TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL (without a
decimal point) are fully interchangeable internally, and no data
narrowing takes place. Depending on the types of the operands, the
result of the operations is returned in a JDBC
<tt class="classname">ResultSet</tt> in any of related Java types:
<tt class="classname">Integer</tt>, <tt class="classname">Long</tt> or
<tt class="classname">BigDecimal</tt>. The
<tt class="literal">ResultSet.getXXXX()</tt> methods can be used to retrieve
the values so long as the returned value can be represented by the
resulting type. This type is deterministically based on the query, not
on the actual rows returned. The type does not change when the same
query that returned one row, returns many rows as a result of adding
more data to the tables.</p><p>If the SELECT statement refers to a simple column or function,
then the return type is the type corresponding to the column or the
return type of the function. For example:</p><div class="informalexample"><pre class="programlisting">
CREATE TABLE t(a INTEGER, b BIGINT); SELECT MAX(a), MAX(b) FROM t;</pre></div><p>would return a result set where the type of the first column is
<tt class="filename">java.lang.Integer</tt> and the second column is
<tt class="filename">java.lang.Long</tt>. However,</p><div class="informalexample"><pre class="programlisting">
SELECT MAX(a) + 1, MAX(b) + 1 FROM t;</pre></div><p>would return <tt class="filename">java.lang.Long</tt> and
<tt class="classname">BigDecimal</tt> values, generated as a result of
uniform type promotion for all the return values.</p><p>There is no built-in limit on the size of intermediate integral
values in expressions. As a result, you should check for the type of the
<tt class="classname">ResultSet</tt> column and choose an appropriate
<tt class="literal">getXXXX()</tt> method to retrieve it. Alternatively, you
can use the <tt class="literal">getObject()</tt> method, then cast the result
to <tt class="classname">java.lang.Number </tt> and use the
<tt class="literal">intValue()</tt> or <tt class="literal">longValue()</tt> methods
on the result.</p><p>When the result of an expression is stored in a column of a
database table, it has to fit in the target column, otherwise an error
is returned. For example when <tt class="literal">1234567890123456789012 /
12345687901234567890</tt> is evaluated, the result can be stored in
any integral type column, even a TINYINT column, as it is a small
value.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N10426"></a>Other Numeric Types</h3></div></div><div></div></div><p>In SQL statements, numbers with a decimal point are treated as
DECIMAL unless they are written with an exponent. Thus
<tt class="literal">0.2</tt> is considered a DECIMAL value but
<tt class="literal">0.2E0</tt> is considered a DOUBLE value.</p><p>When <tt class="literal">PreparedStatement.setDouble()</tt> or
<tt class="literal">setFloat()</tt> is used, the value is treated as a DOUBLE
automatically.</p><p>When a REAL, FLOAT or DOUBLE (all synonymous) is part of an
expression, the type of the result is DOUBLE.</p><p>Otherwise, when no DOUBLE value exists, if a DECIMAL or NUMERIC
value is part an expression, the type of the result is DECIMAL. The
result can be retrieved from a <tt class="classname">ResultSet</tt> in the
required type so long as it can be represented. This means DECIMAL
values can be converted to DOUBLE unless they are beyond the
<tt class="literal">Double.MIN_VALUE - Double.MAX_VALUE</tt> range. Similar to
integral values, when the result of an expression is stored in a table
column, it has to fit in the target column, otherwise an error is
returned.</p><p>The distinction between DOUBLE and DECIMAL is important when a
division takes place. When the terms are DECIMAL, the result is a value
with a scale (number of digits to the right of the decimal point) equal
to the larger of the scales of the two terms. With a DOUBLE term, the
scale will reflect the actual result of the operation. For example,
<tt class="literal">10.0/8.0</tt> (DECIMAL) equals <tt class="literal">1.2</tt> but
<tt class="literal">10.0E0/8.0E0</tt> (DOUBLE) equals <tt class="literal">1.25</tt>.
Without division operations, DECIMAL values represent exact arithmetic;
the resulting scale is the sum of the scales of the two terms when
multiplication is performed.</p><p>REAL, FLOAT and DOUBLE values are all stored in the database as
<tt class="classname">java.lang.Double</tt> objects. Special values such as
NaN and +-Infinity are also stored and supported. These values can be
submitted to the database via JDBC PreparedStatement methods and are
returned in ResultSet objects.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N1045F"></a>Bit and Boolean Types</h3></div></div><div></div></div><p>Since 1.7.2, BIT is simply an alias for BOOLEAN. The primary
representation of BOOLEAN column is <tt class="literal">'true'</tt> or
<tt class="literal">'false'</tt> either as the boolean type or as strings when
used from JDBC. This type of column can also be initialised using values
of any numeric type. In this case <tt class="literal">0</tt> is translated to
<tt class="literal">false</tt> and any other value such as 1 is translated to
<tt class="literal">true</tt>.</p><p>Since 1.7.3 the BOOLEAN type conforms to the SQL standards and
supports the UNDEFINED state in addition to TRUE or FALSE. NULL values
are treated as undefined. This improvement affects queries that contain
NOT IN. See the test text file, TestSelfNot.txt, for examples of the
queries.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N1047A"></a>Storage and Handling of Java Objects</h3></div></div><div></div></div><p>Since version 1.7.2 this support has improved and any serializable
JAVA Object can be inserted directly into a column of type OTHER using
any variation of <tt class="literal">PreparedStatement.setObject()</tt>
methods.</p><p>For comparison purposes and in indexes, any two Java Objects are
considered equal unless one of them is NULL. You cannot search for a
specific object or perform a join on a column of type OTHER.</p><p>Please note that HSQLDB is not an object-relational database. Java
Objects can simply be stored internally and no operations should be
performed on them other than assignment between columns of type OTHER or
tests for NULL. Tests such as <tt class="literal">WHERE object1 =
object2</tt>, or <tt class="literal">WHERE object1 = ? </tt>do not mean
what you might expect, as any non-null object would satisfy such a
tests. But <tt class="literal">WHERE object1 IS NOT NULL</tt> is perfectly
acceptable.</p><p>The engine does not return errors when normal column values are
assigned to Java Object columns (for example assigning an INTEGER or
STRING to such a column with an SQL statement such as <tt class="literal">UPDATE
mytable SET objectcol = intcol WHERE ...</tt>) but this is highly
likely to be disallowed in future. So please use columns of type OTHER
only to store your objects and nothing else.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N10499"></a>Type Size, Precision and Scale</h3></div></div><div></div></div><p>Prior to 1.7.2, all table column type definitions with a column
size, precision or scale qualifier were accepted and ignored.</p><p>In 1.8.0, such qualifiers must conform to the SQL standards. For
example INTEGER(8) is no longer acceptable. The qualifiers are still
ignored unless you set a database property. <tt class="literal">SET PROPERTY
"sql.enforce_strict_size" TRUE </tt>will enforce sizes for
CHARACTER or VARCHAR columns and pad any strings when inserting or
updating a CHARACTER column. The precision and scale qualifiers are also
enforced for DECIMAL and NUMERIC types. TIMESTAMP can be used with a
precision of 0 or 6 only.</p><p>Casting a value to a qualified CHARACTER type will result in
truncation or padding as you would expect. So a test such as
<tt class="literal">CAST (mycol AS VARCHAR(2)) = 'xy'</tt> will find the
values beginning with 'xy'. This is the equivalent of
<tt class="literal">SUBSTRING(mycol FROM 1 FOR 2)</tt> = 'xy'.</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N104AE"></a>Sequences and Identity</h2></div></div><div></div></div><p>The SEQUENCE keyword was introduced in 1.7.2 with a subset of the
SQL 200n standard syntax. Corresponding SQL 200n syntax for IDENTITY
columns has also been introduced.</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N104B3"></a>Identity Auto-Increment Columns</h3></div></div><div></div></div><p>Each table can contain one auto-increment column, known as the
IDENTITY column. An IDENTITY column is always treated as the primary key
for the table (as a result, multi-column primary keys are not possible
with an IDENTITY column present). Support has been added for
<tt class="literal">CREATE TABLE <tablename>(<colname> IDENTITY,
...)</tt> as a shortcut.</p><p>Since 1.7.2, the SQL standard syntax is used by default, which
allows the initial value to be specified. The supported form
is<tt class="literal">(<colname> INTEGER GENERATED BY DEFAULT AS
IDENTITY(START WITH n, [INCREMENT BY m])PRIMARY KEY, ...)</tt>.
Support has also been added for <tt class="literal">BIGINT</tt> identity
columns. As a result, an IDENTITY column is simply an INTEGER or BIGINT
column with its default value generated by a sequence generator.</p><p>When you add a new row to such a table using an <tt class="literal">INSERT
INTO <tablename> ...; </tt>statement, you can use the NULL
value for the IDENTITY column, which results in an auto-generated value
for the column. The <tt class="literal">IDENTITY() </tt>function returns the
last value inserted into any IDENTITY column by this connection. Use
<tt class="literal">CALL IDENTITY(); </tt>as an SQL statement to retrieve this
value. If you want to use the value for a field in a child table, you
can use <tt class="literal">INSERT INTO <childtable> VALUES
(...,IDENTITY(),...);</tt>. Both types of call to<tt class="literal">
IDENTITY()</tt> must be made before any additional update or insert
statements are issued on the database.</p><p>The next IDENTITY value to be used can be set with the
<pre class="programlisting">ALTER TABLE ALTER COLUMN <column name> RESTART WITH <new value>;</pre></p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N104E1"></a>Sequences</h3></div></div><div></div></div><p>The SQL 200n syntax and usage is different from what is supported
by many existing database engines. Sequences are created with the
<tt class="literal">CREATE SEQUENCE</tt> command and their current value can
be modified at any time with <tt class="literal">ALTER SEQUENCE</tt>. The next
value for a sequence is retrieved with the <tt class="literal">NEXT VALUE FOR
<name></tt> expression. This expression can be used for
inserting and updating table rows. You can also use it in select
statements. For example, if you want to number the returned rows of a
SELECT in sequential order, you can use:</p><div class="example"><a name="N104F2"></a><p class="title"><b>Example 2.3. Numbering returned rows of a SELECT in sequential order</b></p><pre class="programlisting">
SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable WHERE ...</pre></div><p>Please note that the semantics of sequences is not exactly the
same as defined by SQL 200n. For example if you use the same sequence
twice in the same row insert query, you will get two different values,
not the same value as required by the standard.</p><p>You can query the SYSTEM_SEQUENCES table for the next value that
will be returned from any of the defined sequences. The SEQUENCE_NAME
column contains the name and the NEXT_VALUE column contains the next
value to be returned.</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N104FC"></a>Issues with Transactions</h2></div></div><div></div></div><p>HSQLDB supports transactions at the READ_UNCOMMITTED level, also
known as level 0 transaction isolation. This means that during the
lifetime of a transaction, other connections to the database can see the
changes made to the data. Transaction support works well in general.
Reported bugs concerning transactions being committed if the database is
abruptly closed have been fixed. However, the following issues may be
encountered only with multiple connections to a database using
transactions:</p><p>If two transactions modify the same row, no exception is raised when
both transactions are committed. This can be avoided by designing your
database in such a way that application data consistency does not depend
on exclusive modification of data by one transaction. You can set a
database property to cause an exception when this happens.<pre class="programlisting">SET PROPERTY "sql.tx_no_multi_rewrite" TRUE</pre>When
an <tt class="literal">ALTER TABLE .. INSERT COLUMN</tt> or <tt class="literal">DROP
COLUMN</tt> command results in changes to the table structure, the
current session is committed. If an uncommitted transaction started by
another connections has changed the data in the affected table, it may not
be possible to roll it back after the <tt class="literal">ALTER TABLE</tt>
command. This may also apply to <tt class="literal">ADD INDEX</tt> or
<tt class="literal">ADD CONSTRAINT</tt> commands. It is recommended to use these
<tt class="literal">ALTER</tt> commands only when it is known that other
connections are not using transactions.</p><p>After a CHECKPOINT command is issued, uncommitted transactions can
be continued, committed, or rolled back. However, if the database is not
subsequently closed properly with the SHUTDOWN command, any such
transaction that still remains uncommitted at the time of shutdown, is
part committed (to the state at CHECKPOINT) at the next startup. It is
recommended to use the CHECKPOINT command either when no uncommitted
transactions is in progress, or it is known that any such transaction is
not likely to last for such a long time that an abnormal shutdown might
affect its data.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N10521"></a>New Features and Changes</h2></div></div><div></div></div><p>In recent versions leading to 1.8.0 many enhancements were made for
better SQL support. These are listed in the <a href="ch09.html" title="Chapter 9. SQL Syntax">SQL Syntax</a> chapter, in
<a href="../changelog_1_8_0.txt" target="_top">../changelog_1_8_0.txt</a> and <a href="../changelog_1_7_2.txt" target="_top">../changelog_1_7_2.txt</a>.
Functions and expressions such as
POSITION(), SUBSTRING(), NULLIF(), COALESCE(), CASE ... WHEN .. ELSE, ANY,
ALL etc. are among them. Other enhancements may not be very obvious in the
documentation but can result in changes of behaviour from previous
versions. Most significant among these are handling of NULL values in
joins (null columns are no longer joined) and OUTER joins (the results are
now correct). You should test your applications with the new version to
ensure they do not rely on past incorrect behaviour of the engine. The
engine will continue to evolve in future versions towards full SQL
standard support, so it is best not to rely on any non-standard feature of
the current version.</p></div></div><div class="navfooter"><hr><table summary="Navigation footer" width="100%"><tr><td align="left" width="40%"><a accesskey="p" href="ch01.html"><img src="navicons/prev.gif" alt="Prev"></a> </td><td align="center" width="20%"><a accesskey="u" href="index.html"><img src="navicons/up.gif" alt="Up"></a></td><td align="right" width="40%"> <a accesskey="n" href="ch03.html"><img src="navicons/next.gif" alt="Next"></a></td></tr><tr><td valign="top" align="left" width="40%">Chapter 1. Running and Using Hsqldb </td><td align="center" width="20%"><a accesskey="h" href="index.html"><img src="navicons/home.gif" alt="Home"></a></td><td valign="top" align="right" width="40%"> Chapter 3. UNIX Quick Start</td></tr></table></div></body></html>
distrib
>
Mageia
>
1
>
i586
>
media
>
core-updates
>
by-pkgid
>
58de6be3705c875194e822c24ebf1a0a
>
files
>
18
