:デモ: 互換性のない SQL の修正

group.by.with.star

SELECT * … GROUP BY… syntax is problematic.

MaxCompute の従来のバージョンでは、select * from group by key * と一致する列が GROUP BY キーに含まれていない場合でも、サポートされています。 Hive と互換性がある MaxCompute 2.0 では、GROUP BY リストが すべてのソーステーブルの列でない限り、この構文を使用できません。 例:

シナリオ 1: GROUP BY キーにすべての列が含まれていません。

誤った構文:

SELECT * FROM t GROUP BY key;

エラーメッセージ:

FAILED: ODPS-0130071:[1,8] Semantic analysis exception - column reference t.value should appear in GROUP BY key

正しい構文:

SELECT DISTINCT key FROM t;

シナリオ 2: GROUP BY キーにすべての列が含まれています。

推奨されない構文:

SELECT * FROM t GROUP BY key, value; -- t has columns key and value

MaxCompute 2.0 ではエラーは返されませんが、次のように構文を修正することを推奨します。

SELECT DISTINCT key, value FROM t;

bad.escape

エスケープシーケンスの誤り

MaxCompute のドキュメントによると、文字列リテラルでは、0 から 127 までのすべての ASCII 文字は、バックスラッシュとそれに続く 3 つの 8 進数の形式で記述する必要があります。 たとえば、 0 は \001、1 は \002 として記述します。 現在、\01 と \0001 は \001 と見なされます。

この形式は新しいユーザーに混乱をもたらす可能性があります。 たとえば、“\000” + “1” は “\0001” とは記述しません。 他のシステムから MaxCompute へデータを移行するユーザーについては、誤ったデータが生成される可能性があります。

MaxCompute 2.0 ではこの問題が解決されます。 スクリプトのオーナーはシーケンスを修正する必要があります。たとえば、

誤った構文:

SELECT split(key, "\01"), value like "\0001" FROM t;

エラーメッセージ:

FAILED: ODPS-0130161:[1,19] Parse exception - unexpected escape sequence: 01
ODPS-0130161:[1,38] Parse exception - unexpected escape sequence: 0001-

正しい構文:

SELECT split(key, "\001"), value like "\001" FROM t;

column.repeated.in.creation

CREATE TABLE 文の実行時、重複する列名の検出

MaxCompute 2.0 では、CREATE TABLE 文の実行時に重複する列名が検出された場合は、エラーが返されます。 例:

誤った構文:

CREATE TABLE t (a BIGINT, b BIGINT, a BIGINT);

エラーメッセージ:

FAILED: ODPS-0130071:[1,37] Semantic analysis exception - column repeated in creation: a

正しい構文:

CREATE TABLE t (a BIGINT, b BIGINT);

string.join.double

結合条件の等号の両側のデータは String 型と Double 型

MaxCompute の旧バージョンでは、String 型と Double 型はBigint 型に変換されます (精度は下がります)。 結合条件の 1.1 = “1” は等しいと見なされますが、 Hive と互換性がある MaxCompute 2.0 では、String 型と Double 型のデータは Double 型に変換されます。

推奨されない構文:

SELECT * FROM t1 JOIN t2 ON t1.double_value = t2.string_value;

警告:

WARNING:[1,48]  implicit conversion from STRING to DOUBLE, potential data loss, use CAST function to suppress

推奨される修正:

select * from t1 join t2 on t.double_value = cast(t2.string_value as double);

必要に応じてデータを変換します。

window.ref.prev.window.alias

同じレベルの選択リスト内の他のエイリアスを参照する Window 関数

例:

t1 に rn がない、誤った構文は次のとおりです。

SELECT row_number() OVER (PARTITION BY c1 ORDER BY c1) rn,
row_number() OVER (PARTITION by c1 ORDER BY rn) rn2
FROM t1;

エラーメッセージ:

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

正しい構文:

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

エイリアスに続く Select *

選択リストでは、* を使用してテーブルのすべての列を選択できますが、 たとえ展開された * が 1 列のみだとしても、* の後に他のエイリアスを続けることはできません。 新しいエディタでは同様の構文に対してエラーが返されます。 例:

誤った構文:

select * as alias from dual;

エラーメッセージ:

FAILED: ODPS-0130161:[1,10] Parse exception - invalid token 'as'

正しい構文:

select * from dual;

agg.having.ref.prev.agg.alias

HAVING が存在する場合に、選択リストの前に付く AGGREGATE 関数のエイリアス 例:

誤った構文:

SELECT count(c1) cnt,
sum(c1) / cnt avg
FROM t1
GROUP BY c2
HAVING cnt > 1;

