MaxCompute:SELECT syntax

Self-reference (not supported)

-- Incorrect: A cannot reference itself.
WITH
A AS (SELECT 1 FROM A)
SELECT * FROM A;
-- FAILED: ODPS-0130161:[1,6] Parse exception - recursive cte A is invalid, it must have an initial_part and a recursive_part, which must be connected by UNION ALL

Circular reference (not supported)

-- Incorrect: A references B, and B references A.
WITH
A AS (SELECT * FROM B),
B AS (SELECT * FROM A)
SELECT * FROM B;
-- FAILED: ODPS-0130071:[1,26] Semantic analysis exception - while resolving view B - [1,51]recursive function call is not supported, cycle is A->B->A

Sequential reference (supported)

WITH
A AS (SELECT 1 AS C),
B AS (SELECT * FROM A)
SELECT * FROM B;
-- Result:
+---+
| c |
+---+
| 1 |
+---+

Select specific columns by name

SELECT shop_name FROM sale_detail;
-- Result:
+------------+
| shop_name  |
+------------+
| s1         |
| s2         |
| s3         |
+------------+

Select all columns with *

-- Enable full table scan for the current session.
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail;
-- Result:
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Filter with WHERE

SELECT * FROM sale_detail WHERE shop_name='s1';
-- Result:
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Select columns by regular expression

Wrap the regular expression in backticks.

Select all columns whose names start with sh:

SELECT `sh.*` FROM sale_detail;
-- Result:
+------------+
| shop_name  |
+------------+
| s1         |
| s2         |
| s3         |
+------------+

Exclude shop_name and return all other columns:

SELECT `(shop_name)?+.+` FROM sale_detail;
-- Result:
+-------------+-------------+------------+------------+
| customer_id | total_price | sale_date  | region     |
+-------------+-------------+------------+------------+
| c1          | 100.1       | 2013       | china      |
| c2          | 100.2       | 2013       | china      |
| c3          | 100.3       | 2013       | china      |
+-------------+-------------+------------+------------+

Exclude multiple columns (shop_name and customer_id):

SELECT `(shop_name|customer_id)?+.+` FROM sale_detail;
-- Result:
+-------------+------------+------------+
| total_price | sale_date  | region     |
+-------------+------------+------------+
| 100.1       | 2013       | china      |
| 100.2       | 2013       | china      |
| 100.3       | 2013       | china      |
+-------------+------------+------------+

Exclude all columns whose names start with t:

SELECT `(t.*)?+.+` FROM sale_detail;
-- Result:
+------------+-------------+------------+------------+
| shop_name  | customer_id | sale_date  | region     |
+------------+-------------+------------+------------+
| s1         | c1          | 2013       | china      |
| s2         | c2          | 2013       | china      |
| s3         | c3          | 2013       | china      |
+------------+-------------+------------+------------+

When excluding multiple columns where one name is a prefix of another, place the longer name first in the expression. For example, if a table has partitions ds and dshh, use ` SELECT (dshh|ds)?+.+ FROM t; rather than SELECT (ds|dshh)?+.+ FROM t; `.

Remove duplicates with DISTINCT

DISTINCT returns unique values; ALL (the default) returns all rows including duplicates.

SELECT DISTINCT region FROM sale_detail;
-- Result:
+------------+
| region     |
+------------+
| china      |
+------------+

When applied to multiple columns, DISTINCT deduplicates based on the combined value of all listed columns:

SELECT DISTINCT region, sale_date FROM sale_detail;
-- Result:
+------------+------------+
| region     | sale_date  |
+------------+------------+
| china      | 2013       |
+------------+------------+

DISTINCT also works with window functions, removing duplicate rows from the window function result:

SET odps.sql.allow.fullscan=true;
SELECT DISTINCT sale_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_price) AS rn FROM sale_detail;
-- Result:
+-----------+------------+
| sale_date | rn         |
+-----------+------------+
| 2013      | 1          |
+-----------+------------+

DISTINCT and GROUP BY cannot be used in the same query. The following statement returns an error:

SELECT DISTINCT shop_name FROM sale_detail GROUP BY shop_name;
-- Error: GROUP BY cannot be used with SELECT DISTINCT

Query a named table

SELECT customer_id FROM sale_detail;
-- Result:
+-------------+
| customer_id |
+-------------+
| c1          |
| c2          |
| c3          |
+-------------+

Use a nested subquery as the source

SELECT * FROM (SELECT region, sale_date FROM sale_detail) t WHERE region = 'china';
-- Result:
+------------+------------+
| region     | sale_date  |
+------------+------------+
| china      | 2013       |
| china      | 2013       |
| china      | 2013       |
+------------+------------+

Filter with a partition range

SELECT *
FROM sale_detail
WHERE sale_date >= '2008' AND sale_date <= '2014';

-- Equivalent to:
SELECT *
FROM sale_detail
WHERE sale_date BETWEEN '2008' AND '2014';
-- Result:
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Run the EXPLAIN statement to verify that partition pruning takes effect. A user-defined function (UDF) or partition conditions in a JOIN ON clause may prevent pruning. For more information, see Check whether partition pruning is effective.

