Hello,
I've identified a number of cases that suffer from problems caused by
the type
resolution algorithm when the "unknown" type is present.
The most common case is that of a UNION query which is even mentioned in the
documentation at https://www.postgresql.org/docs/16/typeconv-union-case.html
under "Example 10.13. Type Resolution in a Nested Union".
I had initially made a false assumption that only UNION, CASE, and Related
Constructs (listed in the documentation) are affected by this. Soon,
however,
I identified some more, which made me consider this a bug or at least a
significant inconvenience for PostgreSQL users because it inflicts the
burden
of inserting explicit type casts. This is especially impactful for
SQL-generating code which has to perform a lot of additional analysis to
determine the appropriate type cast.
Beside the UNION case from the documentation, here are the problematic
cases I
identified:
1. JOIN on integer = unknown (text)
--
SELECT *
FROM (SELECT 2 AS a) AS t1
JOIN (SELECT NULL AS b) AS t2
ON t1.a = t2.b;
SELECT *
FROM (SELECT 2 AS a) AS t1
JOIN (SELECT '2' AS b) AS t2
ON t1.a = t2.b;
--
Both queries fail with:
ERROR: operator does not exist: integer = text
LINE 4: ON t1.a = t2.b;
HINT: No operator matches the given name and argument types. You might
need to
add explicit type casts.
2. WHERE with a comparison between integer and unknown (text)
--
CREATE TABLE mytable (a int);
SELECT a FROM mytable
WHERE
a = (SELECT NULL);
SELECT a FROM Mytable
WHERE
a = (SELECT '1');
--
Both fail with:
ERROR: operator does not exist: integer = text
LINE 3: a = (SELECT NULL); // or LINE 3: a = (SELECT '1');
HINT: No operator matches the given name and argument types. You might
need to add explicit type casts.
3. CASE WHEN (which is subject to the same rules as UNION)
--
CREATE TABLE test_data(a int, b int);
SELECT a FROM test_data
WHERE
a = (CASE WHEN b > 0 THEN NULL ELSE '1' END)
--
Fails with:
ERROR: operator does not exist: integer = text
LINE 3: a = (CASE WHEN b > 0 THEN NULL ELSE '1' END)
HINT: No operator matches the given name and argument types. You might
need to add explicit type casts.
--
Note that all queries above succeed on Microsoft SQL Server.
I wonder if the type resolution algorithm could be changed to delay the
final
decision until the very last moment instead of resolving "unknown" as
"text".
Alternatively, maybe implicit type casting could be made use of during query
analysis, since the following query succeeds:
SELECT ('1'::text)::integer;
--
Cheers