BigQuery兼容模式介绍

类型转换与表达式

查询语义与语法

函数

支持 DATE 类型与 TIMESTAMP_NTZ 类型比较

• 开启 BigQuery 兼容模式：支持 DATE 与 TIMESTAMP_NTZ 类型的直接比较，此时 DATE 类型数据会被视为当天 0 点的 TIMESTAMP_NTZ 参与比较；

• 不开启 BigQuery 兼容模式：DATE 与 TIMESTAMP_NTZ 类型无法直接比较，会报操作数类型不匹配的错误，需显式 CAST 到同一类型。

-- 开启BigQuery 兼容模式：DATE 视为 TIMESTAMP_NTZ '2025-01-01 00:00:00' 进行比较
SET odps.sql.BigQuery.compatible = true;
-- 返回 true 
SELECT DATE '2025-01-01' < TIMESTAMP_NTZ '2025-01-01 00:00:30'; 

-- 不开启BigQuery兼容模式：类型不匹配，返回报错
SET odps.sql.BigQuery.compatible = false;
-- 返回报错，FAILED: ODPS-0130071:[1,8] Semantic analysis exception - invalid operand type(s) DATE,TIMESTAMP_NTZ for operator '<'
SELECT DATE '2025-01-01' < TIMESTAMP_NTZ '2025-01-01 00:00:30';    

支持日期/时间字符串字面量自动转换

• 开启 BigQuery 兼容模式：SQL 中形如 yyyy-MM-dd 的日期字符串字面量在参与二元运算、GREATEST/LEAST 时，会自动转换为 DATE 或 TIMESTAMP 类型参与计算；

• 不开启 BigQuery 兼容模式：字符串字面量不做自动转换，例如，'2025-01-01' + 1 会按数值运算处理，因字符串无法转为有效数值而返回 NULL；若与日期类型混合运算则报类型错误。

注意：该隐式转换规则仅针对 SQL 中的字符串字面量（常量），不会将任意 STRING 类型的列自动视为日期列。

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 2025-01-02 （字面量转 DATE 加 1 天）
SELECT '2025-01-01' + 1;               
-- 返回 1 00:00:00.000000000 （返回INTERVAL_DAY_TIME类型）
SELECT DATE '2025-01-02' - '2025-01-01';
-- 返回 2025-01-02 00:00:00 （返回TIMESTAMP类型）
SELECT '2025-01-01' + INTERVAL 1 DAY;

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
-- 返回 NULL （字符串转数值失败）
SELECT '2025-01-01' + 1;            
-- 返回报错，FAILED: ODPS-0130071:[1,8] Semantic analysis exception - invalid operand type(s) DATE,STRING for operator '-'
SELECT DATE '2025-01-02' - '2025-01-01'; 
-- 返回报错，FAILED: ODPS-0130071:[1,8] Semantic analysis exception - invalid operand type(s) STRING,INTERVAL_DAY_TIME for operator '+'
SELECT '2025-01-01' + INTERVAL 1 DAY; 

DATE 与 INTERVAL 运算的结果类型差异

• 开启 BigQuery 兼容模式：DATE 与 INTERVAL_DAY_TIME（天、小时等）相加减时，结果类型为 TIMESTAMP_NTZ；

• 不开启 BigQuery 兼容模式：DATE 与 INTERVAL_DAY_TIME 相加减时，结果类型为 TIMESTAMP。

注意：两种模式下，DATE 与 INTERVAL_DAY_TIME 运算后的结果都会保留时间部分；DATE 与 INTERVAL_YEAR_MONTH（月、年等）的运算结果均为 DATE。

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 2025-01-01 03:00:00 (返回TIMESTAMP_NTZ类型)
SELECT DATE '2025-01-01' + INTERVAL 3 HOUR;  
-- 返回 2025-02-01（返回DATE类型）
SELECT DATE '2025-01-01' + INTERVAL 1 MONTH;  

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
-- 返回 2025-01-01 03:00:00（返回TIMESTAMP类型）
SELECT DATE '2025-01-01' + INTERVAL 3 HOUR;  
-- 返回 2025-02-01（返回DATE类型）
SELECT DATE '2025-01-01' + INTERVAL 1 MONTH;  

支持 GREATEST / LEAST 中日期字符串自动转换

• 开启 BigQuery 兼容模式：当 GREATEST 或 LEAST 的参数中包含 DATE 类型时，形如 yyyy-m-d 或 yyyy-MM-dd 的字符串字面量会自动转换为 DATE 参与比较；

