What causes "Cannot cast '%s' to BOOLEAN" in prestodb/presto?

Map the values explicitly before casting: CASE WHEN col IN ('1','Y','yes','true') THEN true WHEN col IN ('0','N','no','false') THEN false ... END

prestodb/presto · error · PrestoException

INVALID_CAST_ARGUMENT

INVALID_CAST_ARGUMENT

Error message

Cannot cast '%s' to BOOLEAN

What it means

Thrown when casting a VARCHAR to BOOLEAN and the string is not one of the accepted literals ('true'/'t'/'yes' variants or 'false'/'f'/'no'-style values matched case-insensitively). Presto raises INVALID_CAST_ARGUMENT with the offending value. This backs CAST(varchar AS boolean).

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/type/VarcharOperators.java:137

                return false;
            }
        }
        if ((value.length() == 4) &&
                (toUpperCase(value.getByte(0)) == 'T') &&
                (toUpperCase(value.getByte(1)) == 'R') &&
                (toUpperCase(value.getByte(2)) == 'U') &&
                (toUpperCase(value.getByte(3)) == 'E')) {
            return true;
        }
        if ((value.length() == 5) &&
                (toUpperCase(value.getByte(0)) == 'F') &&
                (toUpperCase(value.getByte(1)) == 'A') &&
                (toUpperCase(value.getByte(2)) == 'L') &&
                (toUpperCase(value.getByte(3)) == 'S') &&
                (toUpperCase(value.getByte(4)) == 'E')) {
            return false;
        }
        throw new PrestoException(INVALID_CAST_ARGUMENT, format("Cannot cast '%s' to BOOLEAN", value.toStringUtf8()));
    }

    private static byte toUpperCase(byte b)
    {
        return isLowerCase(b) ? ((byte) (b - 32)) : b;
    }

    private static boolean isLowerCase(byte b)
    {
        return (b >= 'a') && (b <= 'z');
    }

    @LiteralParameters("x")
    @ScalarOperator(CAST)
    @SqlType(StandardTypes.DOUBLE)
    public static double castToDouble(@SqlType("varchar(x)") Slice slice)
    {
        try {

View on GitHub (pinned to 55bb57d202)

Solutions

Map the values explicitly before casting: CASE WHEN col IN ('1','Y','yes','true') THEN true WHEN col IN ('0','N','no','false') THEN false ... END
Apply trim() and/or lower() to the column before casting to remove whitespace/case issues
Clean the sentinel/garbage values upstream or with regexp_replace
Use try_cast(col AS BOOLEAN) to surface NULL for unconvertible rows instead of failing

Example fix

// before
SELECT CAST(flag AS BOOLEAN) FROM t; -- flag = 'Y'
// after
SELECT CAST(trim(lower(flag)) AS BOOLEAN) FROM t; -- or a CASE mapping 'y'/'1' to true

Defensive patterns

Strategy: validation

Validate before calling

-- Presto SQL: pre-check accepted boolean spellings
SELECT * FROM t
WHERE lower(trim(flag)) NOT IN ('true','t','yes','false','f','no');

Type guard

// JS-side guard
function isBooleanLiteral(s) {
  return ['true','t','yes','false','f','no'].includes(String(s).trim().toLowerCase());
}

Try / catch

SELECT try_cast(trim(flag) AS BOOLEAN) AS b FROM t; -- NULL for unconvertible values

Prevention

Normalize boolean-like columns (trim + lower) before casting
Map 1/0 and Y/N encodings to true/false with an explicit CASE upstream
Ban sentinel text ('N/A') in boolean columns at ingestion
Use try_cast in ETL to route unconvertible rows to a quarantine table

When it happens

Trigger: CAST(varchar_col AS BOOLEAN) with values like '1', '0', 'Y', 'on', 'TRUE ' with whitespace, or any free text not matching the accepted true/false spellings.

Common situations: Boolean-like data encoded as 1/0 or Y/N imported from MySQL or Excel; trailing spaces or hidden characters from CSV parsing; columns mixing real booleans with sentinel text like 'N/A'.

Related errors

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/a775eb0be90e0217. Report an issue: GitHub.