MOT SQL Coverage and Limitations

MOT design enables almost complete coverage of SQL and future feature sets. For example, standard Postgres SQL is mostly supported, as well common database features, such as stored procedures and user defined functions.

The following describes the various types of SQL coverages and limitations –

Unsupported Features

The following features are not supported by MOT –

Isolation – SERIALIZABLE isolation is not supported.
Query Native Compilation (JIT) – Limited SQL coverage.
LOCAL memory is limited to 1 GB. A transaction can only change data of less than 1 GB.
Capacity (Data+Index) is limited to available memory.
No full-text search index.
Do not support Logical copy.
SAVEPOINT – not supported.

In addition, the following are detailed lists of various general limitations of MOT tables, MOT indexes, Query and DML syntax and the features and limitations of Query Native Compilation.

MOT Table Limitations

The following lists the functionality limitations of MOT tables –

Partitioning
AES encryption, row-level access control, dynamic data masking
Stream operations
User-defined types
Sub-transactions – supported only in the context of statement blocks inside stored procedures with the following limitation: MOT cannot recover from a Sub-Transaction containing operations other then SELECT, only read-only rollback is allowed. In such case, the parent transaction is aborted.
DML triggers
DDL triggers
Collations other than “C” or “POSIX”

Unsupported Table DDLs

CREATE FOREIGN table LIKE - Limited support, LIKE can any table (MOT and Heap tables), but without any options, data or indexes.
Create table as select
Partition by range
Create table with no-logging clause
DEFERRABLE primary key
Reindex
Tablespace
Create schema with subcommands

Unsupported Data Types

UUID
User-Defined Type (UDF)
Array data type
NVARCHAR2(n)
Clob
Name
Blob
Raw
Path
Circle
Reltime
Bit varying(10)
Tsvector
Tsquery
JSON
Box
Text
Line
Point
LSEG
POLYGON
INET
CIDR
MACADDR
Smalldatetime
BYTEA
Bit
Varbit
OID
Money
Any unlimited varchar/character varying
HSTORE
XML
Int16
Abstime
Tsrange
Tstzrange
Int8range
Int4range
Numrange
Daterange
HLL

UnsupportedIndex DDLs and Index

Create index on decimal/numeric
Create index on nullable columns
Create index, index per table > 9
Create index on key size > 256
The key size includes the column size in bytes + a column additional size, which is an overhead required to maintain the index. The below table lists the column additional size for different column types.
Additionally, in case of non-unique indexes an extra 8 bytes is required.
Thus, the following pseudo code calculates the key size:
```
keySize =0;

for each (column in index){
      keySize += (columnSize + columnAddSize);
}
if (index is non_unique) {
      keySize += 8;
}
```
Column Type
Column Size
Column Additional Size
varchar
N
4
tinyint
1
1
smallint
2
1
int
4
1
bigint
8
1
float
4
2
float8
8
3
Types that are not specified in above table, the column additional size is zero (for instance timestamp).

Column Type	Column Size	Column Additional Size
varchar	N	4
tinyint	1	1
smallint	2	1
int	4	1
bigint	8	1
float	4	2
float8	8	3

Unsupported DMLs

Merge into
Lock table
Copy from table
Upsert

Unsupported JIT features (Native Compilation and Execution)

JIT SP (Stored Procedures Compilation) – available to SPs accessing only MOT tables.
The query refers to more than two tables
The query has any one of the following attributes –
- Aggregation on non-primitive types
- Window functions
- Sub-query sub-links
- Distinct-ON modifier (distinct clause is from DISTINCT ON)
- Recursive (WITH RECURSIVE was specified)
- Modifying CTE (has INSERT/UPDATE/DELETE in WITH)

In addition, the following clauses disqualify a query from lite execution –

Returning list
Group By clause
Grouping sets
Having clause
Windows clause
Distinct clause
Sort clause that does not conform to native index order - is supported, however all sort columns must be present in the SELECT.
Set operations
Constraint dependencies