• 不开启 BigQuery 兼容模式：字符串字面量不会自动转换为 DATE，混用 STRING 与 DATE 参数时因类型不匹配而报错，需使用 CAST 函数进行显式转换。。

注意：该规则不扩展到 COALESCE 等其他函数。不符合日期格式的字符串会报转换错误。

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 2025-01-03 （返回DATE类型）
SELECT GREATEST('2025-01-01', DATE '2025-01-03');  
-- 返回  2025-01-01 （返回DATE类型）
SELECT LEAST('2025-01-01', DATE '2025-01-03'); 

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
-- 返回报错，FAILED: ODPS-0130071:[1,8] Semantic analysis exception - function greatest cannot match any overloaded functions with (STRING, DATE), candidates are DOUBLE GREATEST(VOID arg0...); T GREATEST(T arg0...)
SELECT GREATEST('2025-01-01', DATE '2025-01-03');  
-- 返回报错，FAILED: ODPS-0130071:[1,8] Semantic analysis exception - function least cannot match any overloaded functions with (STRING, DATE), candidates are DOUBLE LEAST(VOID arg0...); T LEAST(T arg0...)
SELECT LEAST('2025-01-01', DATE '2025-01-03');   

支持 UNION / UNION ALL 跨类型公共类型推导

• 开启 BigQuery 兼容模式：DATE 与 TIMESTAMP_NTZ 做 UNION 时，公共类型为 TIMESTAMP_NTZ；STRING 与时间类型做 UNION 时，取相应的时间类型，字符串值会被解析为时间值；

• 不开启 BigQuery 兼容模式：不做跨类型的公共类型推导，上述 UNION 会报类型不匹配错误，需显式 CAST 到同一类型。

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
SELECT DATE '2025-01-01' AS value
UNION ALL SELECT TIMESTAMP_NTZ '2025-01-02 08:30:00';
-- 返回结果（结果列类型为TIMESTAMP_NTZ）
+-------+
| value |
+-------+
| 2025-01-01 00:00:00 |
| 2025-01-02 08:30:00 |
+-------+
SELECT '2025-01-01' AS value
UNION ALL SELECT DATE '2025-01-02';
-- 返回结果（结果列类型为DATE）
+-------+
| value |
+-------+
| 2025-01-01 |
| 2025-01-02 |
+-------+

-- 不开启 BigQuery 兼容模式：以上两个 UNION 均报类型不匹配错误
SET odps.sql.BigQuery.compatible = false;
-- 返回报错，FAILED: ODPS-0130241:[2,18] Illegal union operation - type mismatch for column 0 of UNION, left is DATE while right is TIMESTAMP_NTZ, left column name is value and right column name is _c0
SELECT DATE '2025-01-01' AS value
UNION ALL SELECT TIMESTAMP_NTZ '2025-01-02 08:30:00';
-- 返回报错，FAILED: ODPS-0130241:[2,18] Illegal union operation - type mismatch for column 0 of UNION, left is STRING while right is DATE, left column name is value and right column name is _c0
SELECT '2025-01-01' AS value
UNION ALL SELECT DATE '2025-01-02';

支持构造 STRUCT 中包含 NULL 字段

• 开启 BigQuery 兼容模式：允许在 STRUCT 表达式中直接使用未显式转换的 NULL，该字段按 STRING 类型处理；需要其他类型时，需显式使用 CAST进行转换。

• 不开启 BigQuery 兼容模式：未定类型的 NULL 字段会报错。

注意：该能力仅适用于 STRUCT(... AS ...) 语法。NAMED_STRUCT(...) 函数在两种模式下都不接受未定类型的 NULL。

关于STRUCT数据类型的构造和详细用法，请参见STRUCT数据类型。

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
SELECT DATE '2025-01-01' AS value
UNION ALL SELECT TIMESTAMP_NTZ '2025-01-02 08:30:00';
-- 返回结果（结果列类型为TIMESTAMP_NTZ）
+-------+
| value |
+-------+
| 2025-01-01 00:00:00 |
| 2025-01-02 08:30:00 |
+-------+
SELECT '2025-01-01' AS value
UNION ALL SELECT DATE '2025-01-02';
-- 返回结果（结果列类型为DATE）
+-------+
| value |
+-------+
| 2025-01-01 |
| 2025-01-02 |
+-------+

