Tag: can39t

I am trying to debug a really strange problem.

One of the parts of my database that used to work correctly suddenly stopped working. I have reduced the problem to this:

First of all: those are my databases

users: id(int), email(var).....
calendar : id(int) , user_id(int)(fk->users(id))
calendar_field : id(int), text(text), rating(int), calendar_id(int)(fk-> calendar(id)), week_number(int)

This is your creation script:

USERS

    -- Table: public.users

-- DROP TABLE public.users;

CREATE TABLE public.users
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    email character varying(254) COLLATE pg_catalog."default" NOT NULL,
    password character varying(60) COLLATE pg_catalog."default" NOT NULL,
    refreshtoken character varying COLLATE pg_catalog."default",
    birth_date date,
    first_name character varying COLLATE pg_catalog."default",
    second_name character varying COLLATE pg_catalog."default",
    years_to_live integer,
    register_date date,
    death_date date,
    weeks_to_live integer,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_email_unique UNIQUE (email)
)

TABLESPACE pg_default;

ALTER TABLE public.users
    OWNER to postgres;

CALENDAR:

        -- Table: public.calendar

-- DROP TABLE public.calendar;

CREATE TABLE public.calendar
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    user_id integer,
    CONSTRAINT calendar_pkey PRIMARY KEY (id),
    CONSTRAINT "userId" FOREIGN KEY (user_id)
        REFERENCES public.users (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)

TABLESPACE pg_default;

ALTER TABLE public.calendar
    OWNER to postgres;

CALENDAR_FIELD

 -- Table: public.calendar_field

-- DROP TABLE public.calendar_field;

    CREATE TABLE public.calendar_field
    (
        id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
        text text COLLATE pg_catalog."default",
        rating integer,
        calendar_id integer NOT NULL,
        week_number integer,
        CONSTRAINT "CalendarField_pkey" PRIMARY KEY (id),
        CONSTRAINT "calendarField_calendar_id_fk_calendar_id" FOREIGN KEY (calendar_id)
            REFERENCES public.calendar (id) MATCH SIMPLE
            ON UPDATE CASCADE
            ON DELETE CASCADE
    )

    TABLESPACE pg_default;

    ALTER TABLE public.calendar_field
        OWNER to postgres;

What is the problem there? My program generates the following data in the database:

users:

- id: 58
- email: email1@email.com
- password: password
- refreshtoken: hash....
- birth_date: 2008-08-08
- first_name: first
- second_name: second
- years_to_live: 100
- register_date: 2020-05-12
- death_date: 2097-12-08
- weeks_to_live 5218

calendar:

- id: 64
- user_id: 58

calendar_field

-id: 462640
-text: notext
-rating: 0
-calendar_id: 64
-week_number: 1

And when I try to do this I select

SELECT * from calendar_field cf join calendar c on (c.user_id = cf.calendar_id) where week_number='1' and user_id='58';

It doesn't return rows, when I remember perfectly how it used to return some rows

It is not the curious fact, it is that when I insert the data manually, filling each field 1 by 1 with pgadmin, for example with this:

users:

- id: 58
- email: email2@email.com
- password: password
- refreshtoken: token
- birth_date: null
- first_name: null
- second_name: null
- years_to_live: null
- register_date: null
- death_date: null
- weeks_to_live null

calendar:

- id: 65
- user_id: 77

calendar_field

-id: 3000
-text: sd
-rating: 0
-calendar_id: 65
-week_number: 1

And then I throw it, it returns 0 rows too

SELECT * from calendar_field cf join calendar c on (c.user_id = cf.calendar_id) where week_number='1' and user_id='77';

(At the time of writing the above selection doesn't work, but sometimes after inserting the data manually it used to work)

And with both data inserted starting

SELECT * from calendar_field cf join calendar c on (c.user_id = cf.calendar_id) ; it will also return 0 rows. What's going on?