This is the preferred way of running a database server and the fastest one. A proprietary communications protocol is used for this mode. A command similar to those used for running tools and described above is used for running the server. The following example of the command for starting the server starts the server with one (default) database with files named "mydb.*" and the public name of "xdb". The public name hides the file names from users.

  java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb

The command line argument --help can be used to get a list of available arguments.

This method of access is used when the computer hosting the database server is restricted to the HTTP protocol. The only reason for using this method of access is restrictions imposed by firewalls on the client or server machines and it should not be used where there are no such restrictions. The HyperSQL HTTP Server is a special web server that allows JDBC clients to connect via HTTP. The server can also act as a small general-purpose web server for static pages.

To run an HTTP server, replace the main class for the server in the example command line above with the following:

  org.hsqldb.server.WebServer

The command line argument --help can be used to get a list of available arguments.

This method of access also uses the HTTP protocol. It is used when a separate servlet engine (or application server) such as Tomcat or Resin provides access to the database. The Servlet Mode cannot be started independently from the servlet engine. The Servlet class, in the HSQLDB jar, should be installed on the application server to provide the connection. The database is specified using an application server property. Refer to the source file src/org/hsqldb/server/Servlet.java to see the details.

Both HTTP Server and Servlet modes can only be accessed using the JDBC driver at the client end. They do not provide a web front end to the database. The Servlet mode can serve only a single database.

Please note that you do not normally use this mode if you are using the database engine in an application server. In this situation, connections to a catalog are usually made in-process, or using a separate Server

When a HyperSQL server is running, client programs can connect to it using the HSQLDB JDBC Driver contained in hsqldb.jar. Full information on how to connect to a server is provided in the Java Documentation for JDBCConnection (located in the /doc/apidocs directory of HSQLDB distribution). A common example is connection to the default port (9001) used for the hsql: protocol on the same machine:

  try {
      Class.forName("org.hsqldb.jdbc.JDBCDriver" );
  } catch (Exception e) {
      System.err.println("ERROR: failed to load HSQLDB JDBC driver.");
      e.printStackTrace();
      return;
  }

  Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb", "SA", "");

If the HyperSQL HTTP server is used, the protocol is http: and the URL will be different:

  Connection c = DriverManager.getConnection("jdbc:hsqldb:http://localhost/xdb", "SA", "");

Note in the above connection URL, there is no mention of the database file, as this was specified when running the server. Instead, the public name defined for dbname.0 is used. Also, see the HyperSQL Network Listeners chapter for the connection URL when there is more than one database per server instance.

When a HyperSQL server is run, network access should be adequately protected. Source IP addresses may be restricted by use of our Access Control List feature, network filtering software, firewall software, or standalone firewalls. Only secure passwords should be used-- most importantly, the password for the default system user should be changed from the default empty string. If you are purposefully providing data to the public, then the wide-open public network connection should be used exclusively to access the public data via read-only accounts. (i.e., neither secure data nor privileged accounts should use this connection). These considerations also apply to HyperSQL servers run with the HTTP protocol.

HyperSQL provides two optional security mechanisms. The encrypted SSL protocol, and Access Control Lists. Both mechanisms can be specified when running the Server or WebServer. From the client, the URL's co connect to an SSL server is slightly different:

  Connection c = DriverManager.getConnection("jdbc:hsqldb:hsqls://localhost/xdb", "SA", "");
  Connection c = DriverManager.getConnection("jdbc:hsqldb:https://localhost/xdb", "SA", "");

A server can provide connections to more than one database. In the examples above, more than one set of database names can be specified on the command line. It is also possible to specify all the databases in a .properties file, instead of the command line. These capabilities are covered in the HyperSQL Network Listeners chapter

TEMPORARY tables are not saved and last only for the lifetime of the Connection object. The contents of each TEMP table is visible only from the Connection that was used to populate it. The definition of TEMP tables conforms to the GLOBAL TEMPORARY type in the SQL standard. The definition of the table persists but each new connections sees its own copy of the table, which is empty at the beginning. When the connection commits, the contents of the table are cleared by default. If the table definition statements includes ON COMMIT PRESERVE ROWS, then the contents are kept when a commit takes place.

HSQLDB supports the Standard definition of persistent base table, but defines three types according to the way the data is stored. These are MEMORY tables, CACHED tables and TEXT tables.

Memory tables are the default type when the CREATE TABLE command is used. Their data is held entirely in memory but any change to their structure or contents is written to the *.log and *.script files. The *.script file and the *.log file are read the next time the database is opened, and the MEMORY tables are recreated with all their contents. So unlike TEMPORARY tables, MEMORY tables are persistent. When the database is opened, all the data for the memory tables is read and inserted. This process may take a long time if the database is larger than tens of megabytes. When the database is shutdown, all the data is saved. This can also take a long time.

CACHED tables are created with the CREATE CACHED TABLE command. Only part of their data or indexes is held in memory, allowing large tables that would otherwise take up to several hundred megabytes of memory. Another advantage of cached tables is that the database engine takes less time to start up when a cached table is used for large amounts of data. The disadvantage of cached tables is a reduction in speed. Do not use cached tables if your data set is relatively small. In an application with some small tables and some large ones, it is better to use the default, MEMORY mode for the small tables.

TEXT tables use a CSV (Comma Separated Value) or other delimited text file as the source of their data. You can specify an existing CSV file, such as a dump from another database or program, as the source of a TEXT table. Alternatively, you can specify an empty file to be filled with data by the database engine. TEXT tables are efficient in memory usage as they cache only part of the text data and all of the indexes. The Text table data source can always be reassigned to a different file if necessary. The commands are needed to set up a TEXT table as detailed in the Text Tables chapter.

With all-in-memory databases, both MEMORY table and CACHED table declarations are treated as declarations for non-persistent memory tables. TEXT table declarations are not allowed in these databases.

The default type of table resulting from future CREATE TABLE statements can be specified with the SQL command:

    SET DATABASE DEFAULT TABLE TYPE { CACHED | MEMORY };

    SET TABLE <table name> TYPE { CACHED | MEMORY };

Lobs are logically stored in columns of tables. Their physical storage is a separate *.lobs file. In version 2.0 this file is created as soon as a BLOB or CLOB is inserted into the database. The file will grow as new lobs are inserted into the database. In version 2.0, the *.lobs file is never deleted even if all lobs are deleted from the database (In this case you can delete the .lobs file after a SHTUDOWN).

TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL (without a decimal point) are the supported integral types. They correspond respectively to byte, short, int, long, BigDecimal and BigDecimal Java types in the range of values that they can represent (NUMERIC and DECIMAL are equivalent). The type TINYINT is an HSQLDB extension to the SQL Standard, while the others conform to the Standard definition. 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. The bit precision of TINYINT, SMALLINT, INTEGER and BIGINT is respectively 8, 16, 32 and 64. For NUMERIC and DECIMAL, decimal precision is used.

DECIMAL and NUMERIC with decimal fractions are mapped to java.math.BigDecimal and can have very large numbers of digits. In HyperSQL the two types are equivalent. These types, together with integral types, are called exact numeric types.

In HyperSQL, REAL, FLOAT, DOUBLE are equivalent and all mapped to double in Java. These types are defined by the SQL Standard as approximate numeric types. The bit-precision of all these types is 64 bits.

The decimal precision and scale of NUMERIC and DECIMAL types can be optionally defined. For example, DECIMAL(10,2) means maximum total number of digits is 10 and there are always 2 digits after the decimal point, while DECIMAL(10) means 10 digits without a decimal point. The bit-precision of FLOAT can also be defined, but in this case, it is ignored and the default bit-precision of 64 is used. The default precision of NUMERIC and DECIMAL (when not defined) is 100.

Note: If a database has been set to ignore type precision limits with the SET DATABASE SQL SIZE FALSE command, then a type definition of DECIMAL with no precision and scale is treated as DECIMAL(100,10). In normal operation, it is treated as DECIMAL(100).

Integral Types

In expressions, TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL (without a decimal point) are fully interchangeable, and no data narrowing takes place.

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:

    CREATE TABLE t(a INTEGER, b BIGINT);
    SELECT MAX(a), MAX(b) FROM t;

will return a ResultSet where the type of the first column is java.lang.Integer and the second column is java.lang.Long. However,

    SELECT MAX(a) + 1, MAX(b) + 1 FROM t;

will return java.lang.Long and BigDecimal values, generated as a result of uniform type promotion for all the return values. Note that type promotion to BigDecimal ensures the correct value is returned if MAX(b) evaluates to Long.MAX_VALUE.

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 ResultSet column and choose an appropriate getXXXX() method to retrieve it. Alternatively, you can use the getObject() method, then cast the result to java.lang.Number and use the intValue() or longValue() methods on the result.

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 1234567890123456789012 / 12345687901234567890 is evaluated, the result can be stored in any integral type column, even a TINYINT column, as it is a small value.

In SQL Statements, an integer literal is treated as INTEGER, unless its value does not fit. In this case it is treated as BIGINT or DECIMAL, depending on the value.

Depending on the types of the operands, the result of the operations is returned in a JDBC ResultSet in any of related Java types: Integer, Long or BigDecimal. The ResultSet.getXXXX() 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.

Other Numeric Types

In SQL statements, number literals with a decimal point are treated as DECIMAL unless they are written with an exponent. Thus 0.2 is considered a DECIMAL value but 0.2E0 is considered a DOUBLE value.

When an approximate numeric type, REAL, FLOAT or DOUBLE (all synonymous) is part of an expression involving different numeric types, the type of the result is DOUBLE. DECIMAL values can be converted to DOUBLE unless they are beyond the Double.MIN_VALUE - Double.MAX_VALUE range. For example, A * B, A / B, A + B, etc. will return a DOUBLE value if either A or B is a DOUBLE.

Otherwise, when no DOUBLE value exists, if a DECIMAL or NUMERIC value is part an expression, the type of the result is DECIMAL or NUMERIC. Similar to integral values, when the result of an expression is assigned to a table column, the value has to fit in the target column, otherwise an error is returned. This means a small, 4 digit value of DECIMAL type can be assigned to a column of SMALLINT or INTEGER, but a value with 15 digits cannot.

When a DECIMAL values is multiplied by a DECIMAL or integral type, the resulting scale is the sum of the scales of the two terms. When they are divided, 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. The precision for both operations is calculated (usually increased) to allow all possible results.

The distinction between DOUBLE and DECIMAL is important when a division takes place. For example, 10.0/8.0 (DECIMAL) equals 1.2 but 10.0E0/8.0E0 (DOUBLE) equals 1.25. Without division operations, DECIMAL values represent exact arithmetic.

REAL, FLOAT and DOUBLE values are all stored in the database as java.lang.Double 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. The result can be retrieved from a ResultSet in the required type so long as it can be represented. When PreparedStatement.setDouble() or setFloat() is used, the value is treated as a DOUBLE automatically.

In short,

<numeric type> ::= <exact numeric type> | <approximate numeric type>

<exact numeric type> ::= NUMERIC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ] | { DECIMAL | DEC } [ <left paren> <precision> [ <comma> <scale> ] <right paren> ] | SMALLINT | INTEGER | INT | BIGINT

<approximate numeric type> ::= FLOAT [ <left paren> <precision> <right paren> ] | REAL | DOUBLE PRECISION

<precision> ::= <unsigned integer>

<scale> ::= <unsigned integer>

The BOOLEAN type conforms to the SQL Standard and represents the values TRUE, FALSE and UNKNOWN. This type of column can be initialised with Java boolean values, or with NULL for the UNKNOWN value.

The three-value logic is sometimes misunderstood. For example, x IN (1, 2, NULL) does not return true if x is NULL.

In previous versions of HyperSQL, BIT was simply an alias for BOOLEAN. In version 2.0, BIT is a single-bit bit map.

<boolean type> ::= BOOLEAN

The SQL Standard does not support type conversion to BOOLEAN apart from character strings that consists of boolean literals. Because the BOOLEAN type is relatively new to the Standard, several database products used other types to represent boolean values. For improved compatibility, HyperSQL allows some type conversions to boolean.

Values of BIT and BIT VARYING types with length 1 can be converted to BOOLEAN. If the bit is set, the result of conversion is the TRUE value, otherwise it is FALSE.

Values of TINYINT, SMALLINT, INTEGER and BIGINT types can be converted to BOOLEAN. If the value is zero, the result is the FALSE value, otherwise it is TRUE.

The CHARACTER, CHARACTER VARYING and CLOB types are the SQL Standard character string types. CHAR, VARCHAR and CHARACTER LARGE OBJECT are synonyms for these types. HyperSQL also supports LONGVARCHAR as a synonym for VARCHAR. If LONGVARCHAR is used without a length, then a length of 1M is assigned.

HyperSQL's default character set is Unicode, therefore all possible character strings can be represented by these types.

The SQL Standard behaviour of the CHARACTER type is a remnant of legacy systems in which character strings are padded with spaces to fill a fixed width. These spaces are sometimes significant while in other cases they are silently discarded. It would be best to avoid the CHARACTER type altogether. With the rest of the types, the strings are not padded when assigned to columns or variables of the given type. The trailing spaces are still considered discardable for all character types. Therefore if a string with trailing spaces is too long to assign to a column or variable of a given length, the spaces beyond the type length are discarded and the assignment succeeds (provided all the characters beyond the type length are spaces).

The VARCHAR and CLOB types have length limits, but the strings are not padded by the system. Note that if you use a large length for a VARCHAR or CLOB type, no extra space is used in the database. The space used for each stored item is proportional to its actual length.

If CHARACTER is used without specifying the length, the length defaults to 1. For the CLOB type, the length limit can be defined in units of kilobyte (K, 1024), megabyte (M, 1024 * 1024) or gigabyte (G, 1024 * 1024 * 1024), using the <multiplier>. If CLOB is used without specifying the length, the length defaults to 1M.

<character string type> ::= { CHARACTER | CHAR } [ <left paren> <character length> <right paren> ] | { CHARACTER VARYING | CHAR VARYING | VARCHAR } <left paren> <character length> <right paren> | LONGVARCHAR [ <left paren> <character length> <right paren> ] | <character large object type>

<character large object type> ::= { CHARACTER LARGE OBJECT | CHAR LARGE OBJECT | CLOB } [ <left paren> <character large object length> <right paren> ]

<character length> ::= <unsigned integer> [ <char length units> ]

<large object length> ::= <length> [ <multiplier> ] | <large object length token>

<character large object length> ::= <large object length> [ <char length units> ]

<large object length token> ::= <digit>... <multiplier>

<multiplier> ::= K | M | G

<char length units> ::= CHARACTERS | OCTETS

CHAR(10)
CHARACTER(10)
VARCHAR(2)
CHAR VARYING(2)
CLOB(1000)
CLOB(30K)
CHARACTER LARGE OBJECT(1M)
LONGVARCHAR

The BINARY, BINARY VARYING and BLOB types are the SQL Standard binary string types. VARBINARY and BINARY LARGE OBJECT are synonyms for BINARY VARYING and BLOB types. HyperSQL also supports LONGVARBINARY as a synonym for VARBINARY.

Binary string types are used in a similar way to character string types. There are several built-in functions that are overloaded to support character, binary and bit strings.

The BINARY type represents a fixed width-string. Each shorter string is padded with zeros to fill the fixed width. Similar to the CHARACTER type, the trailing zeros in the BINARY string are simply discarded in some operations. For the same reason, it is best to avoid this particular type.

If BINARY is used without specifying the length, the length defaults to 1. For the BLOB type, the length limit can be defined in units of kilobyte (K, 1024), megabyte (M, 1024 * 1024) or gigabyte (G, 1024 * 1024 * 1024), using the <multiplier>. If BLOB is used without specifying the length, the length defaults to 1M.

<binary string type> ::= BINARY [ <left paren> <length> <right paren> ] | { BINARY VARYING | VARBINARY } <left paren> <length> <right paren> | LONGVARBINARY [ <left paren> <length> <right paren> ] | <binary large object string type>

<binary large object string type> ::= { BINARY LARGE OBJECT | BLOB } [ <left paren> <large object length> <right paren> ]

<length> ::= <unsigned integer>

BINARY(10)
VARBINARY(2)
BINARY VARYING(2)
BLOB(1000)
BLOB(30K)
BINARY LARGE OBJECT(1M)
LONGVARBINARY

The BIT and BIT VARYING types are the supported bit string types. These types were defined by SQL:1999 but were later removed from the Standard. Bit types represent bit maps of given lengths. Each bit is 0 or 1. The BIT type represents a fixed width-string. Each shorter string is padded with zeros to fill the fixed with. If BIT is used without specifying the length, the length defaults to 1. The BIT VARYING type has a maximum width and shorter strings are not padded.

Before the introduction of the BOOLEAN type to the SQL Standard, a sigle-bit string of the type BIT(1) was commonly used. For compatibility with other products that do not conform to, or extend, the SQL Standard, HyperSQL allows values of BIT and BIT VARYING types with length 1 to be converted to and from the BOOLEAN type. BOOLEAN TRUE is considered equal to B'1', BOOLEAN FALSE is considered equal to B'0'.

For the same reason, numeric values can be assigned to columns and variables of the type BIT(1). For assignment, the numeric value zero is converted to B'0', while all other values are converted to B'1'. For comparison, numeric values 1 is considered equal to B'1' and numeric value zero is considered equal to B'0'.

It is not allowed to perform other arithmetic or boolean operations involving BIT(1) and BIT VARYING(1). The kid of operations allowed on bit strings are analogous to those allowed on BINARY and CHARACTER strings. Several built-in functions support all three types of string.

<bit string type> ::= BIT [ <left paren> <length> <right paren> ] | BIT VARYING <left paren> <length> <right paren>

BIT
BIT(10)
BIT VARYING(2)

Any serializable JAVA Object can be inserted directly into a column of type OTHER using any variation of PreparedStatement.setObject() methods.

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.

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 WHERE object1 = object2 do not mean what you might expect, as any non-null object would satisfy such a tests. But WHERE object1 IS NOT NULL is perfectly acceptable.

The engine does not allow normal column values to be assigned to Java Object columns (for example, assigning an INTEGER or STRING to such a column with an SQL statement such as UPDATE mytable SET objectcol = intcol WHERE ...).

<java object type> ::= OTHER

In older version of HyperSQL, all table column type definitions with a column length, precision or scale qualifier were accepted and ignored. HSQLDB 1.8 enforced correctness but included an option to enforce the length, precision or scale.

In HyperSQL 2.0, length, precision and scale qualifiers are always enforced. For backward compatibility, when older databases which had the property hsqldb.enforce_strict_size=false are converted to version 2.0, this property is retained. However, this is a temporary measure. You should test your application to ensure the length, precision and scale that is used for column definitions is appropriate for the application data. You can test with the default database setting, which enforces the sizes.

String types, including all BIT, BINARY and CHAR string types plus CLOB and BLOB, are generally defined with a length. If no length is specified for BIT, BINARY and CHAR, the default length is 1. For CLOB and BLOB an implementation defined length of 1M is used.

TIME and TIMESTAMP types can be defined with a fractional second precision between 0 and 9. INTERVAL type definition may have precision and, in some cases, fraction second precision. DECIMAL and NUMERIC types may be defined with precision and scale. For all of these types a default precision or scale value is used if one is not specified. The default scale is 0. The default fractional precision for TIME is 0, while it is 6 for TIMESTAMP.

Values can be converted from one type to another in two different ways: by using explicit CAST expression or by implicit conversion used in assignment, comparison and aggregation.

String values cannot be assigned to VARCHAR columns if they are longer than the defined type length. For CHARACTER columns, a long string can be assigned (with truncation) only if all the characters after the length are spaces. Shorter strings are padded with the space character when inserted into a CHARACTER column. Similar rules are applied to VARBINARY and BINARY columns. For BINARY columns, the padding and truncation rules are applied with zero bytes, instead of spaces.

Explicit CAST of a value to a CHARACTER or VARCHAR type will result in forced truncation or padding. So a test such as CAST (mycol AS VARCHAR(2)) = 'xy' will find the values beginning with 'xy'. This is the equivalent of SUBSTRING(mycol FROM 1 FOR 2)= 'xy'.

For all numeric types, the rules of explicit cast and implicit conversion are the same. If cast or conversion causes any digits to be lost from the fractional part, it can take place. If the non-fractional part of the value cannot be represented in the new type, cast or conversion cannot take place and will result in a data exception.

There are special rules for DATE, TIME, TIMESTAMP and INTERVAL casts and conversions.

The type of a table column, a routine parameter, a variable, or the return value of a function can be defined as an array.

<array type> ::= <data type> ARRAY [ <left bracket or trigraph> <maximum cardinality> <right bracket or trigraph> ]

The word ARRAY is added to any valid type definition except BLOB and CLOB type definitions. If the optional <maximum cardinality> is not used, the default value is 1024. The size of the array cannot be extended beyond maximum cardinality.

In the example below, the table contains a column of integer arrays and a column of varchar arrays. The VARCHAR array has an explicit maximum size of 10, which means each array can have between 0 and 10 elements. The INTEGER array has the default maximum size of 1024. The id column has a default clause with an empty array. The default clause can be defined only as DEFAULT NULL or DEFAULT ARRAY[] and does not allow arrays containing elements.

CREATE TABLE t (id INT PRIMARY KEY, scores INT ARRAY DEFAULT ARRAY[], names VARCHAR(20) ARRAY[10])

An array can be constructed from value expressions or a query expression.

<array value constructor by enumeration> ::= ARRAY <left bracket or trigraph> <array element list> <right bracket or trigraph>

<array element list> ::= <value expression> [ { <comma> <value expression> }... ]

<array value constructor by query> ::= ARRAY <left paren> <query expression> [ <order by clause> ] <right paren>

In the examples below, arrays are constructed from values, column references or variables, function calls, or query expressions.

ARRAY [ 1, 2, 3 ]
ARRAY [ 'HOT', 'COLD' ]
ARRAY [ var1, var2, CURRENT_DATE ]
ARRAY (SELECT lastname FROM namestable ORDER BY id)

The most common operations on an array element reference and assignment, which are used when reading or writing an element of the array. Unlike Java and many other languages, arrays are extended if an element is assigned to an index beyond the current length. This can result in gaps containing NULL elements. Array length cannot exceed the maximum cardinality.

Elements of all arrays, including those that are the result of function calls or other operations can be referenced for reading.

<array element reference> ::= <array value expression> <left bracket> <numeric value expression> <right bracket>

Elements of arrays that are table columns or routine variables can be referenced for writing. This is done in a SET statement, either inside an UPDATE statement, or as a separate statement in the case of routine variables, OUT and INOUT parameters.

<target array element specification> ::= <target array reference> <left bracket or trigraph> <simple value specification> <right bracket or trigraph>

<target array reference> ::= <SQL parameter reference> | <column reference>

Note that only simple values or variables are allowed for the array index when an assignment is performed. The examples below demonstrates how elements of the array are referenced in SELECT and an UPDATE statement.

SELECT scores[ranking], names[ranking] FROM t JOIN t1 on (t.id = t1.tid)
UPDATE t SET scores[2] = 123, names[2] = 'Reds' WHERE id = 10

Several SQL operations and functions can be used with arrays.

CONCATENATION

Array concatenation is performed similar to string concatenation. All elements of the array on the right are appended to the array on left.

<array concatenation> ::= <array value expression 1> <concatenation operator> <array value expression 2>

<concatenation operator> ::= ||

FUNCTIONS

Three functions operate on arrays. Details are described in the Built In Functions chapter.

CARDINALITY <left paren> <array value expression> <right paren>

MAX_CARDINALITY <left paren> <array value expression> <right paren>

Array cardinality and max cardinality are functions that return an integer. CARDINALITY returns the element count, while MAX_CARDINALITY returns the maximum declared cardinality of an array.

TRIM_ARRAY <left paren> <array value expression> <comma> <numeric value expression> <right paren>

The TRIM_ARRAY function returns a copy of an array with the specified number of elements removed from the end of the array. The <array value expression> can be any expression that evaluates to an array.

CAST

An array can be cast into an array of a different type. Each element of the array is cast into the element type of the target array type.

UNNEST

Arrays can be converted into table references with the UNNEST keyword.

UNNEST(<array value expression>) [ WITH ORDINALITY ]

