This topic describes how to modify SQL statements that are incompatible with MaxCompute V2.0.

Background information

MaxCompute V2.0 fully embraces open source ecosystems, supports more programming languages and features, and provides higher performance. It also inspects syntax more rigorously. As a result, errors may be returned for some statements that use less rigorous syntax and are successfully executed in the earlier versions.

To enable the smooth canary upgrade to MaxCompute V2.0, the MaxCompute framework supports rollback. If MaxCompute V2.0 fails to execute a job, MaxCompute V1.0 will execute the job instead. The rollback increases the latency of the job. Before you submit jobs, we recommend that you configure set odps.sql.planner.mode=lot; to manually disable the rollback feature. This prevents the impacts from the modification on the MaxCompute rollback policy.

The MaxCompute team notifies the owners of the jobs for which the required SQL statements cannot be executed by email or DingTalk based on the online rollback condition. The job owners must modify the SQL statements for the jobs at the earliest opportunity. Otherwise, the jobs may fail.

group.by.with.star

This statement is equivalent to the select * …group by…statement.
  • In MaxCompute V2.0, all the columns of a source table must be included in the GROUP BY clause. Otherwise, an error is returned.
  • In the earlier version of MaxCompute, select * from group by key is supported even if not all columns of a source table are included in the GROUP BY clause.
Examples
  • Scenario 1: The GROUP BY key does not include all columns.
    • Invalid syntax: 
      select * from t group by key;
    • Error message: 
      FAILED: ODPS-0130071:[1,8] Semantic analysis exception - column reference t.value should appear in GROUP BY key
    • Valid syntax: 
      select distinct key from t;
  • Scenario 2: The GROUP BY key includes all columns.
    • We recommend that you do no use the following syntax: 
      select * from t group by key, value; -- t has columns key and value
    • Even if the preceding syntax causes no errors in MaxCompute V2.0, we recommend that you use the following syntax: 
      select distinct key, value from t;

bad.escape

The escape sequence is invalid.

MaxCompute defines that, in string literal, each ASCII character that ranges from 0 to 127 must be written in the format of a backslash (\) followed by three octal digits. For example, 0 is written as \001, and 1 is written as \002. However, \01 and \0001 are processed as \001.

This method confuses new users. For example, "\0001" cannot be processed as "\000"+"1". For users who migrate data from other systems to MaxCompute, invalid data may be generated.
Note If numbers are appended to \000, such as numbers in the range of \0001 to \0009 or the number \00001, an error may be returned.
MaxCompute V2.0 corrects the sequences in scripts to handle this issue.
  • Invalid syntax: 
    select split(key, "\01"), value like "\0001" from t;
  • Error message: 
    FAILED: ODPS-0130161:[1,19] Parse exception - unexpected escape sequence: 01
ODPS-0130161:[1,38] Parse exception - unexpected escape sequence: 0001
  • Valid syntax: 
    select split(key, "\001"), value like "\001" from t;

column.repeated.in.creation

If duplicate column names are detected when the CREATE TABLE statement is executed, MaxCompute V2.0 returns an error.

Examples
  • Invalid syntax: 
    create table t (a BIGINT, b BIGINT, a BIGINT);
  • Error message: 
    FAILED: ODPS-0130071:[1,37] Semantic analysis exception - column repeated in creation: a
  • Valid syntax: 
    create table t (a BIGINT, b BIGINT);

string.join.double

You want to join the values of the STRING type with those of the DOUBLE type.
  • In the early version of MaxCompute, the values of the STRING and DOUBLE types are converted into the BIGINT type. This causes precision loss. For example, 1.1 = "1" in a JOIN condition is considered equal.
  • In MaxCompute V2.0, the values of the STRING and DOUBLE types are converted into the DOUBLE type because MaxCompute V2.0 is compatible with Hive.
Examples
  • Syntax that is not recommended: 
    select * from t1 join t2 on t1.double_value = t2.string_value;
  • Warning information: 
    WARNING:[1,48]  implicit conversion from STRING to DOUBLE, potential data loss, use CAST function to suppress
  • Recommended syntax: 
    select * from t1 join t2 on t.double_value = cast(t2.string_value as double);

