You need to enable JavaScript to run this app.
导航
ByteHouse云数仓版
  • 文档首页
    • /ByteHouse云数仓版/SQL 语法/SQL 兼容性/ANSI 兼容性
ANSI 兼容性
最近更新时间：2024.11.06 13:58:48首次发布时间：2021.10.22 10:06:12
我的收藏

ANSI 模式旨在使 SQL 语法和执行时的行为，与 ANSI 标准或其他主流数据库更加一致。

设置 ANSI 模式

通过dialect_type选项，可以使ByteHouse 在 ANSI 模式 和 ClickHouse 模式之间切换，例如：

SET dialect_type = 'CLICKHOUSE';
SET dialect_type = 'ANSI';

ByteHouse 默认使用的是 ClickHouse 模式。
说明
推荐使用上述的 SET 命令设置 dialect_type，不建推荐通过查询级别的 SETTINGS 来设置，如：

SELECT ... SETTINGS dialect_type = 'ANSI';

在实现方面，许多 ANSI 功能由单独的子选项控制，dialect_type 可以视为这些子选项的总和。 如果需要，可以使用子选项来控制 ANSI 模式下的特定行为，例如：

SET dialect_type = 'ANSI'；

SELECT ... SETTINGS join_use_nulls = 0;

下面列出了 ANSI 相关功能。 如果此功能有单独的子选项，则由 相关子选项 进行解释 。

兼容性详情

数据类型

默认类型可为空

默认模式下的默认数据类型不可为空。 但 ANSI 标准要求它可以为空。

查询示例

SELECT value, changed FROM system.settings WHERE name LIKE '%dialect_type%';

默认模式下的输出：
0, 0

以ANSI模式输出：
1, 1

时间类型 Time（含时区 timezone）

添加了时间 Time 数据类型。
支持时区的时间数据类型是 ANSI SQL 标准，受 MySQL、PostgreSQL 支持。 时间数据类型的格式为 hh:mm:ss[.nnnnnnn]。 内部表示为 Int64。 它的精度以scale作为参数。 Scale 的最大值为 9，等于小数点后的位数；默认值为 3。
查询示例：使用Time数据类型的函数示例。

select toHour('11:20:30'::Time);
select Hour('11:20:30'::Time);
select toMinute('11:20:30'::Time);
select Minute('11:20:30'::Time);
select toSecond('11:20:30'::Time);
select Second('11:20:30'::Time);
select addYears('11:20:30'::Time(3), 5);
select addYears('11:20:30'::Time(4), 20);
select subtractYears('11:20:30'::Time(5), 5);
select subtractYears('11:20:30'::Time(6), 20);
select addMonths('11:20:30'::Time(3), 5);
select addMonths('11:20:30'::Time(4), 20);
select subtractMonths('11:20:30'::Time(5), 5);
select subtractMonths('11:20:30'::Time(6), 20);
select addDays('11:20:30'::Time(3), 5);
select addDays('11:20:30'::Time(4), 20);
select subtractDays('11:20:30'::Time(5), 5);
select subtractDays('11:20:30'::Time(6), 20);
select addHours('11:20:30'::Time(3), 5);
select addHours('11:20:30'::Time(4), 20);
select subtractHours('11:20:30'::Time(5), 5);
select subtractHours('11:20:30'::Time(6), 20);
select addMinutes('11:20:30'::Time(2), 50);
select addMinutes('11:20:30'::Time(3), 2000);
select subtractMinutes('11:20:30'::Time, 50);
select subtractMinutes('11:20:30'::Time(1), 5000);
select addSeconds('11:20:30'::Time, 500);
select addSeconds('11:20:30'::Time(2), 200000);
select subtractSeconds('11:20:30'::Time, 500);
select subtractSeconds('11:20:30'::Time(1), 500000);

时间日期 DateTime
  1. 新增数据类型 DateTimeWithoutTz。
    • 它不包含时区，本质上类似于时区为 “UTC” 的 DateTime。
  2. 使用 DATETIME 和 INTERVAL 值的 ANSI SQL 标准
    • 默认模式下支持 interval 类型，但其在ANSI模式下格式不同。
    • ANSI 模式下的 interval 类型为 YEAR::MONTH 和 DAY::TIME。
  3. 注意：暂不兼容 yyyy-mm-dd hh:mm:ss[.nnnnnnn] 的 DateTime 格式，请使用 DateTime64 类型进行存储，它的精度以scale作为参数。 Scale 的最大值为 9，等于小数点后的位数；默认值为 3。

DateTime64 类型支持的数据范围为： [1900-01-01 00:00:00, 2299-12-31 23:59:59.99999999] 。

Allow extended type conversion

ClickHouse 模式下，隐式类型转换无法发生在一些数值类型间，如果类型转换会导致精度损失（如 Decimal & Float, Int64 & Float）或性能下降（如 UInt64 & Ints）。ANSI 模式下，这一类隐式转换被允许进行。
相关子选项：allow_extended_type_conversion: 0 / 1

