Set the dialect type to MySQL #

By setting dialect_type='MYSQL', you can run the functions listed below according to MySQL methods.

ENABLE OPTIMIZER=1 
SET dialect_type='MYSQL'; 
or 
[query] + SETTINGS dialect_type='MYSQL'; 
DESC TABLE t1 SETTINGS dialect_type='MYSQL'; 
or 
CLICKHOUSE_CLIENT --dialect_type=MYSQL

• format()
• locate()
• adddate()
• date_sub()
• datediff()
• dayofweek(): In MySQL, the default first day is Sunday. In ByteHouse, the default first day is Monday.
• concat_ws()

Control flow functions #

CASE

• Syntax:
  • Usage 1: CASE value WHEN comparison_value THEN result [ELSE result] END
  • Usage 2: CASE WHEN condition THEN result [ELSE result] END
• Description: Returns a value when the condition is met. Otherwise, returns the ELSE result; if ELSE is not provided, returns NULL. ****

IF function

• Syntax: IF(expr1, expr2, expr3)
• Description: If expr1 is TRUE, returns expr2; otherwise, returns expr3. Three parameters are required; in cases with two parameters, use NULL as the optional third parameter.

IFNULL function

• Syntax: IFNULL(expr1, expr2)
• Description: If expr1 is not NULL, returns expr1; otherwise, returns expr2.

NULLIF function

• Syntax: NULLIF(expr1, expr2)
• Description: If expr1 equals expr2, returns NULL; otherwise, returns expr1.

Number functions #

Basic arithmetic operators

• Addition (+): Calculates the sum of two numbers.
• Subtraction (-): Calculates the difference between two numbers.
• Multiplication (*): Multiplies two numbers.
• Division (/): One number divided by another number.
• Integer division (div): Divides one number by another number and returns the integer part of the quotient.
• Modulo (% or mod): Returns the remainder of the division operation.

Mathematical functions

• Absolute (abs): Returns the absolute value of a number.
• Arc cosine (acos): Calculates the arc cosine of a number.
• Arc sine (asin): Calculates the arc sine of a number.
• Arc tangent (atan): Calculates the arc tangent of a number.
• Arc tangent of two numbers (atan2): Returns the arc tangent of the quotient of its parameters.
• Ceiling (ceiling or ceil): Rounds a number up to the nearest integer.
• Cosine (cos): Calculates the cosine of a number.
• Cotangent (cot): Calculates the cotangent of a number.
• CRC32 (crc32): Calculates the cyclic redundancy check value of a string.
• Degrees (degrees): Converts radians to degrees.
• Exponential (exp): Calculates the exponent of a number.
• Floor (floor): Rounds a number down to the nearest integer.
• Natural logarithm (ln): Calculates the natural logarithm of a number.
• Logarithm (log): Calculates the logarithm of a number. Use log_with_base to represent logarithms with a base.
• Base-2 logarithm (log2): Calculates the logarithm of a number with base 2.
• Base-10 logarithm (log10): Calculates the logarithm of a number with base 10.
• Pi (pi): Returns the value of π.
• Power (power or pow): Raises a number to the power of another number.
• Radians (radians): Converts degrees to radians.
• Random number (rand): Generates a random number. Use randCanonical for numbers between 0 and 1.
• Round (round): Rounds a number to the specified number of decimal places.
• Sign (sign): Determines the sign of a number and returns -1, 0, or 1.
• Sine (sin): Calculates the sine of a number.
• Square root (sqrt): Calculates the square root of a number.
• Tangent (tan): Calculates the tangent of a number.

Date and time functions #

Date and time function operations

• ADDDATE(): Add a specific time interval to a date.
• SUBDATE(): Subtract a specific time interval from a date.
• ADDTIME(): Add a specific time interval to a time or datetime value.
• SUBTIME(): Subtract a specific time interval from a time or datetime value.
• DATE_SUB(): Subtract a time interval from a date.
• DATE_ADD(): Add a time interval to a date.

Date and time extraction

• CURDATE(): Return the current date.
• CURTIME(): Return the current time.
• NOW(): Return the current date and time.
• SYSDATE(): Return the system's current date and time.
• EXTRACT(): Extract a specified part of a date.
• YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND(): Extract a specific component from a date or time value.

Format functions

• DATE_FORMAT(): Format a date according to the specified format.
• TIME_FORMAT(): Format a time according to the specified format.

Conversion functions

• STR_TO_DATE(): Convert a string to a date according to the specified format.
• UNIX_TIMESTAMP(): Convert a date to a UNIX timestamp.
• FROM_UNIXTIME(): Convert a UNIX timestamp to a date.

Timezone functions

• CONVERT_TZ(): Convert a datetime value from one timezone to another.

Other functions

• DATEDIFF(): Return the number of days between two dates.
• TIMEDIFF(): Return the difference between two time or datetime expressions.
• DATE(), TIME(): Extract the date or time part from a datetime expression.
• DAYNAME(), MONTHNAME(): Return the name of the weekday or month from a date.
• WEEK(), YEARWEEK(): Return the week number of a date.

