Historical Database

Troubleshooting the Historical Database¶

Each error below is the literal text the Center returns, paired with what causes it and how to correct the statement.

The errors are grouped by the operation that raises them. Every string is quoted exactly as the Center emits it, so it can be pasted into a search box and matched.

Creating a table¶

Error	Cause	Correction
`USE options not supported: PersistEngine`	`CREATE TABLE` without `PUBLIC`. A non-public table accepts no `USE` options at all, so the message names the option rather than the real cause	Add `PUBLIC`: `CREATE PUBLIC TABLE ...`
`Must have at least one NON-PARTITION column`	Every column was declared `PARTITION`	Leave at least one non-partition column
`Unexpected return value: class java.lang.String`	`USE` options separated by commas	Separate `USE` options with spaces, not commas
`MinOptimizePct must be between 0 to 1: 5`	`MinOptimizePct` set outside the range `0` to `1`	Use a fraction in `0` to `1` inclusive
`HISTORICAL does not support USE option: <name>`	A `USE` option other than the three accepted ones	Use only `PersistEngine`, `MinOptimizePct`, or `MaxOptimizeSeconds`. See Creating a Historical Table

Types and schema changes¶

Error	Cause	Correction
`Invalid type: BIGINT`	A type token that does not exist	Use an exact type token. The arbitrary-precision types are `BIGINTEGER` and `BIGDECIMAL`, spelled in full. See Data Types and Storage Modes
`Invalid type: note2`	`ALTER TABLE ... ADD COLUMN` was used. There is no `COLUMN` keyword, so the parser reads `COLUMN` as the column name and the next token as its type	Drop `COLUMN`: `ALTER TABLE t ADD note2 STRING`
`Invalid mode <mode> for <type>`	A storage mode was declared on a type it does not apply to — e.g. `Invalid mode BITMAP2 for LONG` (a bitmap mode on a fixed-width type), or `Invalid mode FLAT for STRING`. `FLAT`, `FLAT_NONULL`, and `FLAT_NOMIN` apply to fixed-width types; `BITMAP1` and `BITMAP2` to `STRING`; `VARSIZE3`, `VARSIZE4`, and `VARSIZE5` to variable-length types	Use a storage mode valid for the column's type, or omit the mode to take the default. See Data Types and Storage Modes
`Unknown option '<name>' for column <col>`	A storage-mode token that does not exist was declared on a column	Use a valid mode token, spelled exactly. See Data Types and Storage Modes
`Can only add/modify/drop PARTITION columns on empty tables. You must TRUNCATE TABLE first`	Adding, modifying, or dropping a partition column while the table holds rows	`TRUNCATE TABLE` first, or choose the partition column before loading data

Indexes¶

Error	Cause	Correction
`Can not create INDEX on PARTITION column: d`	Indexing a partition column	Partition pruning already covers the partition column; index a non-partition column instead
`Duplicate Index on column: <name> on <col>`	A second index on a column that already has one	One index per column
`Column not found for table <table>: <col>`	Indexing a column that is not on the table	Index an existing column
`Uncaught error: null <-- java.lang.NullPointerException`	An index was created on a `BOOLEAN` column, and the first insert then failed. The table is left unusable	Do not index a `BOOLEAN` column

Warning

An index on a nullable column does not raise an error. It silently changes the result of MIN() to null. See Querying; do not index a nullable column.

Querying and joins¶

Error	Cause	Correction
`Class not found: ZZ_FS_SRC`	A table alias was used in a join	Table aliases are not supported; use the full table name
`Unknown namespace variable: ZZ_FS_SRC.qty`	A `WHERE` clause in a join used a table-qualified column	The join `WHERE` must be unqualified: `col`, not `Table.col`. See Querying
`Multiple JOIN statements not supported: JOIN`	More than two historical tables in one join	A join takes at most two historical tables
`No LIKE operator: use ~~ for a filter match, or =~ for a regex`	`LIKE` was used	Use `~~` for a filter match or `=~` for a regex
`No such method: COUNT_UNIQUE(long)`	`COUNT_UNIQUE` was called	It does not exist
`Unhandled scenario: qty > 10 and ((qty * 2) > 40)`	Two predicates on one column, one of which wraps the column in an expression	Keep both predicates as plain comparisons, e.g. `qty>10 AND qty<100`
`Uncaught error: null <-- java.lang.NullPointerException`	A subquery in `IN`, such as `WHERE qty IN (SELECT ...)`	Rewrite without the nested subquery

Known issues¶

These issues are present in the current release:

Indexing a BOOLEAN column, or a query with a nested IN (SELECT ...), fails with an internal error instead of a clear message.