MaxCompute:Rewriting incompatible SQL

Background information

MaxCompute V2.0 fully embraces the open source ecosystem. It supports more language features and runs faster. However, MaxCompute V2.0 performs stricter syntax checks. Queries with imprecise syntax that ran successfully in earlier compiler versions may cause errors in MaxCompute V2.0.

group.by.with.star

bad.escape

Description: This issue is related to incorrect escape sequences.

According to MaxCompute rules, you must use a backslash followed by a three-digit octal number to represent ASCII characters from 0 to 127 in a string literal. For example, you can use "\001" and "\002" to represent 0 and 1. However, earlier versions also processed \01 and \0001 as \001.

column.repeated.in.creation

Description: In MaxCompute V2.0, an error occurs if you use duplicate column names when you create a table.

string.join.double

window.ref.prev.window.alias

Description: This issue occurs when a window function references an alias of another window function in the same `SELECT` list.

select.invalid.token.after.star

Description: In a `SELECT` list, you can use an asterisk (*) to select all columns from a table. However, you cannot add an alias after the asterisk (*). This syntax is not allowed even if the asterisk (*) expands to only one column. The MaxCompute V2.0 compiler reports an error for this syntax.

agg.having.ref.prev.agg.alias

Description: This issue occurs when the `SELECT` list references an alias of a preceding aggregate function and a `HAVING` clause is present.

order.by.no.limit

Description: By default, MaxCompute requires you to add a limit clause after an order by clause to limit the number of returned results. This requirement exists because order by performs a full sort, which has low execution performance if a limit clause is not used.

Add a limit clause to the order by v.uv desc subquery.

create view table_view as select id from table_view order by id;

To access this view:

select * from table_view;

MaxCompute V1.0 does not report an error. MaxCompute V2.0 reports the following error message:

FAILED: ODPS-0130071:[1,15] Semantic analysis exception - while resolving view xdj.xdj_view_limit - ORDER BY must be used with a LIMIT clause

generated.column.name.multi.window

Description: This issue is related to the use of automatically generated aliases.

Earlier versions of MaxCompute automatically generate an alias for each expression in a `SELECT` statement. This alias is displayed in the console. However, the rules for generating this alias are not guaranteed and may change. Therefore, you should not use automatically generated aliases.

MaxCompute V2.0 issues a warning when automatically generated aliases are used. This practice cannot be prohibited at the moment because it is widely used.

In some cases, the rules for generating aliases change between MaxCompute versions. Because some online jobs rely on these aliases, these queries may fail during a MaxCompute version upgrade or rollback. If you encounter this issue, you must modify your queries to explicitly specify aliases for the columns that you want to use.

non.boolean.filter

Issue with using a non-BOOLEAN filter condition.

MaxCompute does not allow implicit conversions between Boolean types and other data types. However, earlier versions of MaxCompute allow you to use `BIGINT` as a filter condition in some cases. MaxCompute V2.0 no longer allows this behavior. If your scripts contain such filter conditions, you must modify them. An example is as follows:

Incorrect syntax:

select id, count(*) from table_name group by id having id;

Error message:

FAILED: ODPS-0130071:[1,50] Semantic analysis exception - expect a BOOLEAN expression

The correct method is as follows:

select id, count(*) from table_name group by id having id <> 0;

post.select.ambiguous

Issue with referencing columns with conflicting names in `order by`, `cluster by`, `distribute by`, or `sort by` statements.

In earlier versions of MaxCompute, the system selects the last column in the `SELECT` list as the object of the operation by default. MaxCompute V2.0 reports an error. You must modify your statements. An example is as follows:

Incorrect syntax:

select a, b as a from t order by a limit 10;

Error message:

FAILED: ODPS-0130071:[1,34] Semantic analysis exception - a is ambiguous, can be both t.a or null.a

The following is the correct modification:

select a as c, b as a from t order by a limit 10;

This change also covers cases where names conflict but the semantics are the same. Although this does not cause ambiguity, a warning is reported to encourage you to make corrections because this syntax can easily lead to errors.

