I do not understand the behaviour of the following query:
SELECT qs.id_gen::int as qs_i
, fi.id_gen::int as fi_i
, ABS(qs.id_gen::int - fi.id_gen::int) as delta
FROM peap_000.v_tlr_quantita_std qs
, peap_000.dbg_gestore fi
WHERE (
ABS(qs.id_gen::int - fi.id_gen::int) = 0
AND qs.id_tipofonte = fi.id_tipofonte
AND qs.id_questionario = fi.id_questionario
)
- as it is, it returns 1 record (the same I would get with 'qs.id_gen::int = fi.id_gen::int' which is not what I expect from my tables);
- if I replace '= 0' with '< 0' it does not return anything (fine with me);
- if I replace '= 0' with '<= 0' it returns many records (actually, the result I do expect).
Both tables are actually views (v_... is a materialized view), id_gen is a numeric field (int4 in v_... and should also be int4 in dbg... which reports the field from a MySQL table mapped via a foreign data wrapper).
I'm quite puzzled ... What am I missing?
I add additional information and present an alternate form of the query. The following are the DDLs for the relevant part of the foreign table used:
CREATE FOREIGN TABLE peap_000.tlr_tblfontequestionario (
"IdTipoFonte" int4 NOT NULL, -- MySQL original int(11)
"IdQuestionario" int4 NOT NULL, -- MySQL original int(11)
"IdGen" int4 NOT NULL,... -- MySQL original int(11)
)
CREATE FOREIGN TABLE peap_000.tlr_tblquestionario (
"IdQuestionario" int4 NOT NULL, -- MySQL original int(11)
"Gestore" varchar(50) NOT NULL,...
)
Now I create a temporary table so that I can inspect the types of the colunms used by the query under examination:
CREATE TABLE peap_000.tmp123 as
SELECT qs.id_questionario -- reported as int4 by pg_catalog inspection
, qs.anno -- reported as int4 by pg_catalog inspection
, qs.id_tipofonte -- reported as int4 by pg_catalog inspection
, qs.id_gen -- reported as int4 by pg_catalog inspection
, fi.id_gen_fi
, fi.gestore
FROM peap_000.v_tlr_quantita_std qs -- a materialized view
, (
SELECT Q."IdQuestionario" AS id_questionario
, E."Gestore" AS gestore
, Q."IdTipoFonte" AS id_tipofonte
, Q."IdGen" AS id_gen_fi
FROM peap_000.tlr_tblfontequestionario Q
, peap_000.tlr_tblquestionario E
WHERE E."IdQuestionario" = Q."IdQuestionario"
) fi
WHERE (qs.id_tipofonte = fi.id_tipofonte
AND qs.id_questionario = fi.id_questionario
)
-- the corresponding DDL
CREATE TABLE peap_000.tmp123 (
id_questionario int4 NULL,
gestore varchar(50) NULL,
anno int4 NULL,
id_tipofonte int4 NULL,
id_gen int4 NULL,
id_gen_fi int4 NULL
);
Now I test my simple query using the temporary table:
-- returns 740 rows: what I expect
SELECT *
FROM peap_000.tmp123
WHERE (id_gen = id_gen_fi)
Now I try to get the same result without the tmp table but using what I think is the 'same' query (but I am evidently wrong):
-- returns 1 row: I (wrongly, I suppose) think
-- this to be equivalent to previous query using tmp123 table
SELECT qs.id_questionario -- reported as int4 by pg_catalog inspection
, qs.anno -- reported as int4 by pg_catalog inspection
, qs.id_tipofonte -- reported as int4 by pg_catalog inspection
, qs.id_gen -- reported as int4 by pg_catalog inspection
, fi.id_gen_fi
, fi.gestore
FROM peap_000.v_tlr_quantita_std qs -- a materialized view
, (
SELECT Q."IdQuestionario" AS id_questionario
, E."Gestore" AS gestore
, Q."IdTipoFonte" AS id_tipofonte
, Q."IdGen" AS id_gen_fi
FROM peap_000.tlr_tblfontequestionario Q
, peap_000.tlr_tblquestionario E
WHERE E."IdQuestionario" = Q."IdQuestionario"
) fi
WHERE (id_gen = id_gen_fi
AND qs.id_tipofonte = fi.id_tipofonte
AND qs.id_questionario = fi.id_questionario
)
and if instead of the simple check
id_gen = id_gen_fi
I use the different guises
abs(id_gen - id_gen_fi) <= 0 or <0 or =0
I get respectively what I expect (i.e. 740 rows), the empty set, a single row. Well, I remain puzzled and unable to find the shameful error I am committing :/
int?dbgreferring to?