-- 不开启 BigQuery 兼容模式：以上两个 UNION 均报类型不匹配错误
SET odps.sql.BigQuery.compatible = false;
-- 返回报错，FAILED: ODPS-0130241:[2,18] Illegal union operation - type mismatch for column 0 of UNION, left is DATE while right is TIMESTAMP_NTZ, left column name is value and right column name is _c0
SELECT DATE '2025-01-01' AS value
UNION ALL SELECT TIMESTAMP_NTZ '2025-01-02 08:30:00';
-- 返回报错，FAILED: ODPS-0130241:[2,18] Illegal union operation - type mismatch for column 0 of UNION, left is STRING while right is DATE, left column name is value and right column name is _c0
SELECT '2025-01-01' AS value
UNION ALL SELECT DATE '2025-01-02';

放宽 DECIMAL 的隐式写入/赋值

• 开启 BigQuery 兼容模式：放宽 DECIMAL 在写入/赋值时的隐式转换限制；

• 不开启 BigQuery 兼容模式：以下场景均被拒绝，需显式 CAST。

注意：以下示例需先启用 DECIMAL 2.0 类型：SET odps.sql.decimal.odps2 = true;。缩小精度或标度可能发生舍入或溢出，生产任务建议显式 CAST 并在迁移前检查边界值。

CREATE TABLE t_dbl (v DOUBLE);
CREATE TABLE t_dec (v DECIMAL(5,1));

-- 开启 BigQuery 兼容模式：以下两条 INSERT 成功
SET odps.sql.BigQuery.compatible = true;
INSERT INTO t_dbl SELECT CAST(1.25 AS DECIMAL(10,2));   -- DECIMAL → DOUBLE
INSERT INTO t_dec SELECT CAST(1.25 AS DECIMAL(10,2));   -- DECIMAL(10,2) → DECIMAL(5,1)

-- 不开启 BigQuery 兼容模式：以上两条均报错，需改为显式 CAST
SET odps.sql.BigQuery.compatible = false;
-- 返回报错，FAILED: ODPS-0130071:[1,26] Semantic analysis exception - column _c0 in source has incompatible type DECIMAL(10,2) with destination column v, which has type DOUBLE. Implicit conversion is not applied because of potential data loss, but you can still consider to use explicit CAST. Please also note that the implicit conversion rule may be affected by altering odps.sql.type.system.odps2 or odps.sql.hive.compatible flag.
INSERT INTO t_dbl SELECT CAST(1.25 AS DECIMAL(10,2)); 
-- 返回报错，FAILED: ODPS-0130071:[1,26] Semantic analysis exception - column _c0 in source has incompatible type DECIMAL(10,2) with destination column v, which has type DECIMAL(5,1). Implicit conversion is not applied because of potential data loss, but you can still consider to use explicit CAST. Please also note that the implicit conversion rule may be affected by altering odps.sql.type.system.odps2 or odps.sql.hive.compatible flag.
INSERT INTO t_dec SELECT CAST(1.25 AS DECIMAL(10,2));

GROUP BY / HAVING / QUALIFY 中 SELECT 别名的解析优先级

两种模式下，SELECT 别名都可以在 GROUP BY、HAVING、QUALIFY 中引用，基础用法一致。差异在于：当 SELECT 别名与输入表的某个列同名时，名称的解析优先级不同。

• 开启 BigQuery 兼容模式：别名优先，该名称解析为 SELECT 中的别名表达式；

• 不开启 BigQuery 兼容模式：输入列名优先，该名称解析为输入列。

在 BigQuery 兼容模式下若要引用输入列而非别名，请用表别名限定（如 t.a）。

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 输入子查询有列 a、b；这里把 b 也起别名叫 a，再 GROUP BY a
SELECT b AS a, COUNT(*) AS c
FROM (SELECT 1 AS a, 10 AS b UNION ALL SELECT 2 AS a, 10 AS b UNION ALL SELECT 3 AS a, 20 AS b) t
GROUP BY a;
-- 返回结果
+------+------------+
| a    | c          |
+------+------------+
| 10   | 2          |
| 20   | 1          |
+------+------------+

-- 不开启 BigQuery 兼容模式：
SET odps.sql.BigQuery.compatible = false;
-- GROUP BY 绑定输入列 a，b 未出现在分组键中 
SELECT b AS a, COUNT(*) AS c
FROM (SELECT 1 AS a, 10 AS b UNION ALL SELECT 2 AS a, 10 AS b UNION ALL SELECT 3 AS a, 20 AS b) t
GROUP BY a;
-- 返回报错，FAILED: ODPS-0130071:[1,8] Semantic analysis exception - column reference t.b should appear in GROUP BY key

支持 GROUP BY ALL

• 开启 BigQuery 兼容模式：支持 GROUP BY ALL，系统会根据 SELECT 列表自动推导分组键，其中聚合表达式、窗口表达式和常量本身不作为分组键，它们引用的非聚合输入列会按需加入分组键；

