title: Query returning empty association with NOT IN () and with IN ()
body: <p>I have the models <code>Invitation</code> and <code>Person</code>. The attributes and relations important in this question are the following:</p>
  
  <pre><code># has sponsor_id, referral_email
  class Invitation &lt; ApplicationRecord
    belongs_to :sponsor, class_name: 'Person'
    belongs_to :referral, class_name: 'Person',
                          foreign_key: :referral_email, primary_key: :email
    scope :pending, -&gt; { where.not(referral_email: Person.select(:email).distinct) }
  end
  
  # has id, email
  class Person &lt; ApplicationRecord
    has_many :sent_invitations, class_name: 'Invitation', 
                                foreign_key: :sponsor_id
    has_many :received_invitations, class_name: 'Invitation', 
                                    foreign_key: referral_email, 
                                    primary_key: :email
    has_many :referrals, class_name: 'Persona', through: :sent_invitations
    has_many :sponsors, class_name: 'Persona', through: :received_invitations
  end
  </code></pre>
  
  <p>I found a record of <code>Invitation</code> which its <code>referral_email</code> was not present in the table <code>Person</code>, and its <code>sponsor_id</code> was of a valid <code>Person</code>.</p>
  
  <p><strong>The scenario:</strong> From the <code>@sponsor</code>, I tried to see how many <code>sent_invitations.pending</code> it had, but the query returned an empty relation:</p>
  
  <pre><code>@sponsor.sent_invitations.pending.count # =&gt; 0
  </code></pre>
  
  <p>But this is the result of other queries I run while trying to debug:</p>
  
  <pre><code>@sponsor.sent_invitations.count # =&gt; 1
  @sponsor.referrals.count # =&gt; 0
  @sponsor.sent_invitations.first.referral_email # =&gt; 'some_email@example.com'
  Person.pluck(:email).include? @sponsor.sent_invitations.first.referral_email # =&gt; false
  </code></pre>
  
  <p>I inverted the condition of the <code>scope</code> of <code>Invitation</code> to the following:</p>
  
  <pre><code>scope :pending, -&gt; { where(referral_email: Person.select(:email).distinct) }
  </code></pre>
  
  <p>But the intended query kept returning an empty relation</p>
  
  <pre><code>@person.sent_invitations.pending.count # =&gt; 0
  </code></pre>
  
  <p><strong>Does anyone know what may be happending?</strong></p>
  
  <p>The code worked fine on testing and we found this problem in production.</p>
  
  <p><strong>Queries</strong></p>
  
  <p>Query of original try</p>
  
  <pre><code>SELECT "invitations".* FROM "invitations" WHERE "invitations"."sponsor_id" = $1 AND ("invitations"."referral_email" NOT IN (SELECT DISTINCT "person"."email" FROM "person"))
  </code></pre>
  
  <p>Query of second try</p>
  
  <pre><code>SELECT "invitations".* FROM "invitations" WHERE "invitations"."sponsor_id" = $1 AND ("invitations"."referral_email" IN (SELECT DISTINCT "person"."email" FROM "person"))
  </code></pre>