window.ref.prev.window.alias

Window functions reference the aliases of other window functions in the SELECT clause of the same level.

Examples
  • Assume that rn does not exist in t1. Invalid syntax: 
    select row_number() over (partition by c1 order by c1) rn,
row_number() over (partition by c1 order by rn) rn2
from t1;
  • Error message: 
    FAILED: ODPS-0130071:[2,45] Semantic analysis exception - column rn cannot be resolved
  • Valid syntax: 
    select row_number() over (partition by c1 order by rn) rn2
from
(select c1, row_number() over (partition by c1 order by c1) rn
from t1
) tmp;

select.invalid.token.after.star

The SELECT clause allows you to use an asterisk (*) to select all the columns of a table. However, the asterisk cannot be followed by aliases even if the asterisk specifies only one column. The new editor returns errors for similar syntax.

Examples
  • Invalid syntax: 
    select * as alias from table_test;
  • Error message: 
    FAILED: ODPS-0130161:[1,10] Parse exception - invalid token 'as'
  • Valid syntax: 
    select * from table_test;

agg.having.ref.prev.agg.alias

If HAVING exists, the SELECT clause can reference aggregate function aliases.

Examples
  • Invalid syntax: 
    select count(c1) cnt,
sum(c1) / cnt avg
from t1
group by c2
having cnt > 1;
  • Error message: 
    FAILED: ODPS-0130071:[2,11] Semantic analysis exception - column cnt cannot be resolved
ODPS-0130071:[2,11] Semantic analysis exception - column reference cnt should appear in GROUP BY key

    s and cnt do not exist in source table t1. However, the early version of MaxCompute does not return an error because HAVING exists. In MaxCompute V2.0, the error message column cannot be resolve is returned.

  • Valid syntax: 
    select cnt, s, s/cnt avg
from
(
select count(c1) cnt,
sum(c1) s
from t1
group by c2
having count(c1) > 1
) tmp;

order.by.no.limit

In MaxCompute, the ORDER BY clause must be followed by a LIMIT clause to limit the number of data records. ORDER BY is used to sort all data records. If ORDER BY is not followed by a LIMIT clause, the execution performance is low.

Examples
  • Invalid syntax: 
    select * from (select *
from (select cast(login_user_cnt as int) as uv, '3' as shuzi
from test_login_cnt where type = 'device' and type_name = 'mobile') v
order by v.uv desc) v
order by v.shuzi limit 20;
  • Error message: 
    FAILED: ODPS-0130071:[4,1] Semantic analysis exception - ORDER BY must be used with a LIMIT clause

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

In MaxCompute V1.0, view checks are not rigorous. For example, a view is created in a project which does not require a check on the LIMIT clause. odps.sql.validate.orderby.limit=false indicates that the project does not require a check on the LIMIT clause. 
create view table_view as select id from table_view order by id;

Execute the following statement to access the view:

select * from table_view;

MaxCompute V1.0 does not return an error, whereas MaxCompute V2.0 returns the following error: 

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

Automatically generated aliases are used.

In the early version of MaxCompute, an alias is automatically generated for each expression of a SELECT statement. The alias is displayed on the MaxCompute client. However, the early version of MaxCompute does not guarantee that the alias generation rule is correct or remains unchanged. We recommend that you do not use automatically generated aliases.

MaxCompute V2.0 warns you against the use of automatically generated aliases. However, MaxCompute V2.0 does not prohibit the use of automatically generated aliases to avoid adverse impacts.

In some cases, known changes are made to the alias generation rules in the different versions of MaxCompute. Some online jobs depend on automatically generated aliases. These jobs may fail when MaxCompute is being upgraded or rolled back. If you encounter these issues, modify your queries and explicitly specify the aliases of the columns.

Examples
  • Syntax that is not recommended: 
    select _c0 from (select count(*) from table_name) t;
  • Recommended syntax: 
    select c from (select count(*) c from table_name) t;

non.boolean.filter

Non-BOOLEAN filter conditions are used.