The <array value expression> can be any expression that evaluates to an array. A table is returned that contains one column when WITH ORDINALITY is not used, or two columns when WITH ORDINALITY is used. The first column contains the elements of the array (including all the nulls). When the table has two columns, the second column contains the ordinal position of the element in the array. When UNNEST is used in the FROM clause of a query, it implies the LATERAL keyword, which means the array that is converted to table can belong to any table that precedes the UNNEST in the FROM clause. This is explained in the Data Access and Change chapter.

COMPARISON

Arrays can be compared for equality, but they cannot be compared for ordering or ranges. Array expressions are therefore not allowed in an ORDER BY clause, or in a comparison expression such as GREATER THAN. Two arrays are equal if they have the same length and the values at each index position are either equal or both NULL.

USER DEFINED FUNCTIONS and PROCEDURES

Array parameters, variables and return values can be specified in user defined functions and procedures, including aggregate functions. An aggregate function can return an array that contains all the scalar values that have been aggregated. These capabilities allow a wider range of applications to be covered by user defined functions and easier data exchange between the engine and the user's application.

The system attributes reflect the current mode of operation for the session. These attributes can be accessed with function calls and can be referenced in queries. For example, they can be returned using the VALUES <attribute function>, ... statement.

The named attributes such as CURRENT_USER, CURRENT_SCHEMA, etc. are SQL Standard functions. Other attributes of the session, such as auto-commit or read-only modes can be read using other built-in functions. All these functions are listed in the Built In Functions chapter.

Session variables are user-defined variables created the same way as the variables for stored procedures and functions. Currently, these variables cannot be used in general SQL statements. They can be assigned to IN, INOUT and OUT parameters of stored procedures. This allows calling stored procedures which have INOUT or OUT arguments and is useful for development and debugging. See the example in the SQL-Invoked Routines chapter, under Formal Parameters.

  DECLARE counter INTEGER DEFAULT 3;
  DECLARE result VARCHAR(20) DEFAULT NULL;
  SET counter=15;
  CALL myroutine(counter, result)

With necessary access privileges, sessions can access all table, including GLOBAL TEMPORARY tables, that are defined in schemas. Although GLOBAL TEMPORARY tables have a single name and definition which applies to all sessions that use them, the contents of the tables are different for each session. The contents are cleared either at the end of each transaction or when the session is closed.

Session tables are different because their definition is visible only within the session that defines a table. The definition is dropped when the session is closed. Session tables do not belong to schemas.

<temporary table declaration> ::= DECLARE LOCAL TEMPORARY TABLE <table name> <table element list> [ ON COMMIT { PRESERVE | DELETE } ROWS ]

The syntax for declaration is based on the SQL Standard. A session table cannot have FOREIGN KEY constraints, but it can have PRIMARY KEY, UNIQUE or CHECK constraints. A session table definition cannot be modified by adding or removing columns, indexes, etc.

It is possible to refer to a session table using its name, which takes precedence over a schema table of the same name. To distinguish a session table from schema tables, the pseudo schema name, MODULE can be used. An example is given below:

  DECLARE LOCAL TEMPORARY TABLE buffer (id INTEGER PRIMARY KEY, textdata VARCHAR(100)) ON COMMIT PRESERVE ROWS
  INSERT INTO module.buffer SELECT id, firstname || ' ' || lastname FROM customers
  -- do some more work
  DROP TABLE module.buffer

The two-phase locking model is the default mode. It is referred to by the keyword, LOCKS. In the 2PL model, each table that is read by a transaction is locked with a shared lock, and each table that is written to is locked with an exclusive lock. If two sessions read and modify different tables then both go through simultaneously. If one session tries to lock a table that has been locked by the other, if both locks are shared locks, it will go ahead. If either of the locks is an exclusive lock, the engine will put the session in wait until the other session commits or rolls back its transaction. In some cases the engine will invalidate the transaction of the current session, if the action would result in deadlock.

HyperSQL also supports explicit locking of a group of tables for the duration of the current transaction. Use of this command blocks access to the locked tables by other sessions and ensures the current session can complete the intended reads and writes on the locked tables.

If a table is read-only, it will not be locked by any transaction.

The READ UNCOMMITTED isolation level can be used in 2PL modes for read-only operations. It is the same as READ COMMITTED plus read only.

The READ COMMITTED isolation level is the default. It keeps write locks on tables until commit, but releases the read locks after each operation.

The REPEATABLE READ level is upgraded to SERIALIZABLE. These levels keep both read and write locks on tables until commit.

It is possible to perform some critical operations at the SERIALIZABLE level, while the rest of the operations are performed at the READ COMMITTED level.

Note: two phase locking refers to two periods in the life of a transaction. In the first period, locks are acquired, in the second period locks are released. No new lock is acquired after releasing a lock.

This model is referred to as MVLOCKS. It works the same way as normal 2PL as far as updates are concerned.

SNAPSHOT ISOLATION is a multiversion concurrency strategy which uses the snapshot of the whole database at the time of the start of the transaction. In this model, read only transactions use SNAPSHOT ISOLATION. While other sessions are busy changing the database, the read only session sees a consistent view of the database and can access all the tables even when they are locked by other sessions for updates.

There are many applications for this mode of operation. In heavily updated data sets, this mode allows uninterrupted read access to the data.

When multiple connections are used to access the database, the transaction manager controls their activities. When each transaction performs only reads or writes on a single table, there is no contention. Each transaction waits until it can obtain a lock then performs the operation and commits. All contentions occur when transactions perform reads and writes on more than one table, or perform a read, followed by a write, on the same table.

For example, when sessions are working at the SERIALIZABLE level, when multiple sessions first read from a table in order to check if a row exists, then insert a row into the same table when it doesn't exist, there will be regular contention. Transaction A reads from the table, then does Transaction B. Now if either Transaction A or B attempts to insert a row, it will have to be terminated as the other transaction holds a shared lock on the table. If instead of two operations, a single MERGE statement is used to perform the read and write, no contention occurs because both locks are obtained at the same time.

Alternatively, there is the option of obtaining the necessary locks with an explicit LOCK TABLE statement. This statement should be executed before other statements and should include the names of all the tables and the locks needed. After this statement, all the other statements in the transaction can be executed and the transaction committed. The commit will remove all the locks.

HyperSQL is fully multi threaded. It therefore allows different transactions to execute concurrently so long as they are not modifying the same table.

In the MVCC model, there are no shared, read locks. Exclusive locks are used on individual rows, but their use is different. Transactions can read and modify the same table simultaneously, generally without waiting for other transactions.

When transactions are running at READ COMMITTED level, no conflict will normally occur. If a transaction that runs at this level wants to modify a row that has been modified by another uncommitted transaction, then the engine puts the transaction in wait, until the other transaction has committed. The transaction then continues automatically. (Conflict is possible if each transaction is waiting for a different row modified by the other transaction, in which case, one of the transactions is terminated). This isolation level is called READ CONSISTENCY.

When transactions are running in REPEATABLE READ or SERIALIZABLE isolation levels, conflict is more likely to happen. There is no difference in operation between these two isolation levels. If a transaction that runs at these levels wants to modify a row that has been modified by another uncommitted transaction, the engine will invalidate the current transaction and roll back all its changes. This isolation level is called SNAPSHOT ISOLATION.

In the MVCC model, READ UNCOMMITTED is promoted to READ COMMITTED, as the new architecture is based on multi-version rows for uncommitted data and more than one version may exist for some rows.

With MVCC, when a transaction only reads data, then it will go ahead and complete regardless of what other transactions may do. This does not depend on the transaction being read-only or the isolation modes.

The SQL Standard defines the isolation levels as modes of operation that avoid the three unwanted phenomena, "dirty read", "fuzzy read" and "phantom row". The "dirty read" phenomenon occurs when a session can read a row that has been changed by another session. The "fuzzy read" phenomenon occurs when a row that was read by a session is modified by another session, then the first session reads the row again. The "phantom row" phenomenon occurs when a session performs an operation that affects several rows, for example, counts the rows or modifies them using a search condition, then another session adds one or more rows that fulfil the same search condition, then the first session performs an operation that relies on the results of its last operation. According to the Standard, the SERIALIZABLE isolation level avoids all three phenomena and also ensures that all the changes performed during a transaction can be considered as a series of uninterrupted changes to the database without any other transaction changing the database at all for the duration of these actions. The changes made by other transactions are considered to occur before the SERIALIZABLE transaction starts, or after it ends. The READ COMMITTED level avoids "dirty read" only, while the REPEATABLE READ level avoids "dirty read" and "fuzzy read", but not "phantom row".

The Standard allows the engine to return a higher isolation level than requested by the application. HyperSQL promotes a READ UNCOMMITTED request to READ COMMITTED and promotes a REPEATABLE READ request to SERIALIZABLE.

The MVCC model is not covered directly by the Standard. Research has established that the READ CONSISTENCY level fulfills the requirements of (and is stronger than) the READ COMMITTED level. The SNAPSHOT ISOLATION level is stronger than the READ CONSISTENCY level. It avoids the three anomalies defined by the Standard, and is therefore stronger than the REPEATABLE READ level as defined by the Standard. When operating with the MVCC model, HyperSQL treats a REPEATABLE READ or SERIALIZABLE setting for a transaction as SNAPSHOT ISOLATION.

All modes can be used with as many simultaneous connections as required. The default 2PL model is fine for applications with a single connection, or applications that do not access the same tables heavily for writes. With multiple simultaneous connections, MVCC can be used for most applications. Both READ CONSISTENCY and SNAPSHOT ISOLATION levels are stronger than the corresponding READ COMMITTED level in the 2PL mode. Some applications require SERIALIZABLE transactions for at least some of their operations. For these applications, one of the 2PL modes can be used. It is possible to switch the concurrency model while the database is operational. Therefore, the model can be changed for the duration of some special operations, such as synchronization with another data source.

All concurrency models are very fast in operation. When operating mainly on the same tables, the MVCC model may be faster with multiple processors.

There are a few SQL statements that must access a consistent state of the database during their executions. These statements, which include CHECKPOINT and BACKUP, put an exclusive lock on all the tables of the database when they start.

Some schema manipulation statements put an exclusive lock on one or more tables. For example changing the columns of a table locks the table exclusively.

In the MVCC model, all statements that need an exclusive lock on one or more tables, put an exclusive lock on the database catalog until they complete.

The effect of these exclusive locks is similar to the execution of data manipulation statements with write locks. The session that is about to execute the schema change statement waits until no other session is holding a lock on any of the objects. At this point it starts its operation and locks the objects to prevents any other session from accessing the locked objects. As soon as the operation is complete, the locks are all removed.

It was mentioned that there is no limit on the number of sessions that can access the tables and all sessions work simultaneously in multi threaded execution. However there are internal resources that are shared. Simultaneous access to these resources reduces the overall efficiency of the system. MEMORY and TEXT tables do not share resources and do not block multi threaded access. With CACHED tables, each write operation blocks the file and its cache until the operation is finished. With CACHED tables, SELECT operations do not block each other, but selecting from different tables and different parts of a large table causes the row cache to be updated frequently and will reduce overall performance.

The new access pattern is the opposite of the access pattern of version 1.8.x. In the old version, even when 20 sessions are actively reading and writing, only a single session at a time performs an SQL statement completely, before the next session is allowed access. In the new version, while a session is performing a SELECT statement and reading rows of a CACHED table to build a result set, another session may perform an UPDATE statement that reads and writes rows of the same table. The two operations are performed without any conflict, but the row cache is updated more frequently than when one operation is performed after the other operation has finished.

The name of a schema object is an <identifier>. The name belongs to the name-space for the particular kind of schema object. The name is unique within its name-space. For example, each schema has a separate name-space for TRIGGER objects.

In addition to the name-spaces in the schema. Each table has a name-space for the names of its columns.

Because a schema object is always in a schema and a schema always in a catalog, it is possible, and sometimes necessary, to qualify the name of the schema object that is being referenced in an SQL statement. This is done by forming an <identifier chain>. In some contexts, only a simple <identifier> can be used and the <identifier chain> is prohibited. While in some other contexts, the use of <identifier chain> is optional. An identifier chain is formed by qualifying each object with the name of the object that owns its name-space. Therefore a column name is prefixed with a table name, a table name is prefixed with a schema name, and a schema name is prefixed with a catalog name. A fully qualified column name is in the form <catalog name>.<schema name>.<table name>.<column name>, likewise, a fully qualified sequence name is in the form <catalog name>.<schema name>.<sequence name>.

HyperSQL extends the SQL standard to allow renaming all database objects. The ALTER ... RENAME TO command has slightly different forms depending on the type of object. If an object is referenced in a VIEW or ROUTINE definition, it is not always possible to rename it.

A CHARACTER SET is the whole or a subset of the UNICODE character set.

A character set name can only be a <regular identifier>. There is a separate name-space for character sets.

There are several predefined character sets. These character sets belong to INFORMATION_SCHEMA. However, when they are referenced in a statement, no schema prefix can be used in the statement that references them.

The following character sets have been specified by the SQL Standard:

SQL_TEXT, SQL_IDENTIFIER, SQL_CHARACTER, ASCII_GRAPHIC, GRAPHIC_IRV, ASCII_FULL, ISO8BIT, LATIN1, UTF32, UTF16, UTF8.

The ASCII_GRAPHIC is the same as GRAPHIC_IRV and ASCII_FULL is the same as ISO8BIT.

Most of the character sets are defined by well-known standards such as UNICODE.

The SQL_CHARACTER consists of ASCII letters, digits and the symbols used in the SQL language. The SQL_TEXT, SQL_IDENTIFIER are implementation defined. HyperSQL defines SQL_TEXT as the UNICODE character set and SQL_IDENTIFIER as the UNICODE character set minus the SQL language special characters.

The character repertoire of HyperSQL is the UTF16 character set, which covers all possible character sets. If a predefined character set is specified for a table column, then any string stored in the column must contain only characters from the specified character set.

Early releases of HyperSQL version 2.0 may not enforce the CHARACTER SET that is specified for a column and may accept any character string.

A COLLATION is the method used for ordering character strings in ordered sets and to determine equivalence of two character strings.

There are several predefined collations. These collations belong to INFORMATION_SCHEMA. However, when they are referenced in a statement, no schema prefix can be used in the statement that references them.

There is a separate name-space for collations..

Collations for a large number of languages are supported by HyperSQL.

Early releases of HyperSQL version 2.0 only support a single collation for the whole database.

A distinct, user-defined TYPE is simply based on a built-in type. A distinct TYPE is used in table definitions and in CAST statements.

Distinct types share a name-space with domains.

A DOMAIN is a user-defined type, simply based on a built-in type. A DOMAIN can have constraints that limit the values that the DOMAIN can represent. A DOMAIN can be used in table definitions and in CAST statements.

Distinct types share a name-space with domains.

A SEQUENCE object produces INTEGER values in sequence. The SEQUENCE can be referenced in special contexts only within certain SQL statements. For each row where the object is referenced, its value is incremented.

There is a separate name-space for SEQUENCE objects.

IDENTITY columns are columns of tables which have an internal, unnamed SEQUENCE object.

SEQUENCE objects and IDENTITY columns are supported fully according to the latest SQL 2008 Standard syntax.

Sequences

The SQL:2008 syntax and usage is different from what is supported by many existing database engines. Sequences are created with the CREATE SEQUENCE command and their current value can be modified at any time with ALTER SEQUENCE. The next value for a sequence is retrieved with the NEXT VALUE FOR <name> expression. This expression can be used for inserting and updating table rows.

INSERT INTO mytable VALUES 2, 'John', NEXT VALUE FOR mysequence;

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:

SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable WHERE ...

In version 2.0, the semantics of sequences is exactly as defined by SQL:2008. If you use the same sequence twice in the same row in an INSERT statement, you will get the same value as required by the Standard.

The correct way to use a sequence value is the NEXT VALUE FOR expression. You can query the 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. Note that this is only for getting information and you should not use the sequence value.

Identity Auto-Increment Columns

Each table can contain a single auto-increment column, known as the IDENTITY column. An IDENTITY column is a SMALLINT, INTEGER, BIGINT, DECIMAL or NUMERIC column with its value generated by a sequence generator.

In HyperSQL 2.0, an IDENTITY column is not by default treated as the primary key for the table (as a result, multi-column primary keys are possible with an IDENTITY column present).

The SQL standard syntax is used, which allows the initial value and other options to be specified.

<colname> [ INTEGER | BIGINT | DECIMAL | NUMERIC ] GENERATED { BY DEFAULT | ALWAYS} AS IDENTITY [( <options> )] [PRIMARY KEY]

When you add a new row to such a table using an INSERT INTO <tablename> ... statement, you can use the DEFAULT keyword for the IDENTITY column, which results in an auto-generated value for the column. The IDENTITY() function returns the last value inserted into any IDENTITY column by this session. Each session manages this function call separately and is not affected by inserts in other sessions. Use CALL IDENTITY() 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 INSERT INTO <childtable> VALUES (...,IDENTITY(),...);. Both types of call to IDENTITY() must be made before any additional update or insert statements are issued by the session.

The last inserted IDENTITY value can also be retrieved via JDBC, by specifying the Statement or PreparedStatement object to return the generated value.

The next IDENTITY value to be used can be changed with following statement. Note that this statement is not used in normal operation and is only for special purposes:

ALTER TABLE ALTER COLUMN <column name> RESTART WITH <new value>;

In the following example, the identity value for the first INSERT statement is generated automatically using the DEFAULT keyword. The second INSERT statement uses a call to the IDENTITY() function to populate a row in the child table with the generated identity value.

CREATE TABLE star (id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
   firstname VARCHAR(20),
   lastname VARCHAR(20))
CREATE TABLE movies (starid INTEGER, movieid INTEGER PRIMARY KEY, title VARCHAR(40)) 
INSERT INTO star (id, firstname, lastname) VALUES (DEFAULT, 'Felix', 'the Cat')
INSERT INTO movies (starid, movieid, title) VALUES (IDENTITY(), 10, 'Felix in Hollywood')

In the SQL environment, tables are the most essential components, as they hold all persistent data.

If TABLE is considered as metadata (i.e. without its actual data) it is called a relation in relational theory. It has one or more columns, with each column having a distinct name and a data type. A table usually has one or more constraints which limit the values that can potentially be stored in the TABLE. These constraints are discussed in the next section.

A single column of the table can be defined as IDENTITY. The values stored in this column are auto-generated and are based on an (unnamed) identity sequence.

A VIEW is similar to a TABLE but it does not permanently contain rows of data. A view is defined as a QUERY EXPRESSION, which is often a SELECT statement that references views and tables, but it can also consist of a TABLE CONSTRUCTOR that does not reference any tables or views.

A view has many uses:

A VIEW that returns the columns of a single ordinary TABLE may be updatable. Some updatable views are insertable-into. When rows of an updatable view are updated, or new rows are inserted, these changes are reflected in the base table. A VIEW definition may specify that the inserted or updated rows conform to the search condition of the view. This is done with the CHECK OPTION clause.

Views share a name-space with tables.

A CONSTRAINT is a child schema object and can belong to a DOMAIN or a TABLE. CONSTRAINT objects can be defined without specifying a name. In this case the system generates a name for the new object beginning with "SYS_".

In a DOMAIN, CHECK constraints can be defined that limits the value represented by the DOMAIN. These constraints work exactly like a CHECK constraint on a single column of a table as described below.

In a TABLE, a constraint takes three basic forms.

CHECK

A CHECK constraint consists of a <search condition> that must not be false (can be unknown) for each row of the table. The <search condition> can reference all the columns of the current row, and if it contains a <subquery>, other tables and views in the database (excluding its own table).

NOT NULL

A simple form of check constraint is the NOT NULL constraint, which applies to a single column.

UNIQUE

A UNIQUE constraint is based on an equality comparison of values of specific columns (taken together) of one row with the same values from each of the other rows. The result of the comparison must never be true (can be false or unknown). If a row of the table has NULL in any of the columns of the constraint, it conforms to the constraint. A unique constraint on multiple columns (c1, c2, c3, ..) means that in no two rows, the sets of values for the columns can be equal unless at lease one of them is NULL. Each single column taken by itself can have repeat values in different rows. The following example satisfies a UNIQUE constraint on the two columns

PRIMARY KEY

A PRIMARY KEY constraint is equivalent to a UNIQUE constraint on one or more NOT NULL columns. Only one PRIMARY KEY can be defined in each table.

FOREIGN KEY

A FOREIGN key constraint is based on an equality comparison between values of specific columns (taken together) of each row with the values of the columns of a UNIQUE constraint on another table or the same table. The result of the comparison must never be false (can be unknown). A special form of FOREIGN KEY constraint, based on its CHECK clause, allows the result to be unknown only if the values for all columns are NULL. A FOREIGN key can be declared only if a UNIQUE constraint exists on the referenced columns.

Constraints share a name space with assertions.

An ASSERTION is a top-level schema objects. It consists of a <search condition> that must not be false (can be unknown).

Assertions share a name-space with constraints

A TRIGGER is a child schema object that always belongs to a TABLE or a VIEW.

Each time a DELETE, UPDATE or INSERT is performed on the table or view, additional actions are taken by the triggers that have been declared on the table or view.

Triggers are discussed in detail in chapter Triggers .

Routines are user-defined functions or procedures. The names and usage of functions and procedures are different. FUNCTION is a routine that can be referenced in many types of statements. PROCEDURE is a routine that can be referenced only in a CALL statement.

There is a separate name-space for routines.

Because of the possibility of overloading, each routine can have more than one name. The name of the routine is the same for all overloaded variants, but each variant has a specific name, different from all other routine names and specific names in the schema. The specific name can be specified in the routine definition statement. Otherwise it is assigned by the engine. The specific name is used only for schema manipulation statements, which need to reference a specific variant of the routine. For example, if a routine has two signatures, each signature has its own specific name. This allows the user to drop one of the signatures while keeping the other.

Routines are discussed in detail in chapter SQL-Invoked Routines .

Indexes are an implementation-defined extension to the SQL Standard. HyperSQL has a dedicated name-space for indexes in each schema.

These elements and statements are used for different types of object. They are described here, before the statements that can use them.

identifier

definition of identifier

<identifier> ::= <regular identifier> | <delimited identifier> | <SQL language identifier>

<delimited identifier> ::= <double quote> <character sequence> <double quote>

<regular identifier> ::= <special character sequence>

<SQL language identifier> ::= <special character sequence>

A <delimited identifier> is a sequence of characters enclosed with double-quote symbols. All characters are allowed in the character sequence.

A <regular identifier> is a special sequence of characters. It consists of letters, digits and the underscore characters. It must begin with a letter.

A <SQL language identifier> is similar to <regular identifier> but the letters can range only from A-Z in the ASCII character set. This type of identifier is used for names of CHARACTER SET objects.

If the character sequence of a delimited identifier is the same as an undelimited identifier, it represents the same identifier. For example "JOHN" is the same identifier as JOHN. In a <regular identifier> the case-normal form is considered for comparison. This form consists of the upper-case of equivalent of all the letters.

The character sequence length of all identifiers must be between 1 and 128 characters.

A reserved word is one that is used by the SQL Standard for special purposes. It is similar to a <regular identifier> but it cannot be used as an identifier for user objects. If a reserved word is enclosed in double quote characters, it becomes a quoted identifier and can be used for database objects.

CASCADE or RESTRICT

drop behavior

<drop behavior> ::= CASCADE | RESTRICT

The <drop behavior> is a required element of statements that drop a SCHEMA or a schema object. If <drop behavior> is not specified then RESTRICT is implicit. It determines the effect of the statement if there are other objects in the catalog that reference the SCHEMA or the schema object. If RESTRICT is specified, the statement fails if there are referencing objects. If CASCADE is specified, all the referencing objects are modified or dropped with cascading effect. Whether a referencing object is modified or dropped, depends on the kind of schema object that is dropped.

IF EXISTS

drop condition (HyperSQL)

<if exists clause> ::= IF EXISTS

This clause is not part of the SQL standard and is a HyperSQL extension to some commands that drop objects (schemas, tables, views, sequences and indexes). If it is specified, then the statement does not return an error if the drop statement is issued on a non-existent object.

SPECIFIC

specific routine designator

<specific routine designator> ::= SPECIFIC <routine type> <specific name>

<routine type> ::= ROUTINE | FUNCTION | PROCEDURE

This clause is used in statements that need to specify one of the multiple versions of an overloaded routine. The <specific name> is the one specified in the <routine definition> statement.

