Tsurugi 1.2.0 - Known Issues (en)

[akirakw]

1.2.0 Known Issues

Known Issues

The following are unresolved issues in this version.
Unless otherwise noted, these issues are planned to be fixed or mitigated in future versions.

TSURUGI-IS-177 Executing DDL during DML processing can cause the database to become unstable.

Problem:
Executing DDL statements such as CREATE TABLE or DROP TABLE during the execution of DML statements like SELECT or INSERT can cause the database to become unstable.
To avoid this situation, ensure to complete DDL statements before starting any DML statements, so that their execution times do not overlap.

TSURUGI-IS-252 Cannot create an index on a non-empty table.

Problem:
When creating an index using the CREATE INDEX statement, an error occurs if the target table contains data.

Note

Workaround:
Empty the table before creating the index.
You can temporalily dump the table to escape the table data, and then load back the data to the table after creating indexes.

TSURUGI-IS-289 Database startup becomes slower as the transaction log increases.

Problem:
When the database is started with a large amount of data in the transaction log, the startup time becomes longer.
Additionally, the transaction log increases with each write to the database, and deleting rows does not immediate decrease the log.

Note

Workaround:
Using the log compaction tool to compress the transaction log may shorten the database startup time.
This tool reduces the log size by consolidating multiple operations on the same row and removing information about deleted rows from the log.

Version 1.0.0 provides two types of log compaction tools.
The following commands are located in the bin directory under the Tsurugi installation directory.

tglogutil compaction: Offline Log Compaction Tool

tglogutil compaction can compress transaction log files while the database is stopped.

For details on the command, refer to the following document:

• tglogutil-compaction - reorganize a Tsurugi transaction log directory

tgcmpct_logs.sh: (Experimental Feature) Online Log Compaction Tool

tgcmpct_logs.sh can compress transaction log files while the database is running.

This command does not actually delete the log files even if some files become unnecessary as a result of compaction.
To delete such the log files, please execute tgdel_logs.sh separately.

tgdel_logs.sh actually deletes such the unnecessary files, as a result of executing tgcmpct_logs.sh, from the file system.
Basically, use tgcmpct_logs.sh and tgdel_logs.sh in combination.

This feature is currently experimental. These commands and features are subject to change upon official support.
It is recommended to take a backup of the database before using this feature.

Important

The current online log compaction tool does not run automatically.
Additionally, since this tool executes the compression process once and then terminates each time the command is run, if you want to perform log compression regularly, please configure your environment to schedule the above command using external tools.

Example of Measuring Database Startup Time

Status:
In version 1.1.0, the startup time after transaction log compaction has been improved. Below is an example of measuring the database startup time using version 1.1.0.

Below are examples of the number of records, transaction log size, and database startup time for table data with a each record size of approximately 200 bytes.

Number of Records (1M=1,000,000)	Log Size (GB)	Startup Time without Log Compaction (seconds)	Startup Time after Log Compaction (seconds)	Log Compaction Tool Execution Time (seconds)
100M	22G	935	213	683
800M	171G	12,241	1,756	9,181

TSURUGI-IS-448 Interrupting execution of DDL can leave the table definitions inconsistent

Problem:
If a transaction that executes DDL statements such as CREATE TABLE or DROP TABLE is aborted or rolled back, the internal table management information in Tsurugi becomes inconsistent. Operations on tables in this inconsistent state may return incorrect results.

For example, executing the following SQL steps will result in an inconsistent table state:

BEGIN;
CREATE TABLE T(C int);
ROLLBACK;
INSERT INTO T VALUES (1);

If the above situation occurs, promptly execute DROP TABLE to delete the table.

TSURUGI-IS-702 Deleting tables invalidates prepared statements for the table

Problem:
If a DROP TABLE is executed on a target table while holding a PreparedStatement, and then a table with the same name is created using CREATE TABLE, using the PreparedStatement may result in no response from the server or cause the server to crash.

Note

Workaround:
Recreate the PreparedStatement before using it to avoid this issue.

TSURUGI-IS-731 Searching with keys of a different type than the column definition may result in some incorrect results.

Problem:
Using keys of a different type than the column definition for searches can result in incorrect outcomes near the maximum or minimum values of the type.

Here is an example:

tgsql> create table t (c0 int primary key);
tgsql> insert into t values (-2147483648);
tgsql> select * from t where c0=2147483648;
[c0: INT4]
[-2147483648]

In the above example, -2147483648 is included in the results even though it should not match the condition c0=2147483648.
The expected query result is 0 rows.

[!NOTE] Workaround: To avoid comparisons with different types, use a CAST expression to match the type with the column definition in advance. For example, in the previous example, cast the right operand of the WHERE clause to the same type as the left operand.

tgsql> select * from t where c0=CAST(2147483648 AS INT);
[c0: INT4]
(0 rows)

Note that due to the specifications of the CAST expression, CAST(2147483648 AS INT) becomes 2147483647 (the maximum value of INT).

TSURUGI-IS-732 Performing binary operations between DECIMAL and non-DECIMAL types may result in an error.

Problem:
When performing binary operations between DECIMAL and non-DECIMAL types, the result may differ from the specification.

tgsql> create table t (c0 decimal(5) primary key);
tgsql> insert into t values (1);
tgsql> select * from t;
[c0: DECIMAL]
[1]
tgsql> select * from t where c0 = 1e0;
[c0: DECIMAL]
UNSUPPORTED_RUNTIME_FEATURE_EXCEPTION (SQL-02050: unsupported type conversion to:decimal(5, 0) from:float8)

According to the specification, comparison operations between DECIMAL and DOUBLE PRECISION (float8) should implicitly convert the DECIMAL value to DOUBLE PRECISION before comparison. However, the current implementation results in an error.

TSURUGI-IS-891 Performing both INSERT and DELETE on the same row within the same transaction results in an inconsistent state.

Problem:
If the rows inserted in the transaction was deleted within the same transaction, the rows will unfortunately remain after the transaction may completed.

Note

Workaround:
Executing DELETE twice ensures that the record is properly deleted.

TSURUGI-IS-897 The result of numerical operations wraps around.

Problem:
If the result of numeric operations exceeds the range of their types, it gets cycled through without an error.

tgsql> select value from tb1;
[value: INT4]
[2147483647]

tgsql> select value + CAST(1 AS INT) from tb1;
[@#0: INT4]
[-2147483648]

TSURUGI-IS-933 Repeating certain syntax elements excessively in SQL can cause the database to become unstable.

Problem:
Repeating certain syntax elements excessively in SQL can cause the database to exhibit unstable behavior, including crashes.
The currently known examples are as follows:

• Specifying a large number of elements in the value list of an IN clause (e.g., x IN (1, 2, 3, ...))
• Specifying a large number of terms connected by OR in a conditional expression (e.g., x = 1 OR x = 2 OR x = 3 OR ...)

In simple cases, the compiler automatically detects these and then will report a compile error before the database becomes unstable.
However, in complex cases, the compiler may not detect them.

TSURUGI-IS-942 Cannot apply ORDER BY or LIMIT to the entire query expression.

Problem:
In SQL, the ORDER BY and LIMIT clauses can be used to specify the order and number of elements in the query results. However, they cannot be applied to the entire query expression that includes UNION ALL and similar constructs. Here is an example:

SELECT * FROM t1 UNION ALL SELECT * FROM t2 LIMIT 1

The above is expected to retrieve all rows from tables t1 and t2 and, return only upto one row from them. However, in the current implementation, it retrieves only 1 row from t2 and returns the result of adding it to the contents of t1. In other words, the total number of rows becomes (number of rows in t1) + 1.

Note

Workaround:
To get the expected results, retrieve the data in a table subquery, and then apply ORDER BY or LIMIT to the outer query. Here is an example:

SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) AS q LIMIT 1;

TSURUGI-IS-945 Executing COUNT(DISTINCT ...) on a large dataset cause the database to become unstable.

Problem:
Executing SELECT COUNT(DISTINCT ...) on a large dataset cause the database to become unstable, resulting in incorrect or no response.

TSURUGI-IS-959 Including COUNT(DISTINCT) and other aggregate functions in a query may result in an error.

Problem:
Including aggregate functions that operate on DISTINCT values, such as COUNT(DISTINCT ...), and aggregate functions that calculate with duplicates, such as MAX, in the same SELECT statement may result in an error.

tgsql> create table t (c0 int);
tgsql> select max(c0), count(distinct c0) from t;
[@#0: INT4, @#1: INT8]
UNSUPPORTED_RUNTIME_FEATURE_EXCEPTION (SQL-02050: failed to find function definition - it's likely aggregate functions with and without DISTINCT keyword are called in the same query, that is currently unsupported)

TSURUGI-IS-960 UNION ALL and LIMIT operations may perform inefficiently.

Problem:
When concatenating multiple query results using UNION ALL, the process can be inefficient internally.

Specifically, the SQL execution engine expands the operands of UNION ALL into memory and then concatenates them. This increases the amount of memory required to execute the query and can also slow down the overall response time.

LIMIT operations without ORDER BY clause have similar issues. For example, the SQL engine may scan whole the table even when trying to retrieve only one record with LIMIT 1. However, unlike UNION ALL, not all records are expanded into memory, and those exceeding the specified number are discarded.

TSURUGI-IS-139 Conditional expressions containing OR generate inefficient execution plans.

Problem:
When a conditional expression contains OR, it may generate an inefficient execution plan.
For example, executing the following SQL on a table t with a primary key key will generate an execution plan that performs a full table scan on t.

SELECT * FROM t WHERE key = 1 OR key = 2

Additionally, currently, the IN clause is internally expanded to OR, causing similar issues.

Note

Workaround:
You can achieve the same result by connecting each condition term with UNION (DISTINCT) or UNION ALL.

SELECT * FROM t WHERE key = 1
UNION ALL
SELECT * FROM t WHERE key = 2

TSURUGI-IS-681 MIN and MAX generate inefficient execution plans.

Problem:
Even when specifying the aggregate functions MAX and MIN for index key items, an execution plan involving a full scan is generated.
Ideally, these cases should operate quickly by retrieving only one item from the beginning or end of the index, but currently, such optimizations are not implemented.

This also applies to ORDER BY ... LIMIT ... (so-called TOP-N queries).

TSURUGI-IS-863 Cannot specify the join order in JOIN clauses.

Problem:
When performing JOIN operations with three or more tables, the SQL standard allows specifying the join order using syntax like t1 JOIN (t2 JOIN t3 ON ...) ON .... However, this syntax is not currently supported.

Note

Workaround:
Instead, you can use subqueries to explicitly specify the join order.

SELECT * FROM t1 JOIN (SELECT * FROM t2 JOIN t3 ON t2.key = t3.key) AS q(key2, value2, key3, value3) ON t1.key = q.key2

TSURUGI-IS-1011 Cannot specify column alias name defined by AS clause to ORDER BY

If the alias of the column specified in the AS clause is the target of ORDER BY, a compile error will occur as follows.

SELECT C AS COL from T ORDER BY COL;

SYMBOL_ANALYZE_EXCEPTION (SQL-03004: compile failed with error:variable_not_found message:"variable "COL" is not found, searching for {column_variable, field_variable, schema_variable}" location:<input>:1:33+3)

Note

Workaround:
To get the expected results, retrieve the data in a table subquery, and then apply ORDER BY to the outer query. Here is an example:

SELECT * FROM (SELECT C AS COL FROM T) AS T ORDER BY COL;