How CEILING() Works in MariaDB

In MariaDB, CEILING() is a built-in numeric function that returns the smallest integer value not less than its argument.

Syntax

The syntax goes like this:

CEILING(X)

Where X is the value to apply the operation to.

Example

Here’s an example:

SELECT CEILING(2.34);

Result:

+---------------+
| CEILING(2.34) |
+---------------+
|             3 |
+---------------+

Passing a negative value produces the following result:

SELECT CEILING(-2.34);

Result:

+----------------+
| CEILING(-2.34) |
+----------------+
|             -2 |
+----------------+

Compared to ROUND()

The CEILING() function is different to the ROUND() function. The ROUND() function would round the number down in some instances, whereas CEILING() always returns the smallest integer value not less than its argument.

SELECT 
    CEILING(2.34),
    ROUND(2.34);

Result:

+---------------+-------------+
| CEILING(2.34) | ROUND(2.34) |
+---------------+-------------+
|             3 |           2 |
+---------------+-------------+

Also, ROUND() allows us to specify the number of decimal places to round to:

SELECT 
    CEILING(2.34),
    ROUND(2.34, 1);

Result:

+---------------+----------------+
| CEILING(2.34) | ROUND(2.34, 1) |
+---------------+----------------+
|             3 |            2.3 |
+---------------+----------------+

Non-Numeric Arguments

Here’s what happens when we provide a non-numeric argument:

SELECT CEILING('Ten');

Result:

+----------------+
| CEILING('Ten') |
+----------------+
|              0 |
+----------------+
1 row in set, 1 warning (0.001 sec)

Let’s check the warning:

SHOW WARNINGS;

Result:

+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Ten' |
+---------+------+-----------------------------------------+

Missing Arguments

Calling CEILING() without an argument results in an error:

SELECT CEILING();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'CEILING'