postgres select count distinct returning unexpected extra row

Question

If there is one more UID in sessions than there is in users (obviously not supposed to be that way), then I expect to have a non-empty result set when I run the last select, but I get no rows returned - this result just doesn't make logical sense to me...

select count(distinct(uid)) from users;

> 108736

select count(distinct(uid)) from sessions;

> 108737

select count(*) from sessions where uid not in (select uid from users);

> 0

and just for completeness:

select count(*) from users where uid not in (select uid from sessions);

> 0

I have checked for nulls:

select count( * ) from sessions where uid is null; 

> 0 

select count( * ) from users where uid is null;

> 14

The schema is defined in sqlalchemy and includes a foreign key in the session table:

uid = Column(Integer, ForeignKey('users.uid', use_alter=True, name='fk_uid'))

This schema is a static dump for analytics purposes so there is no chance of concurrency issues...

sorry I should have mentioned that I had tried that - no dice — gkennos
– gkennos, Commented Oct 9, 2018 at 23:26
no it isn't - as stated I tried that, but thanks @wildplasser — gkennos
– gkennos, Commented Oct 9, 2018 at 23:42
Just for argument's sake, could you post results of select count(*) from users where uid is null and select count(*) from sessions where uid is null? — Rob Gwynn-Jones
– Rob Gwynn-Jones, Commented Oct 9, 2018 at 23:48
select count( * ) from sessions where mrn is null; --> 0 select count( * ) from users where mrn is null; --> 14... this is the opposite of what I'd expect if that was the answer as sessions has one more uid than users, but as expected considering the workflow of how the records are created by the program — gkennos
– gkennos, Commented Oct 10, 2018 at 0:17

Hinni · Accepted Answer · 2018-10-10 09:26:32Z

4

Your third query does not do what you think it does.

The following query illustrates the problem:

SELECT 1 NOT IN (SELECT unnest(ARRAY[NULL]::int[]));

This returns NULL, because it can't say if 1 <> NULL. So, in your query the where condition is always NULL, because users contains a NULL uid.

I recommend using EXCEPT do find the culprit in your sessions table.

SELECT uid from sessions EXCEPT SELECT uid from users;

answered Oct 10, 2018 at 9:26

Hinni

1661 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres select count distinct returning unexpected extra row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related