RENAME

rename statement (HyperSQL)

<rename statement> ::= ALTER <object type> <name> RENAME TO <new name>

<object type> ::= CATALOG | SCHEMA | DOMAIN | TYPE | TABLE | CONSTRAINT | INDEX | ROUTINE | SPECIFIC ROUTINE

<column rename statement> ::= ALTER TABLE <table name> ALTER COLUMN <name> RENAME TO <new name>

This statement is used to rename an existing object. It is not part of the SQL Standard. The specified <name> is the existing name, which can be qualified with a schema name, while the <new name> is the new name for the object.

COMMENT

comment statement (HyperSQL)

<comment statement> ::= COMMENT ON { TABLE | COLUMN | ROUTINE } <name> IS <character string literal>

Adds a comment to the object metadata, which can later be read from an INFORMATION_SCHEMA view. This command is not part of the SQL Standard. The strange syntax is due to compatibility with other database engines that support the statement. The <name> is the name of a table, view, column or routine. The name of the column consists of dot-separated <table name> . <column name>. The name of the table, view or routine can be a simple name. All names can be qualified with a schema name. If there is alread a comment on the object, the new comment will replace it.

The comments appear in the results returned by JDBC DatabaseMetaData methods, getTables() and getColumns(). The INFORMATION_SCHEMA.SYSTEM_COMMENTS view contains the comments. You can query this view using the schema, table, and column names to retreive the comments.

CREATE SCHEMA

schema definition

The CREATE_SCHEMA or DBA role is required in order to create a schema. A schema can be created with or without schema objects. Schema objects can always be added after creating the schema, or existing ones can be dropped. Within the <schema definition> statement, all schema object creation takes place inside the newly created schema. Therefore, if a schema name is specified for the schema objects, the name must match that of the new schema. In addition to statements for creating schema objects, the statement can include instances of <grant statement> and <role definition>. This is a curious aspect of the SQL standard, as these elements do not really belong to schema creation.

<schema definition> ::= CREATE SCHEMA <schema name clause> [ <schema character set specification> ] [ <schema element>... ]

<schema name clause> ::= <schema name> | AUTHORIZATION <authorization identifier> | <schema name> AUTHORIZATION <authorization identifier>

If the name of the schema is specified simply as <schema name>, then the AUTHORIZATION is the current user. Otherwise, the specified <authorization identifier> is used as the AUTHORIZATION for the schema. If <schema name> is omitted, then the name of the schema is the same as the specified <authorization identifier>.

<schema element> ::= <table definition> | <view definition> | <domain definition> | <character set definition> | <collation definition> | <transliteration definition> | <assertion definition> | <trigger definition> | <user-defined type definition> | <user-defined cast definition> | <user-defined ordering definition> | <transform definition> | <schema routine> | <sequence generator definition> | <grant statement> | <role definition>

An example of the command is given below. Note that a single semicolon appears at the end, there should be no semicolon between the statements:

    CREATE SCHEMA ACCOUNTS AUTHORIZATION DBA
        CREATE TABLE AB(A INTEGER, ...)
        CREATE TABLE CD(C CHAR(10), ...)
        CREATE VIEW VI AS SELECT ...
        GRANT SELECT ON AB TO PUBLIC
        GRANT SELECT ON CD TO JOE;

It is not really necessary to create a schema and all its objects as one command. The schema can be created first, and its objects can be created one by one.

DROP SCHEMA

drop schema statement

<drop schema statement> ::= DROP SCHEMA [ IF EXISTS ] <schema name> [ IF EXISTS ] <drop behavior>

This command destroys an existing schema. If <drop behavior> is RESTRICT, the schema must be empty, otherwise an error is raised. If CASCADE is specified, then all the objects contained in the schema are destroyed with a CASCADE option.

CREATE TABLE

table definition

<table definition> ::= CREATE [ { <table scope> | <table type> } ] TABLE <table name> <table contents source> [ ON COMMIT { PRESERVE | DELETE } ROWS ]

<table scope> ::= { GLOBAL | LOCAL } TEMPORARY

<table type> :: = MEMORY | CACHED

<table contents source> ::= <table element list> | <as subquery clause>

<table element list> ::= <left paren> <table element> [ { <comma> <table element> }... ] <right paren>

<table element> ::= <column definition> | <table constraint definition> | <like clause>

like clause

A <like clause> copies all column definitions from another table into the newly created table. Its three options indicate if the <default clause>, <identity column specification> and <generation clause> associated with the column definitions are copied or not. If an option is not specified, it defaults to EXCLUDING. The <generation clause> refers to columns that are generated by an expression but not to identity columns. All NOT NULL constraints are copied with the original columns, other constraints are not. The <like clause> can be used multiple times, allowing the new table to have copies of the column definitions of one or more other tables.

CREATE TABLE t (id INTEGER PRIMARY KEY, LIKE atable INCLUDING DEFAULTS EXCLUDING IDENTITY)

<like clause> ::= LIKE <table name> [ <like options> ]

<like options> ::= <like option>...

<like option> ::= <identity option> | <column default option> | <generation option>

<identity option> ::= INCLUDING IDENTITY | EXCLUDING IDENTITY

<column default option> ::= INCLUDING DEFAULTS | EXCLUDING DEFAULTS

<generation option> ::= INCLUDING GENERATED | EXCLUDING GENERATED

as subquery clause

<as subquery clause> ::= [ <left paren> <column name list> <right paren> ] AS <table subquery> { WITH NO DATA | WITH DATA }

An <as subquery clause> used in table definition creates a table based on a <table subquery>. This kind of table definition is similar to a view definition. If WITH DATA is specified, then the new table will contain the rows of data returned by the <table subquery>.

CREATE TABLE t (a, b, c) AS (SELECT * FROM atable) WITH DATA

column definition

A column definition consists of a <column name> and in most cases a <data type> or <domain name> as minimum. The other elements of <column definition> are optional. Each <column name> in a table is unique.

<column definition> ::= <column name> [ <data type or domain name> ] [ <default clause> | <identity column specification> | <generation clause> ] [ <column constraint definition>... ] [ <collate clause> ]

<data type or domain name> ::= <data type> | <domain name>

<column constraint definition> ::= [ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]

<column constraint> ::= NOT NULL | <unique specification> | <references specification> | <check constraint definition>

<identity column specification> ::= GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ <left paren> <common sequence generator options> <right paren> ]

<generation clause> ::= GENERATED ALWAYS AS <generation expression>

<generation expression> ::= <left paren> <value expression> <right paren>

The <identity column specification> can be specified for only a single column of the table.

A <column constraint definition> is a shortcut for a <table constraint definition>. A constraint that is defined in this way is automatically turned into a table constraint. A name is automatically generated for the constraint and assigned to it.

The <identity column specification> is used for special columns which represent values based on unnamed sequence generators. It is possible to insert a row into the able without specifying a value for the column. The value is then generated by the sequence generators according to its rules. An identity column may or may not be the primary key. Example below:

CREATE TABLE t (id INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 100), name VARCHAR(20) PRIMARY KEY, )

The <generation clause> is used for special columns which represent values based on the values held in other columns in the same row. The <value expression> must reference only other, non-generated, columns of the table in the same row. Therefore, any function used in the expression may not access SQL-data, and no <query expression> is allowed. When <generation clause> is used, <data type> or <domain name> may be omitted.

A generated column can be part of a foreign key or unique constraints or a column of an index. This capability is the main reason for using generated columns. A generated column may contain a formula that computes a value based on the values of other columns. Fast searches of the computed value can be performed when an index is declared on the generated column. Or the computed values can be declared to be unique, using a UNIQUE constraint on the table.

When a row is inserted into a table, or an existing row is updated, no value except DEFAULT can be specified for a generated column. In the example below, data is inserted into the non-generated columns and the generated column will contain 'Felix the Cat' or 'Pink Panther'.

CREATE TABLE t (id INTEGER PRIMARY KEY, 
   firstname VARCHAR(20),
   lastname VARCHAR(20), 
   fullname VARCHAR(40) GENERATED ALWAYS AS (firstname || ' ' || lastname)) 
INSERT INTO t (id, firstname, lastname) VALUES (1, 'Felix', 'the Cat')
INSERT INTO t (id, firstname, lastname, fullname) VALUES (2, 'Pink', 'Panther', DEFAULT)

DEFAULT

default clause

A default clause can be used if GENERATED is not specified. If a column has a <default clause> then it is possible to insert a row into the table without specifying a value for the column.

<default clause> ::= DEFAULT <default option>

<default option> ::= <literal> | <datetime value function> | USER | CURRENT_USER | CURRENT_ROLE | SESSION_USER | SYSTEM_USER | CURRENT_CATALOG | CURRENT_SCHEMA | CURRENT_PATH | NULL

The type of the <default option> must match the type of the column.

CONSTRAINT

constraint name and characteristics

<constraint name definition> ::= CONSTRAINT <constraint name>

<constraint characteristics> ::= <constraint check time> [ [ NOT ] DEFERRABLE [ <constraint check time> ] ]

<constraint check time> ::= INITIALLY DEFERRED | INITIALLY IMMEDIATE

Specify the name of a constraint and its characteristics. By default the constraint is NOT DEFERRABLE and INITIALLY IMMEDIATE. This means the constraint is enforced as soon as a data change statement is executed. If INITIALLY DEFERRED is specified, then the constraint is enforced when the session commits. The characteristics must be compatible. The constraint check time can be changed temporarily for an SQL session. HyperSQL does not support deferring constraint enforcement. This feature of the SQL Standard has been criticised because it allows a session to read uncommitted data that violates database integrity constraints but has not yet been checked.

CONSTRAINT

table constraint definition

<table constraint definition> ::= [ <constraint name definition> ] <table constraint> [ <constraint characteristics> ]

<table constraint> ::= <unique constraint definition> | <referential constraint definition> | <check constraint definition>

Three kinds of constraint can be defined on a table: UNIQUE (including PRIMARY KEY), FOREIGN KEY and CHECK. Each kind has its own rules to limit the values that can be specified for different columns in each row of the table.

UNIQUE

unique constraint definition

<unique constraint definition> ::= <unique specification> <left paren> <unique column list> <right paren> | UNIQUE ( VALUE )

<unique specification> ::= UNIQUE | PRIMARY KEY

<unique column list> ::= <column name list>

A unique constraint is specified on a single column or on multiple columns. On each set of columns taken together, only one UNIQUE constraint can be specified. Each column of a PRIMARY KEY constraint has an implicit NOT NULL constraint.

If UNIQUE( VALUE ) is specified, the constraint created on all columns of the table.

FOREIGN KEY

referential constraint definition

<referential constraint definition> ::= FOREIGN KEY <left paren> <referencing columns> <right paren> <references specification>

<references specification> ::= REFERENCES <referenced table and columns> [ MATCH <match type> ] [ <referential triggered action> ]

<match type> ::= FULL | PARTIAL | SIMPLE

<referencing columns> ::= <reference column list>

<referenced table and columns> ::= <table name> [ <left paren> <reference column list> <right paren> ]

<reference column list> ::= <column name list>

<referential triggered action> ::= <update rule> [ <delete rule> ] | <delete rule> [ <update rule> ]

<update rule> ::= ON UPDATE <referential action>

<delete rule> ::= ON DELETE <referential action>

<referential action> ::= CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION

A referential constraint allows links to be established between the rows of two tables. The specified list of <referencing columns> corresponds one by one to the columns of the specified list of <referenced columns> in another table (or sometimes in the same table). For each row in the table, a row must exist in the referenced table with equivalent values in the two column lists. There must exist a single unique constraint in the referenced table on all the <referenced columns>.

The [ MATCH match type ] clause is optional and has an effect only on multi-column foreign keys and only on rows containing at least a NULL in one of the <referencing columns>. If the clause is not specified, MATCH SIMPLE is the default. If MATCH SIMPLE is specified, then any NULL means the row can exist (without a corresponding row in the referenced table). If MATCH FULL is specified then either all the column values must be NULL or none of them. MATCH PARTIAL allows any NULL but the non NULL values must match those of a row in the referenced table. HyperSQL does not support MATCH PARTIAL.

Referential actions are specified with ON UPDATE and ON DELETE clauses. These actions take place when a row in the referenced table (the parent table) has referencing rows in the referencing table and it is deleted or modified with any SQL statement. The default is NO ACTION. This means the SQL statement that causes the DELETE or UPDATE is terminated with an exception. The RESTRICT option is similar and works exactly the same without deferrable constraints (which are not allowed by HyperSQL). The other three options, CASCADE, SET NULL and SET DEFAULT all allow the DELETE or UPDATE statement to complete. With DELETE statements the CASCADE option results in the referencing rows to be deleted. With UPDATE statements, the changes to the values of the referenced columns are copied to the referencing rows. With both DELETE or UPDATE statement, the SET NULL option results in the columns of the referencing rows to be set to NULL. Similarly, the SET DEFAULT option results in the columns of the referencing rows to be set to their default values.

CHECK

check constraint definition

<check constraint definition> ::= CHECK <left paren> <search condition> <right paren>

A CHECK constraint can exist for a TABLE or for a DOMAIN. The <search condition> evaluates to an SQL BOOLEAN value for each row of the table. Within the <search condition> all columns of the table row can be referenced. For all rows of the table, the <search condition> evaluates to TRUE or UNKNOWN. When a new row is inserted, or an existing row is updated, the <search condition> is evaluated and if it is FALSE, the insert or update fails.

A CHECK constraint for a DOMAIN is similar. In its <search condition>, the term VALUE is used to represents the value to which the DOMAIN applies.

CREATE TABLE t (a VARCHAR(20) CHECK (a IS NOT NULL AND CHARACTER_LENGTH(a) > 2))

The search condition of a CHECK constraint cannot contain any function that is not deterministic. A check constraint is a data integrity constraint, therefore it must hold with respect to the rest of the data in the database. It cannot use values that are temporal or ephemeral. For example CURRENT_USER is a function that returns different values depending on who is using the database, or CURRENT_DATE changes day-to-day. Some temporal expressions are retrospectively deterministic and are allowed in check constraints. For example, (CHECK VALUE < CURRENT_DATE) is valid, because CURRENT_DATE will not move backwards in time, but (CHECK VALUE > CURRENT_DATE) is not acceptable.

If you want to enforce the condition that a date value that is inserted into the database belongs to the future (at the time of insertion), or any similar constraint, then use a TRIGGER with the desired condition.

SET TABLE writeability

set table write property (HyperSQL)

<set table read only statement> ::= SET TABLE <table name> { READ ONLY | READ WRITE }

Set the writeability property of a table. Tables are writable by default. This statement can be used to change the property between READ ONLY and READ WRITE. This is a feature of HyperSQL.

SET TABLE SOURCE

set table source statement

<set table source statement> ::= SET TABLE <table name> SOURCE <file and options> [DESC]

<file and options>::= <doublequote> <file path> [<semicolon> <property>...] <doublequote>

Set the text source for a text table. This statement cannot be used for tables that are not defined as TEXT TABLE.

In the example below, the text source of the table is set to "myfile", the field separator to the pipe symbol, and the long varchar separator to the tilde symbol.

    SET TABLE mytable SOURCE 'myfile;fs=|;vs=.;lvs=~'

Only a user with the DBA role can execute this statement.

SET TABLE SOURCE HEADER

set table source header statement

<set table source header statement> ::= SET TABLE <table name> SOURCE HEADER <header string>

Set the header for the text source for a text table. If this command is used, the <header string> is used as the first line of the source file of the text table. This line is not part of the table data. Only a user with the DBA role can execute this statement.

SET TABLE SOURCE on-off

set table source on-off statement

<set table source on-off statement> ::= SET TABLE <table name> SOURCE { ON | OFF }

Attach or detach a text table from its text source. This command does not change the properties or the name of the file that is the source of a text table. When OFF is specified, the command detaches the table from its source and closes the file for the source. In this state, it is not possible to read or write to the table. This allows the user to replace the file with a different file, or delete it. When ON is specified, the source file is read. Only a user with the DBA role can execute this statement

ALTER TABLE

alter table statement

<alter table statement> ::= ALTER TABLE <table name> <alter table action>

<alter table action> ::= <add column definition> | <alter column definition> | <drop column definition> | <add table constraint definition> | <drop table constraint definition>

Change the definition of a table. Specific types of this statement are covered below.

ADD COLUMN

add column definition

<add column definition> ::= ADD [ COLUMN ] <column definition> [ BEFORE <other column name> ]

Add a column to an existing table. The <column definition> is specified the same way as it is used in <table definition>. HyperSQL allows the use of [ BEFORE <other column name> ] to specify at which position the new column is added to the table.

If the table contains rows, the new column must have a <default clause> or use one of the forms of GENERATED. The column values for each row is then filled with the result of the <default clause> or the generated value.

ALTER COLUMN

alter column definition

<alter column definition> ::= ALTER [ COLUMN ] <column name> <alter column action>

<alter column action> ::= <set column default clause> | <drop column default clause> | <alter column data type clause> | <alter identity column specification> | <alter column nullability> | <alter column name>

Change a column and its definition. Specific types of this statement are covered below. See also the RENAME statement above.

SET DEFAULT

set column default clause

<set column default clause> ::= SET <default clause>

Set the default clause for a column. This can be used if the column is not defined as GENERATED.

DROP DEFAULT

drop column default clause

<drop column default clause> ::= DROP DEFAULT

Drop the default clause from a column.

SET DATA TYPE

alter column data type clause

<alter column data type clause> ::= SET DATA TYPE <data type>

Change the declared type of a column. The (proposed) SQL Standard allows only changes to type properties such as maximum length, precision, or scale, and only changes that cause the property to enlarge. HyperSQL allows changing the type if all the existing values can be cast into the new type without string truncation or loss of significant digits.

alter identity column

alter identity column specification

<alter identity column specification> ::= <alter identity column option>...

<alter identity column option> ::= <alter sequence generator restart option> | SET <basic sequence generator option>

Change the properties of an identity column. This command is similar to the commands used for changing the properties of named SEQUENCE objects discussed in this section.

SET NULL

alter column nullability

<alter column nullability> ::= SET NULL

Removes a NOT NULL constraint from a column. This option is specific to HyperSQL

DROP COLUMN

drop column definition

<drop column definition> ::= DROP [ COLUMN ] <column name> <drop behavior>

Destroy a column of a base table. The <drop behavior> is either RESTRICT or CASCADE. If the column is referenced in a table constraint that references other columns as well as this column, or if the column is referenced in a VIEW, or the column is referenced in a TRIGGER, then the statement will fail if RESTRICT is specified. If CASCADE is specified, then any CONSTRAINT, VIEW or TRIGGER object that references the column is dropped with a cascading effect.

ADD CONSTRAINT

add table constraint definition

<add table constraint definition> ::= ADD <table constraint definition>

Add a constraint to a table. The existing rows of the table must conform to the added constraint, otherwise the statement will not succeed.

DROP CONSTRAINT

drop table constraint definition

<drop table constraint definition> ::= DROP CONSTRAINT <constraint name> <drop behavior>

Destroy a constraint on a table. The <drop behavior> has an effect only on UNIQUE and PRIMARY KEY constraints. If such a constraint is referenced by a FOREIGN KEY constraint, the FOREIGN KEY constraint will be dropped if CASCADE is specified. If the columns of such a constraint are used in a GROUP BY clause in the query expression of a VIEW or another kind of schema object, and a functional dependency relationship exists between these columns and the other columns in that query expression, then the VIEW or other schema object will be dropped when CASCADE is specified.

DROP TABLE

drop table statement

<drop table statement> ::= DROP TABLE [ IF EXISTS ] <table name> [ IF EXISTS ] <drop behavior>

Destroy a table. The default drop behaviour is RESTRICT and will cause the statement to fail if there is any view or foreign key constraint that references the table. If <drop behavior> is CASCADE, it causes all schema objects that reference the table to drop. Referencing views are dropped. In the case of foreign key constraints that reference the table, the constraint is dropped, rather than the TABLE or DOMAIN that contains it.

CREATE VIEW

view definition

