Re: Oddity that I don't understand

[Perry Smith <pedz@xxxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxxxxx> 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,

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

Attachment: signature.asc
Description: Message signed with OpenPGP