Unable to use a select join with this data, when it used to work earlier

Im trying to debug a really weird issue.

One of the parts of my database that used to work properly suddenty stopped to work. I have narrowed 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 their 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;

Well whats the issue there? My programme 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

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

It returns no rows, when I perfectly remember how it used to return some rows

Answer

The join appears to be incorrect.

Rather than:

c.user_id = cf.calendar_id

it looks like it needs to be:

c.id = cf.calendar_id

This way, the values will match, given the data you provided.

Advertisement

Answer