FAQ about DQL operations

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

  You cannot use a constant after `SELECT DISTINCT`.

• Solution

  You can 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

  You cannot directly reference a column that is not in the `GROUP BY` clause. For more information, see GROUP BY clause (col_list).

• Solution

  Modify the SQL statement to ensure that when you use a `GROUP BY` clause, the 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 data in the same column is similar, the compression ratio is high. When odps.sql.groupby.skewindata=true is enabled, data is scattered, and the compression ratio is low. To improve the compression ratio, you can perform a local sort when you use an SQL statement to write data.

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, it does not. `GROUP BY` has no limit on data volume.

How is the data returned by a MaxCompute query sorted?

Data is read from tables in MaxCompute in an undefined order. If you do not specify a sort order, the query results are also unordered.

If you need the data to be in a specific order, add an order by xx limit n clause to your SQL statement to sort the data.

To perform a full sort on your data, set the value of the limit parameter n to total number of records + 1.

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. Therefore, a `LIMIT` clause is required by default to prevent a single node from processing a large amount of data.

• Solution

  If your scenario requires `ORDER BY` without a `LIMIT` clause, you can disable this requirement in one of the following two 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

    If you disable the requirement that an order by clause must be used with a limit clause, sorting a large amount of data on a single execution node degrades performance metrics such as resource consumption and processing time.

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?

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?

You can use the union all operation for a vertical merge. For a horizontal merge, you can 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, causing the SQL execution to fail. The length of the execution plan is not directly proportional to the length of the SQL statement and therefore cannot be estimated.

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

  • Cause 3: There are too many small files.

• Solutions

  • Solution for Cause 1: Split the long SQL statement to avoid exceeding the length limit.

  • Solution for Cause 2: Adjust the number of partitions. For more information, see Partition.

  • Solution for Cause 3: For more information, see FAQ about small file optimization and job diagnostics

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 message 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 cannot exceed 16 tables.

• Solution

  You can join some of the small tables into a temporary table. 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 items are found 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. You can use the following sample command to check for duplicates:

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

  If you do not want to perform a Cartesian product, you can 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 is an optimization technique that accelerates queries by caching small tables in memory. You can specify table aliases in the MAPJOIN hint.

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

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

| sepal_length    | double     |       |                                             |

| sepal_width     | double     |       |                                             |

| petal_length    | double     |       |                                             |

| petal_width     | double     |       |                                             |

| category        | string     |       |                                             |

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

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, they can. The system distinguishes between the large and small tables based on the amount of space they occupy. The system then loads the small table into memory to accelerate the JOIN operation.

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?

First, filter the data by the partition field, and then by other non-partition fields. The input data volume is calculated after the data is filtered by the partition field.

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

Yes. For example, you can use select * from user_info where address rlike '[0-9]{9}'; to find records that contain a nine-digit ID.

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. You can first use an SQL statement to select 100 records and then perform the sync operation.

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

You can improve query efficiency by partitioning a table with its partition fields. This lets you add, update, or read data in a specific partition without performing a full table scan. For more information, see Table operations.

Does MaxCompute SQL support the WITH AS statement?

Yes, it does. 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?

You can use LATERAL VIEW with table-generating functions, such as Split and Explode, to split one row of data into multiple rows and 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?

You must change use_instance_tunnel=false to use_instance_tunnel=true for instance_tunnel_max_record to control the number of output records.

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

select 'field' rlike '[\\x{4e00}-\\x{9fa5}]+';