MaxCompute prohibits implicit conversions between the BOOLEAN type and other data types. However, the early version of MaxCompute allows the use of BIGINT filter conditions in some cases. MaxCompute V2.0 prohibits the use of BIGINT filter conditions. If your scripts have BIGINT filter conditions, modify them at the earliest opportunity. Examples:

Invalid 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

Valid syntax:

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

post.select.ambiguous

The ORDER BY, CLUSTER BY, DISTRIBUTE BY, and SORT BY clauses reference columns with conflicting names.

In the early version of MaxCompute, the system automatically selects the last column in a SELECT clause as the operation object. However, MaxCompute V2.0 reports an error in this case. Modify your queries at the earliest opportunity. Examples:

Invalid 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

Valid syntax:

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

The change covers the statements that have conflicting column names but have the same syntax. Even though no ambiguity is caused, the system returns an error to warn you against these statements. We recommend that you modify relevant statements.

duplicated.partition.column

Partitions with the same name are specified in a query.

In the early version of MaxCompute, no error is returned if two partition keys with the same name are specified. The latter partition key overwrites the former partition. This causes confusion. MaxCompute V2.0 returns an error in this case. Examples:

Invalid syntax 1:

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

ds = '1' is ignored during execution.

Valid syntax:

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

Invalid syntax 2:

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

Valid syntax:

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

order.by.col.ambiguous

The ORDER BY clause references the duplicate aliases in a SELECT clause.

Invalid syntax:

select id, id
from table_test 
order by id;

Valid syntax:

select id, id id2
from table_name 
order by id;

Remove the duplicate aliases before the ORDER BY clause can reference them.

in.subquery.without.result

If colx in subquery does not return results, colx does not exist in the source table.

Invalid 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

The syntax of a destination table is invalid.

If the destination table exists, the early version of MaxCompute does not check the syntax. However, MaxCompute V2.0 checks the syntax. As a result, a large number of errors may be returned. Examples:

Invalid 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 the early version of MaxCompute, each time a UDF generates a record, a write operation is triggered on Apsara Distributed File System, and a heartbeat packet is sent to Job Scheduler. If the UDF does not generate records for 10 minutes, the following error is returned: 

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 vectoring to process multiple rows of a column at a time. This makes execution more efficient. If multiple records are processed at a time and no heartbeat packets are sent to Job Scheduler within the specific period, vectoring may cause normal statements to time out. The interval between two output records cannot exceed 10 minutes.

If a timeout error occurs, we recommend that you first check the performance of UDFs. It requires several seconds to process each record. If UDFs cannot be optimized, you can manually set batch.rowcount to handle this issue. The default value of batch.rowcount is 1024. 

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

divide.nan.or.overflow

The early version of MaxCompute does not support division constant folding.

The following code shows the physical execution plan in the early version of MaxCompute:

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

The IF and DIVIDE functions are retained. During execution, the first parameter of IF is set to False, and the expression of DIVIDE is not evaluated. Divide-by-zero errors do not occur.

However, MaxCompute V2.0 supports division constant folding. As a result, an error is returned. Examples:

Invalid 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

An overflow error may also occur. Examples:

Invalid 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

Valid syntax:

We recommend that you remove /0 and use valid constants.

A similar issue occurs in the constant folding for CASE WHEN, such as CASE WHEN TRUE THEN 0 ELSE 0/0. During constant folding in MaxCompute V2.0, all subexpressions are evaluated, which causes divide-by-zero errors.

CASE WHEN may involve more complex optimization scenarios. 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 down the division operation to subqueries. The following code shows a similar conversion: 

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

An error is returned for the constant folding in the first clause of UNION ALL. We recommend that you move CASE WHEN in the SQL statement to subqueries and remove useless CASE WHEN statements and /0. 

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

The early version of MaxCompute supports multi-way join optimization. Multiple JOIN operations with the same join key are merged for execution in the same Fuxi task, such as J4_1_2_3_Stg1 in this example. 

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

The following code shows the physical execution plan in the early version 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 MAPJOIN hints are added, the physical execution plan in the early version of MaxCompute remains unchanged. In the early version of MaxCompute, multi-way join optimization is preferentially used, and user-defined MAPJOIN hints can be ignored. 

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