样例查询 1：Decimal 和 Float 的隐式转换

SELECT 1::Decimal32(2) UNION ALL SELECT 1::Float64

ClickHouse 模式下输出：
DB::Exception: There is no supertype for types Decimal(9, 2), Float64 because some of them have no lossless conversion to Decimal

ANSI 模式下输出：
1
1

样例查询 2：Int64 和 Float 的隐式转换

SELECT 1::Int64 UNION ALL SELECT 1::Float64

ClickHouse 模式下输出：
DB::Exception: There is no supertype for types Int64, Float64 because some of them are integers and some are floating point, but there is no floating point type, that can exactly represent all required integers

ANSI 模式下输出：
1
1

样例查询 3：UInt64 和 Int 的隐式转换

SELECT 1::UInt64 UNION ALL SELECT 1::Int32

ClickHouse 模式下输出：
DB::Exception: There is no supertype for types UInt64, Int32 because some of them are signed integers and some are unsigned integers, but there is no signed integer type, that can exactly represent all required unsigned integer values

ANSI 模式下输出：
1
1

解析器

Decimal 定点数据类型解析

默认情况下，带有小数点的数字文字被解析为浮点类型。 在 ANSI 模式下，文字被解析为Decimal 定点数据类型以确保精度。

查询示例：

SELECT toTypeName(1.1);

默认模式下的输出：Float64

以ANSI模式输出：Decimal(18, 1)

分析器

优先解析为表字段

当存在重名的 alias 和表字段时，在 CLICKHOUSE 模式下，会优先解析为 alias；在 ANSI 模式下，则会优先解析为表字段。这一规则不适用 ORDER BY 子句。
相关子选项：prefer_column_name_to_alias: 0 / 1

样例查询 1：

SELECT a + 1 AS a
FROM
 (SELECT 1 AS a)
WHERE a > 1 ORDER BY a;

ClickHouse 模式下输出：
2

ANSI 模式下输出：
空集

样例查询 2：prefer column name to alias 规则不适用 ORDER BY 子句，两种模式下 a 都被解析为 alias

SELECT a AS origin_a, -a AS a
FROM
 (SELECT 1 AS a UNION ALL SELECT 2)
ORDER BY a ASC;

ClickHouse 模式下输出：
2, -2
1, -1

ANSI 模式下输出：
2, -2
1, -1

有歧义的别名

ClickHouse 模式下，当 join 两表存在重名字段时，引用该名字被解释成对第一张表字段的引用。这一特性和主流数据库不符，且在多表 join 的情况下语义更加模糊。ANSI 模式下，禁止对歧义名字的引用。

样例查询：

SELECT a
FROM
  (SELECT 1 AS a) x
  CROSS JOIN
  (SELECT 's' AS a) y;

ClickHouse 模式下输出：
1

ANSI 模式下输出：
DB::Exception: Identifier a is ambiguous.

函数

Cast 可为空

在 ANSI 模式下，如果 CAST 函数的参数为可空 Nullable 类型，则结果类型仍为 Nullable。
相关子选项：cast_keep_nullable

查询示例

SELECT toTypeName(CAST(1::Int32 AS Int64)), toTypeName(CAST(1::Nullable(Int32) AS Int64));

默认模式下的输出：
Int64, Int64

以ANSI模式输出：
Int64, Nullable(Int64)

Decimal 类型计算

Decimal 除法的位数扩展

默认模式下，小数除法结果类型的小数位数为：S = S1。 当 S1 不够大时，这会导致较大的舍入误差。 在 ANSI 模式下，比例值为：S = max (6, S1)。
相关子选项：decimal_division_use_extended_scale

查询示例

SELECT 2.0::Decimal64(1) / 3, toTypeName(2.0::Decimal64(1) / 3);

默认模式下的输出：
0.6, Decimal(18, 1)

以ANSI模式输出：
0.666666, Decimal(18, 6)

扩大存储类型以避免溢出

一些 decimal 小数场景由于重新缩放容易出现溢出错误。在ANSI模式下，会自动提升底层存储类型以最小化溢出。这些场景包括：

  • Decimal 类型之间的乘法和除法
  • Decimal 类型和 Int 整数类型的除法

相关子选项：decimal_arithmetic_promote_storage

查询示例

SELECT 2.0::Decimal32(4) * 3.0::Decimal32(5);

默认模式下的输出：
DB::Exception: Decimal math overflow

以ANSI模式输出：
6.000000000

窗口函数

在 ANSI 模式下，无论帧类型如何，所有ORDER BY表达式中具有相同值的行都被视为相同行。

查询示例

WITH nums AS (SELECT arrayJoin([1, 2, 1, 1]) as src) SELECT percent_rank() OVER (ORDER BY src ROWS CURRENT ROW) from nums

默认模式下的输出：
0
0.3333333333333333
0.6666666666666666
1

