Oddity that I don't understand
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
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!
Show quoted text
On Aug 11, 2022, at 08:23, Perry Smith <pedz@easesoftware.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 CASCADEI 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