Evaluate SQL expressions for zero
Company: Akuna Capital
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
Which of the following SQL expressions evaluate to 0? Evaluate each independently, justify the result, and note any dialect-specific behavior (e.g., MySQL type coercion rules):
1) SELECT '0' = 0;
2) SELECT 0 IS FALSE;
3) SELECT '0' IS FALSE;
4) SELECT STRCMP('0',
0). State your assumptions about the SQL dialect.
Overview: This question evaluates understanding of SQL type coercion, boolean semantics, comparison operators, and dialect-specific evaluation behavior (for example, MySQL), focusing on interpreting expressions that may evaluate to zero.
Assume **PostgreSQL** as the SQL dialect. This question probes how PostgreSQL applies type coercion and boolean evaluation. Consider the following four expressions, each evaluated independently:
1. `'0' = 0` — comparing the string literal `'0'` to the integer `0` (PostgreSQL coerces the untyped string literal to integer).
2. `(0)::boolean IS FALSE` — casting the integer `0` to boolean, then testing `IS FALSE`.
3. `'0'::boolean IS FALSE` — casting the string `'0'` to boolean, then testing `IS FALSE`.
4. The **sign** of comparing the string `'0'` to the string `'0'` (a STRCMP-style comparison): `-1` if the left side sorts before the right, `1` if after, and `0` if they are equal.
Write a **single** SQL query (no input tables are needed — build the rows with literals) that evaluates all four expressions and returns exactly one row per expression with these columns:
- `expr_id` — an integer from 1 to 4 identifying the expression.
- `expression_text` — a short text label describing the expression.
- `result_value` — the integer result of the expression: cast boolean results to integer (`TRUE` → `1`, `FALSE` → `0`); for the comparison in expression 4 return its sign as `-1`, `0`, or `1`.
Order the output by `expr_id` ascending. The output should make clear which expression evaluates to `0`: only the string-comparison sign in expression 4 is `0` (the two strings are equal); the other three all evaluate to `1`.
Hints
- No tables are required — assemble the four result rows with literal SELECTs joined by UNION ALL.
- Cast boolean results to integer with `::int` so TRUE becomes 1 and FALSE becomes 0; in PostgreSQL the string '0' is a valid boolean literal for FALSE.