Message-ID: <ccfa6e1b-4ed3-4829-898f-6992d8764aea@posteo.net>
Date: Thu, 28 Mar 2024 12:03:24 +0000
MIME-Version: 1.0
Content-Language: en-US
To: pgsql-general@lists.postgresql.org
From: rwxrw@posteo.net
Subject: Problems caused by type resolution for the unknown type
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Autocrypt: addr=rwxrw@posteo.net; prefer-encrypt=mutual;
  keydata=xsFNBGNf5UcBEACs/1cjEqbyOrrK+T3HQN9LMYpKtxRR6eeDoboI7mZHhkm+dquel/EHAklK8Gyq
  43QykgJA3U6kVIuH1WsD75A3REPMkWYB+fxGtIxTwQnqXdzazYWsEiQ70UWRrElHTFDmpi9O2zbO
  5vYgIFbjtZ92tCXiAJJkeU5nOUwyg55xU10q19wBkthKydh7S3lhb6m0DV0MiZX9RLsGeRhaqTz6
  +eYNoiblOmkV7OxsWtMdK9cEoHl3EkRTu6NzRDIP3UcDAt7bW1FE5OcUaqmQgkGNY+xHN6HZxB73
  nHT5WrjKrQuHcWkZm/Eg6kgAktl2NV7DUKxgev8bQoHqSR1GscB9oK33Mnz/UDbRs+5rmDNyImik
  s1B/mncXRVkTa/oIKcS5ZOGqliwDn6BxNjvqaDhJL6aqBP7Q+DZzfGLINFXDS7BmUQi7k2zOQhUx
  7/zy9aeVfYq+NiznfHU4esqGYSrpTGtOos5C9ohNdP2u6KaMeQxFqCgNaaAIPcmECVEdyzTsD2uQ
  mX9a894QOPkiCDaVwWLSgnaC+w3U9cOgGYuBq+fUXvYvTyuxvKnUeQyQ/hO0N099rGeeG2xgNvAO
  8j5q3uXmne0AMvyCnEjr7WpmNhI89/cq6zhxqKHK70sL67pdSddHi3v2YMocft0qNVcpBhFmApwU
  8sigAVgiHK+ZmwARAQABzRByd3hyd0Bwb3N0ZW8ubmV0wsGUBBMBCAA+FiEEqoXIacnBpRziiXLt
  n8rERD1rdSUFAmNf5UcCGwMFCQDtTgAFCwkIBwIGFQoJCAsCBBYCAwECHgECF4AACgkQn8rERD1r
  dSXtdw/8C865rQCOGFNzKfwWLMn2zwuO7P1SuWtbQPGYcEpf7tHxcSN5OmyT3b4aX/cjkC4s5XUx
  vE3r2Zi4Fy8EIDKnPsUbibg6sv2eZEZRwYOnSOQv6gsmXyKKsLzjDmfA8dF4Ny+M9f5EU42qHH7h
  o0OdYZTiyoQWKZXMhHUFUfJYLvilWEU7emJql+nEaErPxxthLzTRbyuIcbyuxtZR12Kv/9PS3rMH
  x/kH9WZxC6Z958ZlbnmZruoGy8uDnbhZUfLz6xbuiLFLwvqqXl0VRqQvKV277JUh9HMoM3jr52qN
  vS5IilcYDoATEWhWcPRPLXg3ooOYq6DaonafVf5rb+WocFuOS1A2dAJ0F1l83vSFaUCOXTC2jjvT
  A57qffOfu/4gvXZ2/2VVygu+ZdnAEzbAQY7QDrXh7Bdf1Fh2nZi5IIfoLli+5bpGT0f5DKTloji/
  PFEE6XwA2csa6Bye/S8BRIMgx/ojAGdham7lpEQNFkzRgIbMR4LrJe9jMZEZNl+Q+oaFoxFYdEXq
  P8qaTFFs8iSEVr7jFQrGmIBEkC65SXFvwv+NQvWBuI1/HhHmRrRxpWGXU1L2RakdaqL4hPSi2dIV
  eMOufgaEf3o16H+rUyr1MZeP6CKSOqcH9ZdGiNmwfZRoTXs1Pdb1zDYJ1t1UytOL6ipzt984HX2+
  X46xrq3OwU0EY1/lRwEQAMkZG1pGebziRmyGXFvHa0k0xqiEPgF7XbWRGL+JozI1jEtRJDwybbQd
  udAFmWYC6jZiS5Qae+OjMIRESS6gtqR99AvNvpJOCijx2NbUXv2I4lwRCH9ixwGdCIs9pV7exkro
  KhkOSP/sU+P932qy2GtcY60c0LSKIHhVsdywgA5lE6Mx9d4VmupOJvBFtb99Vh9LalLfiOpIupqx
  FYKp5BagqDkrOKMdlWHZPpMwfn80HmUkPZ1P/exsAZw9aCBdFZY6mpVdBR7KsKmbXiE8kupQkOHk
  O/3n3z8nfy2p8VQNuNnupkCa2xQ8HoOp+7CFamDQpuApj1b7/vhEf7ZXUNN3/L/e8WiEOFHKVZYH
  qVnTH/uYKbEhJvGUMpFoDj685o3uHgzp4hN8rlmg5btSUwQyj4zAp09iWOBobmJ3uDN2qN4bop5t
  wKoBghdzK1VN69iW7ozJD/8jRNqw29TYYZ5vty813BhskO3RLDs80YKDnkYP7H65IINSwW0iElma
  4yJZIklsS4zmqE5sIcsTcNj5M+/dAz7EF3eYxzXQMBEF2t0BE7IKRc9a4YltRNjhmGBg/siscBgT
  KikPYao0y2Fvq29j8iK2H72eZ74L2iT9FbNEodTiuJAMtTvxN884W9Iues8lATLyFDwl28KQhMqB
  dgiUrWcQVPwpeAnMHoL5ABEBAAHCwXwEGAEIACYWIQSqhchpycGlHOKJcu2fysREPWt1JQUCY1/l
  RwIbDAUJAO1OAAAKCRCfysREPWt1JaQFD/wPqWkz6djCLnfe+HKg0nOo2Ai5vQbKve841HaGMWxa
  WeXv/NteSc8Eu9FW4rueHY0Rl1H52pAtyLTt8VIrZOJ4CH17+xkV7irodW8SeuRlWQwbtBgu3AQc
  EzcbEZJx3JcP5ggzYL5PeHsFvZ2AAdlzFQ6tUGNQMbM9xQjrE5EkhCO2V2UFmOQpxo8ds7jgNxN5
  LHsiiOJJWjqoYrj9+p2wocVHSLIgJ9JNNOuGajja5MaxhfRyDt5ISfo8Ui9T6QVIZFFcEumVZFy0
  sHBavlSXJ149/26dRvXQ6fIGpCYULBw1ni4+jPIBsS+0/UFBfEgGEk8c+lWtYsO/gsaLQPiGvrst
  6dHUW7WE0TqOQM4mq2vk0Xr76u72GzGiasOn13bFP7yXi1RitTGBNkOLnYoHTNFRS+UJ0FZKcWy/
  TRcQeehPOOL/Z8TsbiC2srFw5EdFNafxyq+bDHNI8z+WJulq9UwH/2WkPHq/7PhgP9zWTsnohZjK
  9d5LAi/cj4plVgJqIN3JoXkWc4YpCfOijG6lv8sGdMrOP/p6rOsCUnbcBgOVjRDL+U17KNxBuwt2
  xV6cw9v8jUAlE5M486rrAfBwX7Xlq5hlOiZSLVHM4qxe4SLhUYnIG4g5cZaSY/u1dLCFv/W/UAl5
  DsDWVoJkwiNkzz4WrunVulph/W+b2cF0JA==
OpenPGP: url=https://posteo.de/keys/rwxrw@posteo.net.asc; preference=encrypt
Archived-At: 
 <https://www.postgresql.org/message-id/ccfa6e1b-4ed3-4829-898f-6992d8764aea%40posteo.net>
Precedence: bulk

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