<view definition> ::= CREATE [ RECURSIVE ] VIEW <table name> <view specification> AS <query expression> [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

<view specification> ::= [ <left paren> <view column list> <right paren> ]

<view column list> ::= <column name list>

Define a view. The <query expression> is a SELECT or similar statement. The <view column list> is the list of unique names for the columns of the view. The number of columns in the <view column list> must match the number of columns returned by the <query expression>. If <view column list> is not specified, then the columns of the <query expression> should have unique names and are used as the names of the view column.

Some views are updatable. As covered elsewhere, an updatable view is based on a single table or updatable view. For updatable views, the optional CHECK OPTION clause can be specified. If this option is specified, then if a row of the view is updated or a new row is inserted into the view, then it should contain such values that the row would be included in the view after the change. If WITH CASCADED CHECK OPTION is specified, then if the <query expression> of the view references another view, then the search condition of the underlying view should also be satisfied by the update or insert operation.

More on recursive...

DROP VIEW

drop view statement

<drop view statement> ::= DROP VIEW [ IF EXISTS ] <table name> [ IF EXISTS ] <drop behavior>

Destroy a view. The <drop behavior> is similar to dropping a table.

ALTER VIEW

alter view statement

<alter view statement> ::= ALTER VIEW <table name> <view specification> AS <query expression> [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

Alter a view. The statement is otherwise identical to CREATE VIEW. The new definition replaces the old. If there are database objects such as routines or views that reference the view, then these objects are recompiled with the new view definition. If the new definition is not compatible, the statement fails.

CREATE DOMAIN

domain definition

<domain definition> ::= CREATE DOMAIN <domain name> [ AS ] <predefined type> [ <default clause> ] [ <domain constraint>... ] [ <collate clause> ]

<domain constraint> ::= [ <constraint name definition> ] <check constraint definition> [ <constraint characteristics> ]

Define a domain. Although a DOMAIN is not strictly a type in the SQL Standard, it can be informally considered as a type. A DOMAIN is based on a <predefined type>, which is a base type defined by the Standard. It can have a <default clause>, similar to a column default clause. It can also have one or more CHECK constraints which limit the values that can be assigned to a column or variable that has the DOMAIN as its type.

CREATE DOMAIN valid_string AS VARCHAR(20) DEFAULT 'NO VALUE' CHECK (value IS NOT NULL AND CHARACTER_LENGTH(value) > 2) 

ALTER DOMAIN

alter domain statement

<alter domain statement> ::= ALTER DOMAIN <domain name> <alter domain action>

<alter domain action> ::= <set domain default clause> | <drop domain default clause> | <add domain constraint definition> | <drop domain constraint definition>

Change a domain and its definition.

SET DEFAULT

set domain default clause

<set domain default clause> ::= SET <default clause>

Set the default value in a domain.

DROP DEFAULT

drop domain default clause

<drop domain default clause> ::= DROP DEFAULT

Remove the default clause of a domain.

ADD CONSTRAINT

add domain constraint definition

<add domain constraint definition> ::= ADD <domain constraint>

Add a constraint to a domain.

DROP CONSTRAINT

drop domain constraint definition

<drop domain constraint definition> ::= DROP CONSTRAINT <constraint name>

Destroy a constraint on a domain. If the <drop behavior> is CASCADE, and the constraint is a UNIQUE constraint which is referenced by a FOREIGN KEY constraint on another table, then the FOREIGN KEY constraint is also dropped.

DROP DOMAIN

drop domain statement

<drop domain statement> ::= DROP DOMAIN <domain name> <drop behavior>

Destroy a domain. If <drop behavior> is CASCADE, it works differently from most other objects. If a table features a column of the specified DOMAIN, the column survives and inherits the DEFAULT CLAUSE, and the CHECK CONSTRAINT of the DOMAIN.

CREATE TRIGGER

trigger definition

<trigger definition> ::= CREATE TRIGGER <trigger name> <trigger action time> <trigger event> ON <table name> [ REFERENCING <transition table or variable list> ] <triggered action>

<trigger action time> ::= BEFORE | AFTER | INSTEAD OF

<trigger event> ::= INSERT | DELETE | UPDATE [ OF <trigger column list> ]

<trigger column list> ::= <column name list>

<triggered action> ::= [ FOR EACH { ROW | STATEMENT } ] [ <triggered when clause> ] <triggered SQL statement>

<triggered when clause> ::= WHEN <left paren> <search condition> <right paren>

<triggered SQL statement> ::= <SQL procedure statement> | BEGIN ATOMIC { <SQL procedure statement> <semicolon> }... END | [QUEUE <integer literal>] [NOWAIT] CALL <HSQLDB trigger class FQN>

<transition table or variable list> ::= <transition table or variable>...

<transition table or variable> ::= OLD [ ROW ] [ AS ] <old transition variable name> | NEW [ ROW ] [ AS ] <new transition variable name> | OLD TABLE [ AS ] <old transition table name> | NEW TABLE [ AS ] <new transition table name>

<old transition table name> ::= <transition table name>

<new transition table name> ::= <transition table name>

<transition table name> ::= <identifier>

<old transition variable name> ::= <correlation name>

<new transition variable name> ::= <correlation name>

Trigger definition is a relatively complex statement. The combination of <trigger action time> and <trigger event> determines the type of the trigger. Examples include BEFORE DELETE, AFTER UPDATE, INSTEAD OF INSERT. If the optional [ OF <trigger column list> ] is specified for an UPDATE trigger, then the trigger is activated only if one of the columns that is in the <trigger column list> is specified in the UPDATE statement that activates the trigger.

If a trigger is FOR EACH ROW, which is the default option, then the trigger is activated for each row of the table that is affected by the execution of an SQL statement. Otherwise, it is activated once only per statement execution. In the first case, there is a before and after state for each row. For UPDATE triggers, both before and after states exist, representing the row before the update, and after the update. For DELETE, triggers, there is only a before state. For INSERT triggers, there is only an after state. If a trigger is FOR EACH STATEMENT, then a transient table is created containing all the rows for the before state and another transient table is created for the after state.

The [ REFERENCING <transition table or variable> ] is used to give a name to the before and after data row or table. This name can be referenced in the <SQL procedure statement> to access the data.

The optional <triggered when clause> is a search condition, similar to the search condition of a DELETE or UPDATE statement. If the search condition is not TRUE for a row, then the trigger is not activated for that row.

The <SQL procedure statement> is limited to INSERT, DELETE, UPDATE and MERGE statements.

The <HSQLDB trigger class FQN> is a delimited identifier that contains the fully qualified name of a Java class that implements the org.hsqldb.Trigger interface.

Early releases of HyperSQL version 2.0 do not allow the use of OLD TABLE or NEW TABLE in statement level trigger definitions.

DROP TRIGGER

drop trigger statement

<drop trigger statement> ::= DROP TRIGGER <trigger name>

Destroy a trigger.

schema routine

SQL-invoked routine

<SQL-invoked routine> ::= <schema routine>

<schema routine> ::= <schema procedure> | <schema function>

<schema procedure> ::= CREATE <SQL-invoked procedure>

<schema function> ::= CREATE <SQL-invoked function>

<SQL-invoked procedure> ::= PROCEDURE <schema qualified routine name> <SQL parameter declaration list> <routine characteristics> <routine body>

<SQL-invoked function> ::= { <function specification> | <method specification designator> } <routine body>

<SQL parameter declaration list> ::= <left paren> [ <SQL parameter declaration> [ { <comma> <SQL parameter declaration> }... ] ] <right paren>

<SQL parameter declaration> ::= [ <parameter mode> ] [ <SQL parameter name> ] <parameter type> [ RESULT ]

<parameter mode> ::= IN | OUT | INOUT

<parameter type> ::= <data type>

<function specification> ::= FUNCTION <schema qualified routine name> <SQL parameter declaration list> <returns clause> <routine characteristics> [ <dispatch clause> ]

<method specification designator> ::= SPECIFIC METHOD <specific method name> | [ INSTANCE | STATIC | CONSTRUCTOR ] METHOD <method name> <SQL parameter declaration list> [ <returns clause> ] FOR <schema-resolved user-defined type name>

<routine characteristics> ::= [ <routine characteristic>... ]

<routine characteristic> ::= <language clause> | <parameter style clause> | SPECIFIC <specific name> | <deterministic characteristic> | <SQL-data access indication> | <null-call clause> | <returned result sets characteristic> | <savepoint level indication>

<savepoint level indication> ::= NEW SAVEPOINT LEVEL | OLD SAVEPOINT LEVEL

<returned result sets characteristic> ::= DYNAMIC RESULT SETS <maximum returned result sets>

<parameter style clause> ::= PARAMETER STYLE <parameter style>

<dispatch clause> ::= STATIC DISPATCH

<returns clause> ::= RETURNS <returns type>

<returns type> ::= <returns data type> [ <result cast> ] | <returns table type>

<returns table type> ::= TABLE <table function column list>

<table function column list> ::= <left paren> <table function column list element> [ { <comma> <table function column list element> }... ] <right paren>

<table function column list element> ::= <column name> <data type>

<result cast> ::= CAST FROM <result cast from type>

<result cast from type> ::= <data type> [ <locator indication> ]

<returns data type> ::= <data type> [ <locator indication> ]

<routine body> ::= <SQL routine spec> | <external body reference>

<SQL routine spec> ::= [ <rights clause> ] <SQL routine body>

<rights clause> ::= SQL SECURITY INVOKER | SQL SECURITY DEFINER

<SQL routine body> ::= <SQL procedure statement>

<external body reference> ::= EXTERNAL [ NAME <external routine name> ] [ <parameter style clause> ]

<parameter style> ::= SQL | GENERAL

<deterministic characteristic> ::= DETERMINISTIC | NOT DETERMINISTIC

<SQL-data access indication> ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA

<null-call clause> ::= RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

<maximum returned result sets> ::= <unsigned integer>

Define an SQL-invoked routine.

ALTER routine

alter routine statement

<alter routine statement> ::= ALTER <specific routine designator> <alter routine characteristics> <alter routine behavior>

<alter routine characteristics> ::= <alter routine characteristic>...

<alter routine characteristic> ::= <language clause> | <parameter style clause> | <SQL-data access indication> | <null-call clause> | <returned result sets characteristic> | NAME <external routine name>

<alter routine behavior> ::= RESTRICT

Alter a characteristic of an SQL-invoked routine. Early releases of HyperSQL 2.0 may not support this statement.

DROP

drop routine statement

<drop routine statement> ::= DROP <specific routine designator> <drop behavior>

Destroy an SQL-invoked routine.

CREATE SEQUENCE

sequence generator definition

<sequence generator definition> ::= CREATE SEQUENCE <sequence generator name> [ <sequence generator options> ]

<sequence generator options> ::= <sequence generator option> ...

<sequence generator option> ::= <sequence generator data type option> | <common sequence generator options>

<common sequence generator options> ::= <common sequence generator option> ...

<common sequence generator option> ::= <sequence generator start with option> | <basic sequence generator option>

<basic sequence generator option> ::= <sequence generator increment by option> | <sequence generator maxvalue option> | <sequence generator minvalue option> | <sequence generator cycle option>

<sequence generator data type option> ::= AS <data type>

<sequence generator start with option> ::= START WITH <sequence generator start value>

<sequence generator start value> ::= <signed numeric literal>

<sequence generator increment by option> ::= INCREMENT BY <sequence generator increment>

<sequence generator increment> ::= <signed numeric literal>

<sequence generator maxvalue option> ::= MAXVALUE <sequence generator max value> | NO MAXVALUE

<sequence generator max value> ::= <signed numeric literal>

<sequence generator minvalue option> ::= MINVALUE <sequence generator min value> | NO MINVALUE

<sequence generator min value> ::= <signed numeric literal>

<sequence generator cycle option> ::= CYCLE | NO CYCLE

Define a named sequence generator. A SEQUENCE object generates a sequence of integers according to the specified rules. The simple definition without the options defines a sequence of numbers in INTEGER type starting at 1 and incrementing by 1. By default the CYCLE property is set and the minimum and maximum limits are the minimum and maximum limits of the type of returned values. There are self-explanatory options for changing various properties of the sequence. The MAXVALUE and MINVALUE specify the upper and lower limits. If CYCLE is specified, after the sequence returns the highest or lowest value in range, the next value will respectively be the lowest or highest value in range. If NO CYCLE is specified, the use of the sequence generator results in an error once the limit has been reached.

The integer types: SMALLINT, INTEGER, BIGINT, DECIMAL and NUMERIC can be used as the type of the sequence. DECIMAL and NUMERIC types must have a scale of 0 and a precision not exceeding 18.

ALTER SEQUENCE

alter sequence generator statement

<alter sequence generator statement> ::= ALTER SEQUENCE <sequence generator name> <alter sequence generator options>

<alter sequence generator options> ::= <alter sequence generator option>...

<alter sequence generator option> ::= <alter sequence generator restart option> | <basic sequence generator option>

<alter sequence generator restart option> ::= RESTART [ WITH <sequence generator restart value> ]

<sequence generator restart value> ::= <signed numeric literal>

Change the definition of a named sequence generator. The same options that are used in the definition of the SEQUENCE can be used to alter it. The exception is the option for the start value which is RESTART WITH for the ALTER SEQUENCE statement..

DROP SEQUENCE

drop sequence generator statement

<drop sequence generator statement> ::= DROP SEQUENCE [ IF EXISTS ] <sequence generator name> [ IF EXISTS ] <drop behavior>

Destroy an external sequence generator. If the <drop behavior> is CASCADE, then all objects that reference the sequence are dropped. These objects can be VIEW, ROUTINE or TRIGGER objects.

SQL procedure statement

SQL procedure statement

The definition of CREATE TRIGGER and CREATE PROCEDURE statements refers to <SQL procedure statement>. The definition of this element is given below. However, only a subset of these statements are allowed in trigger or routine definition.

<SQL procedure statement> ::= <SQL executable statement>

<SQL executable statement> ::= <SQL schema statement> | <SQL data statement> | <SQL control statement> | <SQL transaction statement> | <SQL connection statement> | <SQL session statement> | <SQL diagnostics statement> | <SQL dynamic statement>

<SQL schema statement> ::= <SQL schema definition statement> | <SQL schema manipulation statement>

<SQL schema definition statement> ::= <schema definition> | <table definition> | <view definition> | <SQL-invoked routine> | <grant statement> | <role definition> | <domain definition> | <character set definition> | <collation definition> | <transliteration definition> | <assertion definition> | <trigger definition> | <user-defined type definition> | <user-defined cast definition> | <user-defined ordering definition> | <transform definition> | <sequence generator definition>

<SQL schema manipulation statement> ::= <drop schema statement> | <alter table statement> | <drop table statement> | <drop view statement> | <alter routine statement> | <drop routine statement> | <drop user-defined cast statement> | <revoke statement> | <drop role statement> | <alter domain statement> | <drop domain statement> | <drop character set statement> | <drop collation statement> | <drop transliteration statement> | <drop assertion statement> | <drop trigger statement> | <alter type statement> | <drop data type statement> | <alter sequence generator statement> | <drop sequence generator statement>

CREATE INDEX

create index statement

<create index statement> ::= CREATE INDEX <index name> ON <table name> <left paren> {<column name> [ASC | DESC]}, ... <left paren>

Creates an index on a group of columns of a table. The optional [ASC | DESC] specifies if the column is indexed in the ascending or descending order, but has no effect on how the index is created (it is allowed for compatibility with other database engines). HyperSQL can use all indexes in ascending or descending order as needed.

DROP INDEX

drop index statement

<drop index statement> ::= DROP INDEX [ IF EXISTS ] <index name> [ IF EXISTS ]

Destroy an index.

CREATE TYPE

user-defined type definition

<user-defined type definition> ::= CREATE TYPE <user-defined type body>

<user-defined type body> ::= <schema-resolved user-defined type name> [ AS <representation> ]

<representation> ::= <predefined type>

Define a user-defined type. Currently only simple distinct types can be defined without further attributes.

CREATE CAST

user-defined cast definition

<user-defined cast definition> ::= CREATE CAST <left paren> <source data type> AS <target data type> <right paren> WITH <cast function> [ AS ASSIGNMENT ]

<cast function> ::= <specific routine designator>

<source data type> ::= <data type>

<target data type> ::= <data type>

Define a user-defined cast. This feature may be supported in a future versions of HyperSQL.

DROP CAST

drop user-defined cast statement

<drop user-defined cast statement> ::= DROP CAST <left paren> <source data type> AS <target data type> <right paren> <drop behavior>

Destroy a user-defined cast. This feature may be supported in a future versions of HyperSQL.

CREATE CHARACTER SET

character set definition

<character set definition> ::= CREATE CHARACTER SET <character set name> [ AS ] <character set source> [ <collate clause> ]

<character set source> ::= GET <character set specification>

Define a character set. A new CHARACTER SET is based on an existing CHARACTER SET. The optional <collate clause> specifies the collation to be used, otherwise the collation is inherited from the default collation for the source CHARACTER SET.

DROP CHARACTER SET

drop character set statement

<drop character set statement> ::= DROP CHARACTER SET <character set name>

Destroy a character set. If the character set name is referenced in any database object, the command fails. Note that CASCADE or RESTRICT cannot be specified for this command.

CREATE COLLATION

collation definition

<collation definition> ::= CREATE COLLATION <collation name> FOR <character set specification> FROM <existing collation name> [ <pad characteristic> ]

<existing collation name> ::= <collation name>

<pad characteristic> ::= NO PAD | PAD SPACE

Define a collation. A new collation is based on an existing COLLATION and applies to an existing CHARACTER SET. The <pad characteristic> specifies whether strings are padded with spaces for comparison. This feature may be supported in a future versions of HyperSQL.

DROP COLLATION

drop collation statement

<drop collation statement> ::= DROP COLLATION <collation name> <drop behavior>

Destroy a collation. If the <drop behavior> is CASCADE, then all references to the collation revert to the default collation that would be in force if the dropped collation was not specified. This feature may be supported in a future versions of HyperSQL.

CREATE TRANSLATION

transliteration definition

<transliteration definition> ::= CREATE TRANSLATION <transliteration name> FOR <source character set specification> TO <target character set specification> FROM <transliteration source>

<source character set specification> ::= <character set specification>

<target character set specification> ::= <character set specification>

<transliteration source> ::= <existing transliteration name> | <transliteration routine>

<existing transliteration name> ::= <transliteration name>

<transliteration routine> ::= <specific routine designator>

Define a character transliteration. This feature may be supported in a future versions of HyperSQL.

DROP TRANSLATION

drop transliteration statement

<drop transliteration statement> ::= DROP TRANSLATION <transliteration name>

Destroy a character transliteration. This feature may be supported in a future versions of HyperSQL.

CREATE ASSERTION

assertion definition

<assertion definition> ::= CREATE ASSERTION <constraint name> CHECK <left paren> <search condition> <right paren> [ <constraint characteristics> ]

Specify an integrity constraint. This feature may be supported in a future versions of HyperSQL.

DROP ASSERTION

drop assertion statement

<drop assertion statement> ::= DROP ASSERTION <constraint name> [ <drop behavior> ]

Destroy an assertion. This feature may be supported in a future versions of HyperSQL.

The SQL Standard defines a number of character sets and domains in the INFORMATION SCHEMA.

These domains are used in the INFORMATION SCHEMA views:

CARDINAL_NUMBER, YES_OR_NO, CHARACTER_DATA, SQL_IDENTIFIER, TIME_STAMP

All available collations are in the INFORMATION SCHEMA.

The following views are defined by the SQL Standard:

ADMINISTRABLE_ROLE_AUTHORIZATIONS

APPLICABLE_ROLES

ASSERTIONS

AUTHORIZATIONS

CHARACTER_SETS

CHECK_CONSTRAINTS

CHECK_CONSTRAINT_ROUTINE_USAGE

COLLATIONS

COLUMNS

COLUMN_COLUMN_USAGE

COLUMN_DOMAIN_USAGE

COLUMN_PRIVILEGES

COLUMN_UDT_USAGE

CONSTRAINT_COLUMN_USAGE

CONSTRAINT_TABLE_USAGE

DATA_TYPE_PRIVILEGES

DOMAINS

DOMAIN_CONSTRAINTS

ENABLED_ROLES

INFORMATION_SCHEMA_CATALOG_NAME

KEY_COLUMN_USAGE

PARAMETERS

REFERENTIAL_CONSTRAINTS

ROLE_AUTHORIZATION_DESCRIPTORS

ROLE_COLUMN_GRANTS

ROLE_ROUTINE_GRANTS

ROLE_TABLE_GRANTS

ROLE_UDT_GRANTS

ROLE_USAGE_GRANTS

ROUTINE_COLUMN_USAGE

ROUTINE_JAR_USAGE

ROUTINE_PRIVILEGES

ROUTINE_ROUTINE_USAGE

ROUTINE_SEQUENCE_USAGE

ROUTINE_TABLE_USAGE

ROUTINES

SCHEMATA

SEQUENCES

SQL_FEATURES

SQL_IMPLEMENTATION_INFO

SQL_PACKAGES

SQL_PARTS

SQL_SIZING

SQL_SIZING_PROFILES

TABLES

TABLE_CONSTRAINTS

TABLE_PRIVILEGES

TRANSLATIONS

TRIGGERED_UPDATE_COLUMNS

TRIGGERS

TRIGGER_COLUMN_USAGE

TRIGGER_ROUTINE_USAGE

TRIGGER_SEQUENCE_USAGE

TRIGGER_TABLE_USAGE

USAGE_PRIVILEGES

USER_DEFINED_TYPES

VIEWS

VIEW_COLUMN_USAGE

VIEW_ROUTINE_USAGE

VIEW_TABLE_USAGE

The following views are specific to HyperSQL:

SYSTEM_BESTROWIDENTIFIER

SYSTEM_CACHEINFO

SYSTEM_COLUMNS

SYSTEM_COMMENTS

SYSTEM_CROSSREFERENCE

SYSTEM_INDEXINFO

SYSTEM_PRIMARYKEYS

SYSTEM_PROCEDURECOLUMNS

SYSTEM_PROCEDURES

SYSTEM_PROPERTIES

SYSTEM_SCHEMAS

SYSTEM_SEQUENCES

SYSTEM_SESSIONINFO

SYSTEM_SESSIONS

SYSTEM_TABLES

SYSTEM_TABLETYPES

SYSTEM_TEXTTABLES

SYSTEM_TYPEINFO

SYSTEM_UDTS

SYSTEM_USERS

SYSTEM_VERSIONCOLUMNS

Text Tables are defined similarly to conventional tables with the added TEXT keyword:

    CREATE TEXT TABLE <tablename> (<column definition> [<constraint definition>])

The table is at first empty and cannot be written to. An additional SET command specifies the file and the separator character that the Text table uses:

   SET TABLE <tablename> SOURCE <quoted_filename_and_options> [DESC]

Text Tables cannot be created in mem: (all-in-memory) databases (databases that have no script file).

The default field separator is a comma (,). A different field separator can be specified within the SET TABLE SOURCE statement. For example, to change the field separator for the table mytable to a vertical bar, place the following in the SET TABLE SOURCE statement, for example:

    SET TABLE mytable SOURCE "myfile;fs=|"

Since HSQLDB treats CHAR and VARCHAR strings the same, the ability to assign a different separator to the latter is provided. When a different separator is assigned to a VARCHAR, it will terminate any CSV field of that type. For example, if the first field is CHAR, and the second field VARCHAR, and the separator fs has been defined as the pipe (|) and vs as the period (.) then the data in the CSV file for a row will look like:

    First field data|Second field data.Third field data

This facility in effect offers an extra, special separator which can be used in addition to the global separator. The following example shows how to change the default separator to the pipe (|), VARCHAR separator to the period (.) within a SET TABLE SOURCE statement:

    SET TABLE mytable SOURCE "myfile;fs=|;vs=."

HSQLDB also recognises the following special indicators for separators:

Furthermore, HSQLDB provides csv file support with three additional boolean options: ignore_first, quoted and all_quoted. The ignore_first option (default false) tells HSQLDB to ignore the first line in a file. This option is used when the first line of the file contains column headings. The all_quoted option (default false) tells the program that it should use quotes around all character fields when writing to the source file. The quoted option (default true) uses quotes only when necessary to distinguish a field that contains the separator character. It can be set to false to prevent the use of quoting altogether and treat quote characters as normal characters. These options may be specified within the SET TABLE SOURCE statement:

    SET TABLE mytable SOURCE "myfile;ignore_first=true;all_quoted=true"

When the default options all_quoted= false and quoted=true are in force, fields that are written to a line of the csv file will be quoted only if they contain the separator or the quote character. The quote character is doubled when used inside a string. When all_quoted=false and quoted=false the quote character is not doubled. With this option, it is not possible to insert any string containing the separator into the table, as it would become impossible to distinguish from a separator. While reading an existing data source file, the program treats each individual field separately. It determines that a field is quoted only if the first character is the quote character. It interprets the rest of the field on this basis.

The character encoding for the source file is ASCII by default. To support UNICODE or source files prepared with different encodings this can be changed to UTF-8 or any other encoding. The default is encoding=ASCII and the option encoding=UTF-8 or other supported encodings can be used.

Finally, HSQLDB provides the ability to read a text file as READ ONLY, by placing the keyword "DESC" at the end of the SET TABLE SOURCE statement:

    SET TABLE mytable SOURCE "myfile" DESC

Text table source files are cached in memory. The maximum number of rows of data that are in memory at any time is controlled by the textdb.cache_scale property. The default value for textdb.cache_scale is 10 and can be changed by setting the property in the .properties file for the database. The number of rows in memory is calculated as 3*(2**scale), which translates to 3072 rows for the default textdb.cache_scale setting (10). The property can also be set for individual text tables:

    SET TABLE mytable SOURCE "myfile;ignore_first=true;all_quoted=true;cache_scale=12"

Text tables may be disconnected from their underlying data source, i.e. the text file.

You can explicitly disconnect a text table from its file by issuing the following statement:

    SET TABLE mytable SOURCE OFF

Subsequently, mytable will be empty and read-only. However, the data source description will be preserved, and the table can be re-connected to it with

    SET TABLE mytable SOURCE ON

When a database is opened, if the source file for an existing text table is missing the table remains disconnected from its data source, but the source description is preserved. This allows the missing source file to be added to the directory and the table re-connected to it with the above command.

Disconnecting text tables from their source has several uses. While disconnected, the text source can be edited outside HSQLDB provided data integrity is respected. When large text sources are used, and several constraints or indexes need to be created on the table, it is possible to disconnect the source during the creation of constraints and indexes and reduce the time it takes to perform the operation.

There are some pre-defined roles in each database; some defined by the SQL Standard, some by HyperSQL. These roles can be assigned to users (directly or via other, user-defined roles). In addition, there is the default initial user, SA, created with each new database.

PUBLIC

the PUBLIC role

The role that is assigned to all authorizations (roles and users) in the database. This role has access rights to all objects in the INFORMATION_SCHEMA. Any roles or rights granted to this role, are in effect granted to all users of the database.

_SYSTEM

the _SYSTEM role

This role is the authorization for the pre-defined (system) objects in the database, including the INFORMATION_SCHEMA. This role cannot be assigned to any authorization.

DBA

the DBA role (HyperSQL-specific)

This is a special role in HyperSQL. A user that has this role can perform all possible administrative tasks on the database. The DBA role can also act as a proxy for all the roles and users in the database. This means it can do everything the authorization for a schema can do, including dropping the schema or its objects, or granting rights on the schema objects to a grantee.

CREATE_SCHEMA

the CREATE_SCHEMA role (HyperSQL-specific)

An authorization that has this role, can create schemas. The DBA authorization has this role and can grant it to other authorizations.

CHANGE_AUTHORIZATION

the CHANGE_AUTHORIZATION role (HyperSQL-specific)

A user that has this role, can change the authorization for the current session to another user. The DBA authorization has this role and can grant it to other authorizations.

SA

the SA user (HyperSQL-specific)

This user is automatically created with a new database and has the DBA role. Initially, the password for this user is an empty string. After connecting to the new database as this user, it is possible to change the password, create other users and created new schema objects. The SA user can be dropped by another user that has the DBA role.

By default, the objects in a schema can only be accessed by the schema owner. But the schema owner can grant privileges (access rights) on the objects to other users or roles.

Things can get far more complex, because the grant of privileges can be made WITH GRANT OPTION. In this case, the role or user that has been granted the privilege can grant the privilege to other roles and users.

Privileges can also be revoked from users or roles.

The statements for granting and revoking privileges normally specify which privileges are granted or revoked. However, there is a shortcut, ALL PRIVILEGES, which means all the privileges that the <grantor> has on the schema object. The <grantor> is normally the CURRENT_USER of the session that issues the statement.

The user or role that is granted privileges is referred to as <grantee> for the granted privileges.

Table

For tables, including views, privileges can be granted with different degrees of granularity. It is possible to grant a privilege on all columns of a table, or on specific columns of the table.

The DELETE privilege applies to the table, rather than its columns. It applies to all DELETE statements.

The SELECT, INSERT and UPDATE privileges may apply to all columns or to individual columns. These privileges determine whether the <grantee> can execute SQL data statements on the table.

The SELECT privilege designates the columns that can be referenced in SELECT statements, as well as the columns that are read in a DELETE or UPDATE statement, including the search condition.

The INSERT privilege designates the columns into which explicit values can be inserted. To be able to insert a row into the table, the user must therefore have the INSERT privilege on the table, or at least all the columns that do not have a default value.

The UPDATE privilege simply designates the table or the specific columns that can be updated.

The REFERENCES privilege allows the <grantee> to define a FOREIGN KEY constraint on a different table, which references the table or the specific columns designated for the REFERENCES privilege.

The TRIGGER privilege allows adding a trigger to the table.

Sequence, Type, Domain, Character Set, Collation, Transliteration,

For these objects, only USAGE can be granted. The USAGE privilege is needed when object is referenced directly in an SQL statement.

Routine

For routines, including procedures or functions, only EXECUTE privilege can be granted. This privilege is needed when the routine is used directly in an SQL statement.

Other Objects

Other objects such as constraints and assertions are not used directly and there is no grantable privilege that refers to them.

The columns of the rows of the result set are determined by the query expression. The number of columns and the type and name characteristics of each column are known when the query expression is compiled and before its execution. This metadata information remains constant regardless of changes to the contents of the tables used in the query expression. The metadata for the JDBC ResultSet is in the form of a ResultSetMetaData object. Various methods of the ResultSetMetaData interface return different properties of each column of the ResultSet.

A result set may contain 0 or more rows. The rows are determined by the execution of the query expression.

The setMaxRows(int) method of JDBC Statement allows limiting the number of rows returned by the statement. This limit is conceptually applied after the result has been built, and the excess rows are discarded.

A cursor is either scrollable or not. Scrollable cursors allow accessing rows by absolute or relative positioning. No-scroll cursors only allow moving to the next row. The cursor can be optionally declared with the SQL qualifiers SCROLL, or NO SCROLL. The JDBC statement parameter can be specified as: TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE. The JDBC type TYPE_SCROLL_SENSITIVE is not supported by HSQLDB.

The default is NO SCROLL or TYPE_FORWARD_ONLY.

When a JDBC ResultSet is opened, it is positioned before the first row. Using the next() method the position is moved to the first row. While the ResultSet is positioned on a row, various getter methods can be used to access the columns of the row.

The result returned by some query expressions is updatable. HSQLDB supports core SQL updatability features, plus some enhancements from the SQL optional features.

A query expression is updatable if it is a SELECT from a single underlying base table (or updatable view) either directly or indirectly. A SELECT statement featuring DISTINCT or GROUP BY or FETCH, LIMIT, OFFSET is not updatable. In an updatable query expression, one or more columns are updatable. An updatable column is a column that can be traced directly to the underlying table. Therefore, columns that contain expressions are not updatable. Examples of updatable query expressions are given below. The view V is updatable when its query expression is updatable. The SELECT statement from this view is also updatable:

SELECT A, B FROM T WHERE C > 5
SELECT A, B FROM (SELECT * FROM T WHERE C > 10) AS TT WHERE TT.B <10
CREATE VIEW V(X,Y) AS SELECT A, B FROM T WHERE C > 0 AND B < 10
SELECT X FROM V WHERE Y = 5

If a cursor is declared with the SQL qualifier, FOR UPDATE OF <column name list>, then only the stated columns in the result set become updatable. If any of the stated columns is not actually updatable, then the cursor declaration will not succeed.

If the SQL qualifier, FOR UPDATE is used, then all the updatable columns of the result set become updatable.

If a cursor is declared with FOR READ ONLY, then it is not updatable.

In HSQLDB, if FOR READ ONLY or FOR UPDATE is not used then all the updatable columns of the result set become updatable. This relaxes the SQL standard rule that in this case limits updatability to only simply updatable SELECT statements (where all columns are updatable).

In JDBC, CONCUR_READ_ONLY or CONCUR_UPDATABLE can be specified for the Statement parameter. CONCUR_UPDATABLE is required if the returning ResultSet is to be updatable. If CONCUR_READ_ONLY, which is the default, is used, then even an updatable ResultSet becomes read-only.

When a ResultSet is updatable, various setter methods can be used to modify the column values. The names of the setter methods begin with "update". After all the updates on a row are done, the updateRow() method must be called to finalise the row update.

An updatable ResultSet may or may not be insertable-into. In an insertable ResultSet, all columns of the result are updatable and any column of the base table that is not in the result must be a generated column or have a default value.

In the ResultSet object, a special pseudo-row, called the insert row, is used to populate values for insertion into the ResultSet (and consequently, into the base table). The setter methods must be used on all the columns, followed by a call to insertRow().

Individual rows from all updatable result sets can be deleted one at a time. The deleteRow() is called when the ResultSet is positioned on a row.

While using an updatable ResultSet to modify data, it is recommended not to change the same data using another ResultSet and not to execute SQL data change statements that modify the same data.

The sensitivity of the cursor relates to visibility of changes made to the data by the same transaction but without using the given cursor. While the result set is open, the same transaction may use statements such as INSERT or UPDATE, and change the data of the tables from which the result set data is derived. A cursor is SENSITIVE if it reflects those changes. It is INSENSITIVE if it ignores such changes. It is ASENSITIVE if behaviour is implementation dependent.

The SQL default is ASENSITIVE, i.e., implantation dependent.

In HSQLDB all cursors are INSENSITIVE. They do not reflect changes to the data made by other statements.

A cursor is holdable if the result set is not automatically closed when the current transaction is committed. Holdability can be specified in the cursor declaration using the SQL qualifiers WITH HOLD or WITHOUT HOLD.

In JDBC, hodability is specified using either of the following values for the Statement parameter: HOLD_CURSORS_OVER_COMMIT, or CLOSE_CURSORS_AT_COMMIT.

The SQL default is WITHOUT HOLD.

The JDBC default for HSQLDB result sets is WITH HOLD for read-only result sets and WITHOUT HOLD for updatable result sets.

If the holdability of a ResultSet is specified in a conflicting manner in the SQL statement and the JDBC Statement object, the JDBC setting takes precedence.

The autocommit property of a connection is a feature of JDBC and ODBC and is not part of the SQL Standard. In autocommit mode, all transactional statements are followed by an implicit commit. In autocommit mode, all ResultSet objects are read-only and holdable.

The JDBC settings, ResultSet.CONCUR_READONLY and ResultSet.CONCUR_UPDATABLE are the alternatives for read-only or updatability. The default is ResultSet.CONCUR_READONLY.

The JDBC settings, ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVE are the alternatives for both scrollability (navigation) and sensitivity. HyperSQL does not support ResultSet.TYPE_SCROLL_SENSITIVE. The two other alternatives can be used for both updatable and read-only result sets.

The JDBC settings ResultSet.CLOSE_CURSORS_AT_COMMIT and ResultSet.HOLD_CURSORS_OVER_COMMIT are the alternatives for the lifetime of the result set. The default is ResultSet.CLOSE_CURSORS_AT_COMMIT. The other setting can only be used for read-only result sets.

Examples of creating statements for updatable result sets are given below:

Connection c = newConnection();
Statement st;
c.setAutoCommit(false);
st = c.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
st = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

When a JDBC PreparedStatement or CallableStatement is used with an SQL statement that contains dynamic parameters, the data types of the parameters are resolved and determined by the engine when the statement is prepared. The SQL Standard has detailed rules to determine the data types and imposes limits on the maximum length or precision of the parameter. HyperSQL applies the standard rules with two exceptions for parameters with String and BigDecimal Java types. HyperSQL ignores the limits when the parameter value is set, and only enforces the necessary limits when the PreparedStatement is executed. In all other cases, parameter type limits are checked and enforce when the parameter is set.

In the example below the setString() calls do not raise an exception, but one of the execute() statements does.

// table definition: CREATE TABLE T (NAME VARCHAR(12), ...)
Connection c = newConnection();
PreparedStatement st = c.prepareStatement("SELECT * FROM T WHERE NAME = ?");
// type of the parameter is VARCHAR(12), which limits length to 12 characters
st.setString(1, "Eyjafjallajokull"); // string is longer than type, but no exception is raised here
set.execute(); // executes with no exception and does not find any rows
// but if an UPDATE is attempted, an exception is raised
st = c.prepareStatement("UPDATE T SET NAME = ? WHERE ID = 10");
st.setString(1, "Eyjafjallajokull"); // string is longer than type, but no exception is raised here
st.execute(); // exception is thrown when HyperSQL checks the value for update

All of the above also applies to setting the values in new and updated rows in updatable ResultSet objects.

JDBC parameters can be set with any compatible type, as supported by the JDBC specification. For CLOB and BLOB types, you can use streams, or create instances of BLOB or CLOB before assigning them to the parameters. You can even use CLOB or BLOB objects returned from connections to other RDBMS servers. The Connection.createBlob() and createClob() methods can be used to create the new LOBs. For very large LOB's the stream methods are preferable as they use less memory.

For array parameters, you must use a java.sql.Array object that contains the array elements before assigning to JDBC parameters. The Connection.createArrayOf(...) method can be used to create a new object, or you can use an Array returned from connections to other RDBMS servers.

The methods of the JDBC ResultSet interface can be used to return values and to convert value to different types as supported by the JDBC specification.

When a CLOB and BLOB object is returned from a ResultSet, no data is transferred until the data is read by various methods of java.sql.CLOB and java.sql.BLOB. Data is streamed in large blocks to avoid excessive memory use.

Array objects are returned as instances of java.sql.Array.

Literals are used to express constant values. The general type of a literal is known by its format. The specific type is based on conventions.

unicode escape elements

unicode escape elements

<Unicode escape specifier> ::= [ UESCAPE <quote><Unicode escape character><quote> ]

<Unicode escape value> ::= <Unicode 4 digit escape value> | <Unicode 6 digit escape value> | <Unicode character escape value>

<Unicode 4 digit escape value> ::= <Unicode escape character><hexit><hexit><hexit><hexit>

<Unicode 6 digit escape value> ::= <Unicode escape character><plus sign> <hexit><hexit><hexit><hexit><hexit><hexit>

<Unicode character escape value> ::= <Unicode escape character><Unicode escape character>

<Unicode escape character> ::= a single character than a <hexit> (a-f, A-F, 0-9), <plus sign>, <quote>, <double quote>, or <white space>

character literal

character literal

<character string literal> ::= [ <introducer><character set specification> ] <quote> [ <character representation>... ] <quote> [ { <separator> <quote> [ <character representation>... ] <quote> }... ]

<introducer> ::= <underscore>

<character representation> ::= <nonquote character> | <quote symbol>

<nonquote character> ::= any character apart from the quote symbol.

<quote symbol> ::= <quote><quote>

<national character string literal> ::= N <quote> [ <character representation>... ] <quote> [ { <separator> <quote> [ <character representation>... ] <quote> }... ]

<Unicode character string literal> ::= [ <introducer><character set specification> ] U<ampersand><quote> [ <Unicode representation>... ] <quote> [ { <separator> <quote> [ <Unicode representation>... ] <quote> }... ] <Unicode escape specifier>

<Unicode representation> ::= <character representation> | <Unicode escape value>

The type of a character literal is CHARACTER. The length of the string literal is the character length of the type. If the quote character is used in a string, it is represented with two quote characters. Long literals can be divided into multiple quoted strings, separated with a space or end-of-line character.

Unicode literals start with U& and can contain ordinary characters and unicode escapes. A unicode escape begins with the backslash ( \ ) character and is followed by four hexadecimal characters which specify the character code.

Example of character literals are given below:

'a literal'  ' string seperated'  ' into parts'
'a string''s literal form with quote character'
U&'Unicode string with Greek delta \0394 and phi \03a6 letters'

binary literal

binary literal

<binary string literal> ::= X <quote> [ <space>... ] [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }... ] <quote> [ { <separator> <quote> [ <space>... ] [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }... ] <quote> }... ]