String functions #

Operations and analysis

• ASCII(): Return the ASCII value of the first character in a string.
• BIN(): Convert a number to a binary string.
• BIT_LENGTH(): Return the bit length of a string.
• CHAR(): Construct a string from a series of ASCII values.
• CHAR_LENGTH(): Return the number of characters in a string.
• CONCAT(): Concatenate two or more strings into one string.
• CONCAT_WS(): Concatenate strings with a specified separator.
• ELT(): Return the Nth element in a list of strings.
• EXPORT_SET(): Return a string representing the bits set in a value.
• FIELD(): Return the index of a value in a list of values.
• FIND_IN_SET(): Find the position of a string in a comma-separated list of strings.
• FORMAT(): Format a number to the specified number of decimal places.
• FROM_BASE64(): Decode a Base64-encoded string.
• HEX(): Convert a string or number to hexadecimal.
• INSTR(): Returns the position of the first occurrence of a substring in a string.
• LCASE() or LOWER(): Converts a string to lowercase.
• LEFT(): Returns the leftmost part of a string of the specified length.
• LENGTH(): Returns the byte length of a string.
• LIKE: Uses SQL pattern matching with wildcards to perform pattern matching.
• LOCATE(): Returns the position of the first occurrence of a substring in a string.
• LPAD(): Pads a string on the left with another string until a certain length is reached.
• LTRIM(): Removes leading spaces from a string.
• MAKE_SET(): Constructs a set of comma-separated strings corresponding to the bits set in the given value.
• MID(), SUBSTR(), SUBSTRING(): Returns a substring from a string.
• OCT(): Converts a number to an octal string.
• ORD(): Returns the ASCII code of the leftmost character in a string.
• POSITION(): Synonym for LOCATE().
• QUOTE(): Escapes special characters in a string for use in SQL statements.
• REPEAT(): Repeats a string a specified number of times.
• REPLACE(): Replaces occurrences of a specified substring with another substring.
• REVERSE(): Reverses a string.
• RIGHT(): Returns the rightmost part of a string of the specified length.
• RPAD(): Pads a string on the right with another string until a certain length is reached.
• RTRIM(): Removes trailing spaces from a string.
• SOUNDEX(): Returns the soundex value for a string.
• SPACE(): Returns a string consisting of a specified number of space characters.
• STRCMP(): Compares two strings.
• SUBSTRING_INDEX(): Returns the substring before the specified number of occurrences of a delimiter in a string.
• TO_BASE64(): Encodes a string using base64.
• TRIM(): Removes leading and trailing spaces from a string.
• UCASE() or UPPER(): Converts a string to uppercase.
• UNHEX(): Converts a hexadecimal string to a regular string.

Aggregate functions #

Average value AVG (Average)

• Calculates the average numerical value in a column.
• ByteHouse returns nan for empty tables, which is different from MySQL and ADB, which return NULL.

BIT_AND, BIT_OR, BIT_XOR bitwise AND, bitwise OR, bitwise XOR

• Performs bitwise operations on the values in a column.
• BIT_AND returns the bitwise AND of all values.
• BIT_OR returns the bitwise OR of all values.
• BIT_XOR returns the bitwise XOR of all values.

COUNT count

• Calculates the number of rows that meet the specified condition.
• COUNT(DISTINCT xx) counts unique values.
• COUNT(ALL xx) counts all values, including repeated items.

MAX and MIN maximum and minimum values

• Return the maximum and minimum values in the column, respectively.
• Can be applied to various data types, including Date and String.
• For numeric types with inserted NULL values, behavior may vary depending on the system.

STD, STDDEV_POP, and VAR, VAR_POP, VAR_SAMP

• Calculates standard deviation and variance.
• STD and STDDEV_POP return the overall standard deviation.
• VAR_POP returns the overall variance.
• VAR_SAMP returns the sample variance.
• The precision and handling of empty sets may differ; ByteHouse returns nan for empty sets.

SUM summation

• Calculates the sum of a numeric column.

Important notes #

• Conversion and default values: When aggregate functions are run on data types with implicit conversion or default values, results may vary greatly across different SQL systems. For example, ByteHouse's handling of numeric default values may differ from MySQL and ADB, affecting functions such as MIN and MAX.
• Handling of NULL values: Aggregate functions' handling of NULL values will affect the result. In ByteHouse, certain functions (such as MIN and MAX) may return 0 or the actual value, while MySQL or ADB will return NULL.
• Precision issues: When calculating standard deviation or variance, precision issues may arise due to the internal representation of floating-point numbers.

Not supported yet #

The following MySQL functions are not supported yet:

• convert_tz() : Currently, GMT/MET timezone parameters are not supported

Inconsistent with MySQL

| | | \

Usage differs from MySQL

The usage of the following functions differs from MySQL. Please use with caution:

| | | | \