エラーメッセージ:

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 と cnt はソーステーブル t1 に存在しませんが、HAVING が存在するため、旧バージョンの MaxCompute ではエラーは返されません。 MaxCompute 2.0 では、“column cannot be resolved” が表示され、エラーが返されます。

正しい構文:

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

LIMIT 文が続かない ORDER BY

デフォルトで、MaxCompute では、データレコード数を制限するために ORDER BY の後に LIMIT 文を続ける必要があります。 ORDER BY は全データのソートに使われるため、LIMIT 文を使用しないと実行パフォーマンスが低くなります。 例:

誤った構文:

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;

エラーメッセージ:

FAILED: ODPS-0130071:[4,1] Semantic analysis exception - ORDER BY must be used with a LIMIT clause

正しい構文:

サブクエリ “order by v.uv desc” に LIMIT 文を追加します。

MaxCompute 1.0 では、ビューの検査は厳密ではありません。 たとえば、LIMIT 文の確認 (odps.sql.validate.orderby.limit=false) が不要なプロジェクトで、ビューが作成されます。

CREATE VIEW dual_view AS SELECT id FROM dual ORDER BY id;

次の文でビューにアクセスします。

SELECT * FROM dual_view;

MaxCompute 1.0 ではエラーは返されませんが、MaxCompute 2.0 では次のエラーが返されます。

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

自動的に生成されたエイリアスの使用

MaxCompute の従来のバージョンでは、すべての SELECT 文の式に対してエイリアスが自動的に生成されます。 エイリアスはコンソールに表示されます。 従来のバージョンでは、 エイリアスの生成ルールが正しいか、変更されていないかは保障されません。 そのため、自動生成されたエイリアスの使用は推奨されません。

MaxCompute 2.0 では、自動生成されたエイリアスの使用に対して警告が表示されますが、影響が大きいため、現時点でこのような使用は禁止されていません。

場合によっては、MaxComputeのさまざまなバージョンで、エイリアスの生成ルールに既知の変更が加えられています。 一部のオンラインジョブは自動生成されたエイリアスに依存しています。 MaxCompute のバージョンアップグレードまたはロールバックを実行する際に、 クエリが失敗する可能性があります。 このような問題がある場合は、クエリを修正し、目的の列のエイリアスを明示的に指定します。 例:

推奨されない構文:

SELECT _c0 FROM (SELECT count(*) FROM dual) t;

推奨される修正:

SELECT c FROM (SELECT count(*) c FROM dual) t;

non.boolean.filter

使用される非 Boolean フィルタ条件

MaxCompute では、Boolean 型と他のデータ型との間の暗黙的な変換は禁止されています。 MaxCompute の従来のバージョンでは、 場合によっては、フィルタ条件に Bigint 型を使用します。 MaxCompute 2.0 では、Bigint 型 のフィルタ条件の使用が禁止されています。 スクリプトに Bigint 型 のフィルタ条件がある場合は、速やかにフィルタ条件を変更してください。 例:

誤った構文:

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

エラーメッセージ:

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

正しい構文:

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

post.select.ambiguous

GROUP BY、CLUSTER BY、DISTRIBUTE BY、および SORT BY 文は、名前が競合する列を参照します。

MaxCompute の旧バージョンでは、デフォルトで、システムは選択リストの最後の列を操作オブジェクトとして選択します。 この場合は、MaxCompute 2.0 ではエラーが報告されます。 タイムリーに適切な修正をします。 例:

誤った構文:

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

エラーメッセージ:

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

正しい構文:

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

プッシュされた変更は、競合する列名を持つ文をカバーしていますが、同じ構文です。 あいまいさはありませんが、これらの文に対してよくエラーが返され、警告がトリガーされます。 適切に修正を加えることを推奨します。

duplicated.partition.column

クエリで同じ名前が指定されたパーティション

MaxCompute の従来のバージョンでは、同じ名前の2 つのパーティションキーが指定される場合は、エラーは返されません。 前者のパーティションキーは後者のもので上書きされます。 この上書きは混乱を招きます。 MaxCompute 2.0 では、 この場合はエラーが返されます。

誤った構文:

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

実際には、実行中に ds = ‘1’ は無視されます。

正しい構文:

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

誤った構文:

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

正しい構文:

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

order.by.col.ambiguous

ORDER BY 句の選択リスト内の重複するエイリアスの参照

誤った構文:

SELECT id, id
FROM dual 
ORDER BY id;

正しい構文:

SELECT id, id id2
FROM dual 
ORDER BY id;

選択リストを ORDER BY 句で参照する前に、重複するエイリアスを削除します。

in.subquery.without.result

サブクエリの colx が結果を返さない場合は、ソーステーブルには colx が存在しません。

誤った構文:

SELECT * FROM dual
WHERE not_exist_col IN (SELECT id FROM dual LIMIT 0);