<hexit> ::= <digit> | A | B | C | D | E | F | a | b | c | d | e | f

The type of a binary literal is BINARY. The octect length of the binary literal is the length of the type. Case-insensitive hexadecimal characters are used in the binary string. Each pair of characters in the literal represents a byte in the binary string. Long literals can be divided into multiple quoted strings, separated with a space or end-of-line character.

X'1abACD34' 'Af'

bit literal

bit literal

<bit string literal> ::= B <quote> [ <bit> ... ] <quote> [ { <separator> <quote> [ <bit>... ] <quote> }... ]

<bit> ::= 0 | 1

The type of a binary literal is BIT. The bit length of the bit literal is the length of the type. Digits 0 and 1 are used to represent the bits. Long literals can be divided into multiple quoted strings, separated with a space or end-of-line character.

B'10001001' '00010'

numeric literal

numeric literal

<signed numeric literal> ::= [ <sign> ] <unsigned numeric literal>

<unsigned numeric literal> ::= <exact numeric literal> | <approximate numeric literal>

<exact numeric literal> ::= <unsigned integer> [ <period> [ <unsigned integer> ] ] | <period> <unsigned integer>

<sign> ::= <plus sign> | <minus sign>

<approximate numeric literal> ::= <mantissa> E <exponent>

<mantissa> ::= <exact numeric literal>

<exponent> ::= <signed integer>

<signed integer> ::= [ <sign> ] <unsigned integer>

<unsigned integer> ::= <digit>...

The type of an exact numeric literal without a decimal point is INTEGER, BIGINT, or DECIMAL, depending on the value of the literal (the smallest type that can represent the value is the type).

The type of an exact numeric literal with a decimal point is DECIMAL. The precision of a decimal literal is the total number of digits of the literal. The scale of the literal is the total number of digits to the right of the decimal point.

The type of an approximate numeric literal is DOUBLE. An approximate numeric literal always includes the mantissa and exponent, separated by E.

12
34.35
+12E-2

boolean literal

boolean literal

<boolean literal> ::= TRUE | FALSE | UNKNOWN

The boolean literal is one of the specified keywords.

datetime and interval literal

datetime and interval literal

<datetime literal> ::= <date literal> | <time literal> | <timestamp literal>

<date literal> ::= DATE <date string>

<time literal> ::= TIME <time string>

<timestamp literal> ::= TIMESTAMP <timestamp string>

<date string> ::= <quote> <unquoted date string> <quote>

<time string> ::= <quote> <unquoted time string> <quote>

<timestamp string> ::= <quote> <unquoted timestamp string> <quote>

<time zone interval> ::= <sign> <hours value> <colon> <minutes value>

<date value> ::= <years value> <minus sign> <months value> <minus sign> <days value>

<time value> ::= <hours value> <colon> <minutes value> <colon> <seconds value>

<interval literal> ::= INTERVAL [ <sign> ] <interval string> <interval qualifier>

<interval string> ::= <quote> <unquoted interval string> <quote>

<unquoted date string> ::= <date value>

<unquoted time string> ::= <time value> [ <time zone interval> ]

<unquoted timestamp string> ::= <unquoted date string> <space> <unquoted time string>

<unquoted interval string> ::= [ <sign> ] { <year-month literal> | <day-time literal> }

<year-month literal> ::= <years value> [ <minus sign> <months value> ] | <months value>

<day-time literal> ::= <day-time interval> | <time interval>

<day-time interval> ::= <days value> [ <space> <hours value> [ <colon> <minutes value> [ <colon> <seconds value> ] ] ]

<time interval> ::= <hours value> [ <colon> <minutes value> [ <colon> <seconds value> ] ] | <minutes value> [ <colon> <seconds value> ] | <seconds value>

<years value> ::= <datetime value>

<months value> ::= <datetime value>

<days value> ::= <datetime value>

<hours value> ::= <datetime value>

<minutes value> ::= <datetime value>

<seconds value> ::= <seconds integer value> [ <period> [ <seconds fraction> ] ]

<seconds integer value> ::= <unsigned integer>

<seconds fraction> ::= <unsigned integer>

<datetime value> ::= <unsigned integer>

The type of a datetime or interval type is specified in the literal. The fractional second precision is the number of digits in the fractional part of the literal. Details are described in the SQL Language chapter

DATE '2008-08-08'
TIME '20:08:08'
TIMESTAMP '2008-08-08 20:08:08.235'

INTERVAL '10' DAY
INTERVAL -'08:08' MINUTE TO SECOND

References are identifier chains, which can be a single identifiers or identifiers chains composed of single identifiers chained together with the period symbol.

identifier chain

identifier chain

<identifier chain> ::= <identifier> [ { <period> <identifier> }... ]

<basic identifier chain> ::= <identifier chain>

A period-separated chain of identifiers. The identifiers in an identifier chain can refer to database objects in a hierarchy. The possible hierarchies are as follows. In each hierarchy, elements from the start or the end can be missing, but the order of elements cannot be changed.

catalog, schema, database object

catalog, schema, table, column

correlation name, column

Examples of identifier chain are given below:

SELECT MYCAT.MYSCHEMA.MYTABLE.MYCOL FROM MYCAT.MYSCHEMA.MYTABLE
DROP TABLE MYCAT.MYSCHEMA.MYTABLE CASCADE
ALTER SEQUENCE MYCAT.MYSCHEMA.MYSEQUENCE RESTART WITH 100

column reference

column reference

<column reference> ::= <basic identifier chain> | MODULE <period> <qualified identifier> <period> <column name>

Reference a column or a routine variable.

SQL parameter reference

SQL parameter reference

<SQL parameter reference> ::= <basic identifier chain>

Reference an SQL routine parameter.

contextually typed value specification

contextually typed value specification

<contextually typed value specification> ::= <null specification> | <default specification>

<null specification> ::= NULL

<default specification> ::= DEFAULT

Specify a value whose data type or value is inferred from its context. DEFAULT is used for assignments to table columns that have a default value, or to table columns that are generated either as an IDENTITY value or as an expression. NULL can be used only in a context where the type of the value is known. For example, a NULL can be assigned to a column of the table in an INSERT or UPDATE statement, because the type of the column is known. But if NULL is used in a SELECT list, it must be used in a CAST statement.

Value expression is a general name for all expressions that return a value. Different types of expressions are allowed in different contexts.

value expression primary

value expression primary

<value expression primary> ::= <parenthesized value expression> | <nonparenthesized value expression primary>

<parenthesized value expression> ::= <left paren> <value expression> <right paren>

<nonparenthesized value expression primary> ::= <unsigned value specification> | <column reference> | <set function specification> | <scalar subquery> | <case expression> | <cast specification> | <next value expression> | <routine invocation>

Specify a value that is syntactically self-delimited.

value specification

value specification

<value specification> ::= <literal> | <general value specification>

<unsigned value specification> ::= <unsigned literal> | <general value specification>

<target specification> ::= <host parameter specification> | <SQL parameter reference> | <column reference> | <dynamic parameter specification>

<simple target specification> ::= <host parameter specification> | <SQL parameter reference> | <column reference> | <embedded variable name>

<host parameter specification> ::= <host parameter name> [ <indicator parameter> ]

<dynamic parameter specification> ::= <question mark>

Specify one or more values, host parameters, SQL parameters, dynamic parameters, or host variables.

row value expression

row value expression

<row value expression> ::= <row value special case> | <explicit row value constructor>

<row value predicand> ::= <row value special case> | <row value constructor predicand>

<row value special case> ::= <nonparenthesized value expression primary>

<explicit row value constructor> ::= <left paren> <row value constructor element> <comma> <row value constructor element list> <right paren> |

ROW <left paren> <row value constructor element list> <right paren> | <row subquery>

Specify a row consisting of one or more elements. A comma separated list of expressions, enclosed in brackets, with the optional keyword ROW. In SQL, a row containing a single element can often be used where a single value is expected.

set function specification

set function specification

<set function specification> ::= <aggregate function> | <grouping operation>

<grouping operation> ::= GROUPING <left paren> <column reference> [ { <comma> <column reference> }... ] <right paren>

Specify a value derived by the application of a function to an argument. Early releases of HyperSQL 2.0 do not support <grouping operation> .

COALESCE

coalesce expression

<coalesce expression> := COALESCE <left paren> <value expression> { <comma> <value expression> }... <right paren>

Replace null values with another value. The coalesce expression has two or more instances of <value expression>. If the first <value expression> evaluates to a non-null value, it is returned as the result of the coalesce expression. If it is null, the next <value expression> is evaluated and if it evaluates to a non-non value, it is returned, and so on.

The type of the return value of a COALESCE expression is the aggregate type of the types of all the <value expression> instances. Therefore, any value returned is implicitly cast to this type. HyperSQL also features built-in functions with similar functionality.

NULLIF

nullif expression

<nullif expression> := NULLIF <left paren> <value expression> <comma> <value expression> <right paren>

Return NULL if two values are equal. If the result of the first <value expression> is not equal to the result of the second, then it is returned, otherwise NULL is returned. The type of the return value is the type of the first <value expression>.

SELECT i, NULLIF(n, 'not defined') FROM t

CASE

case specification

<case specification> ::= <simple case> | <searched case>

<simple case> ::= CASE <case operand> <simple when clause>... [ <else clause> ] END

<searched case> ::= CASE <searched when clause>... [ <else clause> ] END

<simple when clause> ::= WHEN <when operand list> THEN <result>

<searched when clause> ::= WHEN <search condition> THEN <result>

<else clause> ::= ELSE <result>

<case operand> ::= <row value predicand> | <overlaps predicate part 1>

<when operand list> ::= <when operand> [ { <comma> <when operand> }... ]

<when operand> ::= <row value predicand> | <comparison predicate part 2> | <between predicate part 2> | <in predicate part 2> | <character like predicate part 2> | <octet like predicate part 2> | <similar predicate part 2> | <regex like predicate part 2> | <null predicate part 2> | <quantified comparison predicate part 2> | <match predicate part 2> | <overlaps predicate part 2> | <distinct predicate part 2>

<result> ::= <result expression> | NULL

<result expression> ::= <value expression>

Specify a conditional value. The result of a case expression is always a value. All the values introduced with THEN must be of the same type.

An (simple) example of the CASE statement is given below. It returns 'Britain', 'Germany', or 'Other country' depending on the value of dialcode'

CASE dialcode WHEN 44 THEN 'Britain' WHEN 49 THEN 'Germany' ELSE 'Other country' END

The case statement can be far more complex and involve several conditions.

CAST

cast specification

<cast specification> ::= CAST <left paren> <cast operand> AS <cast target> <right paren>

<cast operand> ::= <value expression> | <implicitly typed value specification>

<cast target> ::= <domain name> | <data type>

Specify a data conversion. Data conversion takes place automatically among variants of a general type. For example numeric values are freely converted from one type to another in expressions.

Explicit type conversion is necessary in two cases. One case is to determine the type of a NULL value. The other case is to force conversion for special purposes. Values of data types can be cast to a character type. The exception is BINARY and OTHER types. The result of the cast is the literal expression of the value. Conversely, a value of a character type can be converted to another type if the character value is a literal representation of the value in the target type. Special conversions are possible between numeric and interval types, which are described in the section covering interval types.

The examples below show examples of cast with their result:

CAST (NULL AS TIMESTAMP)
CAST ('   199  ' AS INTEGER) = 199
CAST ('tRue ' AS BOOLEAN) = TRUE
CAST (INTERVAL '2' DAY AS INTEGER) = 2
CAST ('1992-04-21' AS DATE) = DATE '1992-04-21'

NEXT VALUE FOR

next value expression

<next value expression> ::= NEXT VALUE FOR <sequence generator name>

Return the next value of a sequence generator. This expression can be used as a select list element in queries, or in assignments to table columns in data change statements. If the expression is used more than once in a single row that is being evaluated, the same value is returned for each invocation. After evaluation of the particular row is complete, the sequence generator will return a different value from the old value. The new value is generated by the sequence generator by adding the increment to the last value it generated. In the example below:

INSERT INTO MYTABLE(COL1, COL2) VALUES 2, NEXT VALUE FOR MYSEQUENCE