• 不开启 BigQuery 兼容模式：不支持 GROUP BY ALL，裸标识符 all 会被当作列名解析，若不存在名为 all 的列则报错。

名称冲突提醒：若输入表确有名为 all 的列，GROUP BY ALL 中的 all 会被解析为对该列的引用（即"按 all 这一列分组"），而不再触发"自动推导全部分组键"的关键字语义。此时若 SELECT 中还有其他非聚合列，会因这些列未出现在分组键中而报错。为避免歧义，建议显式写出分组列（或给该列改用别名）。

关于GROUP BY的完整介绍和详细用法，请参见SELECT语法。

-- 创建示例表
CREATE TABLE IF NOT EXISTS sales (
    region STRING,
    product STRING,
    amount BIGINT
);

-- 插入测试数据（包含多组不同的 region 和 product）
INSERT INTO sales VALUES
    ('North', 'Apple', 100),
    ('North', 'Apple', 150),
    ('North', 'Banana', 80),
    ('South', 'Apple', 200),
    ('South', 'Banana', 120),
    ('South', 'Banana', 130);

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
SELECT region, product, SUM(amount) AS total_amount
FROM sales
GROUP BY ALL;
-- 返回结果（等价于 GROUP BY region, product）
+--------+---------+--------------+
| region | product | total_amount |
+--------+---------+--------------+
| North  | Apple   | 250          |
| North  | Banana  | 80           |
| South  | Apple   | 200          |
| South  | Banana  | 250          |
+--------+---------+--------------+
-- 常量不入组键，仅按 region 分组
SELECT region, 'online' AS channel, SUM(amount) FROM sales GROUP BY ALL; 
-- 返回结果
+--------+---------+------------+
| region | channel | _c2        |
+--------+---------+------------+
| North  | online  | 330        |
| South  | online  | 450        |
+--------+---------+------------+
 -- 仅聚合表达式则全局聚合
SELECT SUM(amount) FROM sales GROUP BY ALL;     
-- 返回结果
+------------+
| _c0        |
+------------+
| 780        |
+------------+

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
SELECT region, product, SUM(amount) AS total_amount
FROM sales
GROUP BY ALL;
-- 返回报错，FAILED: ODPS-0130071:[3,10] Semantic analysis exception - column `all` cannot be resolved
ODPS-0130071:[1,8] Semantic analysis exception - column reference sales.region should appear in GROUP BY key
ODPS-0130071:[1,16] Semantic analysis exception - column reference sales.product should appear in GROUP BY key

支持 SELECT * EXCEPT(col) 排除多表同名列

• 开启 BigQuery 兼容模式：* EXCEPT(name) 会排除所有匹配该名称的展开列，即使多张输入表存在同名列也不会报歧义。

• 不开启 BigQuery 兼容模式：当被排除的列名在多张输入表中重名（如两表都有 id）时，* EXCEPT(id) 会因该名称歧义而报错，无法排除。

-- 示例数据
CREATE TABLE IF NOT EXISTS shop_info
(
    shop_name   STRING,
    city        STRING,
    manager     STRING
);
INSERT INTO shop_info VALUES ('s1', 'Beijing', 'Tom'), ('s2', 'Shanghai', 'Jerry');

-- 开启 BigQuery 兼容模式
SET odps.sql.bigquery.compatible = true;
-- 左右表的 shop_name都会被排除；不存在的列仍报列解析错误
SELECT * EXCEPT(shop_name)
FROM sale_detail AS l JOIN shop_info AS r ON l.shop_name = r.shop_name;
-- 返回结果
+-------------+-------------+-----------+--------+------+---------+
| customer_id | total_price | sale_date | region | city | manager |
+-------------+-------------+-----------+--------+------+---------+
| c1          | 100.1       | 2013      | china  | Beijing | Tom     |
| c2          | 100.2       | 2013      | china  | Shanghai | Jerry   |
+-------------+-------------+-----------+--------+------+---------+

-- 不开启 BigQuery 兼容模式：上述语句因 shop_name在左右表重名而报歧义错误
SET odps.sql.bigquery.compatible = false;
-- 返回报错，FAILED: ODPS-0130071:[1,17] Semantic analysis exception - shop_name is ambiguouscan be both l.shop_name or r.shop_name
SELECT * EXCEPT(shop_name)
FROM sale_detail AS l JOIN shop_info AS r ON l.shop_name = r.shop_name;

支持 JOIN ... USING 同时访问合并列与两侧原始列