以ANSI模式输出：
0
0
0
1

空值相等判断的准确支持

在 ANSI 模式下，与常规 = 运算符一样，比较两个值的结果为 0（不等于）或 1（等于）； 换句话说：'a' <=> 'b' 产生 0，'a' <=> 'a' 产生 1。
与常规=运算符不同，NULL的值没有特殊含义，因此它永远不会产生NULL作为结果的情况；因此：'a'<=>NULL产生0，NULL<=>NULL产生1。

查询示例

SELECT NULL<=>NULL;
SELECT NULL<=>0;
SELECT 0 IS DISTINCT FROM NULL;
SELECT 0 IS NOT DISTINCT FROM NULL;
SELECT '';

以ANSI模式输出：
1
0
1
0

子查询表达式

关联子查询

ANSI 模式支持关联子查询。
查询示例：

INSERT INTO orders(order_id, customer_id, amount) VALUES
  (1, 101, 100),
  (2, 101, 120),
  (3, 102, 30);

SELECT order_id
FROM customers x
WHERE
  amount = (SELECT avg(amount)FROM customers yWHERE x.customer_id = y.customer_id)

Join 连接

使用空值 NULL

在默认模式下，对于没有连接记录的情况，外连接默认为0或"。在ANSI模式下，默认值为 NULL。
相关子选项：join_use_nulls

查询示例

SELECT a, b FROM (SELECT 1 AS k, 100 AS a) x LEFT JOIN (SELECT 2 AS k, 200 AS b) y ON x.k = y.k;

默认模式下的输出：
100, 0

以ANSI模式输出：
100, NULL

使用外部连接

在默认模式下，可以指定外连接的条件。在ANSI模式下，外连接不允许使用指定条件。

查询示例1：左连接时，连接键的输出结果为左表数据

SELECT k FROM (SELECT 1 as k) LEFT JOIN (SELECT 2 as k) USING k;

默认模式下的输出：
1

以ANSI模式输出：
Syntax error: failed at position 57;

查询示例2：右连接时，连接键的输出结果为右表数据

SELECT k FROM (SELECT 1 as k) RIGHT JOIN (SELECT 2 as k) USING k;

默认模式下的输出：
2

以ANSI模式输出：
Syntax error: failed at position 57;

查询示例3：Full join时，join键的输出结果为左右表数据

SELECT k FROM (SELECT 1 as k) FULL JOIN (SELECT 2 as k) USING k;

默认模式下的输出：
1
2

以ANSI模式输出：
Syntax error: failed at position 57;

使用别名Join

在默认模式下，当使用键的连接具有相同的别名时，该别名对左表可见。此功能在 ANSI 模式下禁用。

查询示例

SELECT a + 1 AS k FROM (SELECT 1 as a) JOIN (SELECT 2::UInt16 AS k) USING k;

默认模式下的输出：
2

以ANSI模式输出：
DB::Exception: Can not find column 'k' in join left side

Hash Join / NestedLoop Join / Merge Join 支持：

在 ANSI 模式下，支持哈希连接/嵌套循环连接/合并连接。
示例

CREATE TABLE nse_lhs (key int, value Nullable(UInt8)) ENGINE=CnchMergeTree order by key; 
CREATE TABLE nse_rhs (key int, value Nullable(UInt8)) ENGINE=CnchMergeTree order by key; 

INSERT INTO nse_lhs VALUES (1,1) (2, 2) (3, NULL) (4, NULL) (5,6) (6, NULL); 
INSERT INTO nse_rhs VALUES (1,1) (2, NULL) (3, 2) (4, NULL) (5,7) (6, 0); 

select '# Null safe join (equi hash join)'; 
SELECT key, value FROM nse_lhs JOIN nse_rhs ON nse_lhs.key=nse_rhs.key AND nse_rhs.value IS NOT DISTINCT FROM nse_lhs.value; 
select ''; 

SET join_algorithm='partial_merge'; 
select '# Null safe join (merge join)'; 
SELECT key, value FROM nse_lhs JOIN nse_rhs ON nse_lhs.key=nse_rhs.key AND nse_rhs.value<=>nse_lhs.value; 

SET join_algorithm='nested_loop'; 
select '# Null safe join (nested loop join)'; 
SELECT key, value FROM nse_lhs JOIN nse_rhs ON nse_lhs.key=nse_rhs.key AND nse_rhs.value<=>nse_lhs.value; SQL

--以ANSI模式输出：
# USING <=> hash
1 1
4 \N# USING <=> partial_merge
1 1
4 \N# USING <=> nested_loop
1 1
4 \N

Set 集合操作

操作默认模式

默认模式下，对于 union / intersect / except 操作，如果没有显示指定 all / distinct（去重） 语义，则默认为 all 语义； ANSI模式下，默认为 distinct 语义；

查询示例

SELECT 1 UNION SELECT 1;

默认模式下的输出：
1
1

以ANSI模式输出：
1