数据类型

默认类型可为空

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

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

时间类型 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

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

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

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

解析器

Decimal 定点数据类型解析

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

SELECT toTypeName(1.1);

分析器

优先解析为表字段

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

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

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

有歧义的别名

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

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

函数

Cast 可为空

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

SELECT toTypeName(CAST(1::Int32 AS Int64)), toTypeName(CAST(1::Nullable(Int32) AS 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);

扩大存储类型以避免溢出

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

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

相关子选项：decimal_arithmetic_promote_storage

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

窗口函数

在 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

空值相等判断的准确支持

在 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 模式支持关联子查询。
查询示例：

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;

使用外部连接

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

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

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

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

使用别名Join

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

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

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;