The preceding physical execution plan in the early version of MaxCompute is applied.

The optimizer of MaxCompute V2.0 preferentially uses user-defined MAPJOIN hints. In this example, if t1 is a large table, an error similar to the following one is returned: 

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 MAPJOIN is not required, we recommend that you remove MAPJOIN hints.

sigkill.oom

sigkill.oom has the same issue as small.table.exceeds.mem.limit. If you specify MAPJOIN hints and the sizes of small tables are large, multiple JOIN statements may be optimized by using multi-way joins in the early version of MaxCompute. As a result, the statements are successfully executed in the early version of MaxCompute. However, in MaxCompute V2.0, some users may use odps.sql.mapjoin.memory.max to prevent small tables from exceeding the size limit. Each MaxCompute worker has a memory limit. If the sizes of small tables are large, MaxCompute workers may be terminated because the memory limit is exceeded. If this happens, an error similar to the following one is returned: 

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

We recommend that you remove MAPJOIN hints and use multi-way joins.

wm_concat.first.argument.const

Based on the WM_CONCAT function described in Aggregate functions, the first parameter of WM_CONCAT must be a constant. However, the early version of MaxCompute does not have rigorous check standards. For example, if the source table has no data, no error is returned even if the first parameter of WM_CONCAT is ColumnReference. 

Function declaration:
string wm_concat(string separator, string str)
Parameters:
separator: the delimiter, which is a constant of the STRING type. Delimiters of other types or non-constant delimiters result in exceptions.

MaxCompute V2.0 checks the validity of parameters during the planning stage. If the first parameter of WM_CONCAT is not a constant, an error is returned. Examples:

Invalid 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');

In the preceding SQL statements, the constants of the INT type in the pt columns of the STRING type are converted into those of the DOUBLE type for comparison. Even if odps.sql.udf.strict.mode=true is configured for the project, the early version of MaxCompute does not return an error and it filters out all pt columns. However, in MaxCompute V2.0, an error is returned. Examples:

Invalid 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.

We recommend that you do not compare the values in the partition key columns of the STRING and INT constants. If such comparison is required, convert the INT constants into the STRING type.

having.use.select.alias

SQL specifications define that the GROUP BY and HAVING clauses precede a SELECT clause. Therefore, the column alias generated by the SELECT clause cannot be used in the HAVING clause.

Examples
  • Invalid syntax:
    select id id2 from table_name group by id having id2 > 0;
  • Error message:
    FAILED: ODPS-0130071:[1,44] Semantic analysis exception - column id2 cannot be resolvedODPS-0130071:[1,44] Semantic analysis exception - column reference id2 should appear in GROUP BY key

    id2 is the column alias generated by the SELECT clause and cannot be used in the HAVING clause.

dynamic.pt.to.static

In MaxCompute V2.0, dynamic partitions may be converted into static partitions by the optimizer.

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

The preceding statement is converted into the following statement:

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

If the specified partition value is invalid, such as '${bizdate}', MaxCompute V2.0 returns an error during syntax checks. For more information, see Partition.

Invalid 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 the early version of MaxCompute, no results are returned by the SQL statements due to LIMIT 0, and no dynamic partitions are created. As a result, no error is returned.

lot.not.in.subquery

Processing of NULL values in the IN subquery.

In a standard SQL IN operation, if the value list contains a NULL value, the return value may be NULL or true, but cannot be false. 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. Likewise, for the NOT IN operation, if the value list contains a NULL value, the return value may be false or NULL, but cannot be true.

MaxCompute V2.0 processes NULL values by using standard execution rules. If you receive a notification for this issue, check whether the subqueries in the IN operation have a NULL value and whether the related execution meets your expectations. If the related execution does not meet your expectations, modify the queries.

Examples 
  • select * from t where c not in (select accepted from c_list);

    If the accepted column does not contain NULL values, ignore this issue. If the accepted column contains NULL values, c not in (select accepted from c_list) returns true in the early version of MaxCompute and NULL in MaxCompute V2.0.

  • Valid syntax:
    select * from t where c not in (select accepted from c_list where accepted is not null)