ANSI mode is designed to make SQL syntax and runtime behaviour more consistent with the ANSI standard or other mainstream database systems.

Set ANSI mode #

By using the dialect_type option, ByteHouse can switch between ANSI mode and ClickHouse mode, for example:

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

By default, ByteHouse uses ClickHouse mode.
Note
It is recommended to use the above SET command to set dialect_type. It is not recommended to set it via query-level SETTINGS, such as:

SELECT ... SETTINGS dialect_type = 'ANSI';

In terms of implementation, many ANSI functions are controlled by separate sub-options, and dialect_type can be regarded as the sum of these sub-options. If necessary, sub-options can be used to control specific behaviour in ANSI mode, for example:

SET dialect_type = 'ANSI'； 

SELECT ... SETTINGS join_use_nulls = 0;

ANSI-related functions are listed in ANSI compatibility details. If a function has a separate sub-option, it is explained by the relevant sub-option.