SQL general: Changes to error messages for subqueries¶

Error messages for subqueries behave in the following manner:

Before the change:

When a subquery returns an error, the error message contains no specific information about the SQL code or object.

After the change:

When some subqueries return an error, the error message contains specific information, such as the following details:

• The line and position of the unsupported code in the subquery.

• The name of the object, such as a view or function, that contains the unsupported subquery. When objects are nested, the name of the outermost object is returned. For example, if a view with an unsupported subquery is nested in a secure view, the error message shows the name of the secure view.

• The name of the type of object, such as a masking policy, that contains the unsupported subquery.

There are no changes to the error codes related to subqueries.

CREATE TABLE testsub1(a INT, b INT)
  AS SELECT * FROM VALUES
    (1, 1),
    (2, 2),
    (NULL, NULL);

CREATE TABLE testsub2(x INT, y INT)
  AS SELECT * FROM VALUES
    (1, 1),
    (2, 2),
    (NULL, NULL);

CREATE TABLE testsub3(u INT, v INT)
  AS SELECT * FROM VALUES
    (1, 1),
    (2, 2),
    (NULL, NULL);

SELECT *
  FROM testsub1
  WHERE a IN(
    SELECT x FROM testsub2 LEFT JOIN testsub3 ON x+a = u
  );

002031 (42601): SQL compilation error:
Unsupported subquery type cannot be evaluated

002031 (42601): SQL compilation error:
Unsupported subquery type cannot be evaluated at line 4, position 4

CREATE SECURE VIEW svw
  AS SELECT *
    FROM testsub1
    WHERE a IN (
      SELECT x FROM testsub2 LEFT JOIN testsub3 ON x+a = u);

SELECT * FROM svw;

002031 (42601): SQL compilation error:
Unsupported subquery type cannot be evaluated

002031 (42601): SQL compilation error:
Unsupported subquery type cannot be evaluated inside VIEW object: SVW

CREATE MASKING POLICY mp AS
  (i INT) RETURNS INT -> IFF(i < (SELECT MAX(a) FROM svw), i, -1);

CREATE TABLE masked_testsub1(a INT, b INT)
  AS SELECT * FROM VALUES
    (1, 1),
    (2, 2),
    (NULL, NULL);

ALTER TABLE masked_testsub1
  ALTER COLUMN a SET MASKING POLICY mp;

SELECT * FROM masked_testsub1;

002031 (42601): SQL compilation error:
Unsupported subquery type cannot be evaluated

002031 (42601): SQL compilation error:
Unsupported subquery type cannot be evaluated inside MASKING POLICY