このトピックでは、互換性のない SQL ステートメントの修正方法について説明します。
背景情報
MaxCompute V2.0 は、オープンソースエコシステムを完全に採用しています。より多くの言語機能をサポートし、より高速に実行されます。ただし、MaxCompute V2.0 は、より厳密な構文チェックを実行します。以前のコンパイラバージョンで正常に実行されていた不正確な構文を持つクエリは、MaxCompute V2.0 ではエラーを引き起こす可能性があります。
group.by.with.star
説明： この問題は、select * …group by… ステートメントを使用した場合に発生します。
MaxCompute V2.0 では、
GROUP BYリストにソーステーブルのすべての列を含める必要があります。そうしないと、エラーが発生します。以前のバージョンの MaxCompute では、
GROUP BYリストにソーステーブルのすべての列が含まれていなくても、select * from ... group by key構文がサポートされていました。
例：
シナリオ 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 テーブルは key と value 列を持ちますこの構文は MaxCompute V2.0 でエラーを引き起こしませんが、ステートメントを次のように変更することを推奨します。
select distinct key, value from t;
bad.escape
説明： この問題は、不正なエスケープシーケンスに関連しています。
MaxCompute のルールによれば、文字列リテラル内で ASCII 文字の 0 から 127 までを表現するには、バックスラッシュの後に 3 桁の 8 進数を続けなければなりません。たとえば、0 と 1 を表現するには「\001」と「\002」を使用できます。しかし、以前のバージョンでは \01 や \0001 も \001 として処理されていました。
この動作は混乱を招く可能性があります。たとえば、「\0001」を使って「\000」の後に「1」を続けることを表現できません。他のシステムから移行するユーザーにとって、この動作は正確性に関するエラーを引き起こす可能性もあります。
\000 の後に数字を追加すると、たとえば \0001～\0009 や \00001 のように、エラーが返される場合があります。
MaxCompute V2.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
説明： MaxCompute V2.0 では、テーブル作成時に重複した列名を使用するとエラーが発生します。
例：
不正な構文：
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
説明： この問題は、JOIN 条件で等号の左側が STRING 型で、右側が DOUBLE 型の場合に発生します。
以前のバージョンの MaxCompute では、両側を
BIGINT型に変換していました。この変換は、大幅な精度低下を引き起こす可能性があります。たとえば、結合条件1.1="1"は true と見なされます。Hive との互換性のため、MaxCompute V2.0 では両側を
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 t1.double_value = cast(t2.string_value as double);
window.ref.prev.window.alias
説明： この問題は、ウィンドウ関数が同じ SELECT リスト内の別のウィンドウ関数のエイリアスを参照した場合に発生します。
例：
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 つの列のみに展開される場合でも許可されません。 MaxCompute V2.0 コンパイラは、この構文に対してエラーを報告します。
例：
不正な構文：
select * as alias from table_test;エラーメッセージ：
FAILED: ODPS-0130161:[1,10] Parse exception - invalid token 'as'正しい構文：
select * from table_test;
agg.having.ref.prev.agg.alias
説明： この問題は、SELECT リストが先行する集計関数のエイリアスを参照し、かつ HAVING 句が存在する場合に発生します。
例：
不正な構文：
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に存在しません。以前のバージョンの MaxCompute では、HAVING句が原因でエラーは報告されませんでした。MaxCompute V2.0 では列を解決できませんというエラーが報告されます。正しい構文：
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
説明: デフォルトでは、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 V1.0 では、ビューのチェックは厳密ではありません。 たとえば、odps.sql.validate.orderby.limit=false を設定することで、limit チェックが不要なプロジェクトにビューを作成できます。
create view table_view as select id from table_view order by id;このビューにアクセスするには：
select * from table_view;MaxCompute V1.0 はエラーを報告しません。MaxCompute V2.0 は次のエラーメッセージを報告します。
FAILED: ODPS-0130071:[1,15] Semantic analysis exception - while resolving view xdj.xdj_view_limit - ORDER BY must be used with a LIMIT clausegenerated.column.name.multi.window
説明: この問題は、自動生成された aliases の使用に関連しています。
以前のバージョンの MaxCompute は、SELECT ステートメント内の各式に対して自動的にエイリアスを生成していました。このエイリアスはコンソールに表示されます。しかし、このエイリアスを生成するルールは保証されておらず、変更される可能性があります。したがって、自動生成されたエイリアスを使用すべきではありません。
MaxCompute V2.0 は、自動生成されたエイリアスが使用されると警告を発行します。この慣行は広く使用されているため、現時点では禁止することはできません。
場合によっては、エイリアスを生成するルールが MaxCompute のバージョン間で変更されることがあります。一部のオンラインジョブはこれらのエイリアスに依存しているため、MaxCompute のバージョンアップグレードまたはロールバック中にこれらのクエリが失敗する可能性があります。この問題に遭遇した場合は、使用したい列に明示的にエイリアスを指定するようにクエリを修正する必要があります。
例：
非推奨：
select _c0 from (select count(*) from table_name) t;推奨される構文：
select c from (select count(*) c from table_name) t;
non.boolean.filter
非BOOLEANのフィルター条件を使用する際の問題。
MaxCompute は、ブール型と他のデータ型との間の暗黙的な変換を許可しません。しかし、以前のバージョンの MaxCompute では、一部のケースで BIGINT をフィルター条件として使用することが許可されていました。MaxCompute V2.0 ではこの動作は許可されなくなりました。スクリプトにこのようなフィルター条件が含まれている場合は、それらを修正する必要があります。以下に例を示します。
不正な構文：
select id, count(*) from table_name group by id having id;エラーメッセージ：
FAILED: ODPS-0130071:[1,50] Semantic analysis exception - expect a BOOLEAN expression正しい方法は次のとおりです。
select id, count(*) from table_name group by id having id <> 0;post.select.ambiguous
order by、cluster by、distribute by、または sort by ステートメントで名前が競合する列を参照する際の問題。
以前のバージョンの MaxCompute では、システムはデフォルトで SELECT リストの最後の列を操作の対象として選択していました。MaxCompute V2.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 では、同じ名前のパーティションキーを指定してもエラーは報告されませんでした。代わりに、後のキーの値が前のキーの値を上書きしていました。この動作は混乱を招きやすいものでした。MaxCompute V2.0 はこのケースでエラーを報告します。以下の例で問題を示します。
不正な構文 1：
insert overwrite table partition (ds = '1', ds = '2'）select ... ;実行時、ds = ‘1’ は無視されます。
正しい方法は次のとおりです。
insert overwrite table partition (ds = '2'）select ... ;不正な構文 2：
create table t (a bigint, ds string) partitioned by (ds string);正しい構文：
create table t (a bigint) partitioned by (ds string);order.by.col.ambiguous
select リスト内の重複したエイリアスが、後に order by 句で参照される際の問題。
不正な構文：
select id, id
from table_test
order by id;正しい方法は次のとおりです。
select id, id id2
from table_name
order by id;order by 句で参照する前に、重複したエイリアスを削除してください。
in.subquery.without.result
colx in subquery が結果を返さない場合に、colx がソーステーブルに存在しないと報告される問題。
不正な構文：
select * from table_name
where not_exist_col in (select id from table_name limit 0);エラーメッセージ：
FAILED: ODPS-0130071:[2,7] Semantic analysis exception - column not_exist_col cannot be resolvedctas.if.not.exists
ターゲットテーブルの構文が不正である問題。
ターゲットテーブルが既に存在する場合、以前のバージョンの MaxCompute は構文チェックを行いませんでした。MaxCompute V2.0 は通常の構文チェックを実行します。この変更により、多くのエラーメッセージが発生する可能性があります。以下に例を示します。
不正な構文：
create table if not exists table_name
as
select * from not_exist_table;エラーメッセージ：
FAILED: ODPS-0130131:[1,50] Table not found - table meta_dev.not_exist_table cannot be resolvedworker.restart.instance.timeout
以前のバージョンの MaxCompute では、ユーザー定義関数 (UDF) によって出力される各レコードが分散ファイルシステムへの書き込み操作をトリガーし、Fuxi にハートビートを送信していました。UDF が 10 分間何も結果を出力しない場合、次のエラーメッセージが表示されます。
FAILED: ODPS-0123144: Fuxi job failed - WorkerRestart errCode:252,errMsg:kInstanceMonitorTimeout, usually caused by bad udf performance.MaxCompute V2.0 のランタイムフレームワークはベクトル化をサポートしています。ベクトル化は、列の複数行を一度に処理して実行効率を向上させます。しかし、ベクトル化により、以前はエラーなく実行されていたステートメントがタイムアウトする可能性があります。この問題は、2 つのレコードの出力間隔が 10 分未満であった場合に発生することがあります。複数行が一度に処理されるため、ハートビートが時間内に Fuxi に送信されない可能性があるためです。
このエラーに遭遇した場合は、まず UDF にパフォーマンスの問題がないか確認してください。たとえば、各レコードの処理に数秒かかっているなどです。UDF のパフォーマンスを最適化できない場合は、バッチ行サイズを手動で設定することで問題を解決できる場合があります。デフォルト値は 1024 です。
set odps.sql.executionengine.batch.rowcount=16;divide.nan.or.overflow
以前のバージョンのMaxComputeでは除算の定数畳み込みが実行されない問題。
たとえば、次のステートメントについて、以前のバージョンの 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このように、IF 関数と Divide 関数は保持されています。実行時、IF の最初のパラメーターが false であるため、2 番目のパラメーターの Divide 式は評価されません。したがって、ゼロ除算例外は発生しません。
しかし、MaxCompute V2.0 は除算の定数畳み込みをサポートしており、次の例に示すようにエラーを報告します。
不正な構文：
select IF(FALSE, 0/0, 1.0)
from table_name;エラーメッセージ：
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上記のエラーに加えて、オーバーフローエラーに遭遇することもあります。例：
不正な構文：
select if(false, 1/0, 1.0)
from table_name;エラーメッセージ：
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 の定数畳み込みにも同様の問題があります。たとえば、CASE WHEN TRUE THEN 0 ELSE 0/0 では、MaxCompute V2.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 0 as c1 from src
union all
select 1/key as c1 from src where key <> 0;small.table.exceeds.mem.limit
以前のバージョンの MaxCompute は、マルチウェイ結合の最適化をサポートしていました。同じ結合キーを使用する複数の結合は、単一の 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: NoneMapJoin ヒントを追加しても、以前のバージョンの MaxCompute の物理実行計画は変更されません。これは、以前のバージョンの MaxCompute がマルチウェイ結合の最適化を優先し、ユーザーが指定した MapJoin ヒントを無視する可能性があることを示しています。
explain
select /* +mapjoin(t1) */ t1.*
from t1 join t2 on t1.c1 = t2.c1
join t3 on t1.c1 = t3.c1;以前のバージョンの MaxCompute の物理実行計画は、上記の計画と同じです。
MaxCompute V2.0 のオプティマイザは、ユーザーが指定した MapJoin ヒントを優先します。上記の例で t1 が大きい場合、次のようなエラーに遭遇する可能性があります。
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 …この場合、MapJoin の動作が期待どおりでない場合は、MapJoin ヒントを削除できます。
sigkill.oom
small.table.exceeds.mem.limit の問題と同様に、MapJoin ヒントを指定し、指定された小さいテーブルが大きい場合、クエリは Multi-way Join として最適化されるため、以前のバージョンの MaxCompute では成功する可能性があります。 MaxCompute V2.0 では、odps.sql.mapjoin.memory.max を設定することで、小さいテーブルがメモリ制限を超えることによって発生するエラーを回避できます。 しかし、各 MaxCompute ワーカーには固定のメモリ制限があります。 小さいテーブルが大きすぎると、メモリ不足 (OOM) エラーが原因で MaxCompute ワーカーは強制終了されます。 エラーは次のようになります。
Fuxi job failed - WorkerRestart errCode:9,errMsg:SigKill(OOM), usually caused by OOM(outof memory).この場合、MapJoin ヒントを削除し、マルチウェイ結合を使用できます。
wm_concat.first.argument.const
「集計関数」における WM_CONCAT の説明では、WM_CONCAT の最初のパラメーターは定数でなければならないと常に規定されてきました。以前のバージョンの MaxCompute はこのチェックに厳格ではありませんでした。たとえば、ソーステーブルにデータがない場合、WM_CONCAT の最初のパラメーターが ColumnReference であってもエラーは報告されませんでした。
関数宣言： string wm_concat(string separator, string str)
パラメーター説明： separator： String 型の定数。これは区切り文字です。他のデータ型や非定数は例外を引き起こします。MaxCompute V2.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
srcpt は、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 と INT 型の定数は、比較のために両方とも DOUBLE 型に変換されます。プロジェクトが odps.sql.udf.strict.mode=true に設定されていても、以前のバージョンの MaxCompute ではエラーが報告されず、すべての pt 値がフィルターで除外されます。MaxCompute V2.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 句より前に処理されると定義されています。したがって、HAVING 句は SELECT 句によって生成された列エイリアスを使用できません。
例：
不正な構文：
select id id2 from table_name 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 V2.0 では、動的パーティションがオプティマイザによって静的パーティションに変換されることがあります。
例：
insert overwrite table srcpt partition(pt) select key, value, 'pt1' from table_name;は次のように変換されます
insert overwrite table srcpt partition(pt='pt1') select key, value from table_name;無効なパーティション値、たとえば '${bizdate}' の不正な使用などを指定した場合、MaxCompute V2.0 は構文チェック段階でエラーを報告します。詳細については、「パーティション」をご参照ください。
不正な構文：
insert overwrite table srcpt partition(pt) select key, value, '${bizdate}' from table_name 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 サブクエリでの NULL 値の処理方法に関連しています。
標準 SQL の IN 操作では、値のリストに NULL が含まれている場合、戻り値は false にはなりません。戻り値は NULL または true のみです。たとえば、1 in (null, 1, 2, 3) は true を返し、1 in (null, 2, 3) は NULL を返し、null in (null, 1, 2, 3) は NULL を返します。同様に、NOT IN 操作の場合、リストに NULL が含まれていると、戻り値は false または NULL のみで、true になることはありません。
MaxCompute V2.0 は、標準 SQL の動作に基づいてこの問題を処理します。この警告が表示された場合は、IN 操作のサブクエリが NULL 値を返す可能性があるかどうかをクエリで確認してください。NULL 値が発生した場合に、その動作が期待どおりであるかどうかを確認してください。そうでない場合は、必要な修正を行ってください。
例：
select * from t where c not in (select accepted from c_list);accepted列にNULL値が含まれていない場合は、この問題を無視できます。列にNULL値が含まれている場合、ステートメントc not in (select accepted from c_list)は、以前はtrueを返していましたが、MaxCompute V2.0 ではNULLを返すようになりました。正しい構文：
select * from t where c not in (select accepted from c_list where accepted is not null)