title: Unexpected effect of filtering on result from crosstab() query with multiple values
body: <p>I have a <code>crosstab()</code> query similar to the one in my previous question:<br>
  <a href="https://stackoverflow.com/questions/57209121/unexpected-effect-of-filtering-on-result-from-crosstab-query/57211646?noredirect=1#comment100947337_57211646">Unexpected effect of filtering on result from crosstab() query</a></p>
  
  <p>The common case is to filter <code>extra1</code> field with multiples values: <code>extra1 IN(value1, value2...)</code>. For each value included on the <code>extra1</code> filter, I have added an ordering expression like this <code>(extra1 &lt;&gt; valueN)</code>, as appear on the above mentioned post. The resulting query is as follows:</p>
  
  <pre><code>SELECT *
  FROM crosstab(
   'SELECT row_name, extra1, extra2..., another_table.category, value
    FROM   table t
    JOIN   another_table ON t.field_id = another_table.field_id
    WHERE  t.field = certain_value AND t.extra1 IN (val1, val2, ...) --&gt; more values
    ORDER  BY row_name ASC, (extra1 &lt;&gt; val1), (extra1 &lt;&gt; val2)', ... --&gt; more ordering expressions
   'SELECT category_name FROM category_name WHERE field = certain_value'
  ) AS ct(extra1, extra2...)
  WHERE extra1 = val1; --&gt; condition on the result
  </code></pre>
  
  <p>The first value of <code>extra1</code> included on the ordering expression <code>value1</code>, get the correct resulting rows. However, the following ones <code>value2</code>, <code>value3</code>..., get wrong number of results, resulting on less rows on each one. Why is that?</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>Giving this as our source table (<code>table t</code>):</p>
  
  <pre><code>+----------+--------+--------+------------------------+-------+
  | row_name | Extra1 | Extra2 | another_table.category | value |
  +----------+--------+--------+------------------------+-------+
  | Name1    | 10     | A      | 1                      | 100   |
  | Name2    | 11     | B      | 2                      | 200   |
  | Name3    | 12     | C      | 3                      | 150   |
  | Name2    | 11     | B      | 3                      | 150   |
  | Name3    | 12     | C      | 2                      | 150   |
  | Name1    | 10     | A      | 2                      | 100   |
  | Name3    | 12     | C      | 1                      | 120   |
  +----------+--------+--------+------------------------+-------+
  </code></pre>
  
  <p>And this as our category table:</p>
  
  <pre><code>+-------------+--------+
  | category_id | value  |
  +-------------+--------+
  | 1           | Cat1   |
  | 2           | Cat2   |
  | 3           | Cat3   |
  +-------------+--------+
  </code></pre>
  
  <p>Using the <code>CROSSTAB</code>, the idea is to get a table like this:</p>
  
  <pre><code>+----------+--------+--------+------+------+------+
  | row_name | Extra1 | Extra2 | cat1 | cat2 | cat3 |
  +----------+--------+--------+------+------+------+
  | Name1    | 10     | A      | 100  | 100  |      |
  | Name2    | 11     | B      |      | 200  | 150  |
  | Name3    | 12     | C      | 120  | 150  | 150  |
  +----------+--------+--------+------+------+------+
  </code></pre>
  
  <p>The idea is to be able to filter the resulting table so I get results with <code>Extra1</code> column with values <code>10</code> or <code>11</code>, as follow:</p>
  
  <pre><code>+----------+--------+--------+------+------+------+
  | row_name | Extra1 | Extra2 | cat1 | cat2 | cat3 |
  +----------+--------+--------+------+------+------+
  | Name1    | 10     | A      | 100  | 100  |      |
  | Name2    | 11     | B      |      | 200  | 150  |
  +----------+--------+--------+------+------+------+
  </code></pre>
  
  <p>The problem is that on my query, I get different result size for <code>Extra1</code> with <code>10</code> as value and <code>Extra1</code> with <code>11</code> as value. With <code>(Extra1 &lt;&gt; 10)</code> I can get the correct result size on <code>Extra1</code> for that value but not in the case of <code>11</code> as value.</p>
  
  <p>Here is a fiddle demonstrating the problem in more detail:</p>
  
  <p><a href="https://dbfiddle.uk/?rdbms=postgres_11&amp;fiddle=5c401f7512d52405923374c75cb7ff04" rel="nofollow noreferrer">https://dbfiddle.uk/?rdbms=postgres_11&amp;fiddle=5c401f7512d52405923374c75cb7ff04</a></p>