DBMS Linter Modification List (4th Quarter of 2012 – 1st Half of 2013)

18.07.2013

RELEX Group has published a report on the modifications and updates made in all the basic versions of DBMS Linter during the 4th quarter of 2012 - 1st half of 2013. Most modifications concern the capabilities of the system, the interaction with some platforms, the operation of the DBMS kernel, SQL compiler, various utilities, program interfaces and service libraries.

New DBMS Capabilities

Table expressions (CTE), except recursive, implemented according to the SQL standard.
Schemes with names other than user names allowed in the database.
Comments to various database objects can now be stored.
New capabilities of the SQL standard added to FETCH FIRST construction (show the specified percentage of all rows from the beginning of the selection; show the specified number or percentage of sorted rows from the beginning of the selection, including all rows equal to the last one in the sorting). The general syntax of FETCH FIRST now looks like this: FETCH FIRST n [PERCENT] [WITH TIES].ORDER BY and LIMIT constructions allowed in any sub-queries.
ORDER BY and LIMIT constructions allowed in any sub-queries.
In stored procedures, break and continue constructions are now implemented in cycles, and their behavior is similar to that in C/C++.
The following constructions are now allowed in stored procedures: EXECUTE [DIRECT] [,] [INTO ] EXECUTE [DIRECT] [USING < parameters >] [INTO ] In case of direct without parameters, the behaviour is the same (exec direct); in case of direct with parameters in execute-time, the expression is compiled first, then bind and exec; In case of non-direct, the expression is compiled to compile-time, then to execute-time bind and exec.
External links are allowed in a SELECT list. Besides, when checking the existence of columns under aggregate functions and without them, these external links are not taken into account (i.e. viewed as constants).
It is now possible not to bind any values to parameters. If the expression for the new value of a column in INSERT/UPDATE/MERGE queries includes only one parameter (INSERT ... VALUES(..., ?, ...) or UPDATE ... SET ... = ?, ...), this column is skipped, i.e. the default value or the old value is assigned to it. In more complicated expressions (e.g. in SET ... = ? + ... operators) the column is not skipped, and the parameter value is NULL.
Additional limitations on ranges of REAL and DOUBLE values (imposed earlier by the DBMS compared to the IEEE754 standard) are no longer imposed.
The mechanism for working with BLOB values enhanced for full-scale support of multibyte encodings, particularly UTF-8.
Queries locking the operation of the kernel longer than the specified time are now interrupted automatically.
/C (/CONSOLE) kernel startup key added to simplify the embedding into an application. Execution of UNION CORRESPONDING allowed for compatible string types.
Execution of UNION CORRESPONDING allowed for compatible string types.
The value for DECIMAL can be specified as an exponent.
FLOAT(N) type is allowed in CAST.
Alternative syntax СREATE TABLE IF NOT EXISTS allowed in addition to СREATE IF NOT EXISTS TABLE.
Syntax CREATE IF NOT EXISTS implemented for CREATE GROUP and CREATE LEVEL.
It is allowed to specify user name in CREATE SEQUENCE and CREATE SYNONYM.
More string expressions can be converted to date using CAST AS DATE.
More arguments of scalar functions can accept non-typified parameters as values.
REVERSE scalar function added. The function accepts one argument ( character string - CHAR, VARCHAR, NCHAR, NVARCHAR) and returns the result of the same type. The result has reverse order of characters.
AUTOINC columns are now supported in MERGE.
Parameter names can partially coincide with SQL key words.
Information about the phrase index subsystem is now displayed during the kernel startup.

Improvements for Certain Platforms

Android

Android is now diagnosed for an application as a separate platform, not as a variation of Linux.
Executive application capabilities enhanced.

UNIX Platforms

/MBX and /NAME kernel startup flags similar to those for WINDOWS platforms are now supported.
errno value loss prior to diagnostics eliminated.
gcc build on platforms not supporting atomic operations corrected.
libc library version incompatibility problems during gcc build eliminated.

SunOS

Build process configurator corrected. The –misalign flags are now added to the compiler flag list only if supported by the corresponding compiler.
DBMS component failures in case of long integer alignment errors corrected.
Several problems specific for MSBF platforms corrected.

QNX

sql process restart problem corrected.
fwrite operation is no longer interrupted by a signal.
Resend message problem eliminated.

INTROS VM 5.4

Build configuration corrected.

DBMS Kernel, SQL Compiler and Stored Procedure Compiler Behaviour Correction

Optimization: Correct Optimizations Allowed, Erroneous Optimizations Prohibited

Optimization performed to eliminate redundant sorting.
OUTER JOIN optimization prohibited in case at least one of the joined tables contains a constant column (to avoid an incorrect response).
PRIMARY KEY/NOT NULL optimization for OUTER JOIN limited to avoid incorrect responses.
Quantization of some processes added to avoid delays in passing the control to the kernel planner (primarily for kernel version 6.1 and SELECT * and SELECT COUNT(*) queries).
Optimization conflict in case of replacing a foreign key with the corresponding primary key resolved.
Incorrect optimization of queries with COUNT(*) corrected.

DBMS Behaviour Standardization and Unification

Processing the aliases of tables with column lists in JOIN queries corrected.
Use of predefined column name lists in NATURAL JOIN added to compare names.
The behaviour of NATURAL JOIN without common columns is now analogous to CROSS JOIN.
Interpretation of end spaces in REPEAT_STRING corrected.
SOUNDEX function result encoding corrected.
The behaviour of external links with MATCH PARTIAL modifier referring to the same table corrected.
Assigning the BLOB value type in operations with BLOB corrected.
<CAST ? AS type> и <?(type)> constructions now operate in the same way.
Default conversion of CAST AS VARCHAR construction to date added (to support .NET provider).
Type match verification for the IN predicate (sub-query) corrected.
String size calculation (for strings converted to char using CAST) corrected.
LPAD/RPAD function processing corrected to work with multibyte encodings.
Calculation of size of geometric values specified in WKB format corrected.
Erroneous prohibition of temporary table extension eliminated.
OUTOFCURSOR procedural function operation corrected.
Verifications added to ignore rows with NULL values for integrity limitations and UNIQUE predicate.
Behaviour of the following functions corrected: INSTR scalar function, SLEEP and RANDOMIZE procedural functions.
RAWTOHEX function is now executed correctly in procedures.

Additional and Corrected Verifications to Avoid Errors

For kernel version 6.1: early verification that the processed ROWID has not been changed by another transaction (up to now, this verification was executed only upon completion of the transaction) to avoid redundant calculations.
Changing indexes for global temporary tables when working with these tables prohibited.
Erroneously allowed insertion of the value list into a column prohibited.
PRIMARY KEY and UNIQUE cannot have the same set of columns.
Column duplication in the DROP COLUMN list prohibited.
Column name duplication in the USING list prohibited.
Erroneously allowed comparison of expressions for some combinations of types (e.g. BYTE and CHAR) is now prohibited.
Queries united using UNION are now checked for compatibility.
In the MERGE operator syntax, constructions for INSERT prohibited in regular INSERT are now prohibited as well.
Ambiguous column references in ORDER BY prohibited.
The same foreign key cannot refer to different primary keys.
SQL expression maximum length verifications corrected.
In case of incorrect OUTER JOIN combination in ORACLE style, the newly added verification will return an error.
Verification of maximum column count in a query added.
Outgoing buffer size (for submitting a query from a procedure) corrected.
Stored procedure translator pool extended to avoid memory shortage errors.
Expression element number limit in the SQL translator increased.

Substantial Modifications of Code to Eliminate Detected Problems

The work with checkpoints in BLOB transactions corrected.
The work with checkpoints in OPTIMISTIC mode corrected.
LOGON/LOGOFF triggers are now executed in autocommit.
For kernel version 6.1: instead of locking the version chain, only the logical ROWID is locked to increase the performance.
Channel context purge in case of error corrected.
Problem associated with several channels working with BLOB in the same transaction corrected.
Converter shift when extending the bitmap of files in the old format corrected.
Correct processing of cyclic table locking added (the same as for triggers and reference integrity).

Problems Eliminated

with integrity of the channel queue after executing procedures initiated by events.
with row search for large-size data files with the new bitmap format.
with long scanning of the log when restarting the database.
with reference integrity violation in the optimistic mode for kernel version 6.1.
with operation of cyclic tables in the optimistic mode.
with processing CHECK integrity limitations due to incorrect result type settings.
with checking the query processing quantum timeout immediately after the kernel start.
with cascade deletion of a user owning a sequence.
with incorrect sorting of hierarchic queries when working via certain interfaces.
with the influence of overfilled stored procedure subsystem buffers on executing subsequent procedures.
with kernel/SQL translator failures after errors.
with losing precision during conversion between the DECIMAL and DOUBLE types.
with comparing NCHAR and NVARCHAR values.
with losing the value’s NULL flag during CAST.
with processing typified NULL values in CASE.
with calculating the NULLIF and COALESCE functions.
with converting the second argument of the RIGHT_SUBSTR function.
with recalculating the string value length after its recoding.
with memory of channels in complex combinations of triggers and reference integrity.
with parallel operation of several channels in the optimistic mode.
with kernel failures when executing triggers for tables with extfile columns.
with empty responses to queries addressed to a global temporary table using an index.
with the growth of the log on the slave base in the hot backup mode.
with Error 501 on executing the RBAC command when trying to read a log file already renamed.
with searching for the minimum and maximum by a simple index in case of quantization.
with memory "notching" when working with a small sorting pool.
with looping when processing the COUNTBLOB function with the second argument UNICODE.
with spoiling indexes when filling in a cyclic table.
with behavior in case of query processing quantum timeout error (resources are now freed correctly).
with global events in the optimistic mode with checkpoints.
with not converting a DEFAULT value defined by an expression whose type is different from the column type.
with not freeing resources occupied by forcedly closed channels (by KILL) in case system triggers (for LOGON/LOGOFF) exist.
with automatic conversion of REAL/DOUBLE to DECIMAL in stored procedures (it should be vice versa).
with incorrect caching of sequence values.
with comparing geometric type data (conversion >= and <= to BETWEEN is cancelled for such data).
with calculating the maximum result length for the REPLACE function.
with the operation of the CORRECT INDEX PAGE command.
with the operation of the MERGE operator with a sub-query in the right part.
with the operation of the CREATE TABLE AS SELECT operator with complex sub-queries.
with CREATE TABLE AS SELECT operator rollback.
with incomplete rollback of adding BLOB via INL.
with the operation of the substring replacement function in case of recoding.
with processing the situation of not supporting in-memory tables.
with loss of data during conversion of the second operand to the first operand type in the IN query.
when changing the capability access group and level values in an UPDATE operator for the entire row when these values are not expressly specified.
with checking the capability access group for separate fields of a row.
with executing queries in case LOGON/LOGOFF triggers exist.
with converting VARCHAR to UNICODE.
with rolling back changes referring to deleted tables.
with operation of aggregate indexes in borderline situations in kernel version 6.1.
with DBMS kernel failures when calculating an external connection in which the compared value is an expression derived from several columns.
with support of the log on a device other than SY00 in the inc dbonly mode.
with negative initial values for autoinc smаllint.
with cascade deletion in case of complex dependencies between tables and views.
with SQL compiler failures in case of unsuccessful column search.
with outputting Error 707 to the console when testing a table with an empty index.
with the operation of the SET DATABASE QUANTUM command.
with checking the [NOT] SIMILAR predicate without using indexes when working in UTF-8.
with reserving insufficient space for a parameter value.
with deleting charsets consisting of several codepages.
with using Russian characters in fields for OLD and NEW values in triggers.
with repeated doubling of UNICODE parameter length in stored procedures.
with processing TOCHAR and TONCHAR functions in stored procedures.
with encodings when executing SELECT from a cursor procedure.
with the operation of the DEFTEXT function for a DOUBLE argument.
with substantial delays of the kernel operation when executing the ROUND and TRUNC functions.
with the CAST VARCHAR result length.
with confusion of selected columns in SELECT from UNION.
with getting NULL values from the first row of a procedure cursor.
with executing CREATE OR REPLACE for a synonym or VIEW having the same name as an existing table.
with skipping the index and key consistency check after deleting a key.
with processing the DEFAULT aggregate function when using indexes.
when searching by index after reconverting the target value from UTF-8.
with the operation of the new bitmap format in borderline cases.
with writing to the log file in borderline cases.
with the operation of autorowid columns in the optimistic mode.

Client Utilities for Working with the DBMS

Hot Backup Server (hserver)

Comparison mode (diff) support for lhb and the backup system. In this mode, only modified blocks of data are downloaded. Modified blocks are detected using checksums. This mode speeds up the transition of the backup system to the ready mode.
The backup system can now work with the database on several devices. The command line accepts the argument specifying which environment variables should be used for working with the main database or with the backup database. It is assumed that these variables are devices, and that they point to database directories. Instead of working with a single directory, the system works with the list of directories.
Deferred deletion of checkpoints has been implemented for quick transition to the ready mode after shutdown.
Deferred deletion of checkpoints has been implemented for quick transition to the ready mode after shutdown.
The system behaviour in case of trying to use the incremental mode has been corrected (to avoid unnecessary download of the entire database). The interaction between the log subsystem of the kernel and lhb has been optimized to detect the possibility of using the incremental mode.
The system behaviour when awaiting the decision about the new main server (refusal to restart lhb, correcting the restart timeout count) corrected.
Hot backup hang-up (with tracing on) eliminated.
Previous lhb log file saving is provided.
When the /mklhbarc option is active, the lhb file is not deleted but renamed.

lhb

The response to commit (altering the database in autocommit) may be deferred till the modified lhb data package is sent. Thus, it is guaranteed that the lhb data will be saved until the response to the data modification query is received. The deferred response to data modification queries is necessary to guarantee the transfer of data from the kernel to lhb during archiving in the wait mode. In this mode, the user does not get a response to a commit operation (or to a data modification query in autocommit) until the modified data is saved by lhb.
The diff mode (downloading only the differences) has been added. It works similarly to the –dbonly mode, but compares the checksums of the local and remote databases instead of downloading all data. Only the differing blocks of data are downloaded.
The archive continuation error in the dbonly mode is corrected.
Backslash processing in scripts is corrected.
The problem with possible column missequencing when saving and restoring a table is corrected.
Message output is corrected, and missing information is added to help.

Lindesk Utility + RELAPI Library

Panel interface (MDI) implemented.
Signal and slot system redesigned.
The work with joint functions of UI event handlers and inspector tree item events substantially optimized.
Utility interface (saving and restoring the settings, highlighting objects, saving multiple files, bookmark context menu) considerably enhanced.
SQL debugger for lindesk and RelAPI editor enhanced.
Compliance with the X Free Desktop standard added.
Database object access information displayed more conveniently.
Synonyms, procedures and triggers can be exported to SQL from the menu in SQL inspector.
Functions for maximizing the window in Windows, Gnome and KDE checked and corrected.
Sorting and filter in the table browsing form corrected.
Problem with browsing roles for a user other than the database owner corrected.
Procedure debugging failures when browsing big variables and executing queries with big parameters eliminated.
Many other problems eliminated.

Inl

Parameter number in Error 1052 (parameter not bound) is now displayed.
Information about indexes and indexed columns is now displayed correctly.
The allowed file name length for the outfil command increased.
BLOB value type parameter added to commands for working with BLOB.
Command line parameter length verification added.
Behaviour corrected in case the input string is too long.
Result text representation buffer size corrected.
Partial conversion of output comments to lower case eliminated.

dbstore

Duplicate procedure information output problem corrected.
Database upload without the $$$PROC table corrected.

loarel

The problem of error string output (last column value not output to .err file) eliminated.

testdb

Geometric type column index testing corrected.
Additional verification implemented to avoid failure when deleting a table from a damaged database.

gendb

-ci startup key to select the interface language added.
Automatic language recognition added.

Network Utilities

/TCPNOGUI console startup key for dbs_wnt and dbc_wnt added (to simplify the embedding into an application).

.NET Data Converter

Utility considerably redesigned.

DBMS Program Interfaces

JDBC

The following settings added: behaviour during close (commit/rollback); treating an empty BLOB as NULL; autoCommit parameter in JDBC URL. Tests to check the settings implemented.
Possibility to shut down the JDBC server when shutting down the parent process added.
In the DatabaseMetaData.getCatalogs() method implementation, the column name is changed from "table_cat" to "TABLE_CAT" (according to the specifications).
The ResultSetMetaData.isSigned(int col) method used to return false, irrespective of the column data type.
JDBC server stability on the 64-bit architecture improved.

.NET

The operation of ADO.NET, Entity, LINQ, NHibernate and DevExpress providers optimized, errors corrected.
Problems with procedure parameter operation and Error 1 (deleted row) code processing corrected.
Unnecessary recursion that could lead to memory leaks removed from the example for .NET2.

LinQT

Multithread version build for Qt 4.0 and greater implemented.
QT5 support added.
Incorrect column number transfer case in the BLOB processing function corrected.
Incorrect driver behaviour in case of error in response to a service query corrected.

LinAPI

EXECUTE BLOCK command support added.
Functions for automatic authentication by environment variables added.

LinCPPAPI

Interface redesigned for correct GCC4 and MinGW build.
Functions for automatic authentication by environment variables added.

LINCPP

The xCLinterStatement::Open function skips an exception instead of returning an error code.
New facilities for working with multibyte encodings added.

ODBC

Some column types returned by the driver added to comply with the ODBC 3 specifications.

PCL (embedded SQL)

Library with PIC code and shared library implemented.

PHP

Problem with binding several parameters to the same query eliminated.

DBMS Service Libraries

TICK Library (Working with Date and Time)

Input argument verification for TFORMAT and TICKFSTRLEN functions added.
Default support of the time format without date added (date without time was implemented earlier).

DATETIME Library (Internal Time Format)

Tick rounding instead of truncation in date format conversion implemented

DECIMALS Library (Working with Fixed Precision Numbers)

Decimal zero output at small values of number precision corrected.

LINNLS Library (Codepage Support)

Implicit input data truncation without returning an error code corrected.
Incorrect operation of the LINNLS_wstrstr function when searching for the last character in a string corrected.

LINGEO Library (Geometric Data)

DIFFERENCE function processing for CIRCLE arguments corrected.
For CROSSES function in case a figure crosses a line, arguments may change places if necessary.

KLIB Library