UNRESOLVED_COLUMN error class

  • Article

SQLSTATE: 42703

A column, variable, or routine parameter with name <objectName> cannot be resolved.

This error class has the following derived error classes.

WITHOUT_SUGGESTION

Parameters

  • objectName: The name of the column or parameter which cannot be resolved.

WITH_SUGGESTION

Did you mean one of the following? [<proposal>]

Parameters

  • objectName: The name of the column or parameter which cannot be resolved.
  • proposal: A comma separated list of potential candidates.

Explanation

Azure Databricks raises this error whenever it cannot identify an identifier in a context where it expects a column, column alias or function parameter.

There are several causes for this error:

  • A spelling error in the column name or parameter name.
  • You actually meant to specify a string literal, and not an identifier.
  • The column was renamed or dropped using ALTER TABLE
  • The column was not included in the select list of a subquery.
  • The column has been renamed using the table alias or column alias.
  • The column reference is correlated, and you did not specify LATERAL.
  • The column reference is to an object that is not visible because it appears earlier in the same select list or within a scalar subquery.

Mitigation

The mitigation of the error depends on the cause:

  • Is the spelling of the name and qualifiers incorrect?

    Compare to the columns offered up in the objectList and fix the spelling.

  • Did you mean to specify a string literal instead?

    Enclose the literal in single quotes, and not back-ticks (accent grave).

  • Was the column omitted from a subquery?

    Add the column to the select list of the subquery.

  • Are you referencing to a column in an earlier relation in the same from clause?

    Add the LATERAL keyword before the subquery with the unresolved column. Correlated query support is limited. You may need to rewrite (de-correlate) the query.

  • It is unclear why the column or field cannot be resolved?

    Refer to Column, field, parameter, and variable resolution for a detailed description on name resolution.

Examples

> CREATE OR REPLACE TEMPORARY VIEW colors(cyan, magenta, yellow) AS VALUES(10, 20, 5);

-- The column reference has been misspelled
> SELECT jello FROM colors;
  [UNRESOLVED_COLUMN.WITH_SUGGESTION] A column or function parameter with name `jello` cannot be resolved.
  Did you mean one of the following? [`colors`.`cyan`, `colors`.`yellow`, `colors`.`magenta`]

-- Correct the spelling
> SELECT yellow FROM colors;
 5

-- The qualifier has been misspelled
> SELECT color.yellow FROM colors;
 [UNRESOLVED_COLUMN.WITH_SUGGESTION] A column or function parameter with name `color`.`yellow` cannot be resolved.
 Did you mean one of the following? [`colors`.`cyan`, `colors`.`yellow`, `colors`.`magenta`]

-- Correct the spelling
> SELECT colors.yellow FROM colors;
 5

-- Forgot to quote a literal
> SELECT hello;
 [UNRESOLVED_COLUMN.WITHOUT_SUGGESTION] A column or function parameter with name `hello` cannot be resolved.

-- Use single quotes
> SELECT 'hello';

-- Used the wrong quotes for a literal
> SELECT `hello`;
 [UNRESOLVED_COLUMN.WITHOUT_SUGGESTION] A column or function parameter with name `hello` cannot be resolved.

-- Use single quotes instead
> SELECT 'hello';

-- Column "got lost" in a subquery.
> SELECT cyan, magenta, yellow
    FROM (SELECT cyan, magenta
            FROM colors) AS c;
 [UNRESOLVED_COLUMN.WITH_SUGGESTION] A column or function parameter with name `yellow` cannot be resolved.
 Did you mean one of the following? [`c`.`cyan`, `c`.`magenta`]

-- Add the missing column
> SELECT cyan, magenta, yellow
    FROM (SELECT cyan, magenta, yellow
            FROM colors) AS c;
 10   20    5

-- Columns got renamed in the table alias
> SELECT cyan, magenta, yellow
    FROM (SELECT cyan, magenta, yellow
            FROM colors) AS c(c, m, y);
 [UNRESOLVED_COLUMN.WITH_SUGGESTION] A column or function parameter with name `cyan` cannot be resolved.
 Did you mean one of the following? [`c`.`c`, `c`.`m`, `c`.`y`];

-- Adjust the names
> SELECT c, m, y
    FROM (SELECT cyan, magenta, yellow
            FROM colors) AS c(c, m, y);
 10   20    5

-- A correlated reference
> SELECT * FROM colors, (SELECT cyan + magenta + yellow AS total_use);
 [UNRESOLVED_COLUMN.WITHOUT_SUGGESTION] A column or function parameter with name `cyan` cannot be resolved.

-- Add LATERAL to permit correation
> SELECT * FROM colors, LATERAL(SELECT cyan + magenta + yellow AS total_use);
 10    20     5    35

-- Or de-correlate
> SELECT *, cyan + magenta + yellow AS total_use FROM colors;
 10    20     5    35

-- A misspelled parameter name
> CREATE OR REPLACE FUNCTION plus(a INT, b INT) RETURNS INT RETURN arg1 + arg2;
 [UNRESOLVED_COLUMN.WITH_SUGGESTION] A column or function parameter with name `arg1` cannot be resolved.
 Did you mean one of the following? [`plus`.`a`, `plus`.`b`]

-- Fix the names
> CREATE OR REPLACE FUNCTION plus(a INT, b INT) RETURNS INT RETURN a + b;
> SELECT plus(1, 2);
 3