value expression

value expression

<value expression> ::= <numeric value expression> | <string value expression> | <datetime value expression> | <interval value expression> | <boolean value expression> | <row value expression>

An expression that returns a value. The value can be a single value, or a row consisting more than one value.

numeric value expression

numeric value expression

<numeric value expression> ::= <term> | <numeric value expression> <plus sign> <term> | <numeric value expression> <minus sign> <term>

<term> ::= <factor> | <term> <asterisk> <factor> | <term> <solidus> <factor>

<factor> ::= [ <sign> ] <numeric primary>

<numeric primary> ::= <value expression primary> | <numeric value function>

Specify a numeric value. The BNF indicates that <asterisk> and <solidus> (the operators for multiplication and division) have precedence over <minus sign> and <plus sign>.

numeric value function

numeric value function

<numeric value function> ::= <position expression> | <extract expression> | <length expression> ...

Specify a function yielding a value of type numeric. The supported numeric value functions are listed and described in the Built In Functions chapter.

string value expression

string value expression

<string value expression> ::= <string concatenation> | <string factor>

<string factor> ::= <value expression primary> | <string value function>

<string concatenation> ::= <string value expression> <concatenation operator> <string factor>

<concatenation operator> ::= ||

Specify a character string value, a binary string value, or a bit string value. The BNF indicates that a string value expression can be formed by concatenation of two or more <value expression primary>. The types of the <value expression primary> elements must be compatible, that is, all must be string, or binary or bit string values.

character value function

string value function

<string value function> ::= ...

Specify a function that returns a character string or binary string. The supported character value functions are listed and described in the Built In Functions chapter.

datetime value expression

datetime value expression

<datetime value expression> ::= <datetime term> | <interval value expression> <plus sign> <datetime term> | <datetime value expression> <plus sign> <interval term> | <datetime value expression> <minus sign> <interval term>

<datetime term> ::= <datetime factor>

<datetime factor> ::= <datetime primary> [ <time zone> ]

<datetime primary> ::= <value expression primary> | <datetime value function>

<time zone> ::= AT <time zone specifier>

<time zone specifier> ::= LOCAL | TIME ZONE <interval primary>

Specify a datetime value. Details are described in the SQL Language chapter.

datetime value function

datetime value function

<datetime value function> ::= ...

Specify a function that returns a datetime value. The supported datetime value functions are listed and described in the Built In Functions chapter.

interval term

interval value expression

<interval value expression> ::= <interval term> | <interval value expression 1> <plus sign> <interval term 1> | <interval value expression 1> <minus sign> <interval term 1> | <left paren> <datetime value expression> <minus sign> <datetime term> <right paren> <interval qualifier>

<interval term> ::= <interval factor> | <interval term 2> <asterisk> <factor> | <interval term 2> <solidus> <factor> | <term> <asterisk> <interval factor>

<interval factor> ::= [ <sign> ] <interval primary>

<interval primary> ::= <value expression primary> [ <interval qualifier> ] | <interval value function>

<interval value expression 1> ::= <interval value expression>

<interval term 1> ::= <interval term>

<interval term 2> ::= <interval term>

Specify an interval value. Details are described in the SQL Language chapter.

interval absolute value function

interval value function

<interval value function> ::= <interval absolute value function>

<interval absolute value function> ::= ABS <left paren> <interval value expression> <right paren>

Specify a function that returns the absolute value of an interval. If the interval is negative, it is negated, otherwise the original value is returned.

boolean value expression

boolean value expression

<boolean value expression> ::= <boolean term> | <boolean value expression> OR <boolean term>

<boolean term> ::= <boolean factor> | <boolean term> AND <boolean factor>

<boolean factor> ::= [ NOT ] <boolean test>

<boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ]

<truth value> ::= TRUE | FALSE | UNKNOWN

<boolean primary> ::= <predicate> | <boolean predicand>

<boolean predicand> ::= <parenthesized boolean value expression> | <nonparenthesized value expression primary>

<parenthesized boolean value expression> ::= <left paren> <boolean value expression> <right paren>

Specify a boolean value.

Predicates are conditions with two sides and evaluate to a boolean value. The left side of the predicate, the <row value predicand>, is the common element of all predicates. This element is a generalisation of both <value expression>, which is a scalar, and of <explicit row value constructor>, which is a row. The two sides of a predicate can be split in CASE statements where the <row value predicand> is part of multiple predicates.

The number of fields in all <row value predicand> used in predicates must be the same and the types of the fields in the same position must be compatible for comparison. If either of these conditions does not hold, an exception is raised. The number of fields in a row is called the degree.

In many types of predicates (but not all of them), if the <row value predicand> evaluates to NULL, the result of the predicate is UNKNOWN. If the <row value predicand> has more than one element, and one or more of the fields evaluate to NULL, the result depends on the particular predicate.

comparison predicand

comparison predicate

<comparison predicate> ::= <row value predicand> <comp op> <row value predicand>

<comp op> ::= <equals operator> | <not equals operator> | <less than operator> | <greater than operator> | <less than or equals operator> | <greater than or equals operator>

Specify a comparison of two row values. If either <row value predicand> evaluates to NULL, the result of <comparison predicate> is UNKNOWN. Otherwise, the result is TRUE, FALSE or UNKNOWN.

If the degree of <row value predicand> is larger than one, comparison is performed between each field and the corresponding field in the other <row value predicand> from left to right, one by one.

When comparing two elements, if either field is NULL then the result is UNKNOWN.

For <equals operator>, if the result of comparison is TRUE for all field, the result of the predicate is TRUE. If the result of comparison is FALSE for one field, the result of predicate is FALSE. Otherwise the result is UNKNOWN.

The <not equals operator> is translated to NOT (<row value predicand> = <row value predicand>).

The <less than or equals operator> is translated to (<row value predicand> = <row value predicand>) OR (<row value predicand> < <row value predicand>). The <greater than or equals operator> is translated similarly.

For the <less than operator> and <greater than operator>, if two fields at a given position are equal, then comparison continues to the next field. Otherwise, the result of the last performed comparison is returned as the result of the predicate. This means that if the first field is NULL, the result is always UNKNOWN.

The logic that governs NULL values and UNKNOWN result is as follows: Suppose the NULL values were substituted by arbitrary real values. If substitution cannot change the result of the predicate, then the result is TRUE or FALSE, based on the existing non-NULL values, otherwise the result of the predicate is UNKNOWN.

The examples of comparison given below use literals, but the literals actually represent the result of evaluation of some expression.

((1, 2, 3, 4) = (1, 2, 3, 4)) IS TRUE
((1, 2, 3, 4) = (1, 2, 3, 5)) IS FALSE
((1, 2, 3, 4) < (1, 2, 3, 4)) IS FALSE
((1, 2, 3, 4) < (1, 2, 3, 5)) IS TRUE
((NULL, 1, NULL) = (NULL, 1, NULL)) IS UNKNOWN  
((NULL, 1, NULL) = (NULL, 2, NULL)) IS FALSE  
((NULL, 1, NULL) <> (NULL, 2, NULL)) IS TRUE  
((NULL, 1, 2) <all operators> (NULL, 1, 2)) IS UNKNOWN
((1, NULL, ...) < (1, 2, ...)) IS UNKNOWN  
((1, NULL, ...) < (2, NULL, ...)) IS TRUE
((2, NULL, ...) < (1, NULL, ...)) IS FALSE

BETWEEN

between predicate

<between predicate> ::= <row value predicand> <between predicate part 2>

<between predicate part 2> ::= [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ] <row value predicand> AND <row value predicand>

Specify a range comparison. The default is ASYMMETRIC. The expression X BETWEEN Y AND Z is equivalent to (X >= Y AND X <= Z). Therefore if Y > Z, the BETWEEN expression is never true. The expression X BETWEEN SYMMETRIC Y AND Z is equivalent to (X >= Y AND X <= Z) OR (X >= Z AND X <= Y). The expression Z NOT BETWEEN ... is equivalent to NOT (Z BETWEEN ...). If any of the three <row value predicand> evaluates to NULL, the result is UNKNOWN.

IN

in predicate

<in predicate> ::= <row value predicand> [ NOT ] IN <in predicate value>

<in predicate value> ::= <table subquery> | <left paren> <in value list> <right paren>

| <left paren> UNNEST <left paren> <array value expression> <right paren> <right paren>

<in value list> ::= <row value expression> [ { <comma> <row value expression> }... ]

Specify a quantified comparison. The expression X NOT IN Y is equivalent to NOT (X IN Y). The ( <in value list> ) is converted into a table with one or more rows. The expression X IN Y is equivalent to X = ANY Y, which is a <quantified comparison predicate>.

If the <table subquery> returns no rows, the result is FALSE. Otherwise the <row value predicand> is compared one by one with each row of the <table subquery>.

If the comparison is TRUE for at least one row, the result is TRUE. If the comparison is FALSE for all rows, the result is FALSE. Otherwise the result is UNKNOWN.

HyperSQL supports an extension to the SQL Standard to allow an array to be used in the <in predicate value>. This is intended to be used with prepared statements where a variable length array of values can be used as the parameter value for each call. The example below shows how this is used.

SELECT * from customer where firstname in ( UNNEST(?) )

LIKE

like predicate

<like predicate> ::= <character like predicate> | <octet like predicate>

<character like predicate> ::= <row value predicand> [ NOT ] LIKE <character pattern> [ ESCAPE <escape character> ]

<character pattern> ::= <character value expression>

<escape character> ::= <character value expression>

<octet like predicate> ::= <row value predicand> [ NOT ] LIKE <octet pattern> [ ESCAPE <escape octet> ]

<octet pattern> ::= <binary value expression>

<escape octet> ::= <binary value expression>

Specify a pattern-match comparison for character or binary strings. The <row value predicand> is always a <string value expression> of character or binary type. The <character pattern> or <octet pattern> is a <string value expression> in which the underscore and percent characters have special meanings. The underscore means match any one character, while the percent means match a sequence of zero or more characters. The <escape character> or <escape octet> is also a <string value expression> that evaluates to a string of exactly one character length. If the underscore or the percent is required as normal characters in the pattern, the specified <escape character> or <escape octet> can be used in the pattern before the underscore or the percent. The <row value predicand> is compared with the <character pattern> and the result of comparison is returned. If any of the expressions in the predicate evaluates to NULL, the result of the predicate is UNKNOWN. The expression A NOT LIKE B is equivalent to NOT (A LIKE B). If the length of the escape is not 1 or it is used in the pattern not immediately before an underscore or a percent character, an exception is raised.

IS NULL

null predicate

<null predicate> ::= <row value predicand> IS [ NOT ] NULL

Specify a test for a null value. The expression X IS NOT NULL is NOT equivalent to NOT (X IS NULL)if the degree of the <row value predicand> is larger than 1. The rules are: If all fields are null, X IS NULL is TRUE and X IS NOT NULL is FALSE. If only some fields are null, both X IS NULL and X IS NOT NULL are FALSE. If all fields are not null, X IS NULL is FALSE and X IS NOT NULL is TRUE.

ALL and ANY

quantified comparison predicate

<quantified comparison predicate> ::= <row value predicand> <comp op> <quantifier> <table subquery>

<quantifier> ::= <all> | <some>

<all> ::= ALL

<some> ::= SOME | ANY

Specify a quantified comparison. For a quantified comparison, the <row value predicand> is compared one by one with each row of the <table sub query>.

If the <table subquery> returns no rows, then if ALL is specified the result is TRUE, but if SOME or ANY is specified the result is FALSE.

If ALL is specified, if the comparison is TRUE for all rows, the result of the predicate is TRUE. If the comparison is FALSE for at least one row, the result is FALSE. Otherwise the result is UNKNOWN.

If SOME or ANY is specified, if the comparison is TRUE for at least one row, the result is TRUE. If the comparison is FALSE for all rows, the result is FALSE. Otherwise the result is UNKNOWN. Note that the IN predicate is equivalent to the SOME or ANY predicate using the <equals operator>.

In the examples below, the date of an invoice is compared to holidays in a given year. In the first example the invoice date must equal one of the holidays, in the second example it must be later than all holidays (later than the last holiday), in the third example it must be on or after some holiday (on or after the first holiday), and in the fourth example, it must be before all holidays (before the first holiday).

invoice_date = SOME (SELECT holiday_date FROM holidays)
invoice_date > ALL (SELECT holiday_date FROM holidays)
invoice_date >= ANY (SELECT holiday_date FROM holidays)
invoice_date < ALL (SELECT holiday_date FROM holidays)

EXISTS

exists predicate

<exists predicate> ::= EXISTS <table subquery>

Specify a test for a non-empty set. If the evaluation of <table subquery> results in one or more rows, then the expression is TRUE, otherwise FALSE.

UNIQUE

unique predicate

<unique predicate> ::= UNIQUE <table subquery>

Specify a test for the absence of duplicate rows. The result of the test is either TRUE or FALSE (never UNKNOWN). The rows of the <table subquery> that contain one or more NULL values are not considered for this test. If the rest of the rows are distinct from each other, the result of the test is TRUE, otherwise it is FALSE. The distinctness of rows X and Y is tested with the predicate X IS DISTINCT FROM Y.

MATCH

match predicate

<match predicate> ::= <row value predicand> MATCH [ UNIQUE ] [ SIMPLE | PARTIAL | FULL ] <table subquery>

Specify a test for matching rows. The default is MATCH SIMPLE without UNIQUE. The result of the test is either TRUE or FALSE (never UNKNOWN).

The interpretation of NULL values is different from other predicates and quite counter-intuitive. If the <row value predicand> is NULL, or all of its fields are NULL, the result is TRUE.

Otherwise, the <row value predicand> is compared with each row of the <table subquery>.

If SIMPLE is specified, if some field of <row value predicate> is NULL, the result is TRUE. Otherwise if <row value predicate> is equal to one or more rows of <table subquery> the result is TRUE if UNIQUE is not specified, or if UNIQUE is specified and only one row matches. Otherwise the result is FALSE.

If PARTIAL is specified, if the non-null values <row value predicate> are equal to those in one or more rows of <table subquery> the result is TRUE if UNIQUE is not specified, or if UNIQUE is specified and only one row matches. Otherwise the result is FALSE.

If FULL is specified, if some field of <row value predicate> is NULL, the result is FALSE. Otherwise if <row value predicate> is equal to one or more rows of <table subquery> the result is TRUE if UNIQUE is not specified, or if UNIQUE is specified and only one row matches.

Note that MATCH can also used be used in FOREIGN KEY constraint definitions. The exact meaning is described in the Schemas and Database Objects chapter.

OVERLAPS

overlaps predicate

<overlaps predicate> ::= <row value predicand> OVERLAPS <row value predicand>

Specify a test for an overlap between two datetime periods. Each <row value predicand> must have two fields and the fields together represent a datetime period. So the predicates is always in the form (X1, X2) OVERLAPS (Y1, Y2). The first field is always a datetime value, while the second field is either a datetime value or an interval value.

If the second value is an interval value, it is replaced with the sum of the datetime value and itself, for example (X1, X1 + X2) OVERLAPS (Y1, Y1 + Y 2).

If any of the values is NULL, the result is UNKNOWN.

The expression is true if there is there is any overlap between the two datetime periods. In the example below, the period is compared with a week long period ending yesterday.

(startdate, enddate) OVERLAPS (CURRENT_DATE - 7 DAY, CURRENT_DATE - 1 DAY)

IS DISTINCT

is distinct predicate

<distinct predicate> ::= <row value predicand> IS [ NOT ] DISTINCT FROM <row value predicand>

Specify a test of whether two row values are distinct. The result of the test is either TRUE or FALSE (never UNKNOWN). The degree the two <row value predicand> must be the same. Each field of the first <row value predicand> is compared to the field of the second <row value predicand> at the same position. If one field is NULL and the other is not NULL, or if the elements are NOT equal, then the result of the expression is TRUE. If no comparison result is TRUE, then the result of the predicate is FALSE. The expression X IS NOT DISTINCT FROM Y is equivalent to NOT (X IS DISTINCT FORM Y). The following check returns true if startdate is not equal to enddate. It also returns true if either startdate or enddate is NULL. It returns false in other cases.

startdate IS DISTINCT FROM enddate

search condition

search condition

<search condition> ::= <boolean value expression>

Specify a condition that is TRUE, FALSE, or UNKNOWN. A search condition is often a predicate.

PATH

path specification

<path specification> ::= PATH <schema name list>

<schema name list> ::= <schema name> [ { <comma> <schema name> }... ]

Specify an order for searching for a user-defined SQL-invoked routine. This is not currently supported by HyperSQL.

routine invocation

routine invocation

<routine invocation> ::= <routine name> <SQL argument list>

<routine name> ::= [ <schema name> <period> ] <qualified identifier>

<SQL argument list> ::= <left paren> [ <SQL argument> [ { <comma> <SQL argument> }... ] ] <right paren>

<SQL argument> ::= <value expression> | <target specification>

Invoke an SQL-invoked routine. Examples are given in the SQL-Invoked Routines chapter.

COLLATE

collate clause

<collate clause> ::= COLLATE <collation name>

Specify a default collation. This is not currently supported by HyperSQL

CONSTRAINT

constraint name definition

<constraint name definition> ::= CONSTRAINT <constraint name>

<constraint characteristics> ::= <constraint check time> [ [ NOT ] DEFERRABLE ] | [ NOT ] DEFERRABLE [ <constraint check time> ]

<constraint check time> ::= INITIALLY DEFERRED | INITIALLY IMMEDIATE

Specify the name of a constraint and its characteristics. This is an optional element of CONSTRAINT definition, not yet supported by HyperSQL.

aggregate function

aggregate function

<aggregate function> ::= COUNT <left paren> <asterisk> <right paren> [ <filter clause> ] | <general set function> [ <filter clause> ]

<general set function> ::= <set function type> <left paren> [ <set quantifier> ] <value expression> <right paren>

<set function type> ::= <computational operation>

<computational operation> ::= AVG | MAX | MIN | SUM | EVERY | ANY | SOME | COUNT | STDDEV_POP | STDDEV_SAMP | VAR_SAMP | VAR_POP

<set quantifier> ::= DISTINCT | ALL

<filter clause> ::= FILTER <left paren> WHERE <search condition> <right paren>

Specify a value computed from a collection of rows. An aggregate function is used exclusively in a <query specification> and its use transforms a normal query into an aggregate query returning a single row instead of the group of multiple rows that the original query returns. For example, SELECT acolumn <table expression> is a query that returns the value of acolumn for all the rows the satisfy the given condition. But SELECT MAX(acolumn) <table expression> returns only one row, containing the largest value in that column. The query SELECT COUNT(*) <table expression> returns the count of rows, while SELECT COUNT(acolumn) <table expression> returns the count of rows where acolumn IS NOT NULL.

If the <table expression> is a grouped table, the aggregate function returns the result of the COUNT or <computational operation> for each group. In this case the result has the same number of rows as the original query. For example SELECT SUM(acolumn) <table expression> when <table expression> has a GROUP BY clause, returns the sum of values for acolumn in each group.

The AVG and SUM operations can be performed on numeric expressions only. AVG returns the average value, while SUM returns the sum of all non-null values. MAX and MIN return the minimum or the maximum value. If all values are NULL, the operations return NULL. The COUNT(*) operation returns the count of all values, while COUNT(<value expression>) returns the count of non-NULL values.

The EVERY, ANY and SOME operations can be performed on boolean expressions only. EVERY returns TRUE if all the values are TRUE, otherwise FALSE. ANY and SOME are the same operation and return TRUE if one of the values is TRUE, otherwise it returns FALSE.

The other operations perform the statistical functions STDDEV_POP, STDDEV_SAMP, VAR_SAMP, VAR_POP on numeric values. NULL values are ignored in calculations.

User defined aggregate functions can be defined and used instead of the built-in aggregate functions. Syntax and examples are given in the SQL-Invoked Routines chapter.

sort specification list

sort specification list

<sort specification list> ::= <value expression> [ASC | DESC] [NULLS FIRST | NULLS LAST]

Specify a sort order. A sort operation is performed on the result of a <query expression> or <query specification> and sorts the result according to one or more <value expression>. The <value expression> is usually a single column of the result, but in some cases it can be a column of the <table expression> that is not used in the select list.

In data access statements, a table can be a database table (or view) or an ephemeral table formed for the duration of the query. Some types of table are <table primary> and can participate in joins without the use of extra parentheses. The BNF in the Table Primary section below lists different types of <table primary>:

Tables can also be formed by specifying the values that are contained in them:

<table value constructor> ::= VALUES <row value expression list>

<row value expression list> ::= <table row value expression> [ { <comma> <table row value expression> }... ]

In the example below a table with two rows and 3 columns is constructed out of some values:

VALUES (12, 14, null), (10, 11, CURRENT_DATE)

When a table is used directly in a UNION or similar operation, the keyword TABLE is used with the name:

<explicit table> ::= TABLE <table or query name>

In the examples below, all rows of the two tables are included in the union. The keyword TABLE is used in the first example. The two examples below are equivalent.

TABLE atable UNION TABLE anothertable
SELECT * FROM atable UNION SELECT * FROM anothertable

A query specification is a SELECT statement. It is the most common form of <derived table> . A <table expression> is a base table, a view or any form of allowed derived table. The SELECT statement performs projection, naming, computing or aggregation on the rows of the <table expression> .

<query specification> ::= SELECT [ DISTINCT | ALL ] <select list> <table expression>

<select list> ::= <asterisk> | <select sublist> [ { <comma> <select sublist> }... ]

<select sublist> ::= <derived column> | <qualified asterisk>

<qualified asterisk> ::= <asterisked identifier chain> <period> <asterisk>

<asterisked identifier chain> ::= <asterisked identifier> [ { <period> <asterisked identifier> }... ]

<asterisked identifier> ::= <identifier>

<derived column> ::= <value expression> [ <as clause> ]

<as clause> ::= [ AS ] <column name>

The qualifier DISTINCT or ALL apply to the results of the SELECT statement after all other operations have been performed. ALL simply returns the rows, while DISTINCT compares the rows and removes the duplicate ones.

Projection is performed by the <select list>.

A single <asterisk> means all columns of the <table expression> are included, in the same order as they appear in the <table expression>. An asterisk qualified by a table name means all the columns of the qualifier table name are included.

A derived column is a <value expression>, optionally named with the <as clause>. A <value expression> can be many things. Common types include: the name of a column in the <table expression>; an expression based on different columns or constant values; a function call; an aggregate function; a CASE WHEN expression.

A table expression is part of the SELECT statement and consists of the FROM clause with optional other clauses that performs selection (of rows) and grouping from the table(s) in the FROM clause.

<table expression> ::= <from clause> [ <where clause> ] [ <group by clause> ] [ <having clause> ]

<from clause> ::= FROM <table reference> [ { <comma> <table reference> }... ]

<table reference> ::= <table primary> | <joined table>

<table primary> ::= <table or query name> [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ]

| <derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ]

| <lateral derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ]

| <collection derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ]

| <table function derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ]

| <parenthesized joined table>

<where clause> ::= WHERE <boolean value expression>

<group by clause> ::= GROUP BY [ <set quantifier> ] <grouping element> [ { <comma> <grouping element> }... ]

<having clause> ::= HAVING <boolean value expression>

The <from clause> contains one or more <table reference> separated by commas. A table reference is often a table or view name or a joined table.

The <where clause> filters the rows of the table in the <from clause> and removes the rows for which the search condition is not TRUE.

The <group by clause> is a comma separated list of columns of the table in the <from clause> or expressions based on the columns.

When a <group by clause> is used, only the columns used in the <group by clause> or expressions used there, can be used in the <select list>, together with any <aggregate function> on other columns. A <group by clause> compares the rows and groups together the rows that have the same values in the columns of the <group by clause>. Then any <aggregate function> in the <select list> is performed on each group, and for each group, a row is formed that contains the values of the columns of the <group by clause> and the values returned from each <aggregate function>. In the first example below, a simple column reference is used in GROUP BY, while in the second example, an expression is used.

CREATE TABLE atable (a INT, b INT, c INT, d INT, e INT, f INT);
SELECT d, e, f FROM atable WHERE a + b = c GROUP BY d, e, f
SELECT d + e, SUM(f) FROM atable WHERE a + b = c GROUP BY d + e HAVING SUM(f) > 2 AND d + e > 4