duplicated.partition.column

Issue with specifying partitions with the same name in a query.

Earlier versions of MaxCompute did not report an error when you specified a partition key with the same name. Instead, the value of the latter key overwrote the value of the former key. This behavior could easily cause confusion. MaxCompute V2.0 reports an error for this case. The following examples show the issue:

Incorrect syntax 1:

insert overwrite table partition (ds = '1', ds = '2'）select ... ;

At runtime, ds = ‘1’ is ignored.

The correct method is as follows:

insert overwrite table partition (ds = '2'）select ... ;

Incorrect syntax 2:

create table t (a bigint, ds string) partitioned by (ds string);

Correct procedure:

create table t (a bigint) partitioned by (ds string);

order.by.col.ambiguous

Issue with a duplicate alias in the `select` list that is later referenced by an `order by` clause.

Incorrect syntax:

select id, id
from table_test 
order by id;

The correct method is as follows:

select id, id id2
from table_name 
order by id;

Remove the duplicate alias before you reference it in the `order by` clause.

in.subquery.without.result

Issue where `colx` is reported as non-existent in the source table if `colx in subquery` returns no results.

Incorrect syntax:

select * from table_name
where not_exist_col in (select id from table_name limit 0);

Error message:

FAILED: ODPS-0130071:[2,7] Semantic analysis exception - column not_exist_col cannot be resolved

ctas.if.not.exists

Issue with incorrect syntax for the destination table.

If the destination table already exists, earlier versions of MaxCompute do not perform syntax checks. MaxCompute V2.0 performs normal syntax checks. This change can cause many error messages. An example is as follows:

Incorrect syntax:

create table if not exists table_name
as
select * from not_exist_table;

Error message:

FAILED: ODPS-0130131:[1,50] Table not found - table meta_dev.not_exist_table cannot be resolved

worker.restart.instance.timeout

In earlier versions of MaxCompute, each record that is output by a User-Defined Function (UDF) triggers a write operation to the distributed file system and sends a heartbeat to Fuxi. If the UDF does not output any results for 10 minutes, you receive the following error message:

FAILED: ODPS-0123144: Fuxi job failed - WorkerRestart errCode:252,errMsg:kInstanceMonitorTimeout, usually caused by bad udf performance.

The runtime framework of MaxCompute V2.0 supports vectorization. Vectorization processes multiple rows of a column at a time to improve execution efficiency. However, vectorization may cause statements that previously ran without errors to time out. This issue can occur if the interval between the output of two records was less than 10 minutes. Because multiple rows are processed at once, heartbeats may not be sent to Fuxi in time.

If you encounter this error, first check whether your UDF has performance issues, such as each record taking several seconds to process. If you cannot optimize the UDF performance, you can try to resolve the issue by manually setting the batch row size. The default value is 1024.

set odps.sql.executionengine.batch.rowcount=16;

divide.nan.or.overflow

Issue where earlier versions of MaxCompute do not perform constant folding for division.

For example, for the following statement, the physical execution plan in earlier versions of MaxCompute is as follows:

explain
select if(false, 0/0, 1.0)
from table_name;
in task M1_Stg1:
    Data source: meta_dev.table_name
    TS: alias: table_name
      SEL: If(False, Divide(UDFToDouble(0), UDFToDouble(0)), 1.0)
        FS: output: None

As you can see, the `IF` and `Divide` functions are retained. At runtime, the `Divide` expression in the second parameter is not evaluated because the first parameter of `IF` is false. Therefore, no division-by-zero exception occurs.

However, MaxCompute V2.0 supports constant folding for division and reports an error, as shown in the following example:

Incorrect syntax:

select IF(FALSE, 0/0, 1.0)
from table_name;

Error message:

FAILED: ODPS-0130071:[1,19] Semantic analysis exception - encounter runtime exception while evaluating function /, detailed message: DIVIDE func result NaN, two params are 0.000000 and 0.000000

In addition to the preceding error, you may also encounter an overflow error. For example:

Incorrect syntax:

select if(false, 1/0, 1.0)
from table_name;

Error message:

FAILED: ODPS-0130071:[1,19] Semantic analysis exception - encounter runtime exception while evaluating function /, detailed message: DIVIDE func result overflow, two params are 1.000000 and 0.000000

The correct method is as follows:

Remove the /0 usage and replace it with a valid constant.

`CASE WHEN` constant folding has a similar issue. For example, in `CASE WHEN TRUE THEN 0 ELSE 0/0`, MaxCompute V2.0 evaluates all sub-expressions during constant folding. This causes a division-by-zero error.

`CASE WHEN` may involve more complex optimization scenarios. For example:

select case when key = 0 then 0 else 1/key end
from (
select 0 as key from src
union all
select key from src) r;

The optimizer pushes the division operation down into the subquery. The transformation is similar to the following:

M (
select case when 0 = 0 then 0 else 1/0 end c1 from src
UNION ALL
select case when key = 0 then 0 else 1/key end c1 from src) r;

Error message:

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: java.lang.ArithmeticException: DIVIDE func result overflow, two params are 1.000000 and 0.000000

In this case, constant folding for the first clause of `UNION ALL` reports an error. To resolve this issue, move the `CASE WHEN` clause in the SQL statement into the subquery, remove the unnecessary `CASE WHEN` clause, and remove the /0 usage:

select c1 end
from (
select 0 c1 end from src
union all
select case when key = 0 then 0 else 1/key end) r;

small.table.exceeds.mem.limit

Earlier versions of MaxCompute support Multi-way Join optimization. Multiple joins that use the same join key are merged into a single Fuxi task. An example is `J4_1_2_3_Stg1` in the following query:

explain
select t1.*
from t1 join t2 on t1.c1 = t2.c1
join t3 on t1.c1 = t3.c1;

Physical execution plan in earlier versions of MaxCompute:

In Job job0:
root Tasks: M1_Stg1, M2_Stg1, M3_Stg1
J4_1_2_3_Stg1 depends on: M1_Stg1, M2_Stg1, M3_Stg1

In Task M1_Stg1:
    Data source: meta_dev.t1

In Task M2_Stg1:
    Data source: meta_dev.t2

In Task M3_Stg1:
    Data source: meta_dev.t3

In Task J4_1_2_3_Stg1:
    JOIN: t1 INNER JOIN unknown INNER JOIN unknown
        SEL: t1._col0, t1._col1, t1._col2
            FS: output: None

If you add a `MapJoin` hint, the physical execution plan in earlier versions of MaxCompute does not change. This indicates that earlier versions of MaxCompute prioritize Multi-way Join optimization and may ignore user-specified `MapJoin` hints.

explain
select /* +mapjoin(t1) */ t1.*
from t1 join t2 on t1.c1 = t2.c1
join t3 on t1.c1 = t3.c1;

The physical execution plan in earlier versions of MaxCompute is the same as the preceding plan.

The MaxCompute V2.0 optimizer prioritizes user-specified `MapJoin` hints. For the preceding example, if t1 is large, you may encounter an error similar to the following:

FAILED: ODPS-0010000:System internal error - SQL Runtime Internal Error: Hash Join Cursor HashJoin_REL… small table exceeds, memory limit(MB) 640, fixed memory used …, variable memory used …

In this case, if the `MapJoin` behavior is not what you expect, you can remove the `MapJoin` hint.

sigkill.oom

Similar to the `small.table.exceeds.mem.limit` issue, if you specify a `MapJoin` hint and the specified small table is large, the query may succeed in earlier versions of MaxCompute because it is optimized as a Multi-way Join. In MaxCompute V2.0, you can set odps.sql.mapjoin.memory.max to avoid errors that occur because the small table exceeds the memory limit. However, each MaxCompute worker has a fixed memory limit. If the small table is too large, the MaxCompute worker is killed due to an out-of-memory (OOM) error. The error is similar to the following:

Fuxi job failed - WorkerRestart errCode:9,errMsg:SigKill(OOM), usually caused by OOM(outof memory).

In this case, you can remove the `MapJoin` hint and use a Multi-way Join.

wm_concat.first.argument.const

The description of `WM_CONCAT` in Aggregate functions has always required the first parameter of `WM_CONCAT` to be a constant. Earlier versions of MaxCompute were not strict with this check. For example, if the source table had no data, no error was reported even if the first parameter of `WM_CONCAT` was a `ColumnReference`.

Function declaration:
string wm_concat(string separator, string str)
Parameter description:
separator: A constant of the String type. This is the separator. Other data types or non-constants will cause an exception.

MaxCompute V2.0 checks the validity of parameters during the planning phase. If the first parameter of `WM_CONCAT` is not a constant, an error is reported immediately. An example is as follows:

Incorrect syntax:

select wm_concat(value, ',') FROM src group by value;

Error message:

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: com.aliyun.odps.lot.cbo.validator.AggregateCallValidator$AggregateCallValidationException: Invalid argument type - The first argument of WM_CONCAT must be constant string.

pt.implicit.convertion.failed

`srcpt` is a partitioned table that has two partitions:

create table srcpt(key STRING, value STRING) partitioned by (pt STRING);
alter table srcpt add partition (pt='pt1');
alter table srcpt add partition (pt='pt2');

For the preceding SQL statement, the `String` type column `pt` and the `INT` type constants are both converted to the `DOUBLE` type for comparison. Even if the project is set to odps.sql.udf.strict.mode=true, earlier versions of MaxCompute do not report an error. All `pt` values are filtered out. MaxCompute V2.0 reports an error. An example is as follows:

Incorrect syntax:

select key from srcpt where pt in (1, 2);

Error message:

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: java.lang.NumberFormatException: ODPS-0123091:Illegal type cast - In function cast, value 'pt1' cannot be casted from String to Double.

Avoid comparing a `STRING` partition key column with `INT` type constants. You must change the `INT` type constants to the `STRING` type.

having.use.select.alias

The SQL specification defines that the `GROUP BY` and `HAVING` clauses are processed before the `SELECT` clause. Therefore, the `HAVING` clause cannot use a column alias that is generated by the `SELECT` clause.

dynamic.pt.to.static

Description: In MaxCompute V2.0, dynamic partitions are sometimes converted into static partitions by the optimizer.

insert overwrite table srcpt partition(pt) select id, 'pt1' from table_name;

is converted to

insert overwrite table srcpt partition(pt='pt1') select id from table_name;

If you specify an invalid partition value, such as an incorrect usage of '${bizdate}', MaxCompute V2.0 reports an error during the syntax check phase. For more information, see Partitions.

Incorrect syntax:

insert overwrite table srcpt partition(pt) select id, '${bizdate}' from table_name limit 0;

Error message:

FAILED: ODPS-0130071:[1,24] Semantic analysis exception - wrong columns count 2 in data source, requires 3 columns (includes dynamic partitions if any)

In earlier versions of MaxCompute, the SQL statement does not output any data because of the `LIMIT 0` clause, and no dynamic partition is created. Therefore, no error is reported.

lot.not.in.subquery

Description: This issue is related to how `NULL` values are handled in an `IN` subquery.

In standard SQL `IN` operations, if the value list contains `NULL`, the return value is not `false`. The return value can only be `NULL` or `true`. For example, `1 in (null, 1, 2, 3)` returns `true`, `1 in (null, 2, 3)` returns `NULL`, and `null in (null, 1, 2, 3)` returns `NULL`. Similarly, for a `NOT IN` operation, if the list contains `NULL`, the return value is only `false` or `NULL`, and never `true`.

MaxCompute V2.0 handles this issue based on standard SQL behavior. If you receive this reminder, check your queries to determine whether the subquery in the `IN` operation can return null values. Check whether the behavior is as you expect when null values occur. If not, make the necessary modifications.