apache/druid · error · ExpressionValidationException

invalid value %s

Error message

invalid value %s

What it means

Thrown when Druid's time-parse expression function cannot parse the first argument's string value using the supplied (or default ISO) formatter. The underlying Joda-Time formatter raises IllegalArgumentException, which is wrapped into a validation failure naming the offending value.

Source

Thrown at processing/src/main/java/org/apache/druid/math/expr/Function.java:3221

      }

      DateTimes.UtcFormatter formatter = DateTimes.ISO_DATE_OPTIONAL_TIME;
      if (args.size() > 1) {
        ExprEval format = args.get(1).eval(bindings);
        if (!format.type().is(ExprType.STRING)) {
          throw validationFailed(
              "second argument should be STRING but got %s instead",
              format.type()
          );
        }
        formatter = DateTimes.wrapFormatter(DateTimeFormat.forPattern(format.asString()));
      }
      DateTime date;
      try {
        date = formatter.parse(value.asString());
      }
      catch (IllegalArgumentException e) {
        throw validationFailed(e, "invalid value %s", value.asString());
      }
      return toValue(date);
    }

    @Override
    public void validateArguments(List<Expr> args)
    {
      validationHelperCheckAnyOfArgumentCount(args, 1, 2);
    }

    @Nullable
    @Override
    public ExpressionType getOutputType(Expr.InputBindingInspector inspector, List<Expr> args)
    {
      return ExpressionType.LONG;
    }

    protected ExprEval toValue(DateTime date)

View on GitHub (pinned to 9b90983fd2)

Solutions

1. Align the format pattern (second argument) with the actual input string, e.g. parse(x, 'yyyy/MM/dd').
2. Test the pattern on a sample of the raw values to catch rows that deviate.
3. Clean/normalize the value first (TRIM, REPLACE) so it matches the pattern.
4. Pre-validate with a TRY/regexp expression so bad rows become NULL instead of failing the query.

Example fix

// before
parse(x, 'yyyy-MM-dd') -- x = '2024/01/05'
// after
parse(x, 'yyyy/MM/dd')

// pre-check in SQL: only parse values matching the pattern
CASE WHEN REGEXP_LIKE(x, '^\\d{4}-\\d{2}-\\d{2}$') THEN parse(x, 'yyyy-MM-dd') ELSE NULL END

try { runQuery(q); } catch (ExpressionValidationException e) { logBadValue(e); }

When it happens

Trigger: Calling the parse function with a date string that does not match the format pattern given as the second argument, or does not match ISO_DATE_OPTIONAL_TIME when no pattern is supplied, e.g. parse('2024/01/05') against pattern 'yyyy-MM-dd'.

Common situations: Locale/timezone-dependent formats; strings with trailing whitespace or unexpected timezone suffixes; data where some rows deviate from the declared pattern; using 'dd/MM' vs 'MM/dd' confusion.

Related errors

• second argument should be STRING but got %s instead
• Invalid segment granularity [%s]
• Incorrect Regex: %s . No match found.
• Unable to parse row [%s]
• Unrecognized unary operator %s

AI-assisted analysis of apache/druid@9b90983fd2 (2026-09-07). Data as JSON: /api/errors/526d32cac08c048e. Report an issue: GitHub.