Arithmetic Operators in ClickHouse: Functions, Symbols, and Examples

ClickHouse, a high-performance columnar database, supports various arithmetic operations as part of its powerful SQL query engine. Like many SQL-based systems, ClickHouse allows both symbolic and functional representations of arithmetic operations. Understanding how ClickHouse handles these operations under the hood, especially how it converts symbolic operators to functions helps developers to write efficient and expressive queries.

 

This post explains what an operator is, what arithmetic operators ClickHouse supports, how operator precedence works, and how you can use both symbols and function names in your queries.

 

What is an Operator?

An operator is a special symbol or function that tells the database engine to perform a specific operation on one or more operands (values). In arithmetic, operators perform mathematical calculations such as addition or multiplication.

 

Arithmetic Operators in ClickHouse

ClickHouse supports standard arithmetic operators. These are automatically transformed into corresponding functions during query parsing.

 

Following table summarizes the Arithmetic Operators.

Symbol	Function	Description	Example (Symbol)	Example (Function)
+	plus	Addition	1 + 2	plus(1, 2)
-	minus	Subtraction	5 - 3	minus(5, 3)
*	multiply	Multiplication	2 * 4	multiply(2, 4)
/	divide	Division	10 / 2	divide(10, 2)
%	modulo	Modulo (remainder)	10 % 3	modulo(10, 3)

 

Operator Precedence and Associativity

ClickHouse parses queries using standard mathematical rules:

·      Precedence: *, /, % are evaluated before +, -.

·      Associativity: Operators of the same precedence are evaluated left to right.

 

-- Addition
SELECT 1 + 2; -- returns 3
SELECT plus(1, 2); -- returns 3

-- Subtraction
SELECT 5 - 3; -- returns 2
SELECT minus(5, 3); -- returns 2

-- Multiplication
SELECT 3 * 4; -- returns 12
SELECT multiply(3, 4); -- returns 12

-- Division
SELECT 10 / 2; -- returns 5
SELECT divide(10, 2); -- returns 5

-- Modulo
SELECT 10 % 3; -- returns 1
SELECT modulo(10, 3); -- returns 1

-- Other Examples
SELECT 10 + 2 * 3; -- Interpreted as: 10 + (2 * 3) = 16
SELECT plus(10, multiply(2, 3)); Returns 16, Equilant of above one using functions.

In summary, ClickHouse arithmetic operators offer both symbolic (+, -, etc.) and functional (plus, minus, etc.) forms. Internally, ClickHouse converts symbolic operators into functions during parsing. Understanding this transformation and operator precedence helps to write clearer and more performant queries.

  

Previous                                                    Next                                                    Home