Data type #

Default type can be empty

In default mode, the default data type cannot be empty. But ANSI standards require it to be nullable.

| ||
|Query example |
|SQL |
|SELECT value, changed FROM system.settings WHERE name LIKE '%dialect_type%'; |
| |\

Time data type (including timezone)

The Time data type has been added.
Time data types with timezone support are part of the ANSI SQL standard and are supported by MySQL and PostgreSQL. The format of the Time data type is hh:mm:ss[.nnnnnnn]. Internally represented as Int64. Its precision uses scale as a parameter. The maximum value of scale is 9, which equals the number of digits after the decimal point; the default value is 3.
Example: Functions using the Time data type.

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);

Date and time type DateTime

1. DateTimeWithoutTz data type added.
   • It does not include a timezone and is essentially similar to DateTime with the timezone set to "UTC".
2. ANSI SQL standard for using DATETIME and INTERVAL values
   • Interval type is supported in default mode, but its format differs in ANSI mode.
   • In ANSI mode, the interval type is YEAR::MONTH and DAY::TIME.
3. Caution: The DateTime format yyyy-mm-dd hh:mm:ss[.nnnnnnn] is not currently supported. Please use the DateTime64 type for storage. Its precision uses scale as a parameter. The maximum value of scale is 9, which equals the number of digits after the decimal point; the default value is 3.

The supported data range for DateTime64 type is: [1900-01-01 00:00:00, 2299-12-31 23:59:59.99999999].

Allow extended type conversion

In ClickHouse mode, implicit type conversion cannot occur between some numerical value types if the conversion would result in precision loss (such as Decimal & Float, Int64 & Float) or performance degradation (such as UInt64 & Ints). In ANSI mode, this type of implicit conversion is allowed.
Related sub-options: allow_extended_type_conversion: 0 / 1

| ||
|Query example 1: Implicit conversion between Decimal and Float |
|SQL |
|SELECT 1::Decimal32(2) UNION ALL SELECT 1::Float64 |
| |\

| ||
|Query example 2: Implicit conversion between Int64 and Float |
|SQL |
|SELECT 1::Int64 UNION ALL SELECT 1::Float64 |
| |\

| ||
|Query example 3: Implicit conversion between UInt64 and Int |
|SQL |
|SELECT 1::UInt64 UNION ALL SELECT 1::Int32 |
| |\

Parser #

Decimal fixed-point data type parsing

By default, numeric literals with a decimal point are parsed as floating-point types. In ANSI mode, literals are parsed as Decimal fixed-point data types to ensure precision.

| ||
|Query example: |
|SQL |
|SELECT toTypeName(1.1); |
| |\

Analyzer #

Prioritize parsing as table fields

When there are duplicate alias and table fields, in CLICKHOUSE Mode, alias is given parsing priority; in ANSI Mode, the table field is given parsing priority. This rule does not apply to the ORDER BY clause.
Related sub-options: prefer_column_name_to_alias: 0 / 1

| ||
|Example Query 1: |
|SQL |
|SELECT a + 1 AS a |
|FROM |
| (SELECT 1 AS a) |
|WHERE a > 1 ORDER BY a; |
| |\

| ||
|Example Query 2: The prefer column name to alias rule does not apply to the ORDER BY clause; in both modes, a is parsed as alias |
|SQL |
|SELECT a AS origin_a, -a AS a |
|FROM |
| (SELECT 1 AS a UNION ALL SELECT 2) |
|ORDER BY a ASC; |
| |\

Ambiguous alias

In ClickHouse Mode, when joining two tables with duplicate fields, referencing that name is interpreted as referencing the field from the first table. This behavior is inconsistent with mainstream databases, and the semantics become even more ambiguous in multi-table joins. In ANSI Mode, referencing ambiguous names is prohibited.

| ||
|Example Query: |
|SQL |
|SELECT a |
|FROM |
| (SELECT 1 AS a) x |
| CROSS JOIN |
| (SELECT 's' AS a) y; |
| |\

