Limits

DDL and DML statements are not supported

High-performance Search Edition does not support Data Definition Language (DDL) or Data Manipulation Language (DML) statements.

ORDER BY requires a LIMIT clause

Every ORDER BY clause must be paired with a LIMIT clause.

-- Supported
SELECT name, score FROM products ORDER BY score DESC LIMIT 20;

-- Not supported
SELECT name, score FROM products ORDER BY score DESC;

UNNEST does not support SELECT *

When querying a child table with UNNEST, you cannot use SELECT * to retrieve all fields. List the fields you need explicitly.

-- Supported
SELECT item.id, item.name FROM orders UNNEST(items) AS item;

-- Not supported
SELECT * FROM orders UNNEST(items) AS item;

JOIN runs only on the Searcher node

The JOIN operator runs exclusively on the Searcher node. Make sure the data you want to join is stored in locations that Searcher workers can access.

-- Supported: both tables accessible to Searcher workers
SELECT a.id, b.name
FROM table_a AS a
JOIN table_b AS b ON a.id = b.id
LIMIT 100;

-- Not supported: joining data not accessible to Searcher workers
SELECT a.id, b.name
FROM table_a AS a
JOIN remote_table AS b ON a.id = b.id;

UNION requires matching fields on both sides

When using UNION, the left and right operands must have the same field names, field types, and number of fields.

-- Supported
SELECT id, title FROM articles
UNION
SELECT id, title FROM drafts;

-- Not supported: field count differs
SELECT id, title FROM articles
UNION
SELECT id, title, created_at FROM drafts;

Numerical analysis indexes require QUERY() syntax

Numerical analysis indexes do not accept the key=value format. Use the QUERY() function with a range expression instead.

-- Supported
WHERE QUERY('consign_time', '[1, 10]')

-- Not supported
WHERE consign_time=5