Use a UDF for partition pruning

A UDF in the WHERE clause runs as a small pre-job, and the result replaces the UDF in the original statement.

To enable UDF-based partition pruning, annotate the UDF class:

@com.aliyun.odps.udf.annotation.UdfProperty(isDeterministic=true)

com.aliyun.odps.udf.annotation.UdfProperty is defined in odps-sdk-udf.jar. Upgrade odps-sdk-udf to version 0.30.x or later.

Alternatively, add SET odps.sql.udf.ppr.deterministic = true; before the SQL statement to treat all UDFs in the statement as deterministic. This method backfills partitions with job results, up to a maximum of 1,000 partitions. If more than 1,000 partitions are backfilled, an error is returned. To suppress the error and disable UDF-based partition pruning, run SET odps.sql.udf.ppr.to.subquery = false;.

The UDF must be in the WHERE clause of the source table for pruning to take effect:

-- Correct: UDF in the WHERE clause of the source table.
SELECT key, value FROM srcp WHERE udf(ds) = 'xx';

-- Incorrect: UDF in a JOIN ON clause does not trigger partition pruning.
SELECT A.c1, A.c2 FROM srcp1 A JOIN srcp2 B ON A.c1 = B.c1 AND udf(A.ds) = 'xx';

Column aliases are not available in WHERE

If a column in SELECT_expr uses a function and is renamed with an alias, the alias cannot be referenced in the WHERE clause. The following statement returns an error:

-- Incorrect: skynet_id is an alias defined in SELECT, not available in WHERE.
SELECT  task_name,
        inst_id,
        settings,
        GET_JSON_OBJECT(settings, '$.SKYNET_ID') AS skynet_id,
        GET_JSON_OBJECT(settings, '$.SKYNET_NODENAME') AS user_agent
FROM    Information_Schema.TASKS_HISTORY
WHERE   ds = '20211215' AND skynet_id IS NOT NULL
LIMIT 10;

Group by a column

SELECT region FROM sale_detail GROUP BY region;
-- Result:
+------------+
| region     |
+------------+
| china      |
+------------+

Aggregate with GROUP BY

SELECT region, SUM(total_price) FROM sale_detail GROUP BY region;
-- Result:
+------------+------------+
| region     | _c1        |
+------------+------------+
| china      | 300.6      |
+------------+------------+

Use a SELECT alias in GROUP BY

SELECT region AS r FROM sale_detail GROUP BY r;
-- Equivalent to:
SELECT region AS r FROM sale_detail GROUP BY region;
-- Result:
+------------+
| r          |
+------------+
| china      |
+------------+

Group by a column expression

SELECT 2 + total_price AS r FROM sale_detail GROUP BY 2 + total_price;
-- Result:
+------------+
| r          |
+------------+
| 102.1      |
| 102.2      |
| 102.3      |
+------------+

Missing GROUP BY column returns an error

-- Incorrect: total_price is not in GROUP BY and not in an aggregate function.
SELECT region, total_price FROM sale_detail GROUP BY region;
-- Error: FAILED: ODPS-0130071:[1,16] Semantic analysis exception - column reference sale_detail.total_price should appear in GROUP BY key

-- Correct:
SELECT region, total_price FROM sale_detail GROUP BY region, total_price;
-- Result:
+------------+-------------+
| region     | total_price |
+------------+-------------+
| china      | 100.1       |
| china      | 100.2       |
| china      | 100.3       |
+------------+-------------+

GROUP BY ALL (BigQuery compatible mode)

When odps.sql.bigquery.compatible=true, GROUP BY ALL automatically groups by all non-aggregated columns in the SELECT list:

-- Explicitly list grouping fields.
SELECT
    shop_name,
    customer_id,
    sale_date,
    region,
    SUM(total_price) AS total_sales
FROM sale_detail
GROUP BY shop_name, customer_id, sale_date, region;

-- Equivalent using GROUP BY ALL.
SET odps.sql.bigquery.compatible=true;
SELECT
    shop_name,
    customer_id,
    sale_date,
    region,
    SUM(total_price) AS total_sales
FROM sale_detail
GROUP BY ALL;
-- Result:
+-----------+-------------+-----------+--------+-------------+
| shop_name | customer_id | sale_date | region | total_sales |
+-----------+-------------+-----------+--------+-------------+
| s1        | c1          | 2013      | china  | 100.1       |
| s2        | c2          | 2013      | china  | 100.2       |
| s3        | c3          | 2013      | china  | 100.3       |
+-----------+-------------+-----------+--------+-------------+

Use positional aliases in GROUP BY

Run SET odps.sql.groupby.position.alias=true; to treat integer constants in GROUP BY as column positions in the SELECT list:

SET odps.sql.groupby.position.alias=true;
-- 1 refers to the first column in the SELECT list (region).
SELECT region, SUM(total_price) FROM sale_detail GROUP BY 1;
-- Result:
+------------+------------+
| region     | _c1        |
+------------+------------+
| china      | 300.6      |
+------------+------------+

Sort ascending (default)

SELECT * FROM sale_detail ORDER BY total_price LIMIT 2;
-- Result:
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Sort descending

SELECT * FROM sale_detail ORDER BY total_price DESC LIMIT 2;
-- Result:
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s3         | c3          | 100.3       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Use a column alias in ORDER BY

SELECT total_price AS t FROM sale_detail ORDER BY t LIMIT 3;
-- Result:
+------------+
| t          |
+------------+
| 100.1      |
| 100.2      |
| 100.3      |
+------------+

Use positional aliases in ORDER BY

Run SET odps.sql.orderby.position.alias=true; to treat integer constants in ORDER BY as column positions:

SET odps.sql.orderby.position.alias=true;
-- 3 refers to the third column in the SELECT list (total_price).
SELECT * FROM sale_detail ORDER BY 3 LIMIT 3;
-- Result:
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Skip rows with OFFSET

ORDER BY...LIMIT m OFFSET n returns m rows starting after the first n rows. LIMIT n, m is an equivalent shorthand.

-- Sort by total_price ascending, skip the first 2 rows, return up to 3.
SELECT customer_id, total_price FROM sale_detail ORDER BY total_price LIMIT 3 OFFSET 2;

-- Equivalent:
SELECT customer_id, total_price FROM sale_detail ORDER BY total_price LIMIT 2, 3;
-- Result (only 1 row remains after skipping 2):
+-------------+-------------+
| customer_id | total_price |
+-------------+-------------+
| c3          | 100.3       |
+-------------+-------------+

Remove the LIMIT requirement for ORDER BY

Because ORDER BY runs on a single node, the LIMIT requirement prevents accidentally sorting very large datasets. To disable this requirement:

• Project level: Run SETPROJECT odps.sql.validate.orderby.limit=false;

• Session level: Submit SET odps.sql.validate.orderby.limit=false; together with the SQL statement.

Sorting large datasets on a single node consumes more resources and takes longer.

Accelerate global sorting with Range Clustering

In typical ORDER BY scenarios, all data must be processed on a single node. Range Clustering samples the data first, divides it into ranges, then sorts each range concurrently—producing a globally ordered result in parallel. For details, see Global sorting acceleration.

DISTRIBUTE BY + SORT BY (distributed sort)

-- Insert additional data to show multiple regions.
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai') VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);

-- Set 2 reducers.
SET odps.stage.reducer.num=2;

-- Hash-shard by region, then sort by total_price ascending within each shard.
SELECT region, total_price FROM sale_detail DISTRIBUTE BY region SORT BY total_price;
-- Result:
+------------+-------------+
| region     | total_price |
+------------+-------------+
| shanghai   | NULL        |
| shanghai   | 100.4       |
| shanghai   | 100.5       |
| china      | 100.1       |
| china      | 100.2       |
| china      | 100.3       |
+------------+-------------+

Sort descending within each shard:

SET odps.stage.reducer.num=2;
SELECT region, total_price FROM sale_detail DISTRIBUTE BY region SORT BY total_price DESC;
-- Result:
+------------+-------------+
| region     | total_price |
+------------+-------------+
| shanghai   | 100.5       |
| shanghai   | 100.4       |
| shanghai   | NULL        |
| china      | 100.3       |
| china      | 100.2       |
| china      | 100.1       |
+------------+-------------+

SORT BY without DISTRIBUTE BY

SORT BY alone sorts within each reducer independently. This does not produce a globally sorted result, but it improves the storage compression ratio and reduces disk reads during filtering, which can speed up subsequent global sorting operations.

SET odps.stage.reducer.num=2;
SELECT region, total_price FROM sale_detail SORT BY total_price DESC;
-- Result (order across reducers is not guaranteed):
+------------+-------------+
| region     | total_price |
+------------+-------------+
| shanghai   | 100.5       |
| shanghai   | 100.4       |
| china      | 100.3       |
| china      | 100.2       |
| china      | 100.1       |
| shanghai   | NULL        |
+------------+-------------+

Column aliases in ORDER BY, DISTRIBUTE BY, and SORT BY can be specified in Chinese.

Remove the screen display limit

If a SELECT statement has no LIMIT clause, or if LIMIT exceeds the project's display limit (n), the results window shows at most n rows.

• Data protection disabled: In odpscmd_config.ini, set use_instance_tunnel=true. Without instance_tunnel_max_record, the display is unlimited. With it, the display is capped by the value—up to 10,000 rows. For details, see Usage notes.

• Data protection enabled: The display is capped by READ_TABLE_MAX_ROW, up to 10,000 rows.

Run SHOW SecurityConfiguration; to check the ProjectProtection setting. If ProjectProtection=true, disable data protection with SET ProjectProtection=false; only if your project's requirements allow it. For more information, see Data protection mechanism.