Contradictory Oracle query result

Question

I perform two queries and get strange result. I'd like to know why is this happening.
Now queries:

SELECT * FROM TABLE_A a, TABLE_B b, TABLE_C c
  WHERE a.a = b.a (+)
  AND b.c = c.c
  AND a.a = 123;

Result is empty.

SELECT COUNT(*) FROM TABLE_A a, TABLE_B b, TABLE_C c
  WHERE a.a = b.a (+)
  AND b.c = c.c
  AND a.a = 123;

Result is 1.

It is really the same query with different returned value.
Table A contains row with 'a' field = 123.
Table B does not contain rows as a.a = b.a.

How can nothing or 1 be returned for the same query?

Update:

When I make it this way

SELECT COUNT(*) FROM TABLE_A a, TABLE_B b, TABLE_C c
  WHERE a.a = b.a (+)
  AND b.c = c.c (+)
  AND a.a = 123;

It works OK.

Answer 1

Possibly you have indices or foreign key constraints that are not consistent with the table data. Since these two queries most likely use different indices, they return inconsistent data.

Have you temporarily disabled the constrataints, or set them up without validating that they have initially been valid (ENABLE NOVALIDATE)?

Try to rebuild the indices and drop and recreate the foreign key constraints to see whether that fixes your problem.