Thread: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
PG Bug reporting form
Date:
The following bug has been logged on the website:
Bug reference: 17233
Logged by: Alexander Korolev
Email address: lxndrkrlv@gmail.com
PostgreSQL version: 14.0
Operating system: Windows
Description:
This SELECT command fails as expected:
SELECT CTID FROM tmp1 INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR UPDATE;
-- ERROR: column "ctid" does not exist.
But if I use same SELECT in WHERE clause of DELETE command
DELETE FROM tmp1 WHERE CTID in (
SELECT CTID FROM tmp1 INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR
UPDATE);
this command is executed without errors.
Repro
--------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS pg_temp.tmp1;
DROP TABLE IF EXISTS pg_temp.tmp2;
CREATE TEMPORARY TABLE tmp1 (id int NOT NULL, name text);
CREATE TEMPORARY TABLE tmp2 (id int NOT NULL, name text);
INSERT INTO tmp1 (id, name) VALUES (1, 'aaa'), (2, 'bbb');
INSERT INTO tmp2 (id, name) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'eee');
SELECT ctid, * from tmp1;
SELECT ctid, * from tmp2;
/* Works as expected: ERROR: column "ctid" does not exist
SELECT CTID
FROM tmp1
INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR UPDATE;
*/
// Executed without errors !!!
DELETE FROM tmp1
WHERE CTID in (
SELECT CTID
FROM tmp1
INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR UPDATE
);
SELECT * FROM tmp1;
SELECT * FROM tmp2;
--------------------------------------------------------------------------------------------
* in some cases, if tables is big, server process is hang. I can't make
small reproducible example.
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
"David G. Johnston"
Date:
On Saturday, October 16, 2021, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 17233
Logged by: Alexander Korolev
Email address: lxndrkrlv@gmail.com
PostgreSQL version: 14.0
Operating system: Windows
Description:
This SELECT command fails as expected:
SELECT CTID FROM tmp1 INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR UPDATE;
-- ERROR: column "ctid" does not exist.
But if I use same SELECT in WHERE clause of DELETE command
DELETE FROM tmp1 WHERE CTID in (
SELECT CTID FROM tmp1 INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR
UPDATE);
this command is executed without errors.
This is not a bug:
The virtual join table doesn’t have a ctid, only physical tables do, and the ctid of physical tables apparently aren’t propogated when they are joined.
David J.
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> The virtual join table doesn’t have a ctid, only physical tables do, and
> the ctid of physical tables apparently aren’t propogated when they are
> joined.
We leave the system columns out of the join because otherwise they'd
surely conflict between the two sides of the join. However, you could
still reference either one with "tmp1.ctid" or "tmp2.ctid".
There might be an opportunity here to improve the error message's hint:
regression=# SELECT CTID
regression-# FROM tmp1
regression-# INNER JOIN tmp2 ON tmp1.id = tmp2.id;
ERROR: column "ctid" does not exist
LINE 1: SELECT CTID
^
HINT: There is a column named "ctid" in table "tmp1", but it cannot be referenced from this part of the query.
"cannot be referenced" is probably a shade misleading, given the
availability of the qualified-name alternative.
regards, tom lane
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
"David G. Johnston"
Date:
On Sunday, October 17, 2021, Tom Lane <tgl@sss.pgh.pa.us> wrote:
There might be an opportunity here to improve the error message's hint:
regression=# SELECT CTID
regression-# FROM tmp1
regression-# INNER JOIN tmp2 ON tmp1.id = tmp2.id;
ERROR: column "ctid" does not exist
LINE 1: SELECT CTID
^
HINT: There is a column named "ctid" in table "tmp1", but it cannot be referenced from this part of the query.
"cannot be referenced" is probably a shade misleading, given the
availability of the qualified-name alternative.
I was actually wondering why the error wasn’t an ambiguous column name error. For a pure select query we already allow duplicate column names in the result.
David J.
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I was actually wondering why the error wasn’t an ambiguous column name
> error. For a pure select query we already allow duplicate column names in
> the result.
To get an "ambiguous column name" error, there'd need to be multiple
*accessible* names, not multiple inaccessible ones. Although I concede
your point that maybe we could adjust the "does not exist" phraseology
too. Maybe something like "column foo is not available"?
regards, tom lane
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
Александр Королев
Date:
This is not a bug:The virtual join table doesn’t have a ctid, only physical tables do, and the ctid of physical tables apparently aren’t propogated when they are joined.
Possibly this is not a bug, but this behavior is strange.
Also, this subquery has different behavior in SELECT and DELETE:
--------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS pg_temp.tmp1;
DROP TABLE IF EXISTS pg_temp.tmp2;
CREATE TEMPORARY TABLE tmp1 (id int NOT NULL, name text);
CREATE TEMPORARY TABLE tmp2 (id int NOT NULL, name text);
INSERT INTO tmp1 (id, name) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
INSERT INTO tmp2 (id, name) VALUES (1, 'aaa');
-- select outputs all rows from tmp1
SELECT * FROM tmp1
WHERE CTID in (
SELECT CTID
FROM tmp1
INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR UPDATE
);
--delete affects only first row from tmp1
DELETE FROM tmp1
WHERE CTID in (
SELECT CTID
FROM tmp1
INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR UPDATE
) RETURNING *;
DROP TABLE IF EXISTS pg_temp.tmp2;
CREATE TEMPORARY TABLE tmp1 (id int NOT NULL, name text);
CREATE TEMPORARY TABLE tmp2 (id int NOT NULL, name text);
INSERT INTO tmp1 (id, name) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
INSERT INTO tmp2 (id, name) VALUES (1, 'aaa');
-- select outputs all rows from tmp1
SELECT * FROM tmp1
WHERE CTID in (
SELECT CTID
FROM tmp1
INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR UPDATE
);
--delete affects only first row from tmp1
DELETE FROM tmp1
WHERE CTID in (
SELECT CTID
FROM tmp1
INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR UPDATE
) RETURNING *;
--------------------------------------------------------------------------------------------------
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
"David G. Johnston"
Date:
On Sunday, October 17, 2021, Александр Королев <lxndrkrlv@gmail.com> wrote:
This is not a bug:The virtual join table doesn’t have a ctid, only physical tables do, and the ctid of physical tables apparently aren’t propogated when they are joined.Possibly this is not a bug, but this behavior is strange.Also, this subquery has different behavior in SELECT and DELETE:
As long as the subquery returns at least one row every row in the table will be returned.
Here, as soon as you delete the single row that the subquery returns no additional rows will be deleted. It seems indeterminate as to how many, and which, rows actually get removed. At least one, but possibly all. The is more procedural an execution plan than I would expect from SQL but it’s all that seems to fit the described behavior.
In short, your subquery is basically bogus and so, yes, you will see strange behavior if you use it.
The server cannot always inform you that you’ve written something bogus (i.e., error) because the same general query form can be used to write something useful. Correlated subqueries are one of those cases.
David J.
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
Bruce Momjian
Date:
On Sun, Oct 17, 2021 at 10:34:18AM -0400, Tom Lane wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > The virtual join table doesn’t have a ctid, only physical tables do, and
> > the ctid of physical tables apparently aren’t propogated when they are
> > joined.
>
> We leave the system columns out of the join because otherwise they'd
> surely conflict between the two sides of the join. However, you could
> still reference either one with "tmp1.ctid" or "tmp2.ctid".
>
> There might be an opportunity here to improve the error message's hint:
>
> regression=# SELECT CTID
> regression-# FROM tmp1
> regression-# INNER JOIN tmp2 ON tmp1.id = tmp2.id;
> ERROR: column "ctid" does not exist
> LINE 1: SELECT CTID
> ^
> HINT: There is a column named "ctid" in table "tmp1", but it cannot be referenced from this part of the query.
>
> "cannot be referenced" is probably a shade misleading, given the
> availability of the qualified-name alternative.
I looked into this by modifying the error message with the attached
patch and running the regression tests. I saw the following regression
failures due to the message change. While the email posted query was
fixed by table-qualifying the column, the first few queries of the
regression tests were fixed by adding LATERAL, but I couldn't get the
UPDATE/DELETE queries to work.
I am feeling there isn't much we can add to this message except to say
maybe:
There is a column named "f1" in table "a", but it cannot be referenced
from this part of the query as structured.
-------------
It suggests you might be able to get it working by restructuring the
query, e.g., table-qualified or LATERAL.
Feedback?
---------------------------------------------------------------------------
-- test some error cases where LATERAL should have been used but wasn't
select f1,g from int4_tbl a, (select f1 as g) ss;
ERROR: column "f1" does not exist
LINE 1: select f1,g from int4_tbl a, (select f1 as g) ss;
^
-HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+HINT: There is a column named "f1" in table "a" and another table so a table-qualified column reference is required.
select f1,g from int4_tbl a, (select a.f1 as g) ss;
ERROR: invalid reference to FROM-clause entry for table "a"
LINE 1: select f1,g from int4_tbl a, (select a.f1 as g) ss;
^
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
select f1,g from int4_tbl a cross join (select f1 as g) ss;
ERROR: column "f1" does not exist
LINE 1: select f1,g from int4_tbl a cross join (select f1 as g) ss;
^
-HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+HINT: There is a column named "f1" in table "a" and another table so a table-qualified column reference is required.
select f1,g from int4_tbl a cross join (select a.f1 as g) ss;
ERROR: invalid reference to FROM-clause entry for table "a"
LINE 1: select f1,g from int4_tbl a cross join (select a.f1 as g) ss...
^
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
-- check behavior of LATERAL in UPDATE/DELETE
create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl;
-- error, can't do this:
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
ERROR: column "x1" does not exist
LINE 1: ... set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
^
-HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
+HINT: There is a column named "x1" in table "xx1" and another table so a table-qualified column reference is
required.
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss;
ERROR: invalid reference to FROM-clause entry for table "xx1"
LINE 1: ...t x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss...
^
HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
-- can't do it even with LATERAL:
update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss;
ERROR: invalid reference to FROM-clause entry for table "xx1"
LINE 1: ...= f1 from lateral (select * from int4_tbl where f1 = x1) ss;
^
HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
-- we might in future allow something like this, but for now it's an error:
update xx1 set x2 = f1 from xx1, lateral (select * from int4_tbl where f1 = x1) ss;
ERROR: table name "xx1" specified more than once
-- also errors:
delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
ERROR: column "x1" does not exist
LINE 1: ...te from xx1 using (select * from int4_tbl where f1 = x1) ss;
^
-HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
+HINT: There is a column named "x1" in table "xx1" and another table so a table-qualified column reference is
required.
delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss;
ERROR: invalid reference to FROM-clause entry for table "xx1"
LINE 1: ...from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss...
^
HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
ERROR: invalid reference to FROM-clause entry for table "xx1"
LINE 1: ...xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
^
-- this should fail because f1 is not exposed for unqualified reference:
create rule rules_foorule as on insert to rules_foo where f1 < 100
do instead insert into rules_foo2 values (f1);
ERROR: column "f1" does not exist
LINE 2: do instead insert into rules_foo2 values (f1);
^
-HINT: There is a column named "f1" in table "old", but it cannot be referenced from this part of the query.
+HINT: There is a column named "f1" in table "old" and another table so a table-qualified column reference is
required.
-- This should fail, because q2 isn't a name of an EXCEPT output column
SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
ERROR: column "q2" does not exist
LINE 1: ... int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1...
^
-HINT: There is a column named "q2" in table "*SELECT* 2", but it cannot be referenced from this part of the query.
+HINT: There is a column named "q2" in table "*SELECT* 2" and another table so a table-qualified column reference is
required.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
Attachment
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> Feedback?
> -- test some error cases where LATERAL should have been used but wasn't
> select f1,g from int4_tbl a, (select f1 as g) ss;
> ERROR: column "f1" does not exist
> LINE 1: select f1,g from int4_tbl a, (select f1 as g) ss;
> ^
> -HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
> +HINT: There is a column named "f1" in table "a" and another table so a table-qualified column reference is
required.
That one has gone from accurate to completely wrong. First, it's not
the case that there's more than one possible referent, and second,
table-qualifying the reference wouldn't help. What *would* help here
is adding LATERAL, but I'm not sure if we can easily tell whether that
is the case.
I think perhaps the existing message is mislabeled: it's not a hint
as written, but errdetail, because it's entirely factual. For this
particular example, what would be on-point is
DETAIL: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
HINT: To reference that column, you must mark this subquery with LATERAL.
While we don't insist that hints be 100% accurate, it's not good
if they're wildly unhelpful. So I'm not sure if we can determine
whether or not it's likely to be on-point.
I didn't look too closely at your other examples.
regards, tom lane
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
Bruce Momjian
Date:
On Thu, Aug 18, 2022 at 05:52:08PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Feedback? > > > -- test some error cases where LATERAL should have been used but wasn't > > select f1,g from int4_tbl a, (select f1 as g) ss; > > ERROR: column "f1" does not exist > > LINE 1: select f1,g from int4_tbl a, (select f1 as g) ss; > > ^ > > -HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query. > > +HINT: There is a column named "f1" in table "a" and another table so a table-qualified column reference is required. > > That one has gone from accurate to completely wrong. First, it's not > the case that there's more than one possible referent, and second, > table-qualifying the reference wouldn't help. What *would* help here > is adding LATERAL, but I'm not sure if we can easily tell whether that > is the case. My error text was written based on the email report and was just a test to see what queries trigger it. > I think perhaps the existing message is mislabeled: it's not a hint > as written, but errdetail, because it's entirely factual. For this > particular example, what would be on-point is > > DETAIL: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query. > HINT: To reference that column, you must mark this subquery with LATERAL. Yes, the problem is that I don't think we want to just be making a suggestion when we have other cases where LATERAL would not help. > While we don't insist that hints be 100% accurate, it's not good > if they're wildly unhelpful. So I'm not sure if we can determine > whether or not it's likely to be on-point. > > I didn't look too closely at your other examples. Yeah, I think someone will have to have a new idea to improve this. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
Bruce Momjian
Date:
On Thu, Aug 18, 2022 at 10:07:02PM -0400, Bruce Momjian wrote:
> > While we don't insist that hints be 100% accurate, it's not good
> > if they're wildly unhelpful. So I'm not sure if we can determine
> > whether or not it's likely to be on-point.
> >
> > I didn't look too closely at your other examples.
>
> Yeah, I think someone will have to have a new idea to improve this.
Thinking some more, my point is that this error message is being
generated for three cases I know of:
1. email reporters case of CTID column, which is fixed by table
qualification
2. adding LATERAL
3. UPDATE/DELETE where adding LATERAL doesn't fix the query
We can't simply improve the error message because there are unfixable
cases, and we know of two possible fixes.
To improve things, it would be good if we could determine if LATERAL
will really fix the error, or at least detect one of the cases above we
have a clearer way to suggest a fix.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> To improve things, it would be good if we could determine if LATERAL
> will really fix the error, or at least detect one of the cases above we
> have a clearer way to suggest a fix.
Here's a proposed patch that tries to determine this by looking at
ParseNamespaceItem flags. I'm not sure it's totally bulletproof,
but it's likely good enough for a HINT.
I felt that the conditional-expression nests in the existing ereport
calls were nearly unintelligible already, so I rearranged the logic
to duplicate portions of the ereports instead. That could be debated
perhaps. Also, as written some paths through errorMissingColumn
will invoke the findNSItemForRTE search twice. I'm not too fussed
about that: it's a pretty cheap search and anyway nobody should be
bothering to shave microseconds off an error reporting path.
regards, tom lane
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index f6b740df0a..e1591c6fb1 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -81,6 +81,8 @@ static void expandTupleDesc(TupleDesc tupdesc, Alias *eref,
int location, bool include_dropped,
List **colnames, List **colvars);
static int specialAttNum(const char *attname);
+static bool rte_visible_if_lateral(ParseState *pstate, RangeTblEntry *rte);
+static bool rte_visible_if_qualified(ParseState *pstate, RangeTblEntry *rte);
static bool isQueryUsingTempRelation_walker(Node *node, void *context);
@@ -3604,17 +3606,27 @@ errorMissingRTE(ParseState *pstate, RangeVar *relation)
badAlias = rte->eref->aliasname;
}
- if (rte)
+ /* If it looks like the user forgot to use an alias, hint about that */
+ if (badAlias)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_TABLE),
errmsg("invalid reference to FROM-clause entry for table \"%s\"",
relation->relname),
- (badAlias ?
- errhint("Perhaps you meant to reference the table alias \"%s\".",
- badAlias) :
- errhint("There is an entry for table \"%s\", but it cannot be referenced from this part of the
query.",
- rte->eref->aliasname)),
+ errhint("Perhaps you meant to reference the table alias \"%s\".",
+ badAlias),
parser_errposition(pstate, relation->location)));
+ /* Hint about case where we found an (inaccessible) exact match */
+ else if (rte)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_TABLE),
+ errmsg("invalid reference to FROM-clause entry for table \"%s\"",
+ relation->relname),
+ errdetail("There is an entry for table \"%s\", but it cannot be referenced from this part of the
query.",
+ rte->eref->aliasname),
+ rte_visible_if_lateral(pstate, rte) ?
+ errhint("To reference that table, you must mark this subquery with LATERAL.") : 0,
+ parser_errposition(pstate, relation->location)));
+ /* Else, we have nothing to offer but the bald statement of error */
else
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_TABLE),
@@ -3639,9 +3651,6 @@ errorMissingColumn(ParseState *pstate,
/*
* Search the entire rtable looking for possible matches. If we find one,
* emit a hint about it.
- *
- * TODO: improve this code (and also errorMissingRTE) to mention using
- * LATERAL if appropriate.
*/
state = searchRangeTableForCol(pstate, relname, colname, location);
@@ -3660,21 +3669,38 @@ errorMissingColumn(ParseState *pstate,
if (!state->rsecond)
{
- /*
- * Handle case where there is zero or one column suggestions to hint,
- * including exact matches referenced but not visible.
- */
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- relname ?
- errmsg("column %s.%s does not exist", relname, colname) :
- errmsg("column \"%s\" does not exist", colname),
- state->rfirst ? closestfirst ?
- errhint("Perhaps you meant to reference the column \"%s.%s\".",
- state->rfirst->eref->aliasname, closestfirst) :
- errhint("There is a column named \"%s\" in table \"%s\", but it cannot be referenced from this part
ofthe query.",
- colname, state->rfirst->eref->aliasname) : 0,
- parser_errposition(pstate, location)));
+ /* If we found no match at all, we have little to report */
+ if (!state->rfirst)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ relname ?
+ errmsg("column %s.%s does not exist", relname, colname) :
+ errmsg("column \"%s\" does not exist", colname),
+ parser_errposition(pstate, location)));
+ /* Handle case where we have a single alternative spelling to offer */
+ else if (closestfirst)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ relname ?
+ errmsg("column %s.%s does not exist", relname, colname) :
+ errmsg("column \"%s\" does not exist", colname),
+ errhint("Perhaps you meant to reference the column \"%s.%s\".",
+ state->rfirst->eref->aliasname, closestfirst),
+ parser_errposition(pstate, location)));
+ /* We found an exact match but it's inaccessible for some reason */
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ relname ?
+ errmsg("column %s.%s does not exist", relname, colname) :
+ errmsg("column \"%s\" does not exist", colname),
+ errdetail("There is a column named \"%s\" in table \"%s\", but it cannot be referenced from this
partof the query.",
+ colname, state->rfirst->eref->aliasname),
+ rte_visible_if_lateral(pstate, state->rfirst) ?
+ errhint("To reference that column, you must mark this subquery with LATERAL.") :
+ (!relname && rte_visible_if_qualified(pstate, state->rfirst)) ?
+ errhint("To reference that column, you must use a table-qualified name.") : 0,
+ parser_errposition(pstate, location)));
}
else
{
@@ -3696,6 +3722,71 @@ errorMissingColumn(ParseState *pstate,
}
}
+/*
+ * Find ParseNamespaceItem for RTE, if it's visible at all.
+ * We assume an RTE couldn't appear more than once in the namespace lists.
+ */
+static ParseNamespaceItem *
+findNSItemForRTE(ParseState *pstate, RangeTblEntry *rte)
+{
+ while (pstate != NULL)
+ {
+ ListCell *l;
+
+ foreach(l, pstate->p_namespace)
+ {
+ ParseNamespaceItem *nsitem = (ParseNamespaceItem *) lfirst(l);
+
+ if (nsitem->p_rte == rte)
+ return nsitem;
+ }
+ pstate = pstate->parentParseState;
+ }
+ return NULL;
+}
+
+/*
+ * Would this RTE be visible, if only the user had written LATERAL?
+ *
+ * This is a helper for deciding whether to issue a HINT about LATERAL.
+ * As such, it doesn't need to be 100% accurate; the HINT could be useful
+ * even if it's not quite right. Hence, we don't delve into fine points
+ * about whether a found nsitem has the appropriate one of p_rel_visible or
+ * p_cols_visible set.
+ */
+static bool
+rte_visible_if_lateral(ParseState *pstate, RangeTblEntry *rte)
+{
+ ParseNamespaceItem *nsitem;
+
+ /* If LATERAL *is* active, we're clearly barking up the wrong tree */
+ if (pstate->p_lateral_active)
+ return false;
+ nsitem = findNSItemForRTE(pstate, rte);
+ if (nsitem)
+ {
+ /* Found it, report whether it's LATERAL-only */
+ return nsitem->p_lateral_only && nsitem->p_lateral_ok;
+ }
+ return false;
+}
+
+/*
+ * Would columns in this RTE be visible if qualified?
+ */
+static bool
+rte_visible_if_qualified(ParseState *pstate, RangeTblEntry *rte)
+{
+ ParseNamespaceItem *nsitem = findNSItemForRTE(pstate, rte);
+
+ if (nsitem)
+ {
+ /* Found it, report whether it's relation-only */
+ return nsitem->p_rel_visible && !nsitem->p_cols_visible;
+ }
+ return false;
+}
+
/*
* Examine a fully-parsed query, and return true iff any relation underlying
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 66d8633e3e..9e9e3bd00c 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -242,7 +242,7 @@ insert into insertconflicttest values (1, 'Apple') on conflict (key) do update s
ERROR: invalid reference to FROM-clause entry for table "excluded"
LINE 1: ...y) do update set fruit = excluded.fruit RETURNING excluded.f...
^
-HINT: There is an entry for table "excluded", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "excluded", but it cannot be referenced from this part of the query.
-- Only suggest <table>.* column when inference element misspelled:
insert into insertconflicttest values (1, 'Apple') on conflict (keyy) do update set fruit = excluded.fruit;
ERROR: column "keyy" does not exist
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 2ed2e542a4..a515399f5e 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -1638,7 +1638,7 @@ SELECT * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t = 'one'; -- er
ERROR: invalid reference to FROM-clause entry for table "j1_tbl"
LINE 1: ... * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t =...
^
-HINT: There is an entry for table "j1_tbl", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "j1_tbl", but it cannot be referenced from this part of the query.
SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.i = 1; -- ok
i | j | t | k
---+---+-----+----
@@ -4975,7 +4975,7 @@ select * from
ERROR: invalid reference to FROM-clause entry for table "y"
LINE 2: ...bl x join (int4_tbl x cross join int4_tbl y) j on q1 = y.f1;
^
-HINT: There is an entry for table "y", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "y", but it cannot be referenced from this part of the query.
select * from
int8_tbl x join (int4_tbl x cross join int4_tbl y(ff)) j on q1 = f1; -- ok
q1 | q2 | f1 | ff
@@ -5003,6 +5003,13 @@ ERROR: column "uunique1" does not exist
LINE 1: select uunique1 from
^
HINT: Perhaps you meant to reference the column "t1.unique1" or the column "t2.unique1".
+select ctid from
+ tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, need qualification
+ERROR: column "ctid" does not exist
+LINE 1: select ctid from
+ ^
+DETAIL: There is a column named "ctid" in table "t1", but it cannot be referenced from this part of the query.
+HINT: To reference that column, you must use a table-qualified name.
--
-- Take care to reference the correct RTE
--
@@ -6036,22 +6043,26 @@ select f1,g from int4_tbl a, (select f1 as g) ss;
ERROR: column "f1" does not exist
LINE 1: select f1,g from int4_tbl a, (select f1 as g) ss;
^
-HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+DETAIL: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+HINT: To reference that column, you must mark this subquery with LATERAL.
select f1,g from int4_tbl a, (select a.f1 as g) ss;
ERROR: invalid reference to FROM-clause entry for table "a"
LINE 1: select f1,g from int4_tbl a, (select a.f1 as g) ss;
^
-HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "a", but it cannot be referenced from this part of the query.
+HINT: To reference that table, you must mark this subquery with LATERAL.
select f1,g from int4_tbl a cross join (select f1 as g) ss;
ERROR: column "f1" does not exist
LINE 1: select f1,g from int4_tbl a cross join (select f1 as g) ss;
^
-HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+DETAIL: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+HINT: To reference that column, you must mark this subquery with LATERAL.
select f1,g from int4_tbl a cross join (select a.f1 as g) ss;
ERROR: invalid reference to FROM-clause entry for table "a"
LINE 1: select f1,g from int4_tbl a cross join (select a.f1 as g) ss...
^
-HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "a", but it cannot be referenced from this part of the query.
+HINT: To reference that table, you must mark this subquery with LATERAL.
-- SQL:2008 says the left table is in scope but illegal to access here
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
ERROR: invalid reference to FROM-clause entry for table "a"
@@ -6081,12 +6092,12 @@ update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
ERROR: column "x1" does not exist
LINE 1: ... set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
^
-HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
+DETAIL: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss;
ERROR: invalid reference to FROM-clause entry for table "xx1"
LINE 1: ...t x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss...
^
-HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
-- can't do it even with LATERAL:
update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss;
ERROR: invalid reference to FROM-clause entry for table "xx1"
@@ -6101,12 +6112,12 @@ delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
ERROR: column "x1" does not exist
LINE 1: ...te from xx1 using (select * from int4_tbl where f1 = x1) ss;
^
-HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
+DETAIL: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss;
ERROR: invalid reference to FROM-clause entry for table "xx1"
LINE 1: ...from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss...
^
-HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
ERROR: invalid reference to FROM-clause entry for table "xx1"
LINE 1: ...xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
index 729ae2eb06..5af344e4e3 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -197,7 +197,7 @@ WHEN NOT MATCHED THEN
ERROR: invalid reference to FROM-clause entry for table "t"
LINE 2: USING (SELECT * FROM source WHERE t.tid > sid) s
^
-HINT: There is an entry for table "t", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "t", but it cannot be referenced from this part of the query.
--
-- initial tests
--
@@ -618,7 +618,7 @@ WHEN NOT MATCHED THEN
ERROR: invalid reference to FROM-clause entry for table "t"
LINE 5: INSERT (tid, balance) VALUES (t.tid, s.delta);
^
-HINT: There is an entry for table "t", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "t", but it cannot be referenced from this part of the query.
-- and again with a constant ON clause
BEGIN;
MERGE INTO target t
@@ -629,7 +629,7 @@ WHEN NOT MATCHED THEN
ERROR: invalid reference to FROM-clause entry for table "t"
LINE 5: INSERT (tid, balance) VALUES (t.tid, s.delta);
^
-HINT: There is an entry for table "t", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "t", but it cannot be referenced from this part of the query.
SELECT * FROM target ORDER BY tid;
ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
@@ -722,7 +722,7 @@ WHEN NOT MATCHED AND t.balance = 100 THEN
ERROR: invalid reference to FROM-clause entry for table "t"
LINE 3: WHEN NOT MATCHED AND t.balance = 100 THEN
^
-HINT: There is an entry for table "t", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "t", but it cannot be referenced from this part of the query.
SELECT * FROM wq_target;
ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 7ec3d2688f..66323c323f 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1194,7 +1194,8 @@ do instead insert into rules_foo2 values (f1);
ERROR: column "f1" does not exist
LINE 2: do instead insert into rules_foo2 values (f1);
^
-HINT: There is a column named "f1" in table "old", but it cannot be referenced from this part of the query.
+DETAIL: There is a column named "f1" in table "old", but it cannot be referenced from this part of the query.
+HINT: To reference that column, you must use a table-qualified name.
-- this is the correct way:
create rule rules_foorule as on insert to rules_foo where f1 < 100
do instead insert into rules_foo2 values (new.f1);
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 7ac4a9380e..220f0c1629 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -910,7 +910,7 @@ SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
ERROR: column "q2" does not exist
LINE 1: ... int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1...
^
-HINT: There is a column named "q2" in table "*SELECT* 2", but it cannot be referenced from this part of the query.
+DETAIL: There is a column named "q2" in table "*SELECT* 2", but it cannot be referenced from this part of the query.
-- But this should work:
SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) ORDER BY 1;
q1
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 27e7e741a1..3504e31260 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1803,6 +1803,8 @@ select t2.uunique1 from
tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, prefer "t2" suggestion
select uunique1 from
tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, suggest both at once
+select ctid from
+ tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, need qualification
--
-- Take care to reference the correct RTE
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
Bruce Momjian
Date:
On Sun, Aug 21, 2022 at 01:50:35PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > To improve things, it would be good if we could determine if LATERAL
> > will really fix the error, or at least detect one of the cases above we
> > have a clearer way to suggest a fix.
>
> Here's a proposed patch that tries to determine this by looking at
> ParseNamespaceItem flags. I'm not sure it's totally bulletproof,
> but it's likely good enough for a HINT.
Wow, this is great. I can see how people would be confused by the need
for LATERAL, and this is really a great boost for them.
> I felt that the conditional-expression nests in the existing ereport
> calls were nearly unintelligible already, so I rearranged the logic
Wow, I am glad you mentioned this. I never saw a ? b ? c : d : e used
before and I had to look it up, and I have been around C for a long time:
https://www.geeksforgeeks.org/c-nested-ternary-operator/
> to duplicate portions of the ereports instead. That could be debated
> perhaps. Also, as written some paths through errorMissingColumn
> will invoke the findNSItemForRTE search twice. I'm not too fussed
> about that: it's a pretty cheap search and anyway nobody should be
> bothering to shave microseconds off an error reporting path.
Exactly.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
Alvaro Herrera
Date:
I gave this a quick spin and couldn't find any faults. The bit about getting an incorrect hint when p_rel_visible is false had me busy for a little while (specifically I ran into it with the "unnamed_subquery" stuff added by commit bcedd8f5fce0), but maybe that's a fringe enough case, as the comment in rte_visible_if_lateral says. I did wonder why errorMissingColumn doesn't consider rte_visible_if_* in the case when there *is* an rsecond candidate. I understand that the reason is that if we come across any exact match we already return that one without looking for a second one. Maybe this deserves a comment (in errorMissingColumn I mean) but I also wonder if we shouldn't scan the whole RT in case there's another exact match that's also not visible. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "Learn about compilers. Then everything looks like either a compiler or a database, and now you have two problems but one of them is fun." https://twitter.com/thingskatedid/status/1456027786158776329
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
Tom Lane
Date:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> I did wonder why errorMissingColumn doesn't consider rte_visible_if_* in
> the case when there *is* an rsecond candidate. I understand that the
> reason is that if we come across any exact match we already return that
> one without looking for a second one. Maybe this deserves a comment (in
> errorMissingColumn I mean) but I also wonder if we shouldn't scan the
> whole RT in case there's another exact match that's also not visible.
Um. I'd not wanted to touch the fuzzy-search stuff because it seemed
like a mess of incomprehensible (if not actually buggy) code. But you
have a point --- I'd already noticed that the code was encouraging
people to qualify with a name that might be the wrong table altogether.
So here's a revision that tries to clean that up a little. 0001 is the
same patch as before, and then 0002 revises the fuzzy-search logic enough
that I can make sense of it. I split them mainly so that you can see the
behavioral difference in the changed test outputs.
regards, tom lane
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 81f9ae2f02..6290ce9b43 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -81,6 +81,8 @@ static void expandTupleDesc(TupleDesc tupdesc, Alias *eref,
int location, bool include_dropped,
List **colnames, List **colvars);
static int specialAttNum(const char *attname);
+static bool rte_visible_if_lateral(ParseState *pstate, RangeTblEntry *rte);
+static bool rte_visible_if_qualified(ParseState *pstate, RangeTblEntry *rte);
static bool isQueryUsingTempRelation_walker(Node *node, void *context);
@@ -3603,17 +3605,27 @@ errorMissingRTE(ParseState *pstate, RangeVar *relation)
badAlias = rte->eref->aliasname;
}
- if (rte)
+ /* If it looks like the user forgot to use an alias, hint about that */
+ if (badAlias)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_TABLE),
errmsg("invalid reference to FROM-clause entry for table \"%s\"",
relation->relname),
- (badAlias ?
- errhint("Perhaps you meant to reference the table alias \"%s\".",
- badAlias) :
- errhint("There is an entry for table \"%s\", but it cannot be referenced from this part of the
query.",
- rte->eref->aliasname)),
+ errhint("Perhaps you meant to reference the table alias \"%s\".",
+ badAlias),
parser_errposition(pstate, relation->location)));
+ /* Hint about case where we found an (inaccessible) exact match */
+ else if (rte)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_TABLE),
+ errmsg("invalid reference to FROM-clause entry for table \"%s\"",
+ relation->relname),
+ errdetail("There is an entry for table \"%s\", but it cannot be referenced from this part of the
query.",
+ rte->eref->aliasname),
+ rte_visible_if_lateral(pstate, rte) ?
+ errhint("To reference that table, you must mark this subquery with LATERAL.") : 0,
+ parser_errposition(pstate, relation->location)));
+ /* Else, we have nothing to offer but the bald statement of error */
else
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_TABLE),
@@ -3638,9 +3650,6 @@ errorMissingColumn(ParseState *pstate,
/*
* Search the entire rtable looking for possible matches. If we find one,
* emit a hint about it.
- *
- * TODO: improve this code (and also errorMissingRTE) to mention using
- * LATERAL if appropriate.
*/
state = searchRangeTableForCol(pstate, relname, colname, location);
@@ -3659,21 +3668,38 @@ errorMissingColumn(ParseState *pstate,
if (!state->rsecond)
{
- /*
- * Handle case where there is zero or one column suggestions to hint,
- * including exact matches referenced but not visible.
- */
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- relname ?
- errmsg("column %s.%s does not exist", relname, colname) :
- errmsg("column \"%s\" does not exist", colname),
- state->rfirst ? closestfirst ?
- errhint("Perhaps you meant to reference the column \"%s.%s\".",
- state->rfirst->eref->aliasname, closestfirst) :
- errhint("There is a column named \"%s\" in table \"%s\", but it cannot be referenced from this part
ofthe query.",
- colname, state->rfirst->eref->aliasname) : 0,
- parser_errposition(pstate, location)));
+ /* If we found no match at all, we have little to report */
+ if (!state->rfirst)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ relname ?
+ errmsg("column %s.%s does not exist", relname, colname) :
+ errmsg("column \"%s\" does not exist", colname),
+ parser_errposition(pstate, location)));
+ /* Handle case where we have a single alternative spelling to offer */
+ else if (closestfirst)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ relname ?
+ errmsg("column %s.%s does not exist", relname, colname) :
+ errmsg("column \"%s\" does not exist", colname),
+ errhint("Perhaps you meant to reference the column \"%s.%s\".",
+ state->rfirst->eref->aliasname, closestfirst),
+ parser_errposition(pstate, location)));
+ /* We found an exact match but it's inaccessible for some reason */
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ relname ?
+ errmsg("column %s.%s does not exist", relname, colname) :
+ errmsg("column \"%s\" does not exist", colname),
+ errdetail("There is a column named \"%s\" in table \"%s\", but it cannot be referenced from this
partof the query.",
+ colname, state->rfirst->eref->aliasname),
+ rte_visible_if_lateral(pstate, state->rfirst) ?
+ errhint("To reference that column, you must mark this subquery with LATERAL.") :
+ (!relname && rte_visible_if_qualified(pstate, state->rfirst)) ?
+ errhint("To reference that column, you must use a table-qualified name.") : 0,
+ parser_errposition(pstate, location)));
}
else
{
@@ -3695,6 +3721,71 @@ errorMissingColumn(ParseState *pstate,
}
}
+/*
+ * Find ParseNamespaceItem for RTE, if it's visible at all.
+ * We assume an RTE couldn't appear more than once in the namespace lists.
+ */
+static ParseNamespaceItem *
+findNSItemForRTE(ParseState *pstate, RangeTblEntry *rte)
+{
+ while (pstate != NULL)
+ {
+ ListCell *l;
+
+ foreach(l, pstate->p_namespace)
+ {
+ ParseNamespaceItem *nsitem = (ParseNamespaceItem *) lfirst(l);
+
+ if (nsitem->p_rte == rte)
+ return nsitem;
+ }
+ pstate = pstate->parentParseState;
+ }
+ return NULL;
+}
+
+/*
+ * Would this RTE be visible, if only the user had written LATERAL?
+ *
+ * This is a helper for deciding whether to issue a HINT about LATERAL.
+ * As such, it doesn't need to be 100% accurate; the HINT could be useful
+ * even if it's not quite right. Hence, we don't delve into fine points
+ * about whether a found nsitem has the appropriate one of p_rel_visible or
+ * p_cols_visible set.
+ */
+static bool
+rte_visible_if_lateral(ParseState *pstate, RangeTblEntry *rte)
+{
+ ParseNamespaceItem *nsitem;
+
+ /* If LATERAL *is* active, we're clearly barking up the wrong tree */
+ if (pstate->p_lateral_active)
+ return false;
+ nsitem = findNSItemForRTE(pstate, rte);
+ if (nsitem)
+ {
+ /* Found it, report whether it's LATERAL-only */
+ return nsitem->p_lateral_only && nsitem->p_lateral_ok;
+ }
+ return false;
+}
+
+/*
+ * Would columns in this RTE be visible if qualified?
+ */
+static bool
+rte_visible_if_qualified(ParseState *pstate, RangeTblEntry *rte)
+{
+ ParseNamespaceItem *nsitem = findNSItemForRTE(pstate, rte);
+
+ if (nsitem)
+ {
+ /* Found it, report whether it's relation-only */
+ return nsitem->p_rel_visible && !nsitem->p_cols_visible;
+ }
+ return false;
+}
+
/*
* Examine a fully-parsed query, and return true iff any relation underlying
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 66d8633e3e..9e9e3bd00c 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -242,7 +242,7 @@ insert into insertconflicttest values (1, 'Apple') on conflict (key) do update s
ERROR: invalid reference to FROM-clause entry for table "excluded"
LINE 1: ...y) do update set fruit = excluded.fruit RETURNING excluded.f...
^
-HINT: There is an entry for table "excluded", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "excluded", but it cannot be referenced from this part of the query.
-- Only suggest <table>.* column when inference element misspelled:
insert into insertconflicttest values (1, 'Apple') on conflict (keyy) do update set fruit = excluded.fruit;
ERROR: column "keyy" does not exist
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index b901d7299f..ebaa289e12 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -1638,7 +1638,7 @@ SELECT * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t = 'one'; -- er
ERROR: invalid reference to FROM-clause entry for table "j1_tbl"
LINE 1: ... * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t =...
^
-HINT: There is an entry for table "j1_tbl", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "j1_tbl", but it cannot be referenced from this part of the query.
SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.i = 1; -- ok
i | j | t | k
---+---+-----+----
@@ -5036,7 +5036,7 @@ select * from
ERROR: invalid reference to FROM-clause entry for table "y"
LINE 2: ...bl x join (int4_tbl x cross join int4_tbl y) j on q1 = y.f1;
^
-HINT: There is an entry for table "y", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "y", but it cannot be referenced from this part of the query.
select * from
int8_tbl x join (int4_tbl x cross join int4_tbl y(ff)) j on q1 = f1; -- ok
q1 | q2 | f1 | ff
@@ -5064,6 +5064,13 @@ ERROR: column "uunique1" does not exist
LINE 1: select uunique1 from
^
HINT: Perhaps you meant to reference the column "t1.unique1" or the column "t2.unique1".
+select ctid from
+ tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, need qualification
+ERROR: column "ctid" does not exist
+LINE 1: select ctid from
+ ^
+DETAIL: There is a column named "ctid" in table "t1", but it cannot be referenced from this part of the query.
+HINT: To reference that column, you must use a table-qualified name.
--
-- Take care to reference the correct RTE
--
@@ -6097,22 +6104,26 @@ select f1,g from int4_tbl a, (select f1 as g) ss;
ERROR: column "f1" does not exist
LINE 1: select f1,g from int4_tbl a, (select f1 as g) ss;
^
-HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+DETAIL: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+HINT: To reference that column, you must mark this subquery with LATERAL.
select f1,g from int4_tbl a, (select a.f1 as g) ss;
ERROR: invalid reference to FROM-clause entry for table "a"
LINE 1: select f1,g from int4_tbl a, (select a.f1 as g) ss;
^
-HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "a", but it cannot be referenced from this part of the query.
+HINT: To reference that table, you must mark this subquery with LATERAL.
select f1,g from int4_tbl a cross join (select f1 as g) ss;
ERROR: column "f1" does not exist
LINE 1: select f1,g from int4_tbl a cross join (select f1 as g) ss;
^
-HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+DETAIL: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+HINT: To reference that column, you must mark this subquery with LATERAL.
select f1,g from int4_tbl a cross join (select a.f1 as g) ss;
ERROR: invalid reference to FROM-clause entry for table "a"
LINE 1: select f1,g from int4_tbl a cross join (select a.f1 as g) ss...
^
-HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "a", but it cannot be referenced from this part of the query.
+HINT: To reference that table, you must mark this subquery with LATERAL.
-- SQL:2008 says the left table is in scope but illegal to access here
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
ERROR: invalid reference to FROM-clause entry for table "a"
@@ -6142,12 +6153,12 @@ update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
ERROR: column "x1" does not exist
LINE 1: ... set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
^
-HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
+DETAIL: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss;
ERROR: invalid reference to FROM-clause entry for table "xx1"
LINE 1: ...t x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss...
^
-HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
-- can't do it even with LATERAL:
update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss;
ERROR: invalid reference to FROM-clause entry for table "xx1"
@@ -6162,12 +6173,12 @@ delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
ERROR: column "x1" does not exist
LINE 1: ...te from xx1 using (select * from int4_tbl where f1 = x1) ss;
^
-HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
+DETAIL: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss;
ERROR: invalid reference to FROM-clause entry for table "xx1"
LINE 1: ...from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss...
^
-HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
ERROR: invalid reference to FROM-clause entry for table "xx1"
LINE 1: ...xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
index 787af41dfe..4bc0ad6b09 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -197,7 +197,7 @@ WHEN NOT MATCHED THEN
ERROR: invalid reference to FROM-clause entry for table "t"
LINE 2: USING (SELECT * FROM source WHERE t.tid > sid) s
^
-HINT: There is an entry for table "t", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "t", but it cannot be referenced from this part of the query.
--
-- initial tests
--
@@ -618,7 +618,7 @@ WHEN NOT MATCHED THEN
ERROR: invalid reference to FROM-clause entry for table "t"
LINE 5: INSERT (tid, balance) VALUES (t.tid, s.delta);
^
-HINT: There is an entry for table "t", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "t", but it cannot be referenced from this part of the query.
-- and again with a constant ON clause
BEGIN;
MERGE INTO target t
@@ -629,7 +629,7 @@ WHEN NOT MATCHED THEN
ERROR: invalid reference to FROM-clause entry for table "t"
LINE 5: INSERT (tid, balance) VALUES (t.tid, s.delta);
^
-HINT: There is an entry for table "t", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "t", but it cannot be referenced from this part of the query.
SELECT * FROM target ORDER BY tid;
ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
@@ -724,7 +724,7 @@ WHEN NOT MATCHED AND t.balance = 100 THEN
ERROR: invalid reference to FROM-clause entry for table "t"
LINE 3: WHEN NOT MATCHED AND t.balance = 100 THEN
^
-HINT: There is an entry for table "t", but it cannot be referenced from this part of the query.
+DETAIL: There is an entry for table "t", but it cannot be referenced from this part of the query.
SELECT * FROM wq_target;
ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 624d0e5aae..acf28ba580 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1194,7 +1194,8 @@ do instead insert into rules_foo2 values (f1);
ERROR: column "f1" does not exist
LINE 2: do instead insert into rules_foo2 values (f1);
^
-HINT: There is a column named "f1" in table "old", but it cannot be referenced from this part of the query.
+DETAIL: There is a column named "f1" in table "old", but it cannot be referenced from this part of the query.
+HINT: To reference that column, you must use a table-qualified name.
-- this is the correct way:
create rule rules_foorule as on insert to rules_foo where f1 < 100
do instead insert into rules_foo2 values (new.f1);
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index dece7310cf..e2613d6777 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -910,7 +910,7 @@ SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
ERROR: column "q2" does not exist
LINE 1: ... int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1...
^
-HINT: There is a column named "q2" in table "*SELECT* 2", but it cannot be referenced from this part of the query.
+DETAIL: There is a column named "q2" in table "*SELECT* 2", but it cannot be referenced from this part of the query.
-- But this should work:
SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) ORDER BY 1;
q1
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index ccbbe5454c..961ff7d103 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1828,6 +1828,8 @@ select t2.uunique1 from
tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, prefer "t2" suggestion
select uunique1 from
tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, suggest both at once
+select ctid from
+ tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, need qualification
--
-- Take care to reference the correct RTE
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 6290ce9b43..4665f0b2b7 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -41,17 +41,35 @@
/*
* Support for fuzzily matching columns.
*
- * This is for building diagnostic messages, where non-exact matching
- * attributes are suggested to the user. The struct's fields may be facets of
- * a particular RTE, or of an entire range table, depending on context.
+ * This is for building diagnostic messages, where multiple or non-exact
+ * matching attributes are of interest.
+ *
+ * "distance" is the current best fuzzy-match distance if rfirst isn't NULL,
+ * otherwise it is the maximum acceptable distance plus 1.
+ *
+ * rfirst/first record the closest non-exact match so far, and distance
+ * is its distance from the target name. If we have found a second non-exact
+ * match of exactly the same distance, rsecond/second record that. (If
+ * we find three of the same distance, we conclude that "distance" is not
+ * a tight enough bound for a useful hint and clear rfirst/rsecond again.
+ * Only if we later find something closer will we re-populate rfirst.)
+ *
+ * rexact1/exact1 record the location of the first exactly-matching column,
+ * if any. If we find multiple exact matches then rexact2/exact2 record
+ * another one (we don't especially care which). Currently, these get
+ * populated independently of the fuzzy-match fields.
*/
typedef struct
{
- int distance; /* Weighted distance (lowest so far) */
- RangeTblEntry *rfirst; /* RTE of first */
- AttrNumber first; /* Closest attribute so far */
- RangeTblEntry *rsecond; /* RTE of second */
- AttrNumber second; /* Second closest attribute so far */
+ int distance; /* Current or limit distance */
+ RangeTblEntry *rfirst; /* RTE of closest non-exact match, or NULL */
+ AttrNumber first; /* Col index in rfirst */
+ RangeTblEntry *rsecond; /* RTE of another non-exact match w/same dist */
+ AttrNumber second; /* Col index in rsecond */
+ RangeTblEntry *rexact1; /* RTE of first exact match, or NULL */
+ AttrNumber exact1; /* Col index in rexact1 */
+ RangeTblEntry *rexact2; /* RTE of second exact match, or NULL */
+ AttrNumber exact2; /* Col index in rexact2 */
} FuzzyAttrMatchState;
#define MAX_FUZZY_DISTANCE 3
@@ -612,47 +630,39 @@ updateFuzzyAttrMatchState(int fuzzy_rte_penalty,
*/
if (columndistance < fuzzystate->distance)
{
- /* Store new lowest observed distance for RTE */
+ /* Store new lowest observed distance as first/only match */
fuzzystate->distance = columndistance;
fuzzystate->rfirst = rte;
fuzzystate->first = attnum;
fuzzystate->rsecond = NULL;
- fuzzystate->second = InvalidAttrNumber;
}
else if (columndistance == fuzzystate->distance)
{
- /*
- * This match distance may equal a prior match within this same range
- * table. When that happens, the prior match may also be given, but
- * only if there is no more than two equally distant matches from the
- * RTE (in turn, our caller will only accept two equally distant
- * matches overall).
- */
- if (AttributeNumberIsValid(fuzzystate->second))
+ /* If we already have a match of this distance, update state */
+ if (fuzzystate->rsecond != NULL)
{
- /* Too many RTE-level matches */
+ /*
+ * Too many matches at same distance. Clearly, this value of
+ * distance is too low a bar, so drop these entries while keeping
+ * the current distance value, so that only smaller distances will
+ * be considered interesting. Only if we find something of lower
+ * distance will we re-populate rfirst (via the stanza above).
+ */
fuzzystate->rfirst = NULL;
- fuzzystate->first = InvalidAttrNumber;
fuzzystate->rsecond = NULL;
- fuzzystate->second = InvalidAttrNumber;
- /* Clearly, distance is too low a bar (for *any* RTE) */
- fuzzystate->distance = columndistance - 1;
}
- else if (AttributeNumberIsValid(fuzzystate->first))
+ else if (fuzzystate->rfirst != NULL)
{
- /* Record as provisional second match for RTE */
+ /* Record as provisional second match */
fuzzystate->rsecond = rte;
fuzzystate->second = attnum;
}
- else if (fuzzystate->distance <= MAX_FUZZY_DISTANCE)
+ else
{
/*
- * Record as provisional first match (this can occasionally occur
- * because previous lowest distance was "too low a bar", rather
- * than being associated with a real match)
+ * Do nothing. When rfirst is NULL, distance is more than what we
+ * want to consider acceptable, so we should ignore this match.
*/
- fuzzystate->rfirst = rte;
- fuzzystate->first = attnum;
}
}
}
@@ -925,21 +935,15 @@ colNameToVar(ParseState *pstate, const char *colname, bool localonly,
* This is different from colNameToVar in that it considers every entry in
* the ParseState's rangetable(s), not only those that are currently visible
* in the p_namespace list(s). This behavior is invalid per the SQL spec,
- * and it may give ambiguous results (there might be multiple equally valid
- * matches, but only one will be returned). This must be used ONLY as a
- * heuristic in giving suitable error messages. See errorMissingColumn.
+ * and it may give ambiguous results (since there might be multiple equally
+ * valid matches). This must be used ONLY as a heuristic in giving suitable
+ * error messages. See errorMissingColumn.
*
* This function is also different in that it will consider approximate
* matches -- if the user entered an alias/column pair that is only slightly
* different from a valid pair, we may be able to infer what they meant to
- * type and provide a reasonable hint.
- *
- * The FuzzyAttrMatchState will have 'rfirst' pointing to the best RTE
- * containing the most promising match for the alias and column name. If
- * the alias and column names match exactly, 'first' will be InvalidAttrNumber;
- * otherwise, it will be the attribute number for the match. In the latter
- * case, 'rsecond' may point to a second, equally close approximate match,
- * and 'second' will contain the attribute number for the second match.
+ * type and provide a reasonable hint. We return a FuzzyAttrMatchState
+ * struct providing information about both exact and approximate matches.
*/
static FuzzyAttrMatchState *
searchRangeTableForCol(ParseState *pstate, const char *alias, const char *colname,
@@ -951,8 +955,8 @@ searchRangeTableForCol(ParseState *pstate, const char *alias, const char *colnam
fuzzystate->distance = MAX_FUZZY_DISTANCE + 1;
fuzzystate->rfirst = NULL;
fuzzystate->rsecond = NULL;
- fuzzystate->first = InvalidAttrNumber;
- fuzzystate->second = InvalidAttrNumber;
+ fuzzystate->rexact1 = NULL;
+ fuzzystate->rexact2 = NULL;
while (pstate != NULL)
{
@@ -962,6 +966,7 @@ searchRangeTableForCol(ParseState *pstate, const char *alias, const char *colnam
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(l);
int fuzzy_rte_penalty = 0;
+ int attnum;
/*
* Typically, it is not useful to look for matches within join
@@ -988,18 +993,27 @@ searchRangeTableForCol(ParseState *pstate, const char *alias, const char *colnam
true);
/*
- * Scan for a matching column; if we find an exact match, we're
- * done. Otherwise, update fuzzystate.
+ * Scan for a matching column, and update fuzzystate. Non-exact
+ * matches are dealt with inside scanRTEForColumn, but exact
+ * matches are handled here. (There won't be more than one exact
+ * match in the same RTE, else we'd have thrown error earlier.)
*/
- if (scanRTEForColumn(orig_pstate, rte, rte->eref, colname, location,
- fuzzy_rte_penalty, fuzzystate)
- && fuzzy_rte_penalty == 0)
+ attnum = scanRTEForColumn(orig_pstate, rte, rte->eref,
+ colname, location,
+ fuzzy_rte_penalty, fuzzystate);
+ if (attnum != InvalidAttrNumber && fuzzy_rte_penalty == 0)
{
- fuzzystate->rfirst = rte;
- fuzzystate->first = InvalidAttrNumber;
- fuzzystate->rsecond = NULL;
- fuzzystate->second = InvalidAttrNumber;
- return fuzzystate;
+ if (fuzzystate->rexact1 == NULL)
+ {
+ fuzzystate->rexact1 = rte;
+ fuzzystate->exact1 = attnum;
+ }
+ else
+ {
+ /* Needn't worry about overwriting previous rexact2 */
+ fuzzystate->rexact2 = rte;
+ fuzzystate->exact2 = attnum;
+ }
}
}
@@ -3645,7 +3659,6 @@ errorMissingColumn(ParseState *pstate,
const char *relname, const char *colname, int location)
{
FuzzyAttrMatchState *state;
- char *closestfirst = NULL;
/*
* Search the entire rtable looking for possible matches. If we find one,
@@ -3654,69 +3667,78 @@ errorMissingColumn(ParseState *pstate,
state = searchRangeTableForCol(pstate, relname, colname, location);
/*
- * Extract closest col string for best match, if any.
- *
- * Infer an exact match referenced despite not being visible from the fact
- * that an attribute number was not present in state passed back -- this
- * is what is reported when !closestfirst. There might also be an exact
- * match that was qualified with an incorrect alias, in which case
- * closestfirst will be set (so hint is the same as generic fuzzy case).
+ * If there are exact match(es), they must be inaccessible for some
+ * reason.
*/
- if (state->rfirst && AttributeNumberIsValid(state->first))
- closestfirst = strVal(list_nth(state->rfirst->eref->colnames,
- state->first - 1));
-
- if (!state->rsecond)
+ if (state->rexact1)
{
- /* If we found no match at all, we have little to report */
- if (!state->rfirst)
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- relname ?
- errmsg("column %s.%s does not exist", relname, colname) :
- errmsg("column \"%s\" does not exist", colname),
- parser_errposition(pstate, location)));
- /* Handle case where we have a single alternative spelling to offer */
- else if (closestfirst)
+ /*
+ * We don't try too hard when there's multiple inaccessible exact
+ * matches, but at least be sure that we don't misleadingly suggest
+ * that there's only one.
+ */
+ if (state->rexact2)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
relname ?
errmsg("column %s.%s does not exist", relname, colname) :
errmsg("column \"%s\" does not exist", colname),
- errhint("Perhaps you meant to reference the column \"%s.%s\".",
- state->rfirst->eref->aliasname, closestfirst),
+ errdetail("There are columns named \"%s\", but they are in tables that cannot be referenced from
thispart of the query.",
+ colname),
+ !relname ? errhint("Try using a table-qualified name.") : 0,
parser_errposition(pstate, location)));
- /* We found an exact match but it's inaccessible for some reason */
- else
+ /* Single exact match, so try to determine why it's inaccessible. */
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ relname ?
+ errmsg("column %s.%s does not exist", relname, colname) :
+ errmsg("column \"%s\" does not exist", colname),
+ errdetail("There is a column named \"%s\" in table \"%s\", but it cannot be referenced from this part
ofthe query.",
+ colname, state->rexact1->eref->aliasname),
+ rte_visible_if_lateral(pstate, state->rexact1) ?
+ errhint("To reference that column, you must mark this subquery with LATERAL.") :
+ (!relname && rte_visible_if_qualified(pstate, state->rexact1)) ?
+ errhint("To reference that column, you must use a table-qualified name.") : 0,
+ parser_errposition(pstate, location)));
+ }
+
+ if (!state->rsecond)
+ {
+ /* If we found no match at all, we have little to report */
+ if (!state->rfirst)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
relname ?
errmsg("column %s.%s does not exist", relname, colname) :
errmsg("column \"%s\" does not exist", colname),
- errdetail("There is a column named \"%s\" in table \"%s\", but it cannot be referenced from this
partof the query.",
- colname, state->rfirst->eref->aliasname),
- rte_visible_if_lateral(pstate, state->rfirst) ?
- errhint("To reference that column, you must mark this subquery with LATERAL.") :
- (!relname && rte_visible_if_qualified(pstate, state->rfirst)) ?
- errhint("To reference that column, you must use a table-qualified name.") : 0,
parser_errposition(pstate, location)));
+ /* Handle case where we have a single alternative spelling to offer */
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ relname ?
+ errmsg("column %s.%s does not exist", relname, colname) :
+ errmsg("column \"%s\" does not exist", colname),
+ errhint("Perhaps you meant to reference the column \"%s.%s\".",
+ state->rfirst->eref->aliasname,
+ strVal(list_nth(state->rfirst->eref->colnames,
+ state->first - 1))),
+ parser_errposition(pstate, location)));
}
else
{
/* Handle case where there are two equally useful column hints */
- char *closestsecond;
-
- closestsecond = strVal(list_nth(state->rsecond->eref->colnames,
- state->second - 1));
-
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
relname ?
errmsg("column %s.%s does not exist", relname, colname) :
errmsg("column \"%s\" does not exist", colname),
errhint("Perhaps you meant to reference the column \"%s.%s\" or the column \"%s.%s\".",
- state->rfirst->eref->aliasname, closestfirst,
- state->rsecond->eref->aliasname, closestsecond),
+ state->rfirst->eref->aliasname,
+ strVal(list_nth(state->rfirst->eref->colnames,
+ state->first - 1)),
+ state->rsecond->eref->aliasname,
+ strVal(list_nth(state->rsecond->eref->colnames,
+ state->second - 1))),
parser_errposition(pstate, location)));
}
}
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index ebaa289e12..72a3abad9e 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -5069,8 +5069,8 @@ select ctid from
ERROR: column "ctid" does not exist
LINE 1: select ctid from
^
-DETAIL: There is a column named "ctid" in table "t1", but it cannot be referenced from this part of the query.
-HINT: To reference that column, you must use a table-qualified name.
+DETAIL: There are columns named "ctid", but they are in tables that cannot be referenced from this part of the query.
+HINT: Try using a table-qualified name.
--
-- Take care to reference the correct RTE
--
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index acf28ba580..7c7adbc004 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1194,8 +1194,8 @@ do instead insert into rules_foo2 values (f1);
ERROR: column "f1" does not exist
LINE 2: do instead insert into rules_foo2 values (f1);
^
-DETAIL: There is a column named "f1" in table "old", but it cannot be referenced from this part of the query.
-HINT: To reference that column, you must use a table-qualified name.
+DETAIL: There are columns named "f1", but they are in tables that cannot be referenced from this part of the query.
+HINT: Try using a table-qualified name.
-- this is the correct way:
create rule rules_foorule as on insert to rules_foo where f1 < 100
do instead insert into rules_foo2 values (new.f1);
Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
From
Tom Lane
Date:
I wrote:
> So here's a revision that tries to clean that up a little. 0001 is the
> same patch as before, and then 0002 revises the fuzzy-search logic enough
> that I can make sense of it. I split them mainly so that you can see the
> behavioral difference in the changed test outputs.
Hearing no further comments, pushed.
regards, tom lane