Hi jian,
> Another issue is
>
> src1=# select cast(NULL::text as unknown);
> ERROR: cannot cast type unknown to text
> ...
> The error cursor points to the word "cast", the statement is "cast
> text as unknown"
> and the error message is "cannot cast type unknown to text".
> So the whole thing is very confusing for regular users.
> Maybe removing parser_coercion_errposition?
I tried that on master (89829354de1), with the same query and ten
others that reach this error, before and after. The position turns
out to be the useful part. It points at the expression whose type is
unknown, which is what the user has to change:
select a, c, cast(b as unknown) as bad, c + 1 from t;
ERROR: cannot cast type unknown to text
LINE 1: select a, c, cast(b as unknown) as bad, c + 1 from t;
^
create view v as
select a,
c,
cast(b as unknown) as bad
from t;
ERROR: cannot cast type unknown to text
LINE 4: cast(b as unknown) as bad
^
Without parser_coercion_errposition() every case prints only the
ERROR line, so in a view like that there is nothing to say which
column it is.
What is confusing in your example is the "to text": that conversion
is the one that resolves the output column, and the user never wrote
it. A hint that says what to do covers that, and keeps the position:
ERROR: cannot cast type unknown to text
LINE 1: select cast(NULL::text as unknown);
^
HINT: Cast the expression to the type you want directly, not to
type unknown.
transformAssignedExpr() does the same for a type mismatch: it keeps
the position and adds "You will need to rewrite or cast the
expression."
I also tried an errdetail saying that only a literal of type unknown
can be converted to another type, and dropped it because it isn't
true: parameters of type unknown are converted too.
PREPARE p(unknown) AS SELECT $1::text works, before and after the
patch.
With the hint, make check passes (239), with no new warnings. One
case has no position with any wording, a UNION branch:
select a from t union all select cast(b as unknown) from t;
The set-operation coercion does not pass a location. That is how it
is today and I left it alone.
> I guess I understand your comments, below are the comments I came up
> with.
> What do you think?
It reads well, and what it says about can_coerce_type() is right: it
does "if (inputTypeId == UNKNOWNOID) continue;". One thing I would
add, for the same reason as the errdetail above: parameters of type
unknown are also handled before this point, through
p_coerce_param_hook, so "Unknown-type literals and parameters were
coerced above."
The SQL and the output of each variant are attached.
Regards,
Manu
-- messages.sql: the cases, run against master (89829354de1) and three
-- wordings of the new ereport() in coerce_type():
-- V0 as posted: message + error position
-- V1 without the error position
-- V2 position + errhint
-- How the error for a non-literal unknown reads to a user, in the places it
-- can be raised. Each case prints the full error: message, LINE with the
-- cursor, and DETAIL/HINT when there is one.
\set ON_ERROR_STOP off
\set VERBOSITY default
\set SHOW_CONTEXT errors
\pset footer off
create table t (a text, b text, c int);
insert into t values ('x', 'y', 1);
\echo '--- M1 the case jian raised: the result column is resolved to text'
select cast(NULL::text as unknown);
\echo '--- M2 same, from a column'
select cast(a as unknown) from t;
\echo '--- M3 one bad expression among several columns'
select a, c, cast(b as unknown) as bad, c + 1 from t;
\echo '--- M4 a multi-line CREATE VIEW, the bad expression on line 4'
create view v as
select a,
c,
cast(b as unknown) as bad
from t;
\echo '--- M5 the user asks for the conversion explicitly'
select cast(a as unknown)::int from t;
\echo '--- M6 as a function argument'
select length(cast(a as unknown)) from t;
\echo '--- M7 in a comparison'
select 1 from t where cast(a as unknown) = 'x';
\echo '--- M8 in a UNION branch'
select a from t union all select cast(b as unknown) from t;
\echo '--- M9 inserted into a column'
insert into t (b) select cast(a as unknown) from t;
\echo '--- M10 the other way in: unknownin()'
select unknownin('x')::text;
\echo '--- M11 PL/pgSQL assignment'
do $$ declare r int; begin r := '1'::text::unknown; end $$;
\echo '--- M12 control, unchanged: a literal of type unknown'
select 'x'::unknown::text;
-- params.sql: parameters of type unknown are converted, before and after
-- Parameters of type unknown: coerce_type() converts those through
-- p_coerce_param_hook when the parser has one. A parameter declared as
-- unknown in PREPARE is a fixed parameter; does it get here?
\set ON_ERROR_STOP off
\set VERBOSITY default
\pset footer off
\echo '--- P1 prepare with no types, $1 used as text'
prepare p1 as select $1::text;
execute p1('x');
\echo '--- P2 prepare p(unknown), $1::text'
prepare p2(unknown) as select $1::text;
execute p2('x');
\echo '--- P3 prepare p(unknown), $1 || text'
prepare p3(unknown) as select $1 || 'a'::text;
execute p3('x');
\echo '--- P4 prepare p(unknown), bare $1 in the target list'
prepare p4(unknown) as select $1;
execute p4('x');
\echo '--- P5 SQL function with an unknown argument'
create function fu(unknown) returns text language sql as 'select $1::text';
======== output: master
CREATE TABLE
INSERT 0 1
--- M1 the case jian raised: the result column is resolved to text
ERROR: failed to find conversion function from unknown to text
--- M2 same, from a column
ERROR: failed to find conversion function from unknown to text
--- M3 one bad expression among several columns
ERROR: failed to find conversion function from unknown to text
--- M4 a multi-line CREATE VIEW, the bad expression on line 4
ERROR: failed to find conversion function from unknown to text
--- M5 the user asks for the conversion explicitly
ERROR: failed to find conversion function from unknown to integer
--- M6 as a function argument
ERROR: failed to find conversion function from unknown to text
--- M7 in a comparison
ERROR: failed to find conversion function from unknown to text
--- M8 in a UNION branch
ERROR: failed to find conversion function from unknown to text
--- M9 inserted into a column
INSERT 0 1
--- M10 the other way in: unknownin()
unknownin
-----------
x
--- M11 PL/pgSQL assignment
DO
--- M12 control, unchanged: a literal of type unknown
text
------
x
======== output: V0
CREATE TABLE
INSERT 0 1
--- M1 the case jian raised: the result column is resolved to text
ERROR: cannot cast type unknown to text
LINE 1: select cast(NULL::text as unknown);
^
--- M2 same, from a column
ERROR: cannot cast type unknown to text
LINE 1: select cast(a as unknown) from t;
^
--- M3 one bad expression among several columns
ERROR: cannot cast type unknown to text
LINE 1: select a, c, cast(b as unknown) as bad, c + 1 from t;
^
--- M4 a multi-line CREATE VIEW, the bad expression on line 4
ERROR: cannot cast type unknown to text
LINE 4: cast(b as unknown) as bad
^
--- M5 the user asks for the conversion explicitly
ERROR: cannot cast type unknown to integer
LINE 1: select cast(a as unknown)::int from t;
^
--- M6 as a function argument
ERROR: cannot cast type unknown to text
LINE 1: select length(cast(a as unknown)) from t;
^
--- M7 in a comparison
ERROR: cannot cast type unknown to text
LINE 1: select 1 from t where cast(a as unknown) = 'x';
^
--- M8 in a UNION branch
ERROR: cannot cast type unknown to text
--- M9 inserted into a column
INSERT 0 1
--- M10 the other way in: unknownin()
unknownin
-----------
x
--- M11 PL/pgSQL assignment
DO
--- M12 control, unchanged: a literal of type unknown
text
------
x
======== output: V1
CREATE TABLE
INSERT 0 1
--- M1 the case jian raised: the result column is resolved to text
ERROR: cannot cast type unknown to text
--- M2 same, from a column
ERROR: cannot cast type unknown to text
--- M3 one bad expression among several columns
ERROR: cannot cast type unknown to text
--- M4 a multi-line CREATE VIEW, the bad expression on line 4
ERROR: cannot cast type unknown to text
--- M5 the user asks for the conversion explicitly
ERROR: cannot cast type unknown to integer
--- M6 as a function argument
ERROR: cannot cast type unknown to text
--- M7 in a comparison
ERROR: cannot cast type unknown to text
--- M8 in a UNION branch
ERROR: cannot cast type unknown to text
--- M9 inserted into a column
INSERT 0 1
--- M10 the other way in: unknownin()
unknownin
-----------
x
--- M11 PL/pgSQL assignment
DO
--- M12 control, unchanged: a literal of type unknown
text
------
x
======== output: V2
CREATE TABLE
INSERT 0 1
--- M1 the case jian raised: the result column is resolved to text
ERROR: cannot cast type unknown to text
LINE 1: select cast(NULL::text as unknown);
^
HINT: Cast the expression to the type you want directly, not to type unknown.
--- M2 same, from a column
ERROR: cannot cast type unknown to text
LINE 1: select cast(a as unknown) from t;
^
HINT: Cast the expression to the type you want directly, not to type unknown.
--- M3 one bad expression among several columns
ERROR: cannot cast type unknown to text
LINE 1: select a, c, cast(b as unknown) as bad, c + 1 from t;
^
HINT: Cast the expression to the type you want directly, not to type unknown.
--- M4 a multi-line CREATE VIEW, the bad expression on line 4
ERROR: cannot cast type unknown to text
LINE 4: cast(b as unknown) as bad
^
HINT: Cast the expression to the type you want directly, not to type unknown.
--- M5 the user asks for the conversion explicitly
ERROR: cannot cast type unknown to integer
LINE 1: select cast(a as unknown)::int from t;
^
HINT: Cast the expression to the type you want directly, not to type unknown.
--- M6 as a function argument
ERROR: cannot cast type unknown to text
LINE 1: select length(cast(a as unknown)) from t;
^
HINT: Cast the expression to the type you want directly, not to type unknown.
--- M7 in a comparison
ERROR: cannot cast type unknown to text
LINE 1: select 1 from t where cast(a as unknown) = 'x';
^
HINT: Cast the expression to the type you want directly, not to type unknown.
--- M8 in a UNION branch
ERROR: cannot cast type unknown to text
HINT: Cast the expression to the type you want directly, not to type unknown.
--- M9 inserted into a column
INSERT 0 1
--- M10 the other way in: unknownin()
unknownin
-----------
x
--- M11 PL/pgSQL assignment
DO
--- M12 control, unchanged: a literal of type unknown
text
------
x
======== output of params.sql, patched (same on master)
--- P1 prepare with no types, $1 used as text
PREPARE
text
------
x
--- P2 prepare p(unknown), $1::text
PREPARE
text
------
x
--- P3 prepare p(unknown), $1 || text
PREPARE
?column?
----------
xa
--- P4 prepare p(unknown), bare $1 in the target list
PREPARE
?column?
----------
x
--- P5 SQL function with an unknown argument
ERROR: SQL functions cannot have arguments of type unknown
