Compare variables of composite type with slightly different column types
Started by Andrey Lepikhovover 3 years ago2 messages
Hi,
Researching on join selectivity improvement I stuck into the code in
rowtypes.c:
/*
* Have two matching columns, they must be same type
*/
if (att1->atttypid != att2->atttypid)
ereport(ERROR, ...
Why, for example, isn't allowed next trivial query:
SELECT *
FROM
(SELECT ROW(1::integer, 'robert'::text)) AS s1,
(SELECT ROW(1::bigint, 'robert'::name)) AS s2
WHERE s1 = s2;
I guess, here the compatible_oper() routine can be used to find a
appropriate operator, or something like that can be invented.
I looked into the 2cd7084 and a4424c5, but don't found any rationale.
--
Regards
Andrey Lepikhov
Postgres Professional
Re: Compare variables of composite type with slightly different column types
On 26/5/2022 14:25, Andrey Lepikhov wrote:
I guess, here the compatible_oper() routine can be used to find a
appropriate operator, or something like that can be invented.
I looked into the 2cd7084 and a4424c5, but don't found any rationale.
In accordance to this idea I prepared a code. For a demo only.
--
regards,
Andrey Lepikhov
Postgres Professional
Attachments:
0001-Try-to-search-compatible-operator-if-columns-of-comp.patchtext/plain; charset=UTF-8; name=0001-Try-to-search-compatible-operator-if-columns-of-comp.patchDownload
From 71cf1a9148b20921aa60d7ae2062620c515557f8 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Date: Sat, 28 May 2022 22:01:01 +0300
Subject: [PATCH] Try to search compatible operator if columns of compared rows
is differ.
---
src/backend/utils/adt/rowtypes.c | 36 +++++++++++++++++++++++---
src/test/regress/expected/rowtypes.out | 11 +++++---
src/test/regress/sql/rowtypes.sql | 2 +-
3 files changed, 42 insertions(+), 7 deletions(-)
diff --git a/src/backend/utils/adt/rowtypes.c b/src/backend/utils/adt/rowtypes.c
index db843a0fbf..432b44ec8c 100644
--- a/src/backend/utils/adt/rowtypes.c
+++ b/src/backend/utils/adt/rowtypes.c
@@ -18,14 +18,17 @@
#include "access/detoast.h"
#include "access/htup_details.h"
+#include "catalog/pg_collation.h"
#include "catalog/pg_type.h"
#include "common/hashfn.h"
#include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
+#include "parser/parse_oper.h"
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/lsyscache.h"
+#include "utils/syscache.h"
#include "utils/typcache.h"
@@ -1071,6 +1074,8 @@ record_eq(PG_FUNCTION_ARGS)
int i1;
int i2;
int j;
+ Oid op_func = InvalidOid;
+ FmgrInfo op_func_finfo;
check_stack_depth(); /* recurses for record-type columns */
@@ -1173,12 +1178,27 @@ record_eq(PG_FUNCTION_ARGS)
* Have two matching columns, they must be same type
*/
if (att1->atttypid != att2->atttypid)
- ereport(ERROR,
+ {
+ Operator op;
+
+ op = compatible_oper(NULL, list_make1(makeString("=")),
+ att1->atttypid, att2->atttypid, false, -1);
+
+ if (!op)
+ ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("cannot compare dissimilar column types %s and %s at record column %d",
format_type_be(att1->atttypid),
format_type_be(att2->atttypid),
j + 1)));
+ else
+ {
+ op_func = oprfuncid(op);
+ fmgr_info(op_func, &op_func_finfo);
+ ReleaseSysCache(op);
+ }
+
+ }
/*
* If they're not same collation, we don't complain here, but the
@@ -1217,8 +1237,18 @@ record_eq(PG_FUNCTION_ARGS)
}
/* Compare the pair of elements */
- InitFunctionCallInfoData(*locfcinfo, &typentry->eq_opr_finfo, 2,
- collation, NULL, NULL);
+ if (!OidIsValid(op_func))
+ /* Compare the pair of elements */
+ InitFunctionCallInfoData(*locfcinfo, &typentry->eq_opr_finfo, 2,
+ collation, NULL, NULL);
+ else
+ {
+ /* XXX: Some collation matching logic needed. */
+ collation = (collation != InvalidOid) ? collation : DEFAULT_COLLATION_OID;
+ InitFunctionCallInfoData(*locfcinfo, &op_func_finfo, 2,
+ collation, NULL, NULL);
+ }
+
locfcinfo->args[0].value = values1[i1];
locfcinfo->args[0].isnull = false;
locfcinfo->args[1].value = values2[i2];
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index a4cc2d8c12..8a3c4e9454 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -423,8 +423,12 @@ select a,b from test_table where (a,b) > ('a','a') order by a,b;
reset enable_sort;
-- Check row comparisons with IN
-select * from int8_tbl i8 where i8 in (row(123,456)); -- fail, type mismatch
-ERROR: cannot compare dissimilar column types bigint and integer at record column 1
+select * from int8_tbl i8 where i8 in (row(123,456)); -- compare rows with columns of slightly different types
+ q1 | q2
+-----+-----
+ 123 | 456
+(1 row)
+
explain (costs off)
select * from int8_tbl i8
where i8 in (row(123,456)::int8_tbl, '(4567890123456789,123)');
@@ -618,7 +622,8 @@ create type testtype3 as (a int, b text);
select row(1, 2)::testtype1 < row(1, 'abc')::testtype3;
ERROR: cannot compare dissimilar column types integer and text at record column 2
select row(1, 2)::testtype1 <> row(1, 'abc')::testtype3;
-ERROR: cannot compare dissimilar column types integer and text at record column 2
+ERROR: operator does not exist: integer = text
+HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
create type testtype5 as (a int);
select row(1, 2)::testtype1 < row(1)::testtype5;
ERROR: cannot compare record types with different numbers of columns
diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql
index ad5b7e128f..5d17416117 100644
--- a/src/test/regress/sql/rowtypes.sql
+++ b/src/test/regress/sql/rowtypes.sql
@@ -166,7 +166,7 @@ select a,b from test_table where (a,b) > ('a','a') order by a,b;
reset enable_sort;
-- Check row comparisons with IN
-select * from int8_tbl i8 where i8 in (row(123,456)); -- fail, type mismatch
+select * from int8_tbl i8 where i8 in (row(123,456)); -- compare rows with columns of slightly different types
explain (costs off)
select * from int8_tbl i8
--
2.36.1