エラーメッセージ:

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

ctas.if.not.exists

誤ったターゲットテーブルの構文

ターゲットテーブルが存在する場合は、MaxCompute の従来のバージョンでは、構文は確認されませんが、MaxCompute 2.0 では確認されます。 この場合は、多くのエラーが返される可能性があります。例：

誤った構文:

CREATE TABLE IF NOT EXISTS dual
AS
SELECT * FROM not_exist_table;

エラーメッセージ:

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

worker.restart.instance.timeout

MaxCompute の従来のバージョンでは、UDF がレコードを出力するたびに、分散ファイルシステムで書き込み操作がトリガーされ、ハートビートパケットが Job scheduling (Fuxi) へ送信されます。 UDF が 10 分間レコードを出力しない場合は、 次のエラーが報告されます。

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

MaxCompute 2.0 のランタイムフレームワークでは、ベクトル化がサポートされています。 実行効率を向上させるために、一度に 1 列の複数行を処理します。 ベクトル化によって、通常の文がタイムアウトする可能性があります。この場合は、 一度に複数のレコードが処理されている間は、ハートビートパケットは指定された時間内に Job scheduling (Fuxi) へ送信されません。 2 つの出力レコード間の間隔は 10 分を超えません。

タイムアウトエラーが発生した場合は、まず UDF パフォーマンスを確認することを推奨します。 各レコードを処理するには数秒かかります。 UDF パフォーマンスが最適化されない場合は、回避策として、手動で “batch row” の値を設定します。 デフォルト値は 1024 です。

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

divide.nan.or.overflow

MaxCompute の旧バージョンでサポートされない、 分割定数畳み込み

MaxCompute の旧バージョンでは、文の物理的な実行計画は次のとおりです。

EXPLAIN
Select if (false, 0/0, 1.0)
FROM dual;
In Task M1_Stg1:
    Data source: meta_dev.dual
    TS: alias: dual
      SEL: If(False, Divide(UDFToDouble(0), UDFToDouble(0)), 1.0)
        FS: output: None

IF と Divide 関数は保持されます。 実行中、IF の最初のパラメータは "false" に設定され、2 番目のパラメータの式の Divide は 評価されません。 ゼロによる除算は正常です。

MaxCompute 2.0 では分割定数畳み込みはサポートされています。 エラーが返されます。 次の通りです。

誤った構文:

SELECT IF(FALSE, 0/0, 1.0)
FROM dual;

エラーメッセージ:

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

nan 以外にオーバーフローエラーが発生する可能性があります。 例:

誤った構文:

SELECT IF(FALSE, 1/0, 1.0)
FROM dual;

エラーメッセージ:

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

正しい構文:

/0 を削除して、有効な定数を使用することを推奨します。

たとえば CASE WHEN TRUE THEN 0 ELSE 0/0 など、CASE WHEN の定数畳み込みでも同様の問題が発生します。 MaxCompute 2.0 では定数畳み込みの間に、 すべての副次式が評価され、誤ったゼロ除算が発生します。

CASE WHEN にはより複雑な最適化シナリオが含まれる可能性があります。例：

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;

オプティマイザは除算処理をサブクエリへ押し下げます。 同様の変換は次のとおりです。

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;

エラーメッセージ:

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

UNION ALL の最初の句の定数畳み込みに対してエラーが返されます。 SQL の CASE WHEN をサブクエリへ移動し、無駄な CASE WHEN 文と /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

MaxCompute の従来のバージョンでは、多重結合の最適化がサポートされています。 同じ結合キーを持つ複数の JOIN 文は、同じ Job scheduling (Fuxi) タスクで実行するためにマージされ、 次の例では、たとえば J4_1_2_3_Stg1 のようになります。

EXPLAIN
SELECT t1. *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
JOIN t3 ON t1.c1 = t3.c1;

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

MaxCompute の従来のバージョンでは、MapJoin ヒントが 追加される場合も物理的な実行計画は保持され、多重結合の最適化においてアプリケーションが優先されます。 ユーザー指定の MapJoin ヒントは無視される可能性があります。

EXPLAIN
SELECT /*+mapjoin(t1)*/ t1. *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
JOIN t3 ON t1.c1 = t3.c1;

MaxCompute の従来のバージョンの物理的な実行計画は、前述のものと同じです。

MaxCompute 2.0 のオプティマイザでは、ユーザー指定の MapJoin ヒントが優先されます。 前述の例では、t1 の値が比較的大きい場合は、次のエラーが返されます。

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

MapJoin が期待される動作でない場合は、MapJoin ヒントを削除することを推奨します。

sigkill.oom