• 开启 BigQuery 兼容模式：JOIN ... USING(key) 同时保留一个可用未限定名称访问的合并列 key、左表原始列 l.key、右表原始列 r.key（两侧原始列必须使用表别名限定）；SELECT * 只输出一份合并列；

• 不开启 BigQuery 兼容模式：USING(key) 只保留一个合并列 key，两侧的原始列 l.key/r.key 无法再限定访问（引用会报"无法解析"）。

合并列取值规则（属标准 USING 语义，两种模式一致）：

-- 开启 BigQuery 兼容模式：可同时访问合并列与两侧原始列
SET odps.sql.BigQuery.compatible = true;
WITH l AS (SELECT 1 AS id, 'L' AS left_value),
     r AS (SELECT 2 AS id, 'R' AS right_value)
SELECT id, l.id AS left_id, r.id AS right_id, left_value, right_value
FROM l FULL JOIN r USING (id);
-- 返回结果
+------+---------+----------+------------+-------------+
| id   | left_id | right_id | left_value | right_value |
+------+---------+----------+------------+-------------+
| 2    | NULL    | 2        | NULL       | R           |
| 1    | 1       | NULL     | L          | NULL        |
+------+---------+----------+------------+-------------+

-- 不开启 BigQuery 兼容模式：l.id / r.id 引用报错"无法解析"
SET odps.sql.BigQuery.compatible = false;
WITH l AS (SELECT 1 AS id, 'L' AS left_value),
     r AS (SELECT 2 AS id, 'R' AS right_value)
SELECT id, l.id AS left_id, r.id AS right_id, left_value, right_value
FROM l FULL JOIN r USING (id);
-- 返回报错，FAILED: ODPS-0130071:[3,14] Semantic analysis exception - column l.id cannot be resolved
ODPS-0130071:[3,31] Semantic analysis exception - column r.id cannot be resolv

支持 STRUCT 按下标访问字段

• 开启 BigQuery 兼容模式：STRUCT 支持使用从 0 开始的整数字面量访问字段；下标必须是非负整数，下标超出字段数量时会报错；

• 不开启 BigQuery 兼容模式：不支持整数下标访问 STRUCT。

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
SELECT STRUCT(10 AS id, 'Alice' AS name)[0],   
       STRUCT(10 AS id, 'Alice' AS name)[1];   
-- 返回结果
+------+------+
| id   | name |
+------+------+
| 10   | Alice |
+------+------+

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
SELECT STRUCT(10 AS id, 'Alice' AS name)[0],   
       STRUCT(10 AS id, 'Alice' AS name)[1];   
-- 返回报错，FAILED: ODPS-0130071:[1,8] Semantic analysis exception - invalid operand type(s) struct<id:INT,name:STRING>,INT for operator '[]'
ODPS-0130071:[2,8] Semantic analysis exception - invalid operand type(s) struct<id:INT,name:STRING>,INT for operator '[]'

相关子查询中 ORDER BY / QUALIFY / 窗口函数的限制

• 开启 BigQuery 兼容模式：引用外层查询列的相关子查询不支持 ORDER BY、QUALIFY、窗口函数，否则编译报错；规避方式为先在独立子查询中完成排序或窗口计算，再与外层数据连接；

• 不开启 BigQuery 兼容模式：不对相关子查询做这些限制，含 ORDER BY、QUALIFY 或窗口函数都不会报错，可正常返回结果。

-- 开启 BigQuery 兼容模式：以下三种相关子查询都会被拒绝
SET odps.sql.BigQuery.compatible = true;
-- 1) 含 ORDER BY 的相关子查询
SELECT (
  SELECT total_price 
  FROM sale_detail AS c
  WHERE c.customer_id = p.customer_id
  ORDER BY sale_date DESC 
  LIMIT 1
) 
FROM sale_detail AS p;
-- 返回报错，FAILED: ODPS-0130071:[5,3] Semantic analysis exception - correlated subquery cannot support ORDER BY clause

-- 2) 含 QUALIFY 的相关子查询
SELECT (
  SELECT total_price 
  FROM sale_detail AS c
  WHERE c.customer_id = p.customer_id
  QUALIFY ROW_NUMBER() OVER (ORDER BY sale_date DESC) = 1
) 
FROM sale_detail AS p;
-- 返回报错，FAILED: ODPS-0130071:[5,3] Semantic analysis exception - correlated subquery cannot support QUALIFY clause
ODPS-0130071:[5,11] Semantic analysis exception - correlated subquery cannot support WINDOW function

