MAP_KEY_DOES_NOT_EXIST error class

Key <keyValue> does not exist. To return NULL instead, use try_element_at. If necessary set <ansiConfig> to false to bypass this error.

Parameters

  • keyValue: The key to look up in the map.

  • ansiConfig: The configuration setting to alter ANSI mode.

Explanation

Databricks raises this error in ANSI mode when keyValue passed using element_at(mapExpr, keyValue), mapExpr[keyValue], or mapExpr.keyValue does not exist.

This error provides context information that isolates the object and the expression within which the error occurred.

Mitigation

The mitigation for this error depends on the cause:

  • Is the keyValue incorrect?

    The key must match one of the keys in the map exactly. For strings this also includes whitespace and case-sensitivity.

    Fix the input and re-run the query.

  • Do you expect to get NULL instead of an error if the key does not exist?

    If you can change the expression, use try_element_at(mapExpr, keyValue) to tolerate references to missing keys.

    If you cannot change the expression, as a last resort, temporarily set the ansiConfig to false to tolerate references to missing keys.

Examples 

-- A MAP_KEY_DOES_NOT_EXIST error raised within a view because of a missing key
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT map(1, 'a', 2, 'b')[key] FROM VALUES(1), (3) AS t(key);
> SELECT c1 FROM v;
 [MAP_KEY_DOES_NOT_EXIST] Key 3 does not exist. Use `try_element_at` to tolerate non-existent key and return NULL instead. If necessary set "ANSI_MODE" to "false" to bypass this error.
 == SQL of VIEW v(line 1, position 8) ==
 SELECT map(1, 'a', 2, 'b')[key] FROM VALUES(1), (3) AS t(key)
        ^^^^^^^^^^^^^^^^^^^^^^^^

-- Use try_element_at to tolerate missing key
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT try_element_at(map(1, 'a', 2, 'b'), key) FROM VALUES(1), (3) AS t(key);
> SELECT c1 FROM v;
 a
 NULL

-- Disable ANSI mode in Databricks SQL for the definition of the view only
> SET ANSI_MODE = false;
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT map(1, 'a', 2, 'b')[key] FROM VALUES(1), (3) AS t(key);
> SET ANSI_MODE = true;
> SELECT c1 FROM v;
  a
  NULL

-- Disable ANSI mode in Databricks Runtime for the definition of the view only
> SET spark.sql.ansi.enabled = false;
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT map(1, 'a', 2, 'b')[key] FROM VALUES(1), (3) AS t(key);
> SET spark.sql.ansi.enabled = true;
> SELECT c1 FROM v;
  a
  NULL