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
- Temporary Tables and Backups
- Natural Self Joins Not Supported
- Columnar Screen Output Gets Truncated
- TimeStamp Date Value Limitations
- ToDate Function Problem With DD Designator
- Dropping Foreign Keys
- Compaction Queue Issue
- Alter Table Issues
- Default Value for Lead and Lag Functions
- CREATE TABLE AS with RIGHT OUTER JOIN
- Importing Data with SYSCS_IMPORT_DATA from Amazon S3
- HDP 2.6.4 and 2.6.3 OLAP Memory Setting
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.
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.
Importing Data with SYSCS_IMPORT_DATA from Amazon S3
There are currently two platform-dependent issue for importing data using the SYSCS_UTIL.IMPORT_DATA system procedure from Amazon S3 in this release. You only need to pay attention to this note if you’re using HDP 2.5.5 or MapR 5.2.0:
Add a custom setting to your
These changes may impact non-SpliceMachine services or applications that rely on an implementation other than the Presto implementation; for example,
HDP 2.6.4 and 2.6.3 OLAP Memory Setting
If you’re using Splice Machine on HDP 2.6.4 or HDP 2.6.3, you need to correct a property setting in the
HBase Service Advanced Configuration Snippet (Safety Valve) settings for
server in this property name:
_ character to produce the correct property name:
Note that the
. character needs to be changed to the
_ character in