-- 3) 含窗口函数的相关子查询
SELECT (
  SELECT ROW_NUMBER() OVER (ORDER BY sale_date) 
  FROM sale_detail AS c
  WHERE c.customer_id = p.customer_id 
  LIMIT 1
) 
FROM sale_detail AS p;
-- 返回报错，FAILED: ODPS-0130071:[5,3] Semantic analysis exception - correlated subquery cannot support ORDER BY clause
ODPS-0130071:[2,10] Semantic analysis exception - correlated subquery cannot support WINDOW function


-- 不开启 BigQuery 兼容模式：以上三种均可正常执行
SET odps.sql.BigQuery.compatible = false;
-- 1) 含 ORDER BY 的相关子查询
SELECT (
  SELECT total_price 
  FROM sale_detail AS c
  WHERE c.customer_id = p.customer_id
  ORDER BY sale_date DESC 
  LIMIT 1
) 
FROM sale_detail AS p;
-- 返回结果
+------------+
| _c0        |
+------------+
| 100.1      |
| NULL       |
| NULL       |
+------------+

-- 2) 含 QUALIFY 的相关子查询
SELECT (
  SELECT total_price 
  FROM sale_detail AS c
  WHERE c.customer_id = p.customer_id
  QUALIFY ROW_NUMBER() OVER (ORDER BY sale_date DESC) = 1
) 
FROM sale_detail AS p;
-- 返回结果
+------------+
| _c0        |
+------------+
| NULL       |
| NULL       |
| 100.3      |
+------------+
-- 3) 含窗口函数的相关子查询
SELECT (
  SELECT ROW_NUMBER() OVER (ORDER BY sale_date) 
  FROM sale_detail AS c
  WHERE c.customer_id = p.customer_id 
  LIMIT 1
) 
FROM sale_detail AS p;
-- 返回结果
+------------+
| _c0        |
+------------+
| 1          |
| 2          |
| 3          |
+------------+

列名长度上限扩展到 256

• 开启 BigQuery 兼容模式：列名长度限制为不超过256字节，否则报错。

• 不开启 BigQuery 兼容模式：列名长度限制为不超过128字节，否则报错。

该长度上限只针对会写入表结构的列名（例如 CREATE TABLE、CREATE TABLE AS SELECT 建出的列）；普通 SELECT 查询中临时的列别名不受此限制。

更多SQL使用限制项，请参见SQL使用限制项。

PIVOT 输出列名拼接顺序

• 开启 BigQuery 兼容模式

  • PIVOT (agg1 as a for axis1 in ('1', '2', '3', ...))：PIVOT处理后列名为聚合函数别名_value，即a_'1',a_'2', ...。

  • PIVOT (agg1 as a, agg2 as b for axis1 in ('1', '2', '3', ...))：PIVOT处理后列名为聚合函数别名_value，即a_'1',a_'2', ..., b_'1', b_'2', ...。

  • PIVOT (agg1 as a, agg2 for axis1 in (expr1, expr2, '3', ...))：PIVOT处理后列名为聚合函数别名_value/表达式别名，即a_generated_alias2, a_generated_alias3, a_'3' ..., generated_alias1_generated_alias2, generated_alias1_generated_alias3,generated_alias1_'3'...。

• 不开启 BigQuery 兼容模式

  • PIVOT (agg1 as a for axis1 in ('1', '2', '3', ...))：PIVOT处理后列名为value_聚合函数别名，即'1'_a, '2'_a, ...。

  • PIVOT (agg1 as a, agg2 as b for axis1 in ('1', '2', '3', ...))：PIVOT处理后列名为value_聚合函数别名，即'1'_a, '2'_a, ..., '1'_b, '2'_b, ...。

  • PIVOT (agg1 as a, agg2 for axis1 in (expr1, expr2, '3', ...))：PIVOT处理后列名为value/表达式别名_聚合函数别名，即generated_alias2_a, generated_alias3_a,'3'_a ..., generated_alias2_generated_alias1, generated_alias3_generated_alias1,'3'_ generated_alias1...。

建议始终为聚合函数和 IN 子句中的值（常量或表达式）显式指定别名，避免依赖系统生成的名称。

关于PIVOT的完整介绍和详细用法，请参见PIVOT、UNPIVOT。

-- 不开启BigQuery兼容模式
SELECT  *
FROM    (
            SELECT  productid
                    ,tran_amt
            FROM    mf_cop_sales
        ) 
PIVOT (SUM(tran_amt)
AS sumbypro FOR productid IN ('apple','orange','banana'))
;
-- 返回结果
+------------------+-------------------+-------------------+
| 'apple'_sumbypro | 'orange'_sumbypro | 'banana'_sumbypro |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

