DocsICP FilingConsole
官方文档
首页

FAQ about DQL operations

更新时间：
复制 MD 格式
产品详情
我的收藏

Frequently asked questions (FAQ) about Data Query Language (DQL) operations in MaxCompute, including GROUP BY, ORDER BY, JOIN, MAPJOIN, subqueries, and set operations.

Category

FAQ

GROUP BY

ORDER BY

Subqueries

When I run a MaxCompute SQL statement with a NOT IN subquery, the subquery returns tens of thousands of data records. However, if the subquery after IN or NOT IN returns partitions, the maximum number of returned partitions is 1,000. How can I implement this query if I must use NOT IN?

Intersection, union, and complement

JOIN

MAPJOIN

Others

How do I resolve the "Repeated key in GROUP BY" error that occurs when I run a MaxCompute SQL statement?

  • Problem

    When you run a MaxCompute SQL statement, the following error is returned:

    FAILED: ODPS-0130071:Semantic analysis exception - Repeated key in GROUP BY.

  • Cause

    A constant is used after `SELECT DISTINCT`, which is not allowed.

  • Solution

    Split the SQL statement into two layers. The inner layer handles the `DISTINCT` logic without constants, and the outer layer adds the constant data.

How do I resolve the "Expression not in GROUP BY key" error that occurs when I run a MaxCompute SQL statement?

  • Problem

    When you run a MaxCompute SQL statement, the following error is returned:

    FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 1:xx ‘xxx’

  • Cause

    A column that is not included in the `GROUP BY` clause is directly referenced. For more information, see GROUP BY clause (col_list).

  • Solution

    Ensure that columns in the `SELECT` list are either columns from the `GROUP BY` clause or columns processed by aggregate functions, such as `SUM` or `COUNT`.

I run GROUP BY on Table A to generate Table B. Table B has fewer rows than Table A, but its physical storage is 10 times larger. Why does this happen?

MaxCompute uses columnar compression for storage. If consecutive values in the same column are similar, the compression ratio is high. When odps.sql.groupby.skewindata=true is enabled, data is scattered, resulting in a lower compression ratio. To improve compression, perform a local sort when writing data with an SQL statement.

Does running a GROUP BY query on 10 billion data records affect performance? Is there a limit on the data volume for GROUP BY?

No. `GROUP BY` has no limit on data volume.

How is the data returned by a MaxCompute query sorted?

Data is read from MaxCompute tables in an undefined order. Without a sort clause, query results are also unordered.

To sort the data, add an order by xx limit n clause to your SQL statement.

For a full sort, set the limit value n to total number of records + 1.

Important

A full sort on a large dataset significantly affects performance and can cause out-of-memory errors. Avoid this operation whenever possible.

Does MaxCompute support the ORDER BY FIELD NULLS LAST syntax?

MaxCompute supports this syntax. For more information, see Differences from other SQL syntaxes.

How do I resolve the "ORDER BY must be used with a LIMIT clause" error that occurs when I run a MaxCompute SQL statement?

  • Problem

    When you run a MaxCompute SQL statement, the following error is returned:

    FAILED: ODPS-0130071:[1,27] Semantic analysis exception - ORDER BY must be used with a LIMIT clause, please set odps.sql.validate.orderby.limit=false to use it.

  • Cause

    `ORDER BY` performs a global sort on a single execution node, so a `LIMIT` clause is required by default to prevent excessive data processing on that node.

  • Solution

    If your scenario requires `ORDER BY` without a `LIMIT` clause, disable this requirement in one of the following ways:

    • At the project level: Run the setproject odps.sql.validate.orderby.limit=false; command to disable the requirement that order by must be used with a limit clause.

    • At the session level: Run the set odps.sql.validate.orderby.limit=false; command to disable the requirement that order by must be used with a limit clause. This command must be submitted together with the SQL statement.

      Note

      Disabling the order by-limit requirement means sorting a large dataset on a single execution node, which degrades performance and increases resource consumption.

For more information about `ORDER BY`, see ORDER BY clause (ORDER_condition).

When I run a MaxCompute SQL statement with a NOT IN subquery, the subquery returns tens of thousands of data records. However, if the subquery after IN or NOT IN returns partitions, the maximum number of returned partitions is 1,000. How can I implement this query if I must use NOT IN?

