You need to enable JavaScript to run this app.
导航
ByteHouse 企业版
  • 文档首页
    • /
  • ByteHouse 企业版

ANSI 兼容性

最近更新时间：2023.11.22 14:07:12

首次发布时间：2023.11.22 14:07:12

我的收藏

ByteHouse 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。

  1. 使用 DATETIME 和 INTERVAL 值的 ANSI SQL 标准

默认模式下支持 interval 类型，但其在ANSI模式下格式不同。
ANSI 模式下的 interval 类型为 YEAR::MONTH 和 DAY::TIME。

  1. 注意：暂不兼容 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] 。

解析器

表达式别名

在 ByteHouse 中，允许在任意子句中的表达式上定义别名；而在 ANSI 模式下，仅允许在 SELECT 子句中的表达式上定义别名。

查询示例1：在With子句中定义别名

WITH
  1 AS a,
  (SELECT 2) AS b
SELECT
  a, b;

默认模式下的输出：
1, 2

以ANSI模式输出：
Syntax Error
（注：语法错误）

查询示例2：在WHERE子句中定义别名

SELECT
  a
WHERE (2 > 1) AS a

默认模式下的输出：
1

以ANSI模式输出：
Syntax Error
（注：语法错误）

子表达式的别名

ByteHouse 模式允许您为子表达式定义别名，但这在 ANSI 模式下被禁用。

查询示例：

SELECT 1 + (1 AS a) > a;

默认模式下的输出：
1

以ANSI模式输出：
Syntax Error
（注：语法错误）

Decimal 定点数据类型解析 *

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

查询示例：

SELECT toTypeName(1.1);

默认模式下的输出：
Float64

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

分析器

SELECT 语句下别名可见范围

默认模式下，SELECT 子句中定义的别名对外部查询和当前查询的任何子句都可见； 在 ANSI 模式下，别名仅对外部查询和当前查询的 ORDER BY 子句可见。

查询示例1：别名对WHERE子句不可见

SELECT 1 AS a
WHERE a > 0;

默认模式下的输出：
1=

以ANSI模式输出：
DB::Exception: Can not resolve identifier: a
（注：DB抛出异常，无法解析标识符 a）

查询示例2：别名对SELECT子句不可见

SELECT 1 AS a, a + 1;

默认模式下的输出：
1, 2

以ANSI模式输出：
DB::Exception: Can not resolve identifier: a
（注：DB抛出异常，无法解析标识符 a）

查询示例3：别名对ORDER BY子句可见

SELECT 1 AS a
ORDER BY a;

默认模式下的输出：
1

以ANSI模式输出：
1

有歧义的别名

在ANSI模式下，多表联接的情况下禁止引用有歧义的名称，该特性与主流数据库不一致。

查询示例：

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

默认模式下的输出：
1

以ANSI模式输出：
DB::Exception: Identifier a is ambiguous.
（注：DB抛出异常，标识符 a 不明确）

函数

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表达式中具有相同值的行都被视为相同行。

Sample query:
查询示例：

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 y
             WHERE 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=Memory;
CREATE TABLE nse_rhs (key int, value Nullable(UInt8)) ENGINE=Memory;

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;

以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