Limits

ORDER BY requires LIMIT

Always pair ORDER BY with a LIMIT clause. Queries that include ORDER BY without LIMIT are rejected.

-- Rejected
SELECT id, price FROM products ORDER BY price DESC;

-- Accepted
SELECT id, price FROM products ORDER BY price DESC LIMIT 10;

UNNEST does not support SELECT *

When querying a child table with UNNEST, explicitly list the fields you need in SELECT. Using * to select all fields is not supported because the engine cannot statically determine the schema of an unnested result set.

-- Rejected
SELECT * FROM table UNNEST(items) AS t;

-- Accepted
SELECT t.item_id, t.item_name FROM table UNNEST(items) AS t;

Use QUERY() for date and range filters

Dates and value ranges cannot be specified in key=value format. Use the QUERY() function instead.

-- Rejected
WHERE consign_time = '[1, 10]'

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

JOIN runs on Searcher nodes only

The JOIN operator executes on Searcher nodes only. The data you want to join must be stored where Searcher workers can access it. Joins against data outside Searcher node storage will fail at query time.

-- Ensure joined data is accessible to Searcher workers before running:
SELECT a.order_id, b.customer_name
FROM orders AS a
JOIN customers AS b ON a.customer_id = b.id
LIMIT 100;

UNION requires matching field definitions

Both operands of a UNION must have identical field names, field types, and field count. A mismatch in any of these causes the query to be rejected.

-- Rejected: field count mismatch
SELECT id, name FROM table_a
UNION
SELECT id, name, status FROM table_b;

-- Accepted: same fields, same types, same count
SELECT id, name FROM table_a
UNION
SELECT id, name FROM table_b;

Unsigned data types are not supported in V3.0

SQL queries in OpenSearch Retrieval Engine Edition V3.0 do not support the unsigned data type. When configuring a schema, avoid specifying fields with an unsigned type. OpenSearch Retrieval Engine Edition may fail to convert unsigned values to values of supported data types.