Re: Oddity that I don't understand

I see why… The select of the view is picking d.ino which is null because there is no match in the dirents table.

Thanks guys!

> On Aug 11, 2022, at 08:23, Perry Smith <pedz(at)easesoftware(dot)com> wrote:
>
> I’m tempted to ask “Is this a bug” but I predict there is an explanation.
>
> I have a view:
> find_dups=# \sv+ dateien
> 1 CREATE OR REPLACE VIEW public.dateien AS
> 2 SELECT d.id <http://d.id/>,
> 3 d.basename,
> 4 d.parent_id,
> 5 d.ino,
> 6 d.ext,
> 7 i.ftype,
> 8 i.uid,
> 9 i.gid,
> 10 i.mode,
> 11 i.mtime,
> 12 i.nlink,
> 13 i.size,
> 14 i.sha1,
> 15 i.file_type
> 16 FROM dirents d
> 17 FULL JOIN inodes i USING (ino)
>
> find_dups=# \d inodes
> Table "public.inodes"
> Column | Type | Collation | Nullable | Default
> ------------+--------------------------------+-----------+----------+---------
> ino | bigint | | not null |
> ftype | character varying | | not null |
> uid | bigint | | not null |
> gid | bigint | | not null |
> mode | bigint | | not null |
> mtime | timestamp without time zone | | not null |
> nlink | bigint | | not null |
> size | bigint | | not null |
> sha1 | character varying | | |
> created_at | timestamp(6) without time zone | | not null |
> updated_at | timestamp(6) without time zone | | not null |
> file_type | character varying | | |
> Indexes:
> "inodes_pkey" PRIMARY KEY, btree (ino)
> "index_inodes_on_ftype_and_size_and_file_type_and_sha1_and_nlink" btree (ftype, size, file_type, sha1, nlink)
> Referenced by:
> TABLE "dirents" CONSTRAINT "fk_rails_f076303053" FOREIGN KEY (ino) REFERENCES inodes(ino)
>
> find_dups=# \d dirents
> Table "public.dirents"
> Column | Type | Collation | Nullable | Default
> ------------+--------------------------------+-----------+----------+-------------------------------------
> id | bigint | | not null | nextval('dirents_id_seq'::regclass)
> basename | character varying | | not null |
> parent_id | bigint | | |
> ino | bigint | | not null |
> created_at | timestamp(6) without time zone | | not null |
> updated_at | timestamp(6) without time zone | | not null |
> ext | character varying | | |
> Indexes:
> "dirents_pkey" PRIMARY KEY, btree (id)
> "index_dirents_on_basename" btree (basename)
> "index_dirents_on_ext" btree (ext)
> "index_dirents_on_ino" btree (ino)
> "index_dirents_on_parent_id_and_basename" UNIQUE, btree (parent_id, basename)
> Foreign-key constraints:
> "fk_rails_4dfefe0fc0" FOREIGN KEY (parent_id) REFERENCES dirents(id) ON DELETE CASCADE
> "fk_rails_f076303053" FOREIGN KEY (ino) REFERENCES inodes(ino)
> Referenced by:
> TABLE "dirents" CONSTRAINT "fk_rails_4dfefe0fc0" FOREIGN KEY (parent_id) REFERENCES dirents(id) ON DELETE CASCADE
>
> I do a select and I get:
>
> find_dups=# select id, basename, ext, parent_id, ino, sha1 from dateien where sha1 = '36f53d60353e0de6ed55d9da70a36b17559039f3' order by parent_id;
> id | basename | ext | parent_id | ino | sha1
> ----------+--------------+-----+-----------+----------+------------------------------------------
> 85276821 | VC5Y8191.CR2 | CR2 | 85273064 | 70163023 | 36f53d60353e0de6ed55d9da70a36b17559039f3
> 85829158 | VC5Y8191.CR2 | CR2 | 85827904 | 79366 | 36f53d60353e0de6ed55d9da70a36b17559039f3
> | | | | | 36f53d60353e0de6ed55d9da70a36b17559039f3
> (3 rows)
>
> How can the third line exist? Or, perhaps I should ask, what is the third line telling me?
>
> Thank you,
> Perry
>
>
>