TOP をテンプレートにして作成
ホーム
バックアップ
一覧
検索
最終更新
ヘルプ
ログイン
開始行
Data Manipulation Language (DML)
Table of Contents
New and Extended DSQL Syntaxes
SELECT Statement & Expression Syntax
New and Extended DSQL Syntaxes
EXECUTE BLOCK Statement
Derived Tables
ROLLBACK RETAIN Syntax
ROWS Syntax
Enhancements to UNION Handling
IIF Expression Syntax Added
CAST() Behaviour Improved
Built-in Function SUBSTRING() Enhanced
Enhancements to NULL Logic
CROSS JOIN is Now Supported
Subqueries and INSERT Statements Can Now Accept UNION Sets
New Extensions to UPDATE and DELETE Syntaxes
New Context Variables
Improvements in Handling User-specified Query Plans
Improvements in Sorting
NEXT VALUE FOR Expression Syntax
RETURNING Clause for Insert Statements
DSQL parsing of table aliases is stricter
In this section are details of DML language statements or...
EXECUTE BLOCK Statement
V. Horsun
The SQL language extension EXECUTE BLOCK makes "dynamic P...
Syntax pattern
EXECUTE BLOCK [ (param datatype = ?, param datatype = ...
[ RETURNS (param datatype, param datatype, ...) ]
AS
[DECLARE VARIABLE var datatype; ...]
BEGIN
...
END
For the client, the call isc_dsql_sql_info with the param...
isc_info_sql_stmt_select if the block has output paramete...
isc_info_sql_stmt_exec_procedure if the block has no outp...
The client should preprocess only the head of the SQL sta...
Example
The user SQL is
EXECUTE BLOCK (X INTEGER = :X)
RETURNS (Y VARCHAR)
AS
DECLARE V INTEGER;
BEGIN
INSERT INTO T(...) VALUES (... :X ...);
SELECT ... FROM T INTO :Y;
SUSPEND;
END
The preprocessed SQL is
EXECUTE BLOCK (X INTEGER = ?)
RETURNS (Y VARCHAR)
AS
DECLARE V INTEGER;
BEGIN
INSERT INTO T(...) VALUES (... :X ...);
SELECT ... FROM T INTO :Y;
SUSPEND;
END
Derived Tables
A. Brinkman
Implemented support for derived tables in DSQL (subquerie...
Syntax Pattern
SELECT
<select list>
FROM
<table reference list>
<table reference list> ::= <table reference> [{<comma...
<table reference> ::=
<table primary>
| <joined table>
<table primary> ::=
<table> [[AS] <correlation name>]
| <derived table>
<derived table> ::=
<query expression> [[AS] <correlation name>]
[<left paren> <derived column list> <right paren>]
<derived column list> ::= <column name> [{<comma> <co...
Examples
a) Simple derived table:
SELECT
*
FROM
(SELECT
RDB$RELATION_NAME, RDB$RELATION_ID
FROM
RDB$RELATIONS) AS R (RELATION_NAME, RELATION_ID)
b) Aggregate on a derived table which also contains an ag...
SELECT
DT.FIELDS,
Count(*)
FROM
(SELECT
R.RDB$RELATION_NAME,
Count(*)
FROM
RDB$RELATIONS R
JOIN RDB$RELATION_FIELDS RF ON (RF.RDB$RELATION_NA...
GROUP BY
R.RDB$RELATION_NAME) AS DT (RELATION_NAME, FIELDS)
GROUP BY
DT.FIELDS
c) UNION and ORDER BY example:
SELECT
DT.*
FROM
(SELECT
R.RDB$RELATION_NAME,
R.RDB$RELATION_ID
FROM
RDB$RELATIONS R
UNION ALL
SELECT
R.RDB$OWNER_NAME,
R.RDB$RELATION_ID
FROM
RDB$RELATIONS R
ORDER BY
2) AS DT
WHERE
DT.RDB$RELATION_ID <= 4
Points to Note
Every column in the derived table must have a name. Unnam...
The number of columns in the column list should be the sa...
The optimizer can handle a derived table very efficiently...
ROLLBACK RETAIN Syntax
D. Yemanov
The ROLLBACK RETAIN statement is now supported in DSQL.
A “rollback retaining” feature was introduced in InterBas...
Firebird 2.0 adds an optional RETAIN clause to the DSQL R...
Syntax pattern: follows that of COMMIT RETAIN.
ROWS Syntax
D. Yemanov
ROWS syntax is used to limit the number of rows retrieved...
It is available in both DSQL and PSQL.
Syntax Pattern
SELECT ...
[ORDER BY <expr_list>]
ROWS <expr1> [TO <expr2>]
Examples
1.
SELECT * FROM T1
UNION ALL
SELECT * FROM T2
ORDER BY COL
ROWS 10 TO 100
2.
SELECT COL1, COL2,
( SELECT COL3 FROM T3 ORDER BY COL4 DESC ROWS 1 )
FROM T4
3.
DELETE FROM T5
ORDER BY COL5
ROWS 1
Points to Note
When <expr2> is omitted, then ROWS <expr1> is semanticall...
There is nothing that is semantically equivalent to a SKI...
Enhancements to UNION Handling
The rules for UNION queries have been improved as follows:
UNION DISTINCT Keyword Implementation
D. Yemanov
UNION DISTINCT is now allowed as a synonym for simple UNI...
Syntax Pattern
UNION [{DISTINCT | ALL}]
Improved Type Coercion in UNIONs
A. Brinkman
Automatic type coercion logic between subsets of a union ...
Syntax Rules
Let DTS be the set of data types over which we must deter...
All of the data types in DTS shall be comparable.
Case:
If any of the data types in DTS is character string, then:
If any of the data types in DTS is variable-length charac...
Otherwise, the result data type is fixed-length character...
The characterset/collation is used from the first charact...
If all of the data types in DTS are exact numeric, then t...
Note
NOTE :: Checking for precision overflows is done at run-t...
If any data type in DTS is approximate numeric, then each...
If some data type in DTS is a date/time data type, then e...
If any data type in DTS is BLOB, then each data type in D...
UNIONs Allowed in ANY/ALL/IN Subqueries
D. Yemanov
The subquery element of an ANY, ALL or IN search may now ...
IIF Expression Syntax Added
O. Loa
IIF (<search_condition>, <value1>, <value2>)
is implemented as a shortcut for
CASE
WHEN <search_condition> THEN <value1>
ELSE <value2>
END
It returns the value of the first sub-expression if the g...
Example
SELECT IIF(VAL > 0, VAL, -VAL) FROM OPERATION
CAST() Behaviour Improved
D. Yemanov
The infamous “Datatype unknown” error (SF Bug #1371274) w...
Example
SELECT CAST(? AS INT) FROM RDB$DATABASE
Built-in Function SUBSTRING() Enhanced
O. Loa, D. Yemanov
The built-in function SUBSTRING() can now take arbitrary ...
Formerly, the inbuilt SUBSTRING() function accepted only ...
Note
The length of the resulting column is the same as the len...
x = varchar(50);
substring(x from 1 for 1);
the new column has a length of 50, not 1. (Thank the SQL ...
Enhancements to NULL Logic
The following features involving NULL in DSQL have been i...
New [NOT] DISTINCT Test Treats Two NULL Operands as Equal
O. Loa, D. Yemanov
A new equivalence predicate behaves exactly like the equa...
Thus, IS NOT DISTINCT treats (NULL equals NULL) as if it ...
Syntax Pattern
<value> IS [NOT] DISTINCT FROM <value>
Examples
1.
SELECT * FROM T1
JOIN T2
ON T1.NAME IS NOT DISTINCT FROM T2.NAME;
2.
SELECT * FROM T
WHERE T.MARK IS DISTINCT FROM 'test';
Note
Points to note
Because the DISTINCT predicate considers that two NULL va...
The NOT DISTINCT predicate can be optimized using an inde...
NULL Comparison Rule Relaxed
D. Yemanov
A NULL literal can now be treated as a value in all expre...
A = NULL
B > NULL
A + NULL
B || NULL
Note
All such expressions evaluate to NULL. The change does no...
NULLs Ordering Changed to Comply with Standard
N. Samofatov
Placement of nulls in an ordered set has been changed to ...
Important
If you override the default nulls placement, no index can...
Examples
Database: proc.fdb
SQL> create table gnull(a int);
SQL> insert into gnull values(null);
SQL> insert into gnull values(1);
SQL> select a from gnull order by a;
A
============
<null>
1
SQL> select a from gnull order by a asc;
A
============
<null>
1
SQL> select a from gnull order by a desc;
A
============
1
<null>
SQL> select a from gnull order by a asc nulls first;
A
============
<null>
1
SQL> select a from gnull order by a asc nulls last;
A
============
1
<null>
SQL> select a from gnull order by a desc nulls last;
A
============
1
<null>
SQL> select a from gnull order by a desc nulls first;
A
============
<null>
1
CROSS JOIN is Now Supported
D. Yemanov
CROSS JOIN is now supported. Logically, this syntax pattern:
A CROSS JOIN B
is equivalent to either of the following:
A INNER JOIN B ON 1 = 1
or, simply:
FROM A, B
Performance Improvement at V.2.0.6
(V.2.0.6) In the rare case where a cross join of three or...
Subqueries and INSERT Statements Can Now Accept UNION Sets
D. Yemanov
SELECT specifications used in subqueries and in INSERT IN...
New Extensions to UPDATE and DELETE Syntaxes
O. Loa
ROWS specifications and PLAN and ORDER BY clauses can now...
Users can now specify explicit plans for UPDATE/DELETE st...
Syntax Pattern
UPDATE ... SET ... WHERE ...
[PLAN <plan items>]
[ORDER BY <value list>]
[ROWS <value> [TO <value>]]
or
DELETE ... FROM ...
[PLAN <plan items>]
[ORDER BY <value list>]
[ROWS <value> [TO <value>]]
New Context Variables
A number of new facilities have been added to extend the ...
Sub-second Values Enabled for Time and DateTime Variables
D. Yemanov
CURRENT_TIMESTAMP, 'NOW' Now Return Milliseconds
The context variable CURRENT_TIMESTAMP and the date/time ...
Seconds Precision Enabled for CURRENT_TIME and CURRENT_TI...
CURRENT_TIME and CURRENT_TIMESTAMP now optionally allow s...
The feature is available in both DSQL and PSQL.
Syntax Pattern
CURRENT_TIME [(<seconds precision>)]
CURRENT_TIMESTAMP [(<seconds precision>)]
Examples
1. SELECT CURRENT_TIME FROM RDB$DATABASE;
2. SELECT CURRENT_TIME(3) FROM RDB$DATABASE;
3. SELECT CURRENT_TIMESTAMP(3) FROM RDB$DATABASE;
Note
The maximum possible precision is 3 which means accuracy ...
If no seconds precision is specified, the following value...
0 for CURRENT_TIME
3 for CURRENT_TIMESTAMP
New System Functions to Retrieve Context Variables
N. Samofatov
Values of context variables can now be obtained using the...
Syntax Pattern
RDB$SET_CONTEXT( <namespace>, <variable>, <value> )
RDB$GET_CONTEXT( <namespace>, <variable> )
These functions are really a form of external function th...
Declaration
DECLARE EXTERNAL FUNCTION RDB$GET_CONTEXT
VARCHAR(80),
VARCHAR(80)
RETURNS VARCHAR(255) FREE_IT;
DECLARE EXTERNAL FUNCTION RDB$SET_CONTEXT
VARCHAR(80),
VARCHAR(80),
VARCHAR(255)
RETURNS INTEGER BY VALUE;
Usage
RDB$SET_CONTEXT and RDB$GET_CONTEXT set and retrieve the ...
Note
Namespace and variable names are case-sensitive.
RDB$GET_CONTEXT retrieves current value of a variable. If...
RDB$SET_CONTEXT sets a value for specific variable, if it...
To delete a variable from a context, set its value to NULL.
Pre-defined Namespaces
A fixed number of pre-defined namespaces is available:
USER_SESSION
Offers access to session-specific user-defined variables....
USER_TRANSACTION
Offers similar possibilities for individual transactions.
SYSTEM
Provides read-only access to the following variables:
NETWORK_PROTOCOL :: The network protocol used by client t...
CLIENT_ADDRESS :: The wire protocol address of the remote...
DB_NAME :: Canonical name of the current database. It is ...
ISOLATION_LEVEL :: The isolation level of the current tra...
TRANSACTION_ID :: The numeric ID of the current transacti...
SESSION_ID :: The numeric ID of the current session. The ...
CURRENT_USER :: The current user. The returned value is t...
CURRENT_ROLE :: Current role for the connection. Returns ...
Notes
To avoid DoS attacks against the Firebird Server, the num...
Example of Use
set term ^;
create procedure set_context(User_ID varchar(40), Trn_...
begin
RDB$SET_CONTEXT('USER_TRANSACTION', 'Trn_ID', Trn_ID);
RDB$SET_CONTEXT('USER_TRANSACTION', 'User_ID', User_...
end ^
create table journal (
jrn_id integer not null primary key,
jrn_lastuser varchar(40),
jrn_lastaddr varchar(255),
jrn_lasttransaction integer
)^
CREATE TRIGGER UI_JOURNAL FOR JOURNAL BEFORE INSERT OR UP...
as
begin
new.jrn_lastuser = rdb$get_context('USER_TRANSACTION'...
new.jrn_lastaddr = rdb$get_context('SYSTEM', 'CLIENT_...
new.jrn_lasttransaction = rdb$get_context('USER_TRANS...
end ^
commit ^
execute procedure set_context('skidder', 1) ^
insert into journal(jrn_id) values(0) ^
set term ;^
Since rdb$set_context returns 1 or zero, it can be made t...
Example
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
0
0 means not defined already; we have set it to 'ru'
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
1
1 means it was defined already; we have changed it to 'ca'
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
1
1 says it existed before; we have changed it to NULL, i.e...
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
0
0, since nothing actually happened this time: it was alre...
Improvements in Handling User-specified Query Plans
D. Yemanov
Plan fragments are propagated to nested levels of joins, ...
A user-supplied plan will be checked for correctness in o...
Short-circuit optimization for user-supplied plans has be...
A user-specified access path can be supplied for any SELE...
Syntax rules
The following schema describing the syntax rules should b...
PLAN ( { <stream_retrieval> | <sorted_streams> | <joined_...
<stream_retrieval> ::= { <natural_scan> | <indexed_retrie...
<navigational_scan> }
<natural_scan> ::= <stream_alias> NATURAL
<indexed_retrieval> ::= <stream_alias> INDEX ( <index_name>
[, <index_name> ...] )
<navigational_scan> ::= <stream_alias> ORDER <index_name>
[ INDEX ( <index_name> [, <index_name> ...] ) ]
<sorted_streams> ::= SORT ( <stream_retrieval> )
<joined_streams> ::= JOIN ( <stream_retrieval>, <stream_r...
[, <stream_retrieval> ...] )
| [SORT] MERGE ( <sorted_streams>, <sorted_streams> )
Details
Natural scan means that all rows are fetched in their nat...
Indexed retrieval uses an index range scan to find row id...
Navigational scan uses an index to return rows in the giv...
The index b-tree is walked from the leftmost node to the ...
If any search criterion is used on a column specified in ...
If any search criterion is used on other columns which ar...
Note
Note that a navigational scan incurs random page I/O, as ...
A sort operation performs an external sort of the given s...
A join can be performed either via the nested loops algor...
An inner nested loop join may contain as many streams as ...
An outer nested loops join always operates with two strea...
A sort merge operates with two input streams which are so...
Examples
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME LIKE 'RDB$%'
PLAN (RDB$RELATIONS NATURAL)
ORDER BY RDB$RELATION_NAME
SELECT R.RDB$RELATION_NAME, RF.RDB$FIELD_NAME
FROM RDB$RELATIONS R
JOIN RDB$RELATION_FIELDS RF
ON R.RDB$RELATION_NAME = RF.RDB$RELATION_NAME
PLAN MERGE (SORT (R NATURAL), SORT (RF NATURAL))
Notes
A PLAN clause may be used in all select expressions, incl...
If a PLAN clause contains some invalid retrieval descript...
ORDER <navigational_index> INDEX ( <filter_indices> ) kin...
Improvements in Sorting
A. Brinkman
Some useful improvements have been made to SQL sorting op...
Order By or Group By <alias-name>
Column aliases are now allowed in both these clauses.
Examples:
ORDER BY
SELECT RDB$RELATION_ID AS ID
FROM RDB$RELATIONS
ORDER BY ID
GROUP BY
SELECT RDB$RELATION_NAME AS ID, COUNT(*)
FROM RDB$RELATION_FIELDS
GROUP BY ID
GROUP BY Arbitrary Expressions
A GROUP BY condition can now be any valid expression.
Example
...
GROUP BY
SUBSTRING(CAST((A * B) / 2 AS VARCHAR(15)) FROM 1 FOR 2)
Order SELECT * Sets by Degree Number
Order by degree (ordinal column position) now works on a ...
Example
SELECT *
FROM RDB$RELATIONS
ORDER BY 9
Parameters and Ordinal Sorts--a “Gotcha”
According to grammar rules, since v.1.5, ORDER BY <value_...
However, while the DSQL parser does not reject the parame...
NEXT VALUE FOR Expression Syntax
D. Yemanov
Added SQL-99 compliant NEXT VALUE FOR <sequence_name> exp...
Examples
1.
SELECT GEN_ID(S_EMPLOYEE, 1) FROM RDB$DATABASE;
2.
INSERT INTO EMPLOYEE (ID, NAME)
VALUES (NEXT VALUE FOR S_EMPLOYEE, 'John Smith');
Note
Currently, increment ("step") values not equal to 1 (one)...
GEN_ID(<name>, 0) allows you to retrieve the current sequ...
RETURNING Clause for Insert Statements
D. Yemanov
The RETURNING clause syntax has been implemented for the ...
Available in DSQL and PSQL.
Syntax Pattern
INSERT INTO ... VALUES (...) [RETURNING <column_list> [...
Example(s)
1.
INSERT INTO T1 (F1, F2)
VALUES (:F1, :F2)
RETURNING F1, F2 INTO :V1, :V2;
2.
INSERT INTO T2 (F1, F2)
VALUES (1, 2)
RETURNING ID INTO :PK;
Note
The INTO part (i.e. the variable list) is allowed in PSQL...
In DSQL, values are being returned within the same protoc...
If the RETURNING clause is present, then the statement is...
Any explicit record change (update or delete) performed b...
Cursor based inserts (INSERT INTO ... SELECT ... RETURNIN...
This clause can return table column values or arbitrary e...
DSQL parsing of table aliases is stricter
A. Brinkman
Alias handling and ambiguous field detecting have been im...
When a table alias is provided for a table, either that a...
Ambiguity checking now checks first for ambiguity at the ...
Examples
When an alias is present it must be used; or no alias at ...
This query was allowed in FB1.5 and earlier versions:
SELECT
RDB$RELATIONS.RDB$RELATION_NAME
FROM
RDB$RELATIONS R
but will now correctly report an error that the field "RD...
Use this (preferred):
SELECT
R.RDB$RELATION_NAME
FROM
RDB$RELATIONS R
or this statement:
SELECT
RDB$RELATION_NAME
FROM
RDB$RELATIONS R
The statement below will now correctly use the FieldID fr...
UPDATE
TableA
SET
FieldA = (SELECT SUM(A.FieldB) FROM TableA A
WHERE A.FieldID = TableA.FieldID)
Note
In Firebird it is possible to provide an alias in an upda...
This example did not run correctly in Firebird 1.5 and ea...
SELECT
RDB$RELATIONS.RDB$RELATION_NAME,
R2.RDB$RELATION_NAME
FROM
RDB$RELATIONS
JOIN RDB$RELATIONS R2 ON
(R2.RDB$RELATION_NAME = RDB$RELATIONS.RDB$RELATIO...
If RDB$RELATIONS contained 90 records, it would return 90...
This failed in Firebird 1.5, but is possible in Firebird 2:
SELECT
(SELECT RDB$RELATION_NAME FROM RDB$DATABASE)
FROM
RDB$RELATIONS
Ambiguity checking in subqueries: the query below would r...
SELECT
(SELECT
FIRST 1 RDB$RELATION_NAME
FROM
RDB$RELATIONS R1
JOIN RDB$RELATIONS R2 ON
(R2.RDB$RELATION_NAME = R1.RDB$RELATION_NAME))
FROM
RDB$DATABASE
SELECT Statement & Expression Syntax
Dmitry Yemanov
About the semantics
A select statement is used to return data to the caller (...
Select expressions retrieve parts of data that construct ...
Syntax rules
<select statement> ::=
<select expression> [FOR UPDATE] [WITH LOCK]
<select expression> ::=
<query specification> [UNION [{ALL | DISTINCT}] <quer...
<query specification> ::=
SELECT [FIRST <value>] [SKIP <value>] <select list>
FROM <table expression list>
WHERE <search condition>
GROUP BY <group value list>
HAVING <group condition>
PLAN <plan item list>
ORDER BY <sort value list>
ROWS <value> [TO <value>]
<table expression> ::=
<table name> | <joined table> | <derived table>
<joined table> ::=
{<cross join> | <qualified join>}
<cross join> ::=
<table expression> CROSS JOIN <table expression>
<qualified join> ::=
<table expression> [{INNER | {LEFT | RIGHT | FULL} [O...
ON <join condition>
<derived table> ::=
'(' <select expression> ')'
Conclusions
FOR UPDATE mode and row locking can only be performed for...
Unions are allowed inside any subquery
Clauses FIRST, SKIP, PLAN, ORDER BY, ROWS are allowed for...
Notes
Either FIRST/SKIP or ROWS is allowed, but a syntax error ...
An INSERT statement accepts a select expression to define...
UPDATE and DELETE statements are always based on an impli...
Clauses allowed at the end of UPDATE/DELETE statements ar...
最終行:
Data Manipulation Language (DML)
Table of Contents
New and Extended DSQL Syntaxes
SELECT Statement & Expression Syntax
New and Extended DSQL Syntaxes
EXECUTE BLOCK Statement
Derived Tables
ROLLBACK RETAIN Syntax
ROWS Syntax
Enhancements to UNION Handling
IIF Expression Syntax Added
CAST() Behaviour Improved
Built-in Function SUBSTRING() Enhanced
Enhancements to NULL Logic
CROSS JOIN is Now Supported
Subqueries and INSERT Statements Can Now Accept UNION Sets
New Extensions to UPDATE and DELETE Syntaxes
New Context Variables
Improvements in Handling User-specified Query Plans
Improvements in Sorting
NEXT VALUE FOR Expression Syntax
RETURNING Clause for Insert Statements
DSQL parsing of table aliases is stricter
In this section are details of DML language statements or...
EXECUTE BLOCK Statement
V. Horsun
The SQL language extension EXECUTE BLOCK makes "dynamic P...
Syntax pattern
EXECUTE BLOCK [ (param datatype = ?, param datatype = ...
[ RETURNS (param datatype, param datatype, ...) ]
AS
[DECLARE VARIABLE var datatype; ...]
BEGIN
...
END
For the client, the call isc_dsql_sql_info with the param...
isc_info_sql_stmt_select if the block has output paramete...
isc_info_sql_stmt_exec_procedure if the block has no outp...
The client should preprocess only the head of the SQL sta...
Example
The user SQL is
EXECUTE BLOCK (X INTEGER = :X)
RETURNS (Y VARCHAR)
AS
DECLARE V INTEGER;
BEGIN
INSERT INTO T(...) VALUES (... :X ...);
SELECT ... FROM T INTO :Y;
SUSPEND;
END
The preprocessed SQL is
EXECUTE BLOCK (X INTEGER = ?)
RETURNS (Y VARCHAR)
AS
DECLARE V INTEGER;
BEGIN
INSERT INTO T(...) VALUES (... :X ...);
SELECT ... FROM T INTO :Y;
SUSPEND;
END
Derived Tables
A. Brinkman
Implemented support for derived tables in DSQL (subquerie...
Syntax Pattern
SELECT
<select list>
FROM
<table reference list>
<table reference list> ::= <table reference> [{<comma...
<table reference> ::=
<table primary>
| <joined table>
<table primary> ::=
<table> [[AS] <correlation name>]
| <derived table>
<derived table> ::=
<query expression> [[AS] <correlation name>]
[<left paren> <derived column list> <right paren>]
<derived column list> ::= <column name> [{<comma> <co...
Examples
a) Simple derived table:
SELECT
*
FROM
(SELECT
RDB$RELATION_NAME, RDB$RELATION_ID
FROM
RDB$RELATIONS) AS R (RELATION_NAME, RELATION_ID)
b) Aggregate on a derived table which also contains an ag...
SELECT
DT.FIELDS,
Count(*)
FROM
(SELECT
R.RDB$RELATION_NAME,
Count(*)
FROM
RDB$RELATIONS R
JOIN RDB$RELATION_FIELDS RF ON (RF.RDB$RELATION_NA...
GROUP BY
R.RDB$RELATION_NAME) AS DT (RELATION_NAME, FIELDS)
GROUP BY
DT.FIELDS
c) UNION and ORDER BY example:
SELECT
DT.*
FROM
(SELECT
R.RDB$RELATION_NAME,
R.RDB$RELATION_ID
FROM
RDB$RELATIONS R
UNION ALL
SELECT
R.RDB$OWNER_NAME,
R.RDB$RELATION_ID
FROM
RDB$RELATIONS R
ORDER BY
2) AS DT
WHERE
DT.RDB$RELATION_ID <= 4
Points to Note
Every column in the derived table must have a name. Unnam...
The number of columns in the column list should be the sa...
The optimizer can handle a derived table very efficiently...
ROLLBACK RETAIN Syntax
D. Yemanov
The ROLLBACK RETAIN statement is now supported in DSQL.
A “rollback retaining” feature was introduced in InterBas...
Firebird 2.0 adds an optional RETAIN clause to the DSQL R...
Syntax pattern: follows that of COMMIT RETAIN.
ROWS Syntax
D. Yemanov
ROWS syntax is used to limit the number of rows retrieved...
It is available in both DSQL and PSQL.
Syntax Pattern
SELECT ...
[ORDER BY <expr_list>]
ROWS <expr1> [TO <expr2>]
Examples
1.
SELECT * FROM T1
UNION ALL
SELECT * FROM T2
ORDER BY COL
ROWS 10 TO 100
2.
SELECT COL1, COL2,
( SELECT COL3 FROM T3 ORDER BY COL4 DESC ROWS 1 )
FROM T4
3.
DELETE FROM T5
ORDER BY COL5
ROWS 1
Points to Note
When <expr2> is omitted, then ROWS <expr1> is semanticall...
There is nothing that is semantically equivalent to a SKI...
Enhancements to UNION Handling
The rules for UNION queries have been improved as follows:
UNION DISTINCT Keyword Implementation
D. Yemanov
UNION DISTINCT is now allowed as a synonym for simple UNI...
Syntax Pattern
UNION [{DISTINCT | ALL}]
Improved Type Coercion in UNIONs
A. Brinkman
Automatic type coercion logic between subsets of a union ...
Syntax Rules
Let DTS be the set of data types over which we must deter...
All of the data types in DTS shall be comparable.
Case:
If any of the data types in DTS is character string, then:
If any of the data types in DTS is variable-length charac...
Otherwise, the result data type is fixed-length character...
The characterset/collation is used from the first charact...
If all of the data types in DTS are exact numeric, then t...
Note
NOTE :: Checking for precision overflows is done at run-t...
If any data type in DTS is approximate numeric, then each...
If some data type in DTS is a date/time data type, then e...
If any data type in DTS is BLOB, then each data type in D...
UNIONs Allowed in ANY/ALL/IN Subqueries
D. Yemanov
The subquery element of an ANY, ALL or IN search may now ...
IIF Expression Syntax Added
O. Loa
IIF (<search_condition>, <value1>, <value2>)
is implemented as a shortcut for
CASE
WHEN <search_condition> THEN <value1>
ELSE <value2>
END
It returns the value of the first sub-expression if the g...
Example
SELECT IIF(VAL > 0, VAL, -VAL) FROM OPERATION
CAST() Behaviour Improved
D. Yemanov
The infamous “Datatype unknown” error (SF Bug #1371274) w...
Example
SELECT CAST(? AS INT) FROM RDB$DATABASE
Built-in Function SUBSTRING() Enhanced
O. Loa, D. Yemanov
The built-in function SUBSTRING() can now take arbitrary ...
Formerly, the inbuilt SUBSTRING() function accepted only ...
Note
The length of the resulting column is the same as the len...
x = varchar(50);
substring(x from 1 for 1);
the new column has a length of 50, not 1. (Thank the SQL ...
Enhancements to NULL Logic
The following features involving NULL in DSQL have been i...
New [NOT] DISTINCT Test Treats Two NULL Operands as Equal
O. Loa, D. Yemanov
A new equivalence predicate behaves exactly like the equa...
Thus, IS NOT DISTINCT treats (NULL equals NULL) as if it ...
Syntax Pattern
<value> IS [NOT] DISTINCT FROM <value>
Examples
1.
SELECT * FROM T1
JOIN T2
ON T1.NAME IS NOT DISTINCT FROM T2.NAME;
2.
SELECT * FROM T
WHERE T.MARK IS DISTINCT FROM 'test';
Note
Points to note
Because the DISTINCT predicate considers that two NULL va...
The NOT DISTINCT predicate can be optimized using an inde...
NULL Comparison Rule Relaxed
D. Yemanov
A NULL literal can now be treated as a value in all expre...
A = NULL
B > NULL
A + NULL
B || NULL
Note
All such expressions evaluate to NULL. The change does no...
NULLs Ordering Changed to Comply with Standard
N. Samofatov
Placement of nulls in an ordered set has been changed to ...
Important
If you override the default nulls placement, no index can...
Examples
Database: proc.fdb
SQL> create table gnull(a int);
SQL> insert into gnull values(null);
SQL> insert into gnull values(1);
SQL> select a from gnull order by a;
A
============
<null>
1
SQL> select a from gnull order by a asc;
A
============
<null>
1
SQL> select a from gnull order by a desc;
A
============
1
<null>
SQL> select a from gnull order by a asc nulls first;
A
============
<null>
1
SQL> select a from gnull order by a asc nulls last;
A
============
1
<null>
SQL> select a from gnull order by a desc nulls last;
A
============
1
<null>
SQL> select a from gnull order by a desc nulls first;
A
============
<null>
1
CROSS JOIN is Now Supported
D. Yemanov
CROSS JOIN is now supported. Logically, this syntax pattern:
A CROSS JOIN B
is equivalent to either of the following:
A INNER JOIN B ON 1 = 1
or, simply:
FROM A, B
Performance Improvement at V.2.0.6
(V.2.0.6) In the rare case where a cross join of three or...
Subqueries and INSERT Statements Can Now Accept UNION Sets
D. Yemanov
SELECT specifications used in subqueries and in INSERT IN...
New Extensions to UPDATE and DELETE Syntaxes
O. Loa
ROWS specifications and PLAN and ORDER BY clauses can now...
Users can now specify explicit plans for UPDATE/DELETE st...
Syntax Pattern
UPDATE ... SET ... WHERE ...
[PLAN <plan items>]
[ORDER BY <value list>]
[ROWS <value> [TO <value>]]
or
DELETE ... FROM ...
[PLAN <plan items>]
[ORDER BY <value list>]
[ROWS <value> [TO <value>]]
New Context Variables
A number of new facilities have been added to extend the ...
Sub-second Values Enabled for Time and DateTime Variables
D. Yemanov
CURRENT_TIMESTAMP, 'NOW' Now Return Milliseconds
The context variable CURRENT_TIMESTAMP and the date/time ...
Seconds Precision Enabled for CURRENT_TIME and CURRENT_TI...
CURRENT_TIME and CURRENT_TIMESTAMP now optionally allow s...
The feature is available in both DSQL and PSQL.
Syntax Pattern
CURRENT_TIME [(<seconds precision>)]
CURRENT_TIMESTAMP [(<seconds precision>)]
Examples
1. SELECT CURRENT_TIME FROM RDB$DATABASE;
2. SELECT CURRENT_TIME(3) FROM RDB$DATABASE;
3. SELECT CURRENT_TIMESTAMP(3) FROM RDB$DATABASE;
Note
The maximum possible precision is 3 which means accuracy ...
If no seconds precision is specified, the following value...
0 for CURRENT_TIME
3 for CURRENT_TIMESTAMP
New System Functions to Retrieve Context Variables
N. Samofatov
Values of context variables can now be obtained using the...
Syntax Pattern
RDB$SET_CONTEXT( <namespace>, <variable>, <value> )
RDB$GET_CONTEXT( <namespace>, <variable> )
These functions are really a form of external function th...
Declaration
DECLARE EXTERNAL FUNCTION RDB$GET_CONTEXT
VARCHAR(80),
VARCHAR(80)
RETURNS VARCHAR(255) FREE_IT;
DECLARE EXTERNAL FUNCTION RDB$SET_CONTEXT
VARCHAR(80),
VARCHAR(80),
VARCHAR(255)
RETURNS INTEGER BY VALUE;
Usage
RDB$SET_CONTEXT and RDB$GET_CONTEXT set and retrieve the ...
Note
Namespace and variable names are case-sensitive.
RDB$GET_CONTEXT retrieves current value of a variable. If...
RDB$SET_CONTEXT sets a value for specific variable, if it...
To delete a variable from a context, set its value to NULL.
Pre-defined Namespaces
A fixed number of pre-defined namespaces is available:
USER_SESSION
Offers access to session-specific user-defined variables....
USER_TRANSACTION
Offers similar possibilities for individual transactions.
SYSTEM
Provides read-only access to the following variables:
NETWORK_PROTOCOL :: The network protocol used by client t...
CLIENT_ADDRESS :: The wire protocol address of the remote...
DB_NAME :: Canonical name of the current database. It is ...
ISOLATION_LEVEL :: The isolation level of the current tra...
TRANSACTION_ID :: The numeric ID of the current transacti...
SESSION_ID :: The numeric ID of the current session. The ...
CURRENT_USER :: The current user. The returned value is t...
CURRENT_ROLE :: Current role for the connection. Returns ...
Notes
To avoid DoS attacks against the Firebird Server, the num...
Example of Use
set term ^;
create procedure set_context(User_ID varchar(40), Trn_...
begin
RDB$SET_CONTEXT('USER_TRANSACTION', 'Trn_ID', Trn_ID);
RDB$SET_CONTEXT('USER_TRANSACTION', 'User_ID', User_...
end ^
create table journal (
jrn_id integer not null primary key,
jrn_lastuser varchar(40),
jrn_lastaddr varchar(255),
jrn_lasttransaction integer
)^
CREATE TRIGGER UI_JOURNAL FOR JOURNAL BEFORE INSERT OR UP...
as
begin
new.jrn_lastuser = rdb$get_context('USER_TRANSACTION'...
new.jrn_lastaddr = rdb$get_context('SYSTEM', 'CLIENT_...
new.jrn_lasttransaction = rdb$get_context('USER_TRANS...
end ^
commit ^
execute procedure set_context('skidder', 1) ^
insert into journal(jrn_id) values(0) ^
set term ;^
Since rdb$set_context returns 1 or zero, it can be made t...
Example
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
0
0 means not defined already; we have set it to 'ru'
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
1
1 means it was defined already; we have changed it to 'ca'
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
1
1 says it existed before; we have changed it to NULL, i.e...
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
0
0, since nothing actually happened this time: it was alre...
Improvements in Handling User-specified Query Plans
D. Yemanov
Plan fragments are propagated to nested levels of joins, ...
A user-supplied plan will be checked for correctness in o...
Short-circuit optimization for user-supplied plans has be...
A user-specified access path can be supplied for any SELE...
Syntax rules
The following schema describing the syntax rules should b...
PLAN ( { <stream_retrieval> | <sorted_streams> | <joined_...
<stream_retrieval> ::= { <natural_scan> | <indexed_retrie...
<navigational_scan> }
<natural_scan> ::= <stream_alias> NATURAL
<indexed_retrieval> ::= <stream_alias> INDEX ( <index_name>
[, <index_name> ...] )
<navigational_scan> ::= <stream_alias> ORDER <index_name>
[ INDEX ( <index_name> [, <index_name> ...] ) ]
<sorted_streams> ::= SORT ( <stream_retrieval> )
<joined_streams> ::= JOIN ( <stream_retrieval>, <stream_r...
[, <stream_retrieval> ...] )
| [SORT] MERGE ( <sorted_streams>, <sorted_streams> )
Details
Natural scan means that all rows are fetched in their nat...
Indexed retrieval uses an index range scan to find row id...
Navigational scan uses an index to return rows in the giv...
The index b-tree is walked from the leftmost node to the ...
If any search criterion is used on a column specified in ...
If any search criterion is used on other columns which ar...
Note
Note that a navigational scan incurs random page I/O, as ...
A sort operation performs an external sort of the given s...
A join can be performed either via the nested loops algor...
An inner nested loop join may contain as many streams as ...
An outer nested loops join always operates with two strea...
A sort merge operates with two input streams which are so...
Examples
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME LIKE 'RDB$%'
PLAN (RDB$RELATIONS NATURAL)
ORDER BY RDB$RELATION_NAME
SELECT R.RDB$RELATION_NAME, RF.RDB$FIELD_NAME
FROM RDB$RELATIONS R
JOIN RDB$RELATION_FIELDS RF
ON R.RDB$RELATION_NAME = RF.RDB$RELATION_NAME
PLAN MERGE (SORT (R NATURAL), SORT (RF NATURAL))
Notes
A PLAN clause may be used in all select expressions, incl...
If a PLAN clause contains some invalid retrieval descript...
ORDER <navigational_index> INDEX ( <filter_indices> ) kin...
Improvements in Sorting
A. Brinkman
Some useful improvements have been made to SQL sorting op...
Order By or Group By <alias-name>
Column aliases are now allowed in both these clauses.
Examples:
ORDER BY
SELECT RDB$RELATION_ID AS ID
FROM RDB$RELATIONS
ORDER BY ID
GROUP BY
SELECT RDB$RELATION_NAME AS ID, COUNT(*)
FROM RDB$RELATION_FIELDS
GROUP BY ID
GROUP BY Arbitrary Expressions
A GROUP BY condition can now be any valid expression.
Example
...
GROUP BY
SUBSTRING(CAST((A * B) / 2 AS VARCHAR(15)) FROM 1 FOR 2)
Order SELECT * Sets by Degree Number
Order by degree (ordinal column position) now works on a ...
Example
SELECT *
FROM RDB$RELATIONS
ORDER BY 9
Parameters and Ordinal Sorts--a “Gotcha”
According to grammar rules, since v.1.5, ORDER BY <value_...
However, while the DSQL parser does not reject the parame...
NEXT VALUE FOR Expression Syntax
D. Yemanov
Added SQL-99 compliant NEXT VALUE FOR <sequence_name> exp...
Examples
1.
SELECT GEN_ID(S_EMPLOYEE, 1) FROM RDB$DATABASE;
2.
INSERT INTO EMPLOYEE (ID, NAME)
VALUES (NEXT VALUE FOR S_EMPLOYEE, 'John Smith');
Note
Currently, increment ("step") values not equal to 1 (one)...
GEN_ID(<name>, 0) allows you to retrieve the current sequ...
RETURNING Clause for Insert Statements
D. Yemanov
The RETURNING clause syntax has been implemented for the ...
Available in DSQL and PSQL.
Syntax Pattern
INSERT INTO ... VALUES (...) [RETURNING <column_list> [...
Example(s)
1.
INSERT INTO T1 (F1, F2)
VALUES (:F1, :F2)
RETURNING F1, F2 INTO :V1, :V2;
2.
INSERT INTO T2 (F1, F2)
VALUES (1, 2)
RETURNING ID INTO :PK;
Note
The INTO part (i.e. the variable list) is allowed in PSQL...
In DSQL, values are being returned within the same protoc...
If the RETURNING clause is present, then the statement is...
Any explicit record change (update or delete) performed b...
Cursor based inserts (INSERT INTO ... SELECT ... RETURNIN...
This clause can return table column values or arbitrary e...
DSQL parsing of table aliases is stricter
A. Brinkman
Alias handling and ambiguous field detecting have been im...
When a table alias is provided for a table, either that a...
Ambiguity checking now checks first for ambiguity at the ...
Examples
When an alias is present it must be used; or no alias at ...
This query was allowed in FB1.5 and earlier versions:
SELECT
RDB$RELATIONS.RDB$RELATION_NAME
FROM
RDB$RELATIONS R
but will now correctly report an error that the field "RD...
Use this (preferred):
SELECT
R.RDB$RELATION_NAME
FROM
RDB$RELATIONS R
or this statement:
SELECT
RDB$RELATION_NAME
FROM
RDB$RELATIONS R
The statement below will now correctly use the FieldID fr...
UPDATE
TableA
SET
FieldA = (SELECT SUM(A.FieldB) FROM TableA A
WHERE A.FieldID = TableA.FieldID)
Note
In Firebird it is possible to provide an alias in an upda...
This example did not run correctly in Firebird 1.5 and ea...
SELECT
RDB$RELATIONS.RDB$RELATION_NAME,
R2.RDB$RELATION_NAME
FROM
RDB$RELATIONS
JOIN RDB$RELATIONS R2 ON
(R2.RDB$RELATION_NAME = RDB$RELATIONS.RDB$RELATIO...
If RDB$RELATIONS contained 90 records, it would return 90...
This failed in Firebird 1.5, but is possible in Firebird 2:
SELECT
(SELECT RDB$RELATION_NAME FROM RDB$DATABASE)
FROM
RDB$RELATIONS
Ambiguity checking in subqueries: the query below would r...
SELECT
(SELECT
FIRST 1 RDB$RELATION_NAME
FROM
RDB$RELATIONS R1
JOIN RDB$RELATIONS R2 ON
(R2.RDB$RELATION_NAME = R1.RDB$RELATION_NAME))
FROM
RDB$DATABASE
SELECT Statement & Expression Syntax
Dmitry Yemanov
About the semantics
A select statement is used to return data to the caller (...
Select expressions retrieve parts of data that construct ...
Syntax rules
<select statement> ::=
<select expression> [FOR UPDATE] [WITH LOCK]
<select expression> ::=
<query specification> [UNION [{ALL | DISTINCT}] <quer...
<query specification> ::=
SELECT [FIRST <value>] [SKIP <value>] <select list>
FROM <table expression list>
WHERE <search condition>
GROUP BY <group value list>
HAVING <group condition>
PLAN <plan item list>
ORDER BY <sort value list>
ROWS <value> [TO <value>]
<table expression> ::=
<table name> | <joined table> | <derived table>
<joined table> ::=
{<cross join> | <qualified join>}
<cross join> ::=
<table expression> CROSS JOIN <table expression>
<qualified join> ::=
<table expression> [{INNER | {LEFT | RIGHT | FULL} [O...
ON <join condition>
<derived table> ::=
'(' <select expression> ')'
Conclusions
FOR UPDATE mode and row locking can only be performed for...
Unions are allowed inside any subquery
Clauses FIRST, SKIP, PLAN, ORDER BY, ROWS are allowed for...
Notes
Either FIRST/SKIP or ROWS is allowed, but a syntax error ...
An INSERT statement accepts a select expression to define...
UPDATE and DELETE statements are always based on an impli...
Clauses allowed at the end of UPDATE/DELETE statements ar...
ページ名:
新規
名前変更
ホーム
一覧
検索
最終更新
バックアップ
ヘルプ
最終更新のRSS