-- 开启BigQuery兼容模式
SET odps.sql.BigQuery.compatible=true;
SELECT  *
FROM    (
            SELECT  productid
                    ,tran_amt
            FROM    mf_cop_sales
        ) 
PIVOT (SUM(tran_amt)
AS sumbypro FOR productid IN ('apple','orange','banana'))
;
-- 返回结果
+------------------+-------------------+-------------------+
| sumbypro_'apple' | sumbypro_'orange' | sumbypro_'banana' |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

支持重复输出列名自动去重

• 开启 BigQuery 兼容模式：子查询或 CTE 产生重复输出列名时，系统会自动为后续的同名列追加 _1、_2 等数字后缀，以确保结果集中的列名唯一。

• 不开启 BigQuery 兼容模式：不允许存在重复的输出列名，系统会直接抛出歧义错误（包括使用 SELECT * 展开时）。

自动后缀只保证结果结构中的列名唯一，不改变名称解析规则：外层直接写 SELECT a FROM t 仍会报歧义，自动生成的 a_1 不能作为可依赖的 SQL 别名。建表查询时，仍需显式指定提供唯一列名。

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
WITH t AS (SELECT 1 AS a, 2 AS a) SELECT * FROM t; 
-- 返回结果
+------+------+
| a    | a_1  |
+------+------+
| 1    | 2    |
+------+------+

-- 不开启 BigQuery 兼容模式：上述语句报歧义错误
SET odps.sql.BigQuery.compatible = false;
WITH t AS (SELECT 1 AS a, 2 AS a) SELECT * FROM t;
-- 返回报错，FAILED: ODPS-0130071:[1,6] Semantic analysis exception - column reference @cte_1786696757513.a is ambiguous
ODPS-0130071:[1,49] Semantic analysis exception - column reference t.a is ambiguous

字符串函数

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 ab
SELECT SUBSTR('abc', 0, 2);
-- 返回 ab
SELECT SUBSTR('abc', -5, 2);
-- 返回 hello
SELECT SUBSTR('hello', 0);
-- 返回报错FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: SQL Runtime Unretryable Error: ODPS-0121095:Invalid argument - in function substr/substring, unsupported length value -1
SELECT SUBSTR('abc', 1, -1);


-- 不开启 BigQuery 兼容模式（默认为false）
SET odps.sql.BigQuery.compatible = false;
-- 返回空字符串
SELECT SUBSTR('abc', 0, 2);  
-- 返回空字符串
SELECT SUBSTR('hello', 0);  

SET odps.sql.udf.strict.mode = false;
-- 返回空字符串
SELECT SUBSTR('abc', 1, -1);   

SET odps.sql.udf.strict.mode = true;
-- 返回报错FAILED: ODPS-0130071:[1,8] Semantic analysis exception - encounter runtime exception while evaluating function substr, detailed message: Argument value out of range - in function substr, unsupported length value: -1
SELECT SUBSTR('abc', 1, -1); 

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 台平算计
SELECT REVERSE('计算平台'); 

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
-- 返回结果乱码（按字节反转）
SELECT REVERSE('计算平台');  

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 12（无分组，返回完整匹配）
SELECT REGEXP_EXTRACT('a12b', '[0-9]+'); 
-- 返回 12（取第一个分组）
SELECT REGEXP_EXTRACT('a12b', 'a([0-9]+)b'); 
-- 返回 [100, 200]
SELECT REGEXP_EXTRACT_ALL('100-200', '[0-9]+'); 

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
-- 无捕获组，返回报错，FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: SQL Runtime Unretryable Error: ODPS-0121065:Argument value out of range - Regex group count is 0, but the specified group index is 1
SELECT REGEXP_EXTRACT('a12b', '[0-9]+');     
-- 返回 12
SELECT REGEXP_EXTRACT('a12b', 'a([0-9]+)b');    

日期与时间函数

