Postgres SELECT query not working properly

Question

I have 2 query execute in pgAdmin 4.

select * from v_anken where gyomu_name like '%ひとり%' and nyuryoku_cd like '0187'

=> when select with gyomu_name and nyuryoku_cd . return 1 row

with tempt as (select * from v_anken ) 
select * from tempt where gyomu_name like '%ひとり%' and nyuryoku_cd like '0187'

=> return 10 row

View:

CREATE OR REPLACE VIEW public.v_anken AS
 SELECT a.anken_seq,
    a.kokyaku_cd,
    b.kokyaku_name,
    b.kokyaku_ryaku,
    a.gyomu_seq,
    c.gyomu_cd,
    c.gyomu_name,
    fnc_getname('ANKEN_KBN'::character varying, c.anken_kbn::integer)::text  AS anken_kbn,
    fnc_getname('SEISAN_KBN'::character varying, c.seisan_kbn::integer)::text AS seisan_kbn,
    a.kotei_seq,
    d.kotei_kbn,
    a.shozoku_seq,
    a.pkg_yosan,
    COALESCE(g.jyuchu_yosan, a.pkg_yosan, NULL::numeric) AS yosan_kei,
    a.kaihatsu_kikan_from,
    a.kaihatsu_kikan_to,
    a.pkg_shokyaku_genka,
    a.pkg_shokyaku_yosan,
    a.nyuryoku_cd,
    concat_ws(' '::text, e.lastname, e.firstname) AS nyuryoku_name,
    a.nyuryoku_ymd AS nyuryoku_date,
    a.shonin_comment,
    a.shonin_cd,
    concat_ws(' '::text, f.lastname, f.firstname) AS shonin_name,
    a.shonin_ymd,
        CASE
            WHEN a.shonin_ymd IS NOT NULL THEN '済'::text
            ELSE ''::text
        END AS shonin_kbn,
    a.sagyo_kanryo_kbn,
    a.sagyo_kanryo_ym,
    a.biko,
    a.horyu_kbn,
    a.refix_time,
    a.inputtable_date,
    a.working_complete_report_checked_date,
    a.end_date
   FROM t_anken a
     JOIN v_kokyaku b ON a.kokyaku_cd = b.kokyaku_cd
     JOIN t_gyomu c ON a.gyomu_seq = c.gyomu_seq
     JOIN t_kotei d ON a.kotei_seq = d.kotei_seq
     JOIN employee e ON a.nyuryoku_cd::text = e.code::text
     LEFT JOIN employee f ON a.shonin_cd::text = f.code::text
     LEFT JOIN ( SELECT t_anken_jyuchu.anken_seq,
            sum(t_anken_jyuchu.gp_yosan) AS jyuchu_yosan
           FROM t_anken_jyuchu
          GROUP BY t_anken_jyuchu.anken_seq) g ON a.anken_seq = g.anken_seq;

ALTER TABLE public.v_anken
    OWNER TO postgres;

Function:

CREATE OR REPLACE FUNCTION public.fnc_getname(
    namekbn character varying,
    code integer)
    RETURNS character varying
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
BEGIN

     RETURN (SELECT name
               FROM t_name
              WHERE name_kbn = namekbn
                AND cd = code
            );

END;
$BODY$;

ALTER FUNCTION public.fnc_getname(character varying, integer)
    OWNER TO postgres;

when select from .. where .. and. sometimes return only 1 row, although returns more than 1. What could be the problem? I have no idea even where to search. Need help. Thanks.

For first query returns data when both conditions are true and second query first retrieve data when one condition then selection data by applying other conditions. Please check few data. — Rahul Biswas
– Rahul Biswas, Commented Nov 4, 2021 at 5:16
I checked the data very carefully. i execute from view contains function. I don't know if it affects anything or not. — Nguyễn Thắng
– Nguyễn Thắng, Commented Nov 4, 2021 at 6:24
@NguyễnThắng you must have run some update inbetween running those two queries...I am sure about it.. — Nikhil
– Nikhil, Commented Nov 4, 2021 at 6:38
PostgreSQL returns only one row. when deleting that row data , then select will produce another data line. — Nguyễn Thắng
– Nguyễn Thắng, Commented Nov 4, 2021 at 6:40

Laurenz Albe · Accepted Answer · 2021-11-04 06:48:58Z

1

That looks like index corruption. Does the problem persist after REINDEX TABLE v_anken? (If it is a view, reindex the underlying tables.)

Perhaps there is a hardware problem, perhaps you upgraded the C library on the operating system without rebuilding the indexes on strings, perhaps you promoted a standby on a different version of the operating system.

answered Nov 4, 2021 at 6:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Nguyễn Thắng Over a year ago

yes, v_anken is a view. contains function. when execute right time wrong time. sometimes will return all data, sometimes just 1 row. when return 1 row, i detele that row data. execute again, the return result is another line of data

Laurenz Albe Over a year ago

Please add the definition of the function and the view to the question.

Nguyễn Thắng Over a year ago

I have updated, you can see it. Thanks you.

Laurenz Albe Over a year ago

I see nothing suspicious at first glance. Did you try rebuilding indexes?

Nguyễn Thắng Over a year ago

I will try, thanks for your support.

Collectives™ on Stack Overflow

Postgres SELECT query not working properly

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related