Skip to content

functions_conditional.md

Latest commit

 

History

History
524 lines (423 loc) · 12.2 KB

functions_conditional.md

File metadata and controls

524 lines (423 loc) · 12.2 KB
Raw

Back to index

Conditional Functions

This page documents conditional expressions.

Function: CASE (searched form)

Name & Aliases: CASE WHEN ... THEN ... ELSE ... END (searched CASE form)

Description:
Evaluates boolean WHEN expressions in order; returns the result expression corresponding to the first true condition; if none match, returns the ELSE expression (or NULL if ELSE omitted).

Syntax:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE default_result
END

Inputs:

  • One or more WHEN condition THEN result pairs. Optional ELSE result.

Output:

  • Type coerced from result expressions (THEN/ELSE).

Examples:

1. Salary banding:

SELECT 
  name,
  salary,
  CASE
    WHEN salary > 100000 THEN 'very_high'
    WHEN salary > 50000 THEN 'high'
    ELSE 'normal'
  END AS salary_band
FROM emp

2. Product status:

SELECT 
  title,
  stock,
  CASE
    WHEN stock = 0 THEN 'Out of Stock'
    WHEN stock < 10 THEN 'Low Stock'
    WHEN stock < 50 THEN 'In Stock'
    ELSE 'Well Stocked'
  END AS stock_status
FROM products

3. Discount calculation:

SELECT 
  title,
  price,
  CASE
    WHEN price > 1000 THEN price * 0.85  -- 15% off
    WHEN price > 500 THEN price * 0.90   -- 10% off
    WHEN price > 100 THEN price * 0.95   -- 5% off
    ELSE price
  END AS discounted_price
FROM products

4. Without ELSE (returns NULL if no match):

SELECT 
  name,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age < 65 THEN 'Adult'
  END AS age_group
FROM persons
-- Returns NULL for age >= 65

Function: CASE (simple / expression form)

Name & Aliases: CASE expr WHEN val1 THEN r1 WHEN val2 THEN r2 ... ELSE rN END (simple CASE)

Description:
Compare expr to valN sequentially using equality; returns corresponding rN for first match; else ELSE result or NULL.

Syntax:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE default_result
END

Inputs:

  • expr (any comparable type) and pairs WHEN value THEN result.

Output:

  • Type coerced from result expressions.

Implementation notes:
The simple form evaluates by comparing expr = value for each WHEN. Both CASE forms are parsed and translated into nested conditional Painless scripts for script_fields when used outside an aggregation push-down.

Examples:

1. Department categorization:

SELECT 
  name,
  department,
  CASE department
    WHEN 'IT' THEN 'tech'
    WHEN 'Sales' THEN 'revenue'
    WHEN 'Marketing' THEN 'revenue'
    WHEN 'Engineering' THEN 'tech'
    ELSE 'other'
  END AS dept_category
FROM emp

2. Status mapping:

SELECT 
  order_id,
  CASE status
    WHEN 'P' THEN 'Pending'
    WHEN 'S' THEN 'Shipped'
    WHEN 'D' THEN 'Delivered'
    WHEN 'C' THEN 'Cancelled'
    ELSE 'Unknown'
  END AS status_label
FROM orders

3. Priority levels:

SELECT 
  ticket_id,
  CASE priority
    WHEN 1 THEN 'Critical'
    WHEN 2 THEN 'High'
    WHEN 3 THEN 'Medium'
    WHEN 4 THEN 'Low'
    ELSE 'Undefined'
  END AS priority_name
FROM tickets

4. Numeric to text conversion:

SELECT 
  product_id,
  CASE rating
    WHEN 5 THEN '★★★★★'
    WHEN 4 THEN '★★★★☆'
    WHEN 3 THEN '★★★☆☆'
    WHEN 2 THEN '★★☆☆☆'
    WHEN 1 THEN '★☆☆☆☆'
    ELSE 'No rating'
  END AS star_display
FROM reviews

COALESCE

Returns the first non-null argument.

Syntax:

COALESCE(expr1, expr2, ...)

Inputs:

  • One or more expressions

Output:

  • Value of first non-null expression (coerced to common type)

Examples:

1. Display name fallback:

SELECT 
  COALESCE(nickname, firstname, 'N/A') AS display_name
FROM users

-- If nickname = 'Jo': returns 'Jo'
-- If nickname = NULL, firstname = 'John': returns 'John'
-- If both NULL: returns 'N/A'

2. Default values:

SELECT 
  title,
  COALESCE(discount_price, price) AS final_price
FROM products
-- Uses discount_price if available, otherwise regular price

3. Multiple fallbacks:

SELECT 
  COALESCE(mobile_phone, work_phone, home_phone, 'No phone') AS contact_phone
FROM customers

4. Handling missing data:

SELECT 
  name,
  COALESCE(email, 'no-email@example.com') AS email,
  COALESCE(country, 'Unknown') AS country
FROM users

NULLIF

Returns NULL if expr1 = expr2; otherwise returns expr1.

Syntax:

NULLIF(expr1, expr2)

Inputs:

  • expr1 - first expression
  • expr2 - second expression

Output:

  • Type of expr1, or NULL if equal

Examples:

1. Normalize unknown values:

SELECT 
  NULLIF(status, 'unknown') AS status_norm
FROM events

-- If status = 'unknown': returns NULL
-- If status = 'active': returns 'active'

2. Handle sentinel values:

SELECT 
  title,
  NULLIF(price, 0) AS valid_price
FROM products
-- Converts 0 prices to NULL

3. Avoid division by zero — no longer needed, and measured not to work:

-- ⚠️ Since 0.24.0 the engine returns NULL for a zero divisor by itself, so write this:
SELECT 
  total_sales / total_orders AS avg_order_value
FROM sales_summary
-- NULL on the rows where total_orders = 0

⚠️ total_sales / NULLIF(total_orders, 0) — the idiom this section used to recommend — throws a null_pointer_exception in a search on exactly the rows where total_orders = 0, and silently drops the computed column in an ingest pipeline. Measured on real Elasticsearch, identically before and after 0.24.0. NULLIF remains correct everywhere else; see the / operator for the division rule.

4. Clean data:

SELECT 
  name,
  NULLIF(TRIM(description), '') AS description
FROM products
-- Converts empty strings to NULL after trimming

Notes

  • The comparison follows what each argument renders, not the type of the column it came from. NULLIF(CAST(zip_code AS BIGINT), 0) compares two numbers even though zip_code is a KEYWORD. Before 0.24.0 it was compared as text and the generated script was rejected by Elasticsearch wherever it appeared — a computed column, a projection or a WHERE. A function argument is evaluated once from 0.24.0 as well; NULLIF(UPPER(name), 'X') used to call UPPER three times, and a CASE as the first argument could return the wrong value.

  • The two arguments must be comparable, and from 0.24.0 the engine says so itself. Two arguments are comparable when both render text, both render a temporal type, both render a number, both render a boolean, or one of them is NULL or a column whose type is not known. Any other pair is refused, because the engine has no way to compare them: NULLIF(status, 0) is refused with

    NULLIF(status, 0) compares KEYWORD with BIGINT: NULLIF requires two arguments of comparable
types, so cast one of them

    — at CREATE TABLE / ALTER TABLE … SET SCRIPT AS for a computed column, and before the query is sent for a projection or a WHERE. Earlier versions built a script around the mismatch instead: in a query Elasticsearch rejected it, and in a computed column the ingest processor's ignore_failure swallowed the failure, so the column was simply missing from every document and the CREATE TABLE still answered 200. Write the comparison in the type you mean: NULLIF(status, '0') or NULLIF(CAST(status AS BIGINT), 0).

    A cast can create the mismatch as well as cure it: NULLIF(CAST(qty AS KEYWORD), 0) renders text against a number and is refused, even though qty is numeric.

    The same rule refuses a date against a number (NULLIF(created_at, 0)) and a boolean against anything but a boolean (NULLIF(is_active, 0)). Before 0.24.0 these were accepted and compared with Painless ==, which answers false for a date against a number without ever failing — so the NULLIF returned its first argument for every row and the query answered 200 with the wrong values. If you relied on one of these, write the comparison in the type you mean.

  • A string literal against a date column is refused, and the message depends on the literal. A malformed one is named as such — NULLIF(created_at, 'yesterday') reports that 'yesterday' is not a date for that field's format, checked against the column's mapping exactly as a WHERE comparison is. A well-formed one — NULLIF(created_at, '2024-01-15') — is refused too, with a message saying the comparison is not supported yet: the engine cannot turn a string into a temporal value inside a script, so accepting it would mean comparing a date with a string, which silently never matches. Cast the literal instead: NULLIF(created_at, CAST('2024-01-15' AS DATE)).

  • expr2 being NULL is not a match: SQL says the comparison is then UNKNOWN, so the answer is expr1, not NULL.

