LEAST()

LEAST(value1, value2, …) returns the smallest (minimum) value from a list of two or more arguments. Mixed numeric types are implicitly promoted to a common type following MatrixOne’s type promotion rules. Returns NULL if any argument is NULL.

Description

LEAST() returns the smallest value from the arguments provided. It compares two or more values and returns the minimum.

With the v4.1.0 update, LEAST() implicitly promotes mixed numeric argument types rather than rejecting them. For example, comparing a BIGINT with a DOUBLE automatically promotes both to DOUBLE before comparison.

When any argument is NULL, the function returns NULL, matching MySQL behavior.

Syntax

> LEAST(value1, value2, ...)

Arguments

Arguments

Description

value1, value2, …

Required. Two or more values to compare. Supported types include integers, floating-point numbers, decimals, strings, and temporal types.

Type Promotion Rules

When arguments have different numeric types, LEAST() promotes them to the widest common type:

  • TINYINT + INT promotes to INT (or the wider integer type).

  • BIGINT + DOUBLE promotes to DOUBLE.

  • BIGINT + DECIMAL promotes to DECIMAL with appropriate scale.

  • Signed BIGINT + BIGINT UNSIGNED that exceeds INT64 range promotes to DECIMAL128 (lossless).

  • Mixed-scale decimals promote to the wider precision/scale.

  • All-unsigned arguments promote to the widest unsigned type.

Same-type comparisons proceed without promotion.

Examples

DROP DATABASE IF EXISTS dbfuncs;
CREATE DATABASE dbfuncs;
USE dbfuncs;

-- Basic comparison
SELECT LEAST(1, 2.0) AS result;
-- result: 1.0

-- Mixed integer and float types
SELECT LEAST(CAST(1 AS BIGINT), CAST(2.0 AS DOUBLE)) AS result;
-- result: 1.0

SELECT LEAST(CAST(5 AS BIGINT), CAST(2.5 AS DECIMAL(10,2))) AS result;
-- result: 2.50

-- Mixed integer widths
SELECT LEAST(CAST(1 AS TINYINT), CAST(2000 AS INT)) AS result;
-- result: 1

-- NULL handling: any NULL argument makes the result NULL
SELECT LEAST(1, NULL, 2.0) AS result;
-- result: NULL

-- Table-driven: aggregate vs aggregate (BIGINT vs DOUBLE)
CREATE TABLE toll_transactions(id INT, gate_processing_sec DOUBLE, lanes INT);
INSERT INTO toll_transactions VALUES
  (1, 3.5, 2), (2, 7.0, 2), (3, 1.0, 4), (4, 12.5, 3);

SELECT LEAST(COUNT(*), AVG(gate_processing_sec)) AS result FROM toll_transactions;
-- result: 4.0

-- Per-row mixed column types
CREATE TABLE mixed_num(a BIGINT, b DOUBLE, c DECIMAL(20,4));
INSERT INTO mixed_num VALUES (10, 2.5, 7.25), (3, 9.0, 4.5), (-5, -1.0, 100.0);

SELECT a, b, LEAST(a, b) AS lab FROM mixed_num ORDER BY a;
SELECT a, b, c, LEAST(a, b, c) AS l3 FROM mixed_num ORDER BY a;

-- Unsigned-only mixed widths
CREATE TABLE uns(u1 TINYINT UNSIGNED, u2 INT UNSIGNED, u3 BIGINT UNSIGNED);
INSERT INTO uns VALUES (1, 300, 5000000000), (200, 50, 1), (100, 70000, 9000000000);

SELECT LEAST(u1, u2) AS l_u FROM uns ORDER BY u1;
SELECT LEAST(u1, u2, u3) AS l3u FROM uns ORDER BY u1;

-- Signed + unsigned exceeding int64 promotes to decimal128
SELECT LEAST(CAST(-3 AS BIGINT), CAST(9000000000000000000 AS BIGINT UNSIGNED)) AS result;
-- result: -3

-- Clamp pattern: GREATEST(0, expr) pipeline partner
SELECT LEAST(0, COUNT(*) - lanes * (3600.0 / AVG(gate_processing_sec))) AS est
FROM toll_transactions GROUP BY lanes ORDER BY lanes;

DROP DATABASE dbfuncs;