1

I am running a query like this:

SELECT distinct me.*
    FROM me
    LEFT JOIN has_things ON has_things.me_id = me.id
    LEFT JOIN has_refs ON has_refs.thing_id = has_things.thing_id
    WHERE
        has_refs.ref_id = 456
        AND me.id=123;

In the first variant, I omit the last line AND me.id = 123 and get 0 rows while in the second variant I include that line and get 1 row.

In another variant where the last line is AND me.id >= 123, I get 0 rows.

I am using psql to query the database.

What am I missing, and how can I fix this? I thought AND conditions can only reduce the number of matches.

Does anyone know of such PostgreSQL bugs where rows just disappear or vanish, maybe with links to bug tickets or something?

Seems related to my other question:
How can PDO not return a row which pg_query() does?

Arc
  • 11,143
  • 4
  • 52
  • 75
  • 2
    What data type is `me.id`? Can you provide a complete test setup to reproduce this? Does the execution plan show an index lookup for that column? If yes, does recreating the index help? –  May 10 '16 at 10:20
  • 2
    It's likely in combination with what you have going on in your LEFT JOIN and the remainder parts of WHERE – Allan S. Hansen May 10 '16 at 10:20
  • `me.id` is of type `serial NOT NULL`. We will try recreating the index during the night hours where load is low. I updated my query with the real one, which I managed to minimize while still producing the same results. To me, the `JOIN`s look entirely unsuspicious. – Arc May 10 '16 at 10:53

1 Answers1

0

Recreating the indexes fixed the problem.

Now I wonder how one can detect corrupt or inconsistent indexes to automatically perform a reindexing.

Arc
  • 11,143
  • 4
  • 52
  • 75
  • 1
    What is the **exact** Postgres version you are using? –  May 10 '16 at 12:26
  • The most recent 9.4 version is 9.4.7 and there were a lot of bugfixes between 9.4.2 and 9.4.7 so I wouldn't be surprised if the current version did not show this behavior. [Quote from the homepage](http://www.postgresql.org/support/versioning/): "*All users should upgrade to the most recent minor release as soon as possible. While upgrades always have some risk, PostgreSQL minor releases fix only frequently-encountered, security, and data corruption bugs to reduce the risk of upgrading. **The community considers not upgrading to be riskier than upgrading***" –  May 10 '16 at 14:29
  • I know, but that's not in my power. – Arc May 10 '16 at 15:24