0

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 :/

4
  • 1
    Without some sample input data this is impossible to answer. Maybe create a little dbfiddle to show the problem. Commented Oct 21, 2022 at 10:30
  • What data types are those columns? Why do you need to cast them to int? Commented Oct 21, 2022 at 10:32
  • What is dbg referring to? Commented Oct 21, 2022 at 15:57
  • @a_horse_with_no_name: actually there should be no need ... I was just trying to get the correct results but to no avail: I provided additional info in the post on the various datatypes and they seem correct to me ... Commented Oct 24, 2022 at 9:51

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.