通过

UNRESOLVED_COLUMN 错误类

  • 项目

SQLSTATE: 42703

无法解析名称为 <objectName> 的列、变量或例程参数。

此错误类具有以下派生错误类。

WITHOUT_SUGGESTION

参数

  • objectName：无法解析的列或参数的名称。

WITH_SUGGESTION

你是否指以下其中一项？ 【<proposal>】

参数

  • objectName：无法解析的列或参数的名称。
  • proposal：用逗号分隔的潜在候选项列表。

说明

每当 Azure Databricks 在需要列、列别名或函数参数的上下文中无法识别标识符时，就会引发此错误。

导致此错误的原因有多种：

  • 列名称或参数名称存在拼写错误。
  • 你实际上想要指定字符串字面量，而不是标识符。
  • 使用 ALTER TABLE 重命名或删除了该列
  • 该列未包含在子查询的选择列表中。
  • 已使用表别名或列别名对该列进行了重命名。
  • 列引用具有关联性，你未指定 LATERAL。
  • 列引用指向一个不可见的对象，因为它之前出现在同一选择列表或标量子查询中。

缓解措施

错误的缓解方法取决于具体原因：

  • 名称和限定符的拼写是否错误？

    与 objectList 中提供的列进行比较，并修改拼写。

  • 是想改为指定字符串字面量？

    用单引号而不是反引号（重音符）将字面量括起来。

  • 子查询中是否省略了该列？

    将该列添加到子查询的选择列表。

  • 是否在同一个 from 子句中引用之前的关系中的列？

    在包含未解析列的子查询之前添加 LATERAL 关键字。 相关查询支持受到限制。 可能需要重写（去相关）查询。

  • 不清楚为何无法解析列或字段？

    请参阅列、字段、参数和变量解析，了解名称解析的详细说明。

示例

> 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