BIGINT｜INT DATEDIFF(TIMESTAMP <end_date>, TIMESTAMP <start_date>[, STRING <date_part>

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 1  （按两个时刻之间实际经过的完整单位数计算，62 秒 = 1 个完整分钟）
SELECT DATEDIFF(TIMESTAMP '2025-03-11 00:02:01',
                TIMESTAMP '2025-03-11 00:00:59',
                'mi');
                
-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
-- 返回 2 （先截断到分钟边界再作差）
SELECT DATEDIFF(TIMESTAMP '2025-03-11 00:02:01',
                TIMESTAMP '2025-03-11 00:00:59',
                'mi');

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 2025-07-28
SELECT TO_DATE(TIMESTAMP '2025-07-29 00:30:00.123', 'Asia/Jakarta');

-- 不开启 BigQuery 兼容模式：不支持输入timezone参数。
SET odps.sql.BigQuery.compatible = false;
-- 返回报错，FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: SQL Runtime Unretryable Error: ODPS-0121095:Invalid argument - format string doesn't have year part : Asia/Jakarta
SELECT TO_DATE(TIMESTAMP '2025-07-29 00:30:00.123', 'Asia/Jakarta');

JSON 函数

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 {"f0":null,"f1":"300"}
SELECT TO_JSON(STRUCT(CAST(NULL AS STRING) AS f0, '300' AS f1));

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
-- 返回 {"f1":"300"}
SELECT TO_JSON(STRUCT(CAST(NULL AS STRING) AS f0, '300' AS f1));

聚合函数

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
SELECT STDDEV_SAMP(value), VAR_SAMP(value) FROM VALUES (1.0) t(value);
-- 返回结果
+------------+------------+
| _c0        | _c1        |
+------------+------------+
| NULL       | NULL       |
+------------+------------+

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
SELECT STDDEV_SAMP(value), VAR_SAMP(value) FROM VALUES (1.0) t(value);
-- 返回结果
+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 0.0        | 0.0        |
+------------+------------+

其他函数

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 2025-03-24 10:00:00
SELECT CAST('2025-03-24T10:00:00' AS TIMESTAMP); 
-- 返回 2025-03-24 18:00:00（UTC 10:00 → +08）
SELECT CAST('2025-03-24 10:00:00Z' AS TIMESTAMP);      
-- 返回 2025-03-24 12:30:00（由 +05:30 换算到 +08）
SELECT CAST('2025-03-24 10:00:00+05:30' AS TIMESTAMP);    
-- 返回 2025-03-24 09:00:00 (由东京 +09 换算到 +08）
SELECT CAST('2025-03-24 10:00:00 Asia/Tokyo' AS TIMESTAMP);
-- 返回 2025-03-24 10:00:00.123456789
SELECT CAST('2025-03-24 10:00:00.123456789' AS TIMESTAMP); 

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
-- 返回 NULL（不支持 T 分隔符）
SELECT CAST('2025-03-24T10:00:00' AS TIMESTAMP); 
-- 返回 2025-03-24 10:00:00（忽略 Z，按本地时间）
SELECT CAST('2025-03-24 10:00:00Z' AS TIMESTAMP);     

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 true
SELECT CAST('true'  AS BOOLEAN);  
-- 返回 false 
SELECT CAST('FaLsE' AS BOOLEAN);   
-- 返回报错，FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: SQL Runtime Unretryable Error: ODPS-0121095:Invalid argument - Bad bool value: yes
SELECT CAST('yes'   AS BOOLEAN);  
-- 返回报错，FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: SQL Runtime Unretryable Error: ODPS-0121095:Invalid argument - Bad bool value: 1
SELECT CAST('1'     AS BOOLEAN); 
-- 返回报错，FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: SQL Runtime Unretryable Error: ODPS-0121095:Invalid argument - Bad bool value: 
SELECT CAST(''      AS BOOLEAN);   

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
-- 返回 true
SELECT CAST('yes'   AS BOOLEAN);   
-- 返回 true
SELECT CAST('false' AS BOOLEAN);   
-- 返回 false
SELECT CAST(''      AS BOOLEAN);   

-- 以下示例假定项目时区为 Asia/Shanghai

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 2015-09-14 14:24:09.120+08
SELECT CAST(TIMESTAMP '2015-09-14 14:24:09.120' AS STRING);

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
-- 返回 2015-09-14 14:24:09.12（无时区后缀，小数秒被裁剪）
SELECT CAST(TIMESTAMP '2015-09-14 14:24:09.120' AS STRING);

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 2015-09-14 14:24:09.120
SELECT CAST(TIMESTAMP_NTZ '2015-09-14 14:24:09.120' AS STRING);

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
-- 返回 2015-09-14 14:24:09.12（小数秒被裁剪）
SELECT CAST(TIMESTAMP_NTZ '2015-09-14 14:24:09.120' AS STRING);

-- 开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = true;
-- 返回 2025-04-22 00:00:00
SELECT CAST('2025-04-22' AS TIMESTAMP);   

-- 不开启 BigQuery 兼容模式
SET odps.sql.BigQuery.compatible = false;
-- 返回 NULL
SELECT CAST('2025-04-22' AS TIMESTAMP);