ISNULL

Tests if expression is NULL.

Syntax:

ISNULL(expr)

Inputs:

  • expr - expression to test

Output:

  • BOOLEAN - TRUE if NULL, FALSE otherwise

Examples:

1. Check for missing manager:

SELECT 
  name,
  ISNULL(manager) AS manager_missing
FROM emp

-- Result: TRUE if manager is NULL, else FALSE

2. Filter NULL values:

SELECT * 
FROM products
WHERE ISNULL(description)
-- Returns products without description

3. Count NULLs:

SELECT 
  COUNT(*) as total,
  SUM(CASE WHEN ISNULL(email) THEN 1 ELSE 0 END) as missing_emails
FROM users

4. Conditional logic:

SELECT 
  name,
  CASE
    WHEN ISNULL(last_login) THEN 'Never logged in'
    ELSE 'Active user'
  END AS user_status
FROM users

ISNOTNULL

Tests if expression is NOT NULL.

Syntax:

ISNOTNULL(expr)

Inputs:

  • expr - expression to test

Output:

  • BOOLEAN - TRUE if NOT NULL, FALSE if NULL

Examples:

1. Check for existing manager:

SELECT 
  name,
  ISNOTNULL(manager) AS has_manager
FROM emp

-- Result: TRUE if manager is NOT NULL, else FALSE

2. Filter non-NULL values:

SELECT * 
FROM products
WHERE ISNOTNULL(description)
-- Returns products with description

3. Count non-NULLs:

SELECT 
  COUNT(*) as total,
  SUM(CASE WHEN ISNOTNULL(email) THEN 1 ELSE 0 END) as with_emails
FROM users

4. Required fields validation:

SELECT 
  product_id,
  CASE
    WHEN ISNOTNULL(title) AND ISNOTNULL(price) AND ISNOTNULL(category) 
    THEN 'Valid'
    ELSE 'Incomplete'
  END AS validation_status
FROM products

GREATEST

Returns the largest value among the given numeric expressions. NULL arguments are ignored (PostgreSQL-style NULL handling); the result is NULL only if every argument is NULL.

Syntax:

GREATEST(expression1, expression2, ...)

Inputs:

  • One or more numeric expressions to compare

Output:

  • Numeric (widest input type), or NULL if every argument is NULL

Examples:

1. Highest of multiple prices:

SELECT GREATEST(price_us, price_eu, price_uk) AS max_price
FROM products

2. Floor on a computed value:

SELECT GREATEST(0, base_price - rebate) AS net
FROM orders
-- Never lets the net go below zero

Notes:

  • Ignores NULL arguments (PostgreSQL-style NULL handling); returns NULL only if every argument is NULL.
  • All arguments should be numeric and have comparable types.
  • See also: LEAST, COALESCE.

LEAST

Returns the smallest value among the given numeric expressions. NULL arguments are ignored (PostgreSQL-style NULL handling); the result is NULL only if every argument is NULL.

Syntax:

LEAST(expression1, expression2, ...)

Inputs:

  • One or more numeric expressions to compare

Output:

  • Numeric (widest input type), or NULL if every argument is NULL

Examples:

1. Lowest of multiple prices:

SELECT LEAST(price_us, price_eu, price_uk) AS min_price
FROM products

2. Cap on a computed value:

SELECT LEAST(rebate, base_price) AS applied_rebate
FROM orders
-- Never lets the rebate exceed the base price

Notes:

  • Ignores NULL arguments (PostgreSQL-style NULL handling); returns NULL only if every argument is NULL.
  • All arguments should be numeric and have comparable types.
  • See also: GREATEST, COALESCE.

Back to index