Define and use wildcard columns - Lindorm

When a table schema has hundreds or thousands of columns that aren't fixed in advance — common in Internet of Vehicles (IoV) scenarios — defining every column up front isn't feasible. Adding a new column each time with ALTER TABLE is slow and operationally expensive.

Lindorm supports dynamic columns for writing and querying data without a fixed schema, but dynamic columns only accept VARBINARY data. Wildcard columns extend this by letting you declare a naming pattern and a target data type: any column written to the table whose name matches the pattern is automatically accepted and cast to that type.

How it works

A wildcard column is declared in the WITH clause using the wildcard_column property. The column name contains one or more wildcard characters:

Wildcard	Matches
`*`	A character sequence of any length, including empty
`?`	Exactly one character

For example, declaring c* INT means any column whose name starts with c — such as c10, c11, or c20 — is accepted and its value cast to INT.

At write time, Lindorm matches the incoming column name against the declared patterns. At query time, use the actual column names (not the wildcard patterns) in SELECT and WHERE clauses.

Prerequisites

LindormTable 2.5.3 or later. To check or upgrade your version, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

DDL

Create a table with wildcard columns

The following statement creates table tb with two wildcard columns: c2* (BIGINT) and c3* (VARCHAR). Both are declared in the WITH clause via wildcard_column.

CREATE TABLE tb (
  pk  INTEGER,
  c1  VARCHAR,
  `c2*` BIGINT,
  `c3*` VARCHAR,
  PRIMARY KEY (pk)
) WITH (wildcard_column='c2*,c3*');

After this table is created:

Any column whose name starts with c2 (for example, c2, c21, c22) is accepted. If its value is not BIGINT, it is cast to BIGINT.
Any column whose name starts with c3 (for example, c32, c33, c35) is accepted. If its value is not VARCHAR, it is cast to VARCHAR.

Create a search index on wildcard columns

Search indexes can be created on wildcard columns. The index covers all columns whose names match the wildcard patterns at write time.

CREATE INDEX idx USING SEARCH ON tb (`c2*`, `c3*`);

Secondary indexes cannot be created on wildcard columns. The following statement is not supported:

CREATE INDEX idx ON tb (c2*);

DML

Write data

Using the tb table created above, the following UPSERT statement writes a row with columns that match both wildcard patterns (c2, c21, c22 match c2*; c31 matches c3*):

UPSERT INTO tb (pk, c1, c2, c21, c22, c31)
VALUES (1, 'a1', 2, 21, 22, 'c3');

Each column in the statement must satisfy two conditions:

Its name matches one of the declared wildcard patterns.
Its value is compatible with the data type of the matching wildcard column (or can be cast to it).

Query data

Use actual column names — not wildcard patterns — in SELECT and WHERE clauses. The following query reads from the tb table created and populated above:

SELECT pk, c1, c2, c21, c31
FROM tb
WHERE c21 > 10 AND c31 < 'c300';

To query all columns in a table that contains wildcard columns, include a LIMIT clause:

SELECT * FROM tb LIMIT 10;

Limitations

Limitation	Detail
Primary keys	Wildcard columns cannot be used as primary keys.
`SELECT *`	Must include a `LIMIT` clause when the table has wildcard columns.
Index types	Only search indexes are supported. Secondary indexes are not supported.
Query conditions	Only actual column names can be used in `SELECT` lists and `WHERE` clauses. Wildcard patterns cannot be used as conditions.

The following statements use wildcard patterns as query conditions and are not supported:

-- Not supported: wildcard pattern in SELECT list
SELECT c1*, c2 FROM tb WHERE c2 > 10;

-- Not supported: wildcard pattern in WHERE clause
SELECT c1*, c2 FROM tb WHERE c1* > 10;

Use actual column names instead:

-- Supported
SELECT pk, c1, c2, c21, c31 FROM tb WHERE c21 > 10 AND c31 < 'c300';