SELECT Query Rules Across Lindorm Engines - Lindorm

Queries data from a table.

Applicable engines

The SELECT syntax applies to LindormTable and LindormTSDB.

Limitations

SELECT supports only flattened (single-table) queries. Multi-table join queries (JOIN) and set queries (UNION, INTERSECT) are not supported.

Syntax

SELECT [hint_clause] ( select_clause | '*' )
    FROM table_name
    [ WHERE where_clause ]
    [ GROUP BY group_by_clause ]
    [ ORDER BY ordering_clause ]
    [ LIMIT integer [OFFSET integer] ] | [LIMIT integer, integer]
select_clause    ::=  selector [ AS identifier ] ( ',' selector [ AS identifier ] )
selector         ::=  column_name
                      | term
                      | function_name '(' [ selector ( ',' selector )* ] ')'
where_clause     ::=  relation ( AND|OR relation )*
relation         ::=  column_name operator term
                      |'(' column_name ( ',' column_name )* ')' operator tuple_literal
operator         ::=  '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | IS NOT? NULL | LIKE
group_by_clause  ::=  column_name ( ',' column_name )*
ordering_clause  ::=  column_name [ ASC | DESC ] ( ',' column_name [ ASC | DESC ] )*

hint_clause::=/*+hint_items*/
hint_items::=hint_item(','hint_item)*
hint_item::=identifier('('hint_option(','hint_option)*')')
hint_option::=expression

Usage notes

SELECT clause

The SELECT clause specifies the columns, constants, and function expressions to return. Use AS to define a temporary alias for a column in the result set.

WHERE clause

The WHERE clause specifies query conditions.

Wide table queries

Important

To avoid inefficient queries on wide tables, follow these rules:

Use only the first primary key column or the first index column of the index table as the query condition, OR
If you need to filter on any other column, set an upper and lower bound for the first primary key column or the first index column. Queries that violate both rules are flagged as inefficient and blocked by default. To force such a query to run, add /*+ _l_allow_filtering_ */. See Force an inefficient query.

The following example shows the difference between an efficient and an inefficient query on the sensor table, which has device_id as the first primary key column:

-- Allowed: first primary key column (device_id) is used as a condition
SELECT * FROM sensor WHERE device_id = 'F07A1260' AND humidity = 20;

-- Not allowed: no bound on device_id; filtering on a non-primary-key column only
-- This query is flagged as inefficient and blocked unless _l_allow_filtering_ is added.
SELECT * FROM sensor WHERE humidity = 20;

Dynamic columns

If dynamic columns are enabled for the wide table:

Include primary key or index columns in the WHERE clause to ensure query performance.
Conditions on dynamic columns in the WHERE clause must be HexStrings.

For more information, see Dynamic columns.

ORDER BY clause

Specifies one or more columns to sort the result set.

Important

Every column in the ORDER BY clause must also appear in the SELECT clause. Otherwise, a semantic check error occurs.
The ORDER BY clause does not support aliases.

LIMIT clause

The LIMIT clause controls how many rows are returned and supports paged queries.

Syntax	Behavior
`LIMIT n`	Returns `n` rows
`LIMIT n, m`	Skips `n` rows, then returns `m` rows
`LIMIT m OFFSET n`	Equivalent to `LIMIT n, m`

Important

Keep OFFSET at 5,000 or less. Even with an OFFSET, the query still retrieves the skipped rows internally, so large OFFSET values increase query overhead.
If dynamic columns are enabled, SELECT * requires a LIMIT clause to ensure correct metadata in the result set. The default maximum LIMIT for such queries is 5,000; exceeding this value returns an error.

HINT

LindormTable and LindormTSDB support hints to influence query plans or enable specific query behaviors. For details, see HINT overview.

Examples

The examples in this topic use the following table:

CREATE TABLE sensor (
    device_id   VARCHAR NOT NULL,
    region      VARCHAR NOT NULL,
    time        BIGINT  NOT NULL,
    temperature DOUBLE,
    humidity    BIGINT,
    PRIMARY KEY(device_id, region, time)
);

Simple queries

-- Select all columns
SELECT * FROM sensor;

-- Select specific columns
SELECT device_id, region, humidity FROM sensor;

-- Limit results with an offset (LIMIT 5, 20 is equivalent)
SELECT * FROM sensor WHERE device_id = 'F07A1260' LIMIT 20 OFFSET 5;

-- Define a column alias
SELECT count(*) AS countRow FROM sensor;

Spatiotemporal function examples:

-- Return the WKT representation of a geometry column
SELECT id, ST_AsText(g) FROM gps_data;

-- Return a buffered geometry
SELECT id, ST_Buffer(g, 1.0) AS buffer FROM gps_data;

Aggregate queries

Count all records for device F07A1260 at a specific time:

SELECT count(*) FROM sensor
WHERE device_id = 'F07A1260' AND time = '2021-04-22 15:33:00';

Get the maximum temperature for device F07A1260 over a time range:

SELECT max(temperature) AS max_temp FROM sensor
WHERE device_id = 'F07A1260'
  AND time >= '2021-04-22 15:33:00'
  AND time <  '2021-04-22 15:33:20';

Conditional queries

Query humidity for device F07A1260 where the region and temperature meet compound conditions, and humidity is not null:

SELECT device_id, humidity FROM sensor
WHERE ((region = 'north-cn' AND temperature > 15)
    OR (region = 'south-cn' AND temperature > 10))
  AND device_id = 'F07A1260'
  AND humidity IS NOT NULL;

Query data for multiple devices using IN:

SELECT * FROM sensor WHERE device_id IN ('F07A1260', 'F07A1261');

Spatiotemporal conditional queries — the following three spatial conditions support efficient execution:

-- ST_Contains: points inside a polygon
SELECT id FROM gps_data
WHERE ST_Contains(ST_GeomFromText('POLYGON((...))'), g);

-- ST_DWithin: points within a planar distance (unit: degrees)
SELECT id FROM gps_data
WHERE ST_DWithin(ST_GeomFromText('POINT(0 0)'), g, 100);

-- ST_DWithinSphere: points within a spherical distance (unit: meters)
SELECT id FROM gps_data
WHERE ST_DWithinSphere(ST_GeomFromText('POINT(0 0)'), g, 100);

Force an inefficient query

When LindormTable detects a SELECT statement as inefficient, it blocks the query by default and throws an exception. This protects overall system performance.

A query is considered inefficient when all of the following conditions are true:

The WHERE clause does not set an upper and lower bound for the first primary key column.
The WHERE clause does not set an upper and lower bound for the first index column of the index table.
The WHERE clause filters on a column that is neither the first primary key column nor the first index column.

To skip the check and force the query to run, add /*+ _l_allow_filtering_ */:

SELECT /*+ _l_allow_filtering_ */ * FROM sensor WHERE humidity = 20;

For guidance on resolving or avoiding inefficient queries, see How to resolve or avoid inefficient queries.