Rewrite the query using a left outer join:

select * from a where a.ds not in (select ds from b);
Change the statement to the following:
select a.* from a left outer join (select distinct ds from b) bb on a.ds=bb.ds where bb.ds is null;

How do I merge two tables that have no association?

For a vertical merge, use union all. For a horizontal merge, use the row_number function to add an ID column to both tables, join them on the ID, and then select the required fields. For more information, see Union or ROW_NUMBER.

How do I resolve the "ValidateJsonSize error" that occurs during a UNION ALL operation?

  • Symptoms

    When you run an SQL statement that contains 200 UNION ALL operations, such as select count(1) as co from client_table union all ..., the following error occurs:

    FAILED: build/release64/task/fuxiWrapper.cpp(344): ExceptionBase: Submit fuxi Job failed, {
    "ErrCode": "RPC_FAILED_REPLY",
    "ErrMsg": "exception: ExceptionBase:build/release64/fuxi/fuximaster/fuxi_master.cpp(1018): ExceptionBase: StartAppFail: ExceptionBase:build/release64/fuxi/fuximaster/app_master_mgr.cpp(706): ExceptionBase: ValidateJsonSize error: the size of compressed plan is larger than 1024KB\nStack

  • Causes

    • Cause 1: The execution plan exceeds the 1024 KB limit of the underlying architecture. The length of the execution plan is not directly proportional to the SQL statement length and cannot be estimated in advance.

    • Cause 2: The number of partitions is too large.

    • Cause 3: There are too many small files.

  • Solutions

How do I resolve the "Both left and right aliases encountered in JOIN" error that occurs during a JOIN operation?

  • Problem

    When you run a MaxCompute SQL statement, the following error is returned:

    FAILED: ODPS-0130071:Semantic analysis exception - Both left and right aliases encountered in JOIN : line 3:3 ‘xx’: . I f you really want to perform this join, try mapjoin

  • Causes

    • Cause 1: A non-equi-join is specified in the ON clause, such as table1.c1>table2.c3.

    • Cause 2: One side of the JOIN condition references columns from both tables, such as table1.col1 = concat(table1.col2,table2.col3).

  • Solutions

    • Solution for Cause 1: Modify the SQL statement. The join condition must be an equi-join.

      Note

      If you must use a non-equi-join, you can add a mapjoin hint. For more information, see ODPS-0130071.

    • Solution for Cause 2: If one of the tables is small, you can use the MAPJOIN method.

How do I resolve the "Maximum 16 join inputs allowed" error that occurs during a JOIN operation?

  • Symptoms

    When you run a MaxCompute SQL statement, the following error is returned:

    FAILED: ODPS-0123065:Join exception - Maximum 16 join inputs allowed

  • Cause

    In MaxCompute SQL, a MAPJOIN operation supports a maximum of six small tables, and a single JOIN operation supports a maximum of 16 tables.

  • Solution

    Join some of the small tables into a temporary table first. This reduces the number of input tables.

During a JOIN operation, the number of data records in the result is larger than that in the original table. How do I fix this?

  • Symptoms

    After you run the following MaxCompute SQL statement, the number of records in the query result is greater than the number of records in table1.

    select count(*) from table1 a left outer join table2 b on a.ID = b.ID;

  • Cause

    A left outer join returns all records from table1, even if no matching records exist in table2. If table2 contains duplicate IDs, the number of returned records increases. For example:

    Assume that `table1` contains the following data.

    id

    values

    1

    a

    1

    b

    2

    c

    Assume that `table2` contains the following data.

    id

    values

    1

    A

    1

    B

    3

    D

    The select count(*) from table1 a left outer join table2 b on a.ID = b.ID; command returns the following result.

    id1

    values1

    id2

    values2

    1

    b

    1

    B

    1

    b

    1

    A

    1

    a

    1

    B

    1

    a

    1

    A

    2

    c

    NULL

    NULL

    • Records with `id=1` exist in both tables. A Cartesian product is performed, and four records are returned.

    • The record with `id=2` exists only in `table1`. One record is returned.

    • Records with `id=3` exist only in `table2`. No records are returned because there are no matching records in `table1`.

  • Solution

    Check whether `table2` contains duplicate IDs:

    select id, count(*) as cnt from table2 group by id having cnt>1 limit 10;

    To avoid the Cartesian product, rewrite the SQL statement as follows:

    select * from table1 a left outer join (select distinct id from table2) b on a.id = b.id;

Why is a full table scan prohibited in a JOIN operation even if a partition condition is specified?

  • Problem

    When the same code is executed in two projects, it succeeds in one project but fails in the other.

    select t.stat_date 
from fddev.tmp_001 t  
left outer join (select '20180830' as ds from fddev.dual ) t1 
on t.ds = 20180830
group by t.stat_date;

    The failed execution reports the following error:

    Table(fddev,tmp_001) is full scan with all partitions,please specify partitions predicates.

  • Cause

    When you perform a SELECT operation, the partition condition must be in the WHERE clause. Using the ON clause for this purpose is non-standard.

    The execution succeeded in one project because it was configured with the set odps.sql.outerjoin.supports.filters=false command. This command converts the condition in the ON clause into a filter condition. This behavior is compatible with Hive syntax but does not comply with the SQL standard.

  • Solution

    Place the partition filter condition in the WHERE clause.

For a JOIN operation, does partition pruning take effect if the partition pruning condition is in the ON clause or in the WHERE clause?

  • If the partition pruning condition is in the WHERE clause, partition pruning takes effect.

  • If the condition is in the ON clause, partition pruning takes effect on the details table but not on the primary table. This results in a full table scan of the primary table.

For more information about partition pruning, see Evaluate the validity of partition pruning.

How do I use MAPJOIN to cache multiple small tables?

MAPJOIN accelerates queries by caching small tables in memory. Specify table aliases in the MAPJOIN hint.

Assume that a table named iris exists in the project. The table data is as follows.

+——————————————————————————————————————————+

| Field           | Type       | Label | Comment                                     |
+——————————————————————————————————————————+

| sepal_length    | double     |       |                                             |

| sepal_width     | double     |       |                                             |

| petal_length    | double     |       |                                             |

| petal_width     | double     |       |                                             |

| category        | string     |       |                                             |

+——————————————————————————————————————————+

The following sample command uses MAPJOIN to cache small tables.

select 
  /*+ mapjoin(b,c) */
  a.category,
  b.cnt as cnt_category,
  c.cnt as cnt_all
from iris a
join
(
  select count(*) as cnt,category from iris group by category
) b
on a.category = b.category
cross join 
(
  select count(*) as cnt from iris
) c;

Can the large and small tables in a MAPJOIN be swapped?

Yes. The system distinguishes between large and small tables based on storage size and loads the small table into memory to accelerate the JOIN operation.

Important

Swapping the tables does not cause an error, but performance degrades.

After I set a filter condition for a MaxCompute SQL statement, an error indicates that the input data exceeds 100 GB. How do I fix this?

Filter the data by the partition field first, then by other non-partition fields. The input data volume is calculated after partition-level filtering.

Does the WHERE clause for fuzzy queries in MaxCompute SQL support regular expressions?

Yes. For example, select * from user_info where address rlike '[0-9]{9}'; finds records that contain a nine-digit number.

If I want to sync only 100 data records, how can I use LIMIT in the WHERE filter condition?

LIMIT is not supported in a filter condition. Use an SQL statement to select 100 records first, then perform the sync operation.

How can I improve query efficiency? Can I adjust the partition settings?

Partition a table by its partition fields to enable adding, updating, or reading data in specific partitions without a full table scan. For more information, see Table operations.

Does MaxCompute SQL support the WITH AS statement?

Yes. MaxCompute supports standard SQL Common Table Expressions (CTEs) to improve readability and execution efficiency. For more information, see COMMON TABLE EXPRESSION (CTE).

How do I split one row of data into multiple rows?

Use LATERAL VIEW with table-generating functions such as Split and Explode to split one row into multiple rows, then aggregate the resulting data.

In the odps_config.ini file on the client, I set use_instance_tunnel=false and instance_tunnel_max_record=10. Why does a SELECT statement still output many records?

Change use_instance_tunnel=false to use_instance_tunnel=true for the instance_tunnel_max_record setting to take effect.

How do I use a regular expression to determine if a field contains Chinese characters?

Example:

select 'field' rlike '[\\x{4e00}-\\x{9fa5}]+';
Previous：FAQ about DML operationsNext：FAQ about built-in functions