Function #

Cast can be nullable

In ANSI Mode, if the parameter of the CAST function is of nullable type, the result type remains nullable.
Related sub-options: cast_keep_nullable

| ||
|Example Query |
|SQL |
|SELECT toTypeName(CAST(1::Int32 AS Int64)), toTypeName(CAST(1::Nullable(Int32) AS Int64)); |
| |\

Decimal type calculation

Decimal division scale extension

In Default Mode, the decimal places of the result type for decimal division are: S = S1. When S1 is not large enough, this can cause significant rounding errors. In ANSI Mode, the scale value is: S = max (6, S1).
Related sub-options: decimal_division_use_extended_scale

| ||
|Example Query |
|SQL |
|SELECT 2.0::Decimal64(1) / 3, toTypeName(2.0::Decimal64(1) / 3); |
| |\

Promote storage type to avoid overflow

Some decimal scenarios are prone to overflow errors due to rescaling. In ANSI Mode, the underlying storage type is automatically promoted to minimize overflow. These scenarios include:

• Multiplication and division between decimal types
• Division between decimal type and Int integer type

Related sub-options: decimal_arithmetic_promote_storage

| ||
|Example Query |
|SQL |
|SELECT 2.0::Decimal32(4) * 3.0::Decimal32(5); |
| |\

Window function

In ANSI Mode, regardless of the frame type, all rows with the same value in the ORDER BY expression are treated as the same row.

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

Accurate support for null value equality judgment

In ANSI mode, just like the regular = operator, comparing two values results in 0 (not equal) or 1 (equal). In other words: 'a' <=> 'b' produces 0, 'a' <=> 'a' produces 1.
Unlike the regular = operator, NULL values have no special meaning, so it never produces NULL as a result. Therefore: 'a'<=>NULL produces 0, NULL<=>NULL produces 1.

| ||
|Example: |
|SQL |
|SELECT NULL<=>NULL; |
|SELECT NULL<=>0; |
|SELECT 0 IS DISTINCT FROM NULL; |
|SELECT 0 IS NOT DISTINCT FROM NULL; |
|SELECT ''; |
| |\

Subquery expression #

Correlated subquery

ANSI mode supports correlated subqueries.
Example:

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

Using NULL values

In default mode, for cases where there is no join record, the outer join defaults to 0 or "". In ANSI mode, the default value is NULL.
Related suboption: join_use_nulls

| ||
|Example: |
|SQL |
|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; |
| |\

Using outer join

In default mode, you can specify conditions for the outer join. In ANSI mode, specifying conditions for the outer join is not allowed.

| ||
|Example 1: When using a left join, the output of the join key is the data from the left table |
|SQL |
|SELECT k FROM (SELECT 1 as k) LEFT JOIN (SELECT 2 as k) USING k; |
| |\

| ||
|Example 2: When using a right join, the output of the join key is the data from the right table |
|SQL |
|SELECT k FROM (SELECT 1 as k) RIGHT JOIN (SELECT 2 as k) USING k; |
| |\

| ||
|Example 3: When using a full join, the output of the join key is data from both tables |
|SQL |
|SELECT k FROM (SELECT 1 as k) FULL JOIN (SELECT 2 as k) USING k; |
| |\

Using alias join

In default mode, when the join uses a key with the same alias, the alias is visible to the left table. This feature is disabled in ANSI mode.

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

Hash join / nested loop join / merge join support:

In ANSI mode, hash join, nested loop join, and merge join are supported.
Example:

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 mode output: 
# USING <=> hash 
1 1 
4 \N# USING <=> partial_merge 
1 1 
4 \N# USING <=> nested_loop 
1 1 
4 \N

Set operation

Operation in default mode

In default mode, for union / intersect / except operations, if all / distinct (deduplication) semantics are not explicitly specified, the default is all semantics; in ANSI mode, the default is distinct semantics.

| ||
|Example: |
|SQL |
|SELECT 1 UNION SELECT 1; |
| |\