mall.table.exceeds.mem.limit のように、MapJoin ヒントを指定し、小さなテーブルが比較的大きいサイズである場合は、MaxCompute の従来のバージョンでは、 複数の JOIN 文 は多重結合によって最適化され、正常に実行されます。 MaxCompute 2.0 では、一部のユーザーは 小さなテーブルがサイズ制限を超えるのを防ぐために、odps.sql.mapjoin.memory.max を設定します。 各 MaxCompute ワーカーには固定メモリ制限があります。 小さなテーブルのサイズが大きい場合は、MaxCompute ワーカーは メモリ制限を超えたために、強制終了される可能性があります。 エラーは次のようになります。

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

MapJoin ヒントを削除し、多重結合を使用することを推奨します。

wm_concat.first.argument.const

Aggregate 関数ドキュメントによると、 WM_CONCAT の最初のパラメータは定数である必要があります。 MaxCompute の旧バージョンでは、厳密な確認基準がありません。 たとえば、ソーステーブルにデータがない場合は、 WM_CONCAT の最初のパラメータが ColumnReference だとしても、エラーは返されません。

The function statement is as follows:
string wm_concat(string separator, string str)
Description of parameters:
Separator: String-type constant.  他の型の定数または定数でない場合は、例外が発生します。

MaxCompute 2.0 では計画段階でパラメータの有効性が確認されます。 WM_CONCAT の最初のパラメータが定数でない場合は、エラーが返されます。 例:

誤った構文: -

SELECT wm_concat(value, ',') FROM src GROUP BY value;

エラーメッセージ:

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

スクリプトは 2 つのパーティションを持つパーティションテーブルです。

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

前述の SQL 文については、String 型の pt 列の IN INT 型の定数は、比較のため Double 型に変換されます。 プロジェクトで odps.sql.udf.strict.mode が "true" に設定されているとしても、 MaxCompute の旧バージョンではエラーが返されず、すべての pt 列が除外されますが、MaxCompute 2.0 では エラーが返されます。 例:

誤った構文:

SELECT key FROM srcpt WHERE pt IN (1, 2);

エラーメッセージ:

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.

String 型のパーティション列と INT 型定数を比較することを防ぎ、INT 型定数を String 型に変換することを推奨します。

having.use.select.alias

SQL では、GROUP BY+HAVING 句が SELECT 句の前に指定されます。 したがって、SELECT 句によって生成された列のエイリアスは HAVING 句では使用されません。 例:

誤った構文:

SELECT id id2 FROM DUAL GROUP BY id HAVING id2 > 0;

エラーメッセージ:

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 は SELECT 句によって生成された列のエイリアスであり、HAVING 句では使用されません。

dynamic.pt.to.static

MaxCompute 2.0 では、動的パーティションはオプティマイザによって静的パーティションに変換されます。 例:

INSERT OVERWRITE TABLE srcpt PARTITION(pt) SELECT id, 'pt1' FROM dual;

前述の例は後述の例に変換されます。

INSERT OVERWRITE TABLE srcpt PARTITION(pt='pt1') SELECT id FROM dual;

指定されたパーティション値が無効な場合は (たとえば、‘${bizdate}’ が使用されている場合) 、MaxCompute 2.0 では構文チェック中にエラーが返されます。 詳しくは、「パーティション」をご参照ください。

誤った構文:

INSERT OVERWRITE TABLE srcpt PARTITION(pt) SELECT id, '${bizdate}' FROM dual LIMIT 0;

エラーメッセージ:

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

MaxCompute の従来のバージョンでは、LIMIT 0 を指定した場合は、SQL 文によって結果は返されず、動的パーティションも作成されません。 この場合は、エラーは返されません。

lot.not.in.subquery

In サブクエリでのヌル値の処理

標準的な SQL の IN の操作では、値リストにヌル値が含まれている場合は、戻り値は "null" または "true" になりますが、"false" にはなりません。 たとえば、1 in (null, 1, 2, 3) は “true” ですが、 1 in (null, 2, 3) は “null” 、null in (null, 1, 2, 3) は “null” です。 同様に、NOT IN 操作については、 値リストにヌル値が含まれている場合は、戻り値は “false” または “null” になりますが、"true" にはなりません。

MaxCompute 2.0 では標準の動作で処理が実行されます。 この問題に関する通知を受信した場合は、 IN 操作のサブクエリにヌル値があるかと、関連する動作が期待する動作と合っているかを判断するために、クエリを確認してください。 そうでない場合は、関連する変更を加えます。 例:

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

“accepted” にヌル値がない場合は、この問題を無視します。 ヌル値が存在する場合は、c not in (select accepted from c_list) は、従来のバージョンでは値 "true" を返しますが、 新しいバージョンではヌル値を返します。

正しい構文:

select * from t where c not in (select accepted from c_list where accepted is not null)