A <having clause> filters the rows of the table that is formed after applying the <group by clause> using its search condition. The search condition must be an expression based on the expressions in the GROUP BY list or the aggregate functions used.

Table primary refers to different forms of table reference in the FROM clause.

The simplest form of reference is simply a name. This is the name of a table, a view, a transition table in a trigger definition, or a query name specified in the WITH clause of a query expression.

<table or query name> ::= <table name> | <transition table name> | <query name>

A query expression that is enclosed in parentheses and returns from zero to many rows is a <table subquery>. In a <derived table> the query expression is self contained and cannot reference the columns of other table references.

<derived table> ::= <table subquery>

When the word LITERAL is used before a <table subquery>, it means the query expression can reference the columns of other table references that precede it.

<lateral derived table> ::= LATERAL <table subquery>

UNNEST is similar to LATERAL, but instead of a query expression, and expression that returns an array is used. This expression is converted into a table which has one column that contains the elements of the array, and, if WITH ORDINALITY is used, a second column that contains the index of each element. The array expression usually contains a reference to a column of the table reference preceding the current table reference.

<collection derived table> ::= UNNEST <left paren> <array value expression> <right paren> [ WITH ORDINALITY ]

When TABLE is used in this context, it also converts an array value expression to a table, but this array must be the result of a function call. A function that returns a MULTISET can also be used in this context and each row of the multiset is expanded into a row of the table.

<table function derived table> ::= TABLE <left paren> <collection value expression> <right paren>

The column list that is specified for the table reference must contain names that are unique within the list

<derived column list> ::= <column name list>

<column name list> ::= <column name> [ { <comma> <column name> }... ]

A parenthesized joined table is simply a joined table contained in parentheses. Joined tables are discussed below.

<parenthesized joined table> ::= <left paren> <parenthesized joined table> <right paren> | <left paren> <joined table> <right paren>

Joins are operators with two table as the operands, resulting in a third table, called joined table. All join operators are evaluated left to right, therefore, with multiple joins, the table resulting from the first join operator becomes an operand of the next join operator. Parentheses can be used to group sequences of joined tables and change the evaluation order. So if more than two tables are joined together with join operators, the end result is also a joined table. There are different types of join, each producing the result table in a different way.

CROSS JOIN

The simplest form of join is CROSS JOIN. The CROSS JOIN of two tables is a table that has all the columns of the first table, followed by all the columns of the second table, in the original order. Each row of the first table is combined with each row of the second table to fill the rows of the new table. If the rows of each table form a set, then the rows of the CROSS JOIN table form the Cartesian product of the rows of the two table operands.

The CROSS JOIN can be expressed in two forms. The first form is A CROSS JOIN B. The second form is A, B. This type of join is not generally very useful, as it returns large result tables.

UNION JOIN

The UNION JOIN has limited use in queries. The result table has the same columns as that of CROSS JOIN. Each row of the first table is extended to the right with nulls and added to the new table. Each row of the second table is extended to the left with nulls and added to the new table. The UNION JOIN is expressed as A UNION JOIN B. This should not be confused with A UNION B, which is a set operation. Union join is for special applications and is not commonly used.

JOIN ... ON

The condition join is similar to CROSS JOIN, but a condition is tested for each row of the new table and the row is created only if the condition is true. This form of join is expressed as A JOIN B ON (<search condition>).

Equijoin is a condition join in which the search condition is an equality condition between on or more pairs of columns from the two table. Equijoin is the most commonly used type of join.

JOIN ... USING

NATURAL JOIN

Joins with USING or NATURAL keywords joins are similar to an equijoin but they cannot be replaced simply with an equijoin. The new table is formed with the specified or implied shared columns of the two tables, followed by the rest of the columns from each table. In NATURAL JOIN, the shared columns are all the column pairs that have the same name in the first and second table. In JOIN USING, only columns names that are specified by the USING clause are shared. The joins are expressed as A NATURAL JOIN B, and A JOIN B USING (<comma separated column name list>).

The columns of the joined table are formed by the following procedures: In JOIN ... USING the shared columns are added to the joined table in the same order as they appear in the column name list. In NATURAL JOIN the shared columns are added to the joined table in the same order as they appear in the first table. In bother forms of join, the non-shared columns of the first table are added in the order they appear in the first table, finally the non-shared columns of the second table are added in the order they appear in the second table.

The type of each shared column of the joined table is based on the type of the columns in the original tables. If the original types are not exactly the same, the type of the shared column is formed by type aggregation. Type aggregations selects a type that can represent values of both aggregated types. Simple type aggregation picks one of the types. For example SMALLINT and INTEGER, results in INTEGER, or VARCHAR(10) and VARCHAR(20) results in VARCHAR(20). More complex type aggregation inherits properties from both types. For example DECIMAL(8) and DECIMAL (6,2) results in DECIMAL (8,2).

OUTER JOIN

LEFT, RIGHT and FULL OUTER JOIN

The three qualifiers can be added to all types of JOIN apart from CROSS JOIN and UNION JOIN. First the new table is populated with the rows from the original join. If LEFT is specified, all the rows from the first table that did not make it into the new table are extended to the right with nulls and added to the table. If RIGHT is specified, all the rows from the second table that did not make it into the new table are extended to the left with nulls and added to the table. If FULL is specified, the addition of leftover rows is performed from both the first and the second table. These forms are expressed by prefixing the join specification with the given keyword. For example A LEFT OUTER JOIN B ON (<search condition>) or A NATURAL FULL OUTER JOIN B or A FULL OUTER JOIN B USING (<comma separated column name list>).

Despite the name, selection has nothing to do with the list of columns in a SELECT statement. In fact, it refers to the search condition used to limit the rows that from a result table (selection of rows, not columns). In SQL, simple selection is expressed with a WHERE condition appended to a single table or a joined table. In some cases, this method of selection is the only method available. But when it is possible to perform the selection with join conditions, this is the better method, as it results in a clearer expression of the query.

Projection is selection of the columns from a simple or joined table to form a result table. Explicit projection is performed in the SELECT statement by specifying the select column list. Some form of projection is also performed in JOIN ... USING and NATURAL JOIN.

The joined table has columns that are formed according to the rules mentioned above. But in many cases, not all the columns are necessary for the intended operation. If the statement is in the form, SELECT * FROM <joined table>, then all the columns of <joined table> are returned. But normally, the columns to be returned are specified after the SELECT keyword, separated from each other with commas.

In the select list, it is possible to use expressions that reference any columns of <joined table>. Each of these expressions forms a computed column. It is computed for each row of the result table, using the values of the columns of the <joined table> for that row.

Naming is used to hide the original names of tables or table columns and to replace them with new names in the scope of the query. Naming is also used for defining names for computed columns.

Naming in Joined Table

Naming is performed by adding a new name after a table's real name and by adding a list of column names after the new table name. Both table naming and column naming are optional, but table naming is required for column naming. The expression A [AS] X (<comma separated column name list>) means table A is used in the query expression as table X and its columns are named as in the given list. The original name A, or its original column names, are not visible in the scope of the query. The BNF is given below. The <correlation name> can be the same or different from the name of the table. The <derived column list> is a comma separated list of column names. The degree of this list must be equal to the degree of the table. The column names in the list must be distinct. They can be the same or different from the names of the table's columns.

<table or query name> [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ]

In the examples below, the columns of the original tables are named (a, b, c, d, e, f). The two queries are equivalent. In the second query, the table and its columns are renamed and the new names are used in the WHERE clauses:

CREATE TABLE atable (a INT, b INT, c INT, d INT, e INT, f INT);
SELECT d, e, f FROM atable WHERE a + b = c
SELECT x, y, z FROM atable AS t (u, v, w, x, y, z)  WHERE u + v = w

Naming in Select List

Naming in the SELECT list logically takes place after naming in the joined table. The new names for columns are not visible in the immediate query expression or query expression. They become visible in the ORDER BY clause and in the result table that is returned to the user. Or if the query expression is used as a derived table in an enclosing query expression.

In the example below, the query is on the same table but with column renaming in the Select list. The new names are used in the ORDER BY clause:

SELECT x + y AS xysum, y + z AS yzsum FROM atable AS t (u, v, w, x, y, z)  WHERE u + v = w ORDER BY xysum, yzsum

If the names xysum or yzsum are not used, the computed columns cannot be referenced in the ORDER BY list.

Name Resolution

In a joined table, if a column name appears in tables on both sides then any reference to the name must use the table name in order to specify which table is being referred to.

Grouping Operations

Grouping results in the elimination of duplicate rows. A grouping operation is performed after the operations discussed above. A simple form of grouping is performed by the use of DISTINCT after SELECT. This eliminates all the duplicate rows (rows that have the same value in each of their columns when compared to another row). The other form of grouping is performed with the GROUP BY clause. This form is usually used together with aggregation.

Aggregation is an operation that computes a single value from the values of a column over several rows. The operation is performed with an aggregate function. The simplest form of aggregation is counting, performed by the COUNT function.

Other common aggregate functions return the maximum, minimum and average value among the values in different rows.

Set and Multiset Operations

While join operations generally result in laterally expanded tables, SET and COLLECTION operations are performed on two tables that have the same degree and result in a table of the same degree. The SET operations are UNION, INTERSECT and EXCEPT (difference). When each of these operations is performed on two tables, the collection of rows in each table and in the result is reduced to a set of rows, by eliminating duplicates. The set operations are then performed on the two tables, resulting in the new table which itself is a set of rows. Collection operations are similar but the tables are not reduced to sets before or after the operation and the result is not necessarily a set, but a collection of rows.

The set operations on two tables A and B are: A UNION [DISTINCT] B, A INTERSECT [DISTINCT] B and A EXCEPT [DISTINCT] B. The result table is formed in the following way: The UNION operation adds all the rows from A and B into the new table, but avoids copying duplicate rows. The INTERSECT operation copies only those rows from each table that also exist in the other table, but avoids copying duplicate rows. The EXCEPT operation copies those rows from the first table which do not exist in the second table, but avoids copying duplicate rows.

The collection operations are similar to the set operations, but can return duplicate rows. They are A UNION ALL B, A INTERSECT ALL B and A EXCEPT ALL B. The UNION ALL operation adds all the rows from A and B into the new table. The INTERSECT operation copies only those rows from each table that also exist in the other table. If n copies of a rows exists in one table, and m copies in the other table, the number of copies in the result table is the smaller of n and m. The EXCEPT operation copies those rows from the first table which do not exist in the second table. If n copies of a row exist in the first table and m copies in the second table the number of copies in the result table is n-m, or if n < m, then zero.

A query expression consists of an optional WITH clause and a query expression body. The WITH clause lists one or more named ephemeral tables that can be referenced in the query expression body.

<query expression> ::= [ <with clause> ] <query expression body>

<with clause> ::= WITH <with list>

<with list> ::= <with list element> [ { <comma> <with list element> }... ]

<with list element> ::= <query name> [ <left paren> <with column list> <right paren> ] AS <left paren> <query expression> <right paren>

<with column list> ::= <column name list>

A query expression body refers to a table formed by using UNION and other set operations. The query expression body is evaluated from left to right and the INTERSECT operator has precedence over the UNION and EXCEPT operators. A simplified BNF is given below:

<query expression body> ::= <query term> | <query expression body> UNION | EXCEPT [ ALL | DISTINCT ] [ <corresponding spec> ] <query term>

<query term> ::= <query primary> | <query term> INTERSECT [ ALL | DISTINCT ] [ <corresponding spec> ] <query term>

<query primary> ::= <simple table> | <left paren> <query expression body> [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] <right paren>

<simple table> ::= <query specification> | <table value constructor> | <explicit table> <explicit table> ::= TABLE <table or query name>

<corresponding spec> ::= CORRESPONDING [ BY <left paren> <column name list> <right paren> ]

A <query term> and a <query primary> can be a SELECT statement, an <explicit table>, or a <table value constructor>.

The CORRESPONDING clause is optional. If it is not specified, then the <query term> and the <query primary> must have the same number of columns. If CORRESPONDING is specified, the two sides need not have the same number of columns. If no column list is used with CORRESPONDING, then all the column names that are common in the tables on two sides are used in the order in which they appear in the first table. If a columns list is used, it allows you to select only some columns of the tables on the left and right side to create the new table. In the example below the columns named u and v from the two SELECT statements are used to create the UNION table.

SELECT * FROM atable UNION CORRESPONDING BY (u, v) SELECT * FROM anothertable

When the rows of the result table have been formed, it is possible to specify the order in which they are returned to the user. The ORDER BY clause is used to specify the columns used for ordering, and whether ascending or descending ordering is used. It can also specify whether NULL values are returned first or last.

SELECT x + y AS xysum, y + z AS yzsum FROM atable AS t (u, v, w, x, y, z)  WHERE u + v = w ORDER BY xysum NULLS LAST, yzsum NULLS FIRST

The ORDER BY clause specifies one or more <value expressions>. The list of rows is sorted according to the first <value expression>. When some rows are sorted equal then they are sorted according to the next <value expression> and so on.

<order by clause> ::= ORDER BY <sort specification> [ { <comma> <sort specification> }... ]

<sort specification> ::= <value expression> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

A different form of limiting the rows can be performed on the result table after it has been formed according to all the other operations (selection, grouping, ordering etc.). This is specified by the FETCH ... ROWS and OFFSET clauses of a SELECT statement. In this form, the specified OFFSET rows are removed from start of the table, then up to the specified FETCH rows are kept and the rest of the rows are discarded.

<result offset clause> ::= OFFSET <offset row count> { ROW | ROWS }

<fetch first clause> ::= FETCH { FIRST | NEXT } [ <fetch first row count> ] { ROW | ROWS } ONLY

<limit clause> ::= LIMIT [ <fetch first row count> ]

A slicing operation takes the result set that has been already processed and ordered. It then discards the specified number of rows from the start of the result set and returns the specified number of rows after the discarded rows.

SELECT a, b FROM atable WHERE d < 5 ORDER BY absum OFFSET 3 FETCH 2 ROWS ONLY 
SELECT a, b FROM atable WHERE d < 5 ORDER BY absum OFFSET 3 LIMIT 2 /* alternative keyword */ 

DELETE FROM

delete statement: searched

<delete statement: searched> ::= DELETE FROM <target table> [ [ AS ] <correlation name> ] [ WHERE <search condition> ]

Delete rows of a table. The search condition is a <boolean value expression> that is evaluated for each row of the table. If the condition is true, the row is deleted. If the condition is not specified, all the rows of the table are deleted. In fact, an implicit SELECT is performed in the form of SELECT * FROM <target table> [ WHERE <search condition>] and the selected rows are deleted. When used in JDBC, the number of rows returned by the implicit SELECT is returned as the update count.

If there are FOREIGN KEY constraints on other tables that reference the subject table, and the FOREIGN KEY constraints have referential actions, then rows from those other tables that reference the deleted rows are either deleted, or updated, according to the specified referential actions.

In the second example below the rows that have the maximum value for column A are deleted;

DELETE FROM T WHERE C > 5
DELETE FROM T AS TT WHERE TT.A = (SELECT MAX(A) FROM T)

TRUNCATE TABLE

truncate table statement

<truncate table statement> ::= TRUNCATE TABLE <target table> [ <identity column restart option> ]

<identity column restart option> ::= CONTINUE IDENTITY | RESTART IDENTITY

Delete all rows of a table without firing its triggers. This statement can only be used on base tables (not views). If the table is referenced in a FOREIGN KEY constraint, the statement causes an exception. Triggers defined on the table are not executed with this statement. The default for <identity column restart option> is CONTINUE IDENTITY. This means no change to the IDENTITY sequence of the table. If RESTART IDENTITY is specified, then the sequence is reset to its start value.

TRUNCATE is faster than ordinary DELETE. The TRUNCATE statement is an SQL Standard data change statement, therefore it is performed under transaction control and can be rolled back if the connection is not in the auto-commit mode.

INSERT INTO

insert statement

<insert statement> ::= INSERT INTO <target table> <insert columns and source>

<insert columns and source> ::= <from subquery> | <from constructor> | <from default>

<from subquery> ::= [ <left paren> <insert column list> <right paren> ] [ <override clause> ] <query expression>

<from constructor> ::= [ <left paren> <insert column list> <right paren> ] [ <override clause> ] <contextually typed table value constructor>

<override clause> ::= OVERRIDING USER VALUE | OVERRIDING SYSTEM VALUE

<from default> ::= DEFAULT VALUES

<insert column list> ::= <column name list>

Insert new rows in a table. An INSERT statement inserts one or more rows into the table.

The special form, INSERT INTO <target table> DEFAULT VALUES can be used with tables which have a default value for each column.

With the other forms of INSERT, the optional (<insert column list>) specifies to which columns of the table the new values are assigned.

In one form, the inserted values are from a <query expression> and all the rows that are returned by the <query expression> are inserted into the table. If the <query expression> returns no rows, nothing is inserted.

In the other form, a comma separated list of values called <contextually typed table value constructor> is used to insert one or more rows into the table. This list is contextually typed, because the keywords NULL and DEFAULT can be used for the values that are assigned to each column of the table. The keyword DEFAULT means the default value of the column and can be used only if the target column has a default value or is an IDENTITY or GENERATED column of the table.

The <override clause> must be used when a value is explicitly assigned to a column that has been defined as GENERATED ALWAYS AS IDENTITY. The clause, OVERRIDE SYSTEM VALUE means the provided values are used for the insert, while OVERRIDING USER VALUE means the provided values are simply ignored and the values generated by the system are used instead.

An array can be inserted into a column of the array type by using literals, by specifying a parameter in a prepared statement or an existing array returned by query expression. The last example below inserts an array.

The rows that are inserted into the table are checked against all the constraints that have been declared on the table. The whole INSERT operation fails if any row fails to inserted due to constraint violation. Examples:

INSERT INTO T DEFAULT VALUES /* all columns of T have DEFAULT clauses */
INSERT INTO T (SELECT * FROM Z) /* table Z has the same columns as table T */
INSERT INTO T (A,B) VALUES ((1,2),(3,NULL), (DEFAULT,6)) /* three rows are inserted into table T */
INSERT INTO T VALUES 3, ARRAY['hot','cold']

UPDATE

update statement: searched

<update statement: searched> ::= UPDATE <target table> [ [ AS ] <correlation name> ] SET <set clause list> [ WHERE <search condition> ]

Update rows of a table. An UPDATE statement selects rows from the <target table> using an implicit SELECT statement formed in the following manner:

SELECT * FROM <target table> [ [ AS ] <correlation name> ] [ WHERE <search condition> ]

Then it applies the SET <set clause list> expression to each selected row.

If the implicit SELECT returns no rows, no update takes place. When used in JDBC, the number of rows returned by the implicit SELECT is returned as the update count.

If there are FOREIGN KEY constraints on other tables that reference the subject table, and the FOREIGN KEY constraints have referential actions, then rows from those other tables that reference the updated rows are updated, according to the specified referential actions.

The rows that are updated are checked against all the constraints that have been declared on the table. The whole UPDATE operation fails if any row violates any constraint.

set clause list

set clause list

<set clause list> ::= <set clause> [ { <comma> <set clause> }... ]

<set clause> ::= <multiple column assignment> | <set target> <equals operator> <update source>

<multiple column assignment> ::= <set target list> <equals operator> <assigned row>

<set target list> ::= <left paren> <set target> [ { <comma> <set target> }... ] <right paren>

<assigned row> ::= <contextually typed row value expression>

<set target> ::= <column name>

<update source> ::= <value expression> | <contextually typed value specification>

Specify a list of assignments. This is used in UPDATE, MERGE and SET statements to assign values to a scalar or row target.

Apart from setting a whole target to a value, a SET statement can set individual elements of an array to new values. The last example below shows this form of assignment to the array in the column named B.

In the examples given below, UPDATE statements with single and multiple assignments are shown. Note in the third example, a SELECT statement is used to provide the update values for columns A and C, while the update value for column B is given separately. The SELECT statement must return exactly one row . In this example the SELECT statement refers to the existing value for column C in its search condition.

UPDATE T SET A = 5 WHERE ...
UPDATE T SET (A, B) = (1, NULL) WHERE ...
UPDATE T SET (A, C) = (SELECT X, Y FROM U WHERE Z = C), B = 10 WHERE ...
UPDATE T SET A = 3, B[3] = 'warm'

MERGE INTO

merge statement

<merge statement> ::= MERGE INTO <target table> [ [ AS ] <merge correlation name> ] USING <table reference> ON <search condition> <merge operation specification>

<merge correlation name> ::= <correlation name>

<merge operation specification> ::= <merge when clause>...

<merge when clause> ::= <merge when matched clause> | <merge when not matched clause>

<merge when matched clause> ::= WHEN MATCHED THEN <merge update specification>

<merge when not matched clause> ::= WHEN NOT MATCHED THEN <merge insert specification>

<merge update specification> ::= UPDATE SET <set clause list>

<merge insert specification> ::= INSERT [ <left paren> <insert column list> <right paren> ] [ <override clause> ] VALUES <merge insert value list>

<merge insert value list> ::= <left paren> <merge insert value element> [ { <comma> <merge insert value element> }... ] <right paren>

<merge insert value element> ::= <value expression> | <contextually typed value specification>

Update rows, or insert new rows into the <target table>. The MERGE statement uses a second table, specified by <table reference>, to determine the rows to be updated or inserted. It is possible to use the statement only to update rows or to insert rows, but usually both update and insert are specified.

The <search condition> matches each row of the <table reference> with each row of the <target table>. If the two rows match then the UPDATE clause is used to update the matching row of the target table. Those rows of <table reference> that have no matching rows are then used to insert new rows into the <target table>. Therefore, a MERGE statement can update between 0 and all the rows of the <target table> and can insert between 0 and the number of the rows in <table reference> into the <target table>. If any row in the <target table> matches more than one row in <table reference> a cardinality error is raised. On the other hand, several rows in the <target table> can matches a single row in <table reference> without any error. The constraints and referential actions specified on the database tables are enforced the same way as for an update and an insert statement.

The MERGE statement can be used with only the WHEN NOT MATCHED clause as a conditional INSERT statement that inserts a row if no existing rows match a condition.

In the first example below, the table originally contains two rows for different furniture. The <table reference> is the (VALUES(1, 'conference table'), (14, 'sofa'), (5, 'coffee table')) expression, which evaluates to a table with 3 rows. When the x value for a row matches an existing row, then the existing row is updated. When the x value does not match, the row is inserted. Therefore one row of table t is updated from 'dining table' to 'conference table', and two rows are inserted into table t. The second example uses a SELECT statement as the source of the values for the MERGE.

In the third example, a new row in inserted into the table only when the primary key for the new row does not exist. This example uses parameters and should be executed as a JDBC PreparedStatement.

CREATE TABLE t (id INT PRIMARY KEY, description VARCHAR(100))
INSERT INTO t VALUES (1, 'dining table'), (2, 'deck chair')
MERGE INTO t USING (VALUES(1, 'conference table'), (14, 'sofa'), (5, 'coffee table')) 
   AS vals(x,y) ON t.id = vals.x
   WHEN MATCHED THEN UPDATE SET t.description = vals.y
   WHEN NOT MATCHED THEN INSERT VALUES vals.x, vals.y

MERGE INTO t USING (SELECT * FROM tt WHERE acol = 2) AS vals(x,y) ON t.id = vals.x
   WHEN MATCHED THEN UPDATE SET t.description = vals.y
   WHEN NOT MATCHED THEN INSERT VALUES vals.x, vals.y

MERGE INTO t USING (VALUES(CAST(? AS INT))) AS vals(x) ON t.id = vals.x
   WHEN NOT MATCHED THEN INSERT VALUES vals.x, ?

The following SQL Statements can be used only in routines.

<handler declaration>

<assignment statement>

<compound statement>

<case statement>

<if statement>

<while statement>

<repeat statement>

<for statement>

<loop statement>

<iterate statement

<leave statement>

<signal statement>

<resignal statement>

<return statement>

<select statement: single row>

The following SQL Statements can be used in procedures but not in functions.

<call statement>

<delete statement>

<insert statement>

<update statement>

<merge statement>

As shown in the examples below, the formal parameters and the variables of the routine can be used in statements, similar to the way a column reference is used.

A compound statement is enclosed in a BEGIN / END block with optional labels. It can contain one or more <SQL variable declaration> or <handler declaration> before at least one SQL statement. The BNF is given below:

<compound statement> ::= [ <beginning label> <colon> ] BEGIN [[NOT] ATOMIC] [{<SQL variable declaration> <semicolon>} ...] [{<handler declaration> <semicolon>}...] {<SQL procedure statement> <semicolon>} ... END [ <ending label> ]

An example of a simple compound statement body is given below. It performs the common task of inserting related data into two table. The IDENTITY value that is automatically inserted in the first table is retrieved using the IDENTITY() function and inserted into the second table.

CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname  VARCHAR(50), address VARCHAR(100))
  MODIFIES SQL DATA
    BEGIN ATOMIC
    INSERT INTO customers VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
    INSERT INTO addresses VALUES (DEFAULT, IDENTITY(), address);
  END

A <variable declaration> defines the name and data type of the variable and, optionally, its default value. In the next example, a variable is used to hold the IDENTITY value. In addition, the formal parameters of the procedure are identified as input parameters with the use of the optional IN keyword. This procedure does exactly the same job as the procedure in the previous example.

CREATE PROCEDURE new_customer(IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100))
  MODIFIES SQL DATA
  BEGIN ATOMIC
    DECLARE temp_id INTEGER;
    INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
    SET temp_id = IDENTITY();
    INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address);
  END

The BNF for variable declaration is given below:

<SQL variable declaration> ::= DECLARE <variable name list> <data type> [DEFAULT <default value>]

<variable name list> ::= <variable name> [ { <comma> <variable name> }... ]

Examples of variable declaration are given below. Note that in a DECLARE statement with multiple comma-separated variable names, the type and the default value applies to all the variables in the list:

  BEGIN ATOMIC
    DECLARE temp_zero DATE;
    DECLARE temp_one, temp_two INTEGER DEFAULT 2;
    DECLARE temp_three VARCHAR(20) DEFAULT 'no name';
    -- more statements ...
    SET temp_zero = DATE '2010-03-18';
    SET temp_two = 5;
    -- more statements ...
  END

A <handler declaration> defines the course of action when an exception or warning is raised during the execution of the compound statement. A compound statement may have one or more handler declarations. These handlers become active when code execution enters the compound statement block and remain active in any sub-block and statement within the block. The handlers become inactive when code execution leaves the block.

In the previous example, if an exception is thrown during the execution of either SQL statement, the execution of the compound statement is terminated and the exception is propagated and thrown by the CALL statement for the procedure. A handler declaration can resolve the thrown exception within the compound statement without propagating it, and allow the execution of the <compound statement> to continue.

In the example below, the UNDO handler declaration catches any exception that is thrown during the execution of the compound statement inside the BEGIN / END block. As it is an UNDO handler, all the changes to data performed within the compound statement (BEGIN / END) block are rolled back. The procedure then returns without throwing an exception.

CREATE PROCEDURE NEW_CUSTOMER(IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100))
    MODIFIES SQL DATA
  label_one: BEGIN ATOMIC
    DECLARE temp_id INTEGER;
    DECLARE UNDO HANDLER FOR SQLEXCEPTION LEAVE label_one;
    INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
    SET temp_id = IDENTITY();
    INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address);
  END

Other types of hander are CONTINUE and EXIT handlers. A CONTINUE handler ignores any exception and proceeds to the next statement in the block. An EXIT handler terminates execution without undoing the data changes performed by the previous (successful) statements.

The conditions can be general conditions, or specific conditions. Among general conditions that can be specified, SQLEXCEPTION covers all exceptions, SQLWARNING covers all warnings, while NOT FOUND covers the not-found condition, which is raised when a DELETE, UPDATE, INSERT or MERGE statement completes without actually affecting any row. Alternatively, one or more specific conditions can be specified (separated with commas) which apply to specific exceptions or warnings or classes or exceptions or warnings. A specific condition is specified with SQLSTATE <value>, for example SQLSTATE 'W_01003' specifies the warning raised after a SQL statement is executed which contains an aggregate function which encounters a null value during execution. An example is given below which activates the handler when either of the two warnings is raised:

DECLARE UNDO HANDLER FOR SQLSTATE 'W_01003', 'W_01004' LEAVE label_one;

The BNF for <handler declaration> is given below:

<handler declaration> ::= DECLARE {UNDO | CONTINUE | EXIT} HANDLER FOR {SQLEXCEPTION | SQLWARNING | NOT FOUND} | { SQL_STATE <state value> [, ...]} [<SQL procedure statement>];

A handler declaration may specify an SQL procedure statement to be performed when the handler is activated. When an exception occurs, the example below performs the UNDO as in the previous example, then inserts the (invalid) data into a separate table.

DECLARE UNDO HANDLER FOR SQLEXCEPTION
    INSERT INTO invalid_customers VALUES(firstanme, lastname, address);

The <SQL procedure statement> is required by the SQL Standard but is optional in HyperSQL. If the execution of the <SQL procedure statement> specified in the handler declaration throws an exception itself, then it is handled by the handlers that are currently active. The <SQL procedure statement> can itself be a compound statement with its own handlers.

The SET statement is used for assignment. It can be used flexibly with rows or single values. The BNF is given below:

<assignment statement> ::= <singleton variable assignment> | <multiple variable assignment>

<singleton variable assignment> ::= SET <assignment target> <equals operator> <assignment source>

<multiple variable assignment> ::= SET (<variable or parameter>, ...) = <row value expression>

In the example below, the result of the SELECT is assigned to two OUT or INOUT arguments. The SELECT must return one row. If it returns more than one, an exception is raised. If it returns no row, no change is made to ARG1 and ARG2.

SET (arg1, arg2) = (SELECT col1, col2 FROM atable WHERE id = 10);

In the example below, the result of a function call is assigned to VAR1.

SET var1 = SQRT(var2);

A special form of SELECT can also be used for assigning values from a query to one or more arguments or variables. This works similar to a SET statement that has a SELECT statement as the source.

SELECT : SINGLE ROW

select statement: single row

<select statement: single row> ::= SELECT [ <set quantifier> ] <select list> INTO <select target list> <table expression>

<select target list> ::= <target specification> [ { <comma> <target specification> }... ]

Retrieve values from a specified row of a table and assign the fields to the specified targets. The example below has an identical effect to the example of SET statement given above.

SELECT col1, col2 INTO arg1, arg2 FROM atable WHERE id = 10;

Each parameter of a procedure can be defined as IN, OUT or INOUT. An IN parameter is an input to the procedure and is passed by value. The value cannot be modified inside the procedure body. An OUT parameter is a reference for output. An INOUT parameter is a reference for both input and output. An OUT or INOUT parameter argument is passed by reference, therefore only a dynamic parameter argument or a variable within an enclosing procedure can be passed for it. The assignment statement is used to assign a value to an OUT or INOUT parameter.

In the example below, the procedure is declared with an OUT parameter.

CREATE PROCEDURE new_customer(OUT newid INT, IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100))
  MODIFIES SQL DATA
  BEGIN ATOMIC
    DECLARE temp_id INTEGER;
    INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
    SET temp_id = IDENTITY();
    INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address);
    SET newid = temp_id;
  END

In the SQL session, or in the body of another stored procedure, a variable must be assigned to the OUT parameter. After the procedure call, this variable will hold the new identity value that was generated inside the procedure.

In the example below, a session variable, the_new_id is declared. After the call to new_customer, the value for the identity is stored in the_new_id variable. This is returned via the next CALL statement. Alternatively, the_new_id can be used as an argument to another CALL statement.

DECLARE the_new_id INT DEFAULT NULL;
CALL new_customer(the_new_id, 'John', 'Smith', '10 Parliament Square'); 
CALL the_new_id;

Various iterated statements can be used in routines. In these statements, the <SQL statement list> consists of one or more SQL statements. The <search condition> can be any valid SQL expression of BOOLEAN type.

<loop statement> ::= [ <beginning label> <colon> ] LOOP <SQL statement list> END LOOP [ <ending label> ]

<while statement> ::= [ <beginning label> <colon> ] WHILE <search condition> DO <SQL statement list> END WHILE [ <ending label> ]

<repeat statement> ::= [ <beginning label> <colon> ]

REPEAT <SQL statement list> UNTIL <search condition> END REPEAT [ <ending label>

In the example below, a multiple rows are inserted into a table in a WHILE loop:

loop_label: WHILE my_var > 0 DO
  INSERT INTO CUSTOMERS VALUES (DEFAULT, my_var);
  SET my_var = my_var - 1;
  IF my_var = 10 THEN SET my_var = 8; END IF;
  IF my_var = 22 THEN LEAVE loop_label; END IF;
END WHILE loop_label;

There are two types of CASE ... WHEN statement and the IF ... THEN statement.

CASE WHEN

case when statement

The simple case statement uses a <case operand> as the predicand of one or more predicates. For the right part of each predicate, it specifies one or more SQL statements to execute if the predicate evaluates TRUE. If the ELSE clause is not specified, at least one of the search conditions must be true, otherwise an exception is raised.

<simple case statement> ::= CASE <case operand> <simple case statement when clause>... [ <case statement else clause> ] END CASE

<simple case statement when clause> ::= WHEN <when operand list> THEN <SQL statement list>

<case statement else clause> ::= ELSE <SQL statement list>

A skeletal example is given below. The variable var_one is first tested for equality with 22 or 23 and if the test evaluates to TRUE, then the INSERT statement is performed and the statement ends. If the test does not evaluate to TRUE, the next condition test, which is an IN predicate, is performed with var_one and so on. The statement after the ELSE clause is performed if none the previous tests returns TRUE.

CASE var_one
  WHEN 22, 23 THEN INSERT INTO t_one ...;
  WHEN IN (2, 4, 5) THEN DELETE FROM t_one WHERE ...;
  ELSE UPDATE t_one ...;
  END CASE

The searched case statement uses one or more search conditions, and for each search condition, it specifies one or more SQL statements to execute if the search condition evaluates TRUE. An exception is raised if there is no ELSE clause and none of the search conditions evaluates TRUE.

<searched case statement> ::= CASE <searched case statement when clause>... [ <case statement else clause> ] END CASE

<searched case statement when clause> ::= WHEN <search condition> THEN <SQL statement list>

The example below is partly a rewrite of the previous example, but a new condition is added:

CASE WHEN var_one = 22 OR var_one = 23 THEN INSERT INTO t_one ...;
  WHEN var_one IN (2, 4, 5) THEN DELETE FROM t_one WHERE ...;
  WHEN var_two IS NULL THEN UPDATE t_one ...;
  ELSE UPDATE t_one ...;
  END CASE

IF

if statement

The if statement is very similar to the searched case statement. The difference is that no exception is raised if there is no ELSE clause and no search condition evaluates TRUE.

<if statement> ::= IF <search condition> <if statement then clause> [ <if statement elseif clause>... ] [ <if statement else clause> ] END IF

<if statement then clause> ::= THEN <SQL statement list>

<if statement elseif clause> ::= ELSEIF <search condition> THEN <SQL statement list>

<if statement else clause> ::= ELSE <SQL statement list>

The RETURN statement is required and used only in functions. The body of a function is either a RETURN statement, or a compound statement that contains a RETURN statement.

RETURN

return statement

<return statement> ::= RETURN <return value>

<return value> ::= <value expression> | NULL

Return a value from an SQL function. If the function is defined as RETURNS TABLE, then the value is a TABLE expression such as RETURN TABLE(SELECT ...) otherwise, the value expression can be any scalar expression. In the examples below, the same function is written with or without a BEGIN END block. In both versions, the RETURN value is a scalar expression.

CREATE FUNCTION an_hour_before_max (e_type INT)
  RETURNS TIMESTAMP
  RETURN (SELECT MAX(event_time) FROM atable WHERE event_type = e_type) - 1 HOUR

CREATE FUNCTION an_hour_before_max (e_type INT)
  RETURNS TIMESTAMP
  BEGIN ATOMIC
    DECLAR max_event TIMESTAMP;
    SET max_event = SELECT MAX(event_time) FROM atable WHERE event_type = e_type;
    RETURN max_event - 1 HOUR;
  END

In addition to the RETURN statement, the following statements can be used in specific contexts.

ITERATE STATEMENT

The ITERATE statement can be used to cause the next iteration of a labeled iterated statement (a WHILE, REPEAT or LOOP statement). It is similar to the "continue" statement in C and Java.

<iterate statement> ::= ITERATE <statement label>

LEAVE STATEMENT

The LEAVE statement can be used to leave a labeled block. When used in an iterated statement, it is similar to the "break" statement is C and Java. But it can be used in compound statements as well.

<leave statement> ::= LEAVE <statement label>

Signal and Resignal Statements

The SIGNAL statement is used to throw an exception (or force an exception). When invoked, any exception handler for the given exception is in turn invoked. If there is no handler, the exception is propagated to the enclosing context.

<signal statement> ::= SIGNAL SQL_STATE <state value>

The RESIGNAL statement is used to throw an exception from an exception handler’s <SQL procedure statement>, in effect propagating the exception to the enclosing context without further action by the currently active handlers.

<resignal statement> ::= RESIGNAL SQL_STATE <state value>

More than one version of a routine can be created.

For procedures, the different versions must have different parameter counts.  When the procedure is called, the parameter count determines which version is called.

For functions, the different versions can have the same or different parameter counts. When the parameter count of two versions of a function is the same, the type of parameters must be different. The best matching version of the function is called, according to both the parameter count and parameter types.

Two versions of an overloaded function are given below. One version accepts TIMESTAMP while the other accepts TIME arguments.

CREATE FUNCTION an_hour_before_or_now(t TIMESTAMP)
  RETURNS TIMESTAMP
  IF t > CURRENT_TIMESTAMP THEN
    RETURN CURRENT_TIMESTAMP;
  ELSE
    RETURN t - 1 HOUR;
  END IF

CREATE FUNCTION an_hour_before_or_now(t TIME)
  RETURNS TIME
  CASE t
    WHEN > CURRENT_TIME THEN
      RETURN CURRENT_TIME;
    WHEN >= TIME'01:00:00' THEN
      RETURN t - 1 HOUR;
    ELSE
      RETURN CURRENT_TIME;
  END CASE

more ..

The OUT or INOUT parameters of a procedure are used to assign simple values to dynamic parameters or to variables in the calling context. In addition, a SQL/PSM procedure may return result sets to the calling context. These result sets are dynamic in the sense that a procedure may return a different number of result sets or none at all in different invocations.

The SQL Standard uses a mechanism called CURSORS for accessing and modifying rows of a result set one by one. This mechanism is absolutely necessary when the database is accessed from an external application program. The JDBC ResultSet interface allows this method of access from Java programs and is supported by HyperSQL.

The SQL Standard uses cursors within the body of a procedure to return result sets. It specifies a somewhat complex mechanism to allow access to these cursors from the calling contexts. HyperSQL does not support access to such result sets within a calling SQL/PSM procedure. This is considered redundant as all operations on data can be performed with non-cursor SQL statements.

(feature to be implemented) HyperSQL will support returning single or multiple result sets from SQL/PSM procedures only via the JDBC CallableStatement interface. Cursors are declared and opened within the body of the procedure. No further operation is performed on the cursors within the procedure. When the execution of the procedure is complete, the cursors become available as Java ResultSet objects via the CallableStatement instance that called the SQL/PSM procedure.

Currently, a single result can be returned from FUNCTION routines, when the function is defined as RETURNS TABLE ( .. )

To return a table from a SELECT statement, you should use a return statement such as RETURN TABLE( SELECT ...); in a SQL/PSM function. A Java function should return a JDBCResultSet instance. For an example of how to construct a JDBCResultSet for this purpose, see the source code for the org.hsqldb.jdbc.JDBCArray class.

The JDBC CallableStatement class is used with the SQL statement CALL <routine name> ( <argument 1>, ... ) to call both functions and procedures. The getXXX() methods can be used to retrieve INOUT or OUT arguments after the call. The getResultSet() call can be used to access the ResultSet returned from a function that returns a result set.

If two versions of the same SQL invoked routine with different parameter types are required, they can be defined to point to the same method name or different method names, or even methods in different classes. In the example below, the first two definitions refer to the same method name in the same class. In the Java class, the two static methods are defined with corresponding method signatures.

In the third example, the Java function returns a result set and the SQL declaration includes RETURNS TABLE.

CREATE FUNCTION an_hour_before_or_now(t TIME)
  RETURNS TIME
  NO SQL
  LANGUAGE JAVA PARAMETER STYLE JAVA
  EXTERNAL NAME 'CLASSPATH:org.npo.lib.nowLessAnHour'

CREATE FUNCTION an_hour_before_or_now(t TIMESTAMP)
  RETURNS TIMESTAMP
  NO SQL
  LANGUAGE JAVA PARAMETER STYLE JAVA
  EXTERNAL NAME 'CLASSPATH:org.npo.lib.nowLessAnHour'

CREATE FUNCTION testquery(INTEGER) 
  RETURNS TABLE(n VARCHAR(20), i INT) 
  READS SQL DATA
  LANGUAGE JAVA
  EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.TestJavaFunctions.getQueryResult'

In the Java class:

    public static java.sql.Time nowLessAnHour(java.sql.Time value) {
        ...
    }
    public static java.sql.Timestamp nowLessAnHour(java.sql.Timestamp value)
        ...
    }

    public static ResultSet getQueryResult(Connection connection, int i) throws SQLException {
        Statement st = connection.createStatement();
        return st.executeQuery("SELECT * FROM T WHERE I < " + i);
    }

Java procedures are defined similarly to functions. The differences are:

An example of a procedure definition is given below:

CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname  VARCHAR(50), address VARCHAR(100))
  MODIFIES SQL DATA
  LANGUAGE JAVA
  EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.Test01.newCustomerProcedure'

The legacy HyperSQL statement, CREATE ALIAS <name> FOR <fully qualified Java method name> is no longer supported directly. It is supported when importing databases and translates to a special CREATE FUNCTION <name> statement that creates the function in the PUBLIC schema.

The direct use of a Java method as a function is still supported but deprecated. It is internally translated to a special CREATE FUNCTION statement where the name of the function is the double quoted, fully qualified name of the Java method used.

An aggregate function is always defined with 4 parameters. The first parameter is the parameter that is used when the function is invoked in SQL statements, the rest of the parameter are invisible to the invoking statement. The type of the first parameter is user defined. The type of the second parameter must be BOOLEAN. The third and fourth parameters have user defined types and must be defined as INOUT parameters. The defined return type of the function determines the type of the value returned when the function is invoked.

CREATE AGGREGATE FUNCTION

user defined aggregate function definition

Aggregate function definition is similar to normal function definition and has the mandatory <returns clause>. The BNF is given below.

<user defined aggregate function> ::= CREATE AGGREGATE FUNCTION <schema qualified routine name> <SQL aggregate parameter declaration list> <returns clause> <routine characteristics> <routine body>

The parameter declaration list BNF is given below. The type of the first parameter is used when the function is invoked as part of an SQL statement. When multiple versions of a function are required, each version will have the first parameter of a different type.

<SQL aggregate declaration list> ::= <left paren> [IN] [ <SQL parameter name> ] <parameter type> <comma> [IN] [ <SQL parameter name> ] BOOLEAN <comma> INOUT [ <SQL parameter name> ] <parameter type> <comma> INOUT [ <SQL parameter name> ] <parameter type> <right paren>

The return type is user defined. This is the type of the resulting value when the function is called. Usually an aggregate function is defined with CONTAINS SQL, as it normally does not read the data in database tables, but it is possible to define the function with READS SQL DATA and access the database tables.

HyperSQL invokes the aggregate function, with all the arguments set, once per each row in order to compute the values. Finally, it invokes the function once more to return the final result.

In the computation phase, the first argument is the value of the user argument as specified in the SQL statement, computed for the current row. The second argument is the boolean FALSE. The third and fourth argument values are initially null, but they can be updated in the body of the function during each invocation. The third and fourth arguments act as registers and hold their values between invocations. The return value of the function is ignored during the computation phase (when the second parameter is FALSE).

After the computation phase, the function is invoked once more to get the final result. In this invocation, the first argument is NULL and the second argument is boolean TRUE. The third and fourth arguments hold the values they held at the end of the last invocation. The value returned by the function in this invocation is used as the result of the aggregate function computation in the invoking SQL statement. In SQL queries with GROUP BY, the call sequence is repeated for each separate group.

The example below features a user defined version of the Standard AVG(<value expression>) aggregate function for INTEGER input and output types. This function behaves differently from the Standard AVG function as it returns 0 when all the input values are null.

CREATE AGGREGATE FUNCTION udavg(IN x INTEGER, IN flag BOOLEAN, INOUT addup BIGINT, INOUT counter INT)
  RETURNS INTEGER
  CONTAINS SQL
  BEGIN ATOMIC
    IF flag THEN
      RETURN addup / counter;
    ELSE
      SET counter = COALESCE(counter, 0) + 1;
      SET addup = COALESCE(addup, 0) || COALESCE(x, 0);
      RETURN NULL;
    END IF;
  END

The user defined aggregate function is used in a select statement in the example below. Only the first parameter is visible and utilised in the select statement.

SELECT udavg(id) FROM customers GROUP BY lastname;

In the example below, the function returns an array that contains all the values passed for the aggregated column. For use with longer arrays, you can optimise the function by defining a larger array in the first iteration, and using the TRIM_ARRAY function on the RETURN to cut the array to size :

CREATE AGGREGATE FUNCTION array_aggregate(IN val VARCHAR(100), IN flag boolean, INOUT buffer VARCHAR(100) ARRAY, INOUT counter INT)
  RETURNS VARCHAR(100) ARRAY
  CONTAINS SQL
  BEGIN ATOMIC
    IF flag THEN
      RETURN buffer;
    ELSE
      IF val IS NULL THEN RETURN NULL; END IF;
      IF counter IS NULL THEN SET counter = 0; END IF;
      SET counter = counter + 1;
      IF counter = 1 THEN SET buffer = ARRAY[val];
      ELSE SET buffer[counter] = val; END IF;
      RETURN NULL;
    END IF;
  END

The tables and data for the select statement below are created with the DatabaseManager or DatabaseManagerSwing GUI apps. Part of the output is shown. Each row of the output includes an array containing the values for the invoices for each customer.

SELECT ID, FIRSTNAME, LASTNAME, ARRAY_AGGREGATE(CAST(INVOICE.TOTAL AS VARCHAR(100))) 
  FROM customer JOIN INVOICE ON ID =CUSTOMERID
  GROUP BY ID, FIRSTNAME, LASTNAME

11 Susanne   Karsen    ARRAY['3988.20']                               
12 John      Peterson  ARRAY['2903.10','4382.10','4139.70','3316.50'] 
13 Michael   Clancy    ARRAY['6525.30']                               
14 James     King      ARRAY['3665.40','905.10','498.00']             
18 Sylvia    Clancy    ARRAY['634.20','4883.10']                      
20 Bob       Clancy    ARRAY['3414.60','744.60']

A Java aggregate function is defined similarly to PSM functions, apart from the routine body, which is defined as EXTERNAL NAME ... The Java function signature must follow the rules for both nullable and INOUT parameters, therefore:

No agrument is defined as a primitive or primitive array type. This allows nulls to be passed to the function. The second and third arguments must be defined as arrays of the JDBC non-primitive types listed in the table in the previous section.

In the example below, a user-defined aggregate function for geometric mean is defined.

CREATE AGGREGATE FUNCTION geometric_mean(IN val DOUBLE, IN flag BOOLEAN, INOUT register DOUBLE, INOUT counter INT)
 RETURNS DOUBLE
 NO SQL
 LANGUAGE JAVA
 EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.Test01.geometricMean'

The Java function definition is given below:

public static Double geometricMean(Double in, Boolean flag,
        Double[] register, Integer[] counter) {

    if (flag) {
        if (register[0] == null) { return null; }
        double a = register[0].doubleValue();
        double b = 1 / (double) counter[0];
        return Double.valueOf(java.lang.Math.pow(a, b));
    }
    if (in == null) { return null; }
    if (in.doubleValue() == 0) { return null; }
    if (register[0] == null) {
        register[0] = in;
        counter[0]  = Integer.valueOf(1);
    } else {
        register[0] = Double.valueOf(register[0].doubleValue() * in.doubleValue());
        counter[0] = Integer.valueOf(counter[0].intValue() + 1);
    }
    return null;
}

In a select statement, the function is used like built in aggregate functions:

SELECT geometric_mean(age) FROM  FROM customer