Limitations and Workarounds in This Release of Splice Machine
This topic describes workarounds for known limitations in this release of the Splice Machine Database. These can include previously unstated limitations or workarounds for problems that will be fixed in a future release.
These are the notes and workarounds for known issues in this release:
With Clauses and Temporary Tables
You cannot currently use temporary tables in
Natural Self Joins Not Supported
Splice Machine does not currently support
NATURAL SELF JOIN operations.
Temporary Tables and Backups
There’s a subtle issue with performing a backup when you’re using a temporary table in your session: although the temporary table is (correctly) not backed up, the temporary table’s entry in the system tables will be backed up. When the backup is restored, the table entries will be restored, but the temporary table will be missing.
There’s a simple workaround:
- Exit your current session, which will automatically delete the temporary table and its system table entries.
- Start a new session (reconnect to your database).
- Start your backup job.
Columnar Screen Output Gets Truncated
Explain Plan and other commands that generate lengthy output lines, you may see some output columns truncated on the screen.
WORKAROUND: Use the
maximumdisplaywidth=0 command to force all column contents to be displayed.
TimeStamp Date Value limitations
Dates in TIMESTAMP Data Type values only work correctly when limited to this range of date values:
1678-01-01 to 2261-12-31
ToDate Function Problem With DD Designator
The TO_DATE function currently returns the wrong date if you specify
DD for the day field; however, specifying
dd instead works properly.
dd instead of
Dropping Foreign Keys
DROP FOREIGN KEY clause of the ALTER TABLE statement is currently unavailable in Splice Machine.
WORKAROUND: Re-create the table without the foreign key constraint.
Compaction Queue Issue
We have seen a problem in which the compaction queue grows quite large after importing large amounts of data, and are investigating a solution; for now, please use the following workaround.
Run a full compaction on tables into which you have imported a large amount of data, using the SYSCS_UTIL.SYSCS_PERFORM_MAJOR_COMPACTION_ON_TABLE system procedure.</div>
Alter Table Issues
ALTER TABLE against
PRIMARY KEY columns does not currently work properly.
Default Value for Lead and Lag Functions
This release of Splice Machine features several new window functions, including LAG do not support the default value parameter that you can specify in some other implementations. We expect to add this parameter in future releases.
CREATE TABLE AS with RIGHT OUTER JOIN
There is a known problem using the
CREATE TABLE AS form of the RIGHT OUTER JOIN operation. For example, the following statement currently produces a table with all
CREATE TABLE t3 AS SELECT t1.a,t1.b,t2.c,t2.d FROM t1 RIGHT OUTER JOIN t2 ON t1.b = t2.c WITH DATA;
There’s a simple workaround for now: create the table without inserting the data, and then insert the data; for example:
CREATE TABLE t3 AS SELECT t1.a,t1.b,t2.c,t2.d FROM t1 RIGHT OUTER JOIN t2 ON t1.b = t2.c WITH NO DATA; INSERT INTO t3 SELECT t1.a,t1.b,t2.c,t2.d FROM t1 RIGHT OUTER JOIN t2 ON t1.b = t2.c;
Import Performance Issues With Many Foreign Key References
The presence of many foreign key references on a table will slow down imports of data into that table.