Oracle Select * returns rows but Select count(1) return 0

Question

So, this is bizarre and it's something I have never seen before. I'm hoping someone has the magic answer that can shed some light on this problem...

SELECT * FROM TABLE -- returns rows... a lot of rows

however,

SELECT COUNT(1) FROM TABLE -- returns zero (0), as in the number zero (0) as the result

Here's the table structure:

CREATE TABLE TRACKING (
  A_ID NUMBER,
  D_CODE NUMBER,
  HOD NUMBER,
  ADR_CNT NUMBER,
  TTL_CNT NUMBER,
  CREATED DATE,
  MODIFIED DATE
);
CREATE INDEX HOD_D_CODE_IDX ON TRACKING (HOD, D_CODE);
CREATE UNIQUE INDEX TRACKING_PK ON TRACKING (A_ID, D_CODE, HOD);
CREATE INDEX MOD_DATE_IDX ON TRACKING (MODIFIED);
ALTER TABLE TRACKING ADD CONSTRAINT TRACKING_PK PRIMARY KEY (A_ID, D_CODE, HOD);

How can an Oracle table have rows but count(1) return zero? I've done some searching on the web but found nothing. The only other post I found was in relation to MS SQL Server. This is happening in Oracle.

Any idea? Anyone?

Thank you in advance for any help you can provide.

Another thing I might add in hopes that it will help answer the puzzle, this table was used by an Oracle Job to aggregate and populate another table. However, it's been done for a few days now. The other table is fully populated and shows expected record counts. I checked the Oracle Job Log and it shows all success and not a single error.

Answer 1

Wrong results can be caused by corruption, bugs, and features that silently change SQL statements.

1. Corrupt index. Very rarely an index gets corrupt and the data from an index does not match the data from a table. This causes unexpected results when the query plan changes and an index is used, but everything looks normal for different queries that use table access. Sometimes simply re-building objects can fix this. If it doesn't, you'll need to create a fully reproducible test case (including data); either post it here or submit it to Oracle Support. It can take many hours to track this down.
2. Bug. Very rarely a bug can cause queries to fail when returning or changing data. Again, a fully reproducible test case is required to diagnose this, and it can take a while.
3. Feature that switches SQL There are a few ways to transparently alter SQL statements. Look into Virtual Private Database (VPD), DBMS_ADVANCED_REWRITE, and the SQL Translation Framework.

To rule out #3, the code below shows you one of the evil ways to do this, and how to detect it. First, create the schema and some data:

CREATE TABLE TRACKING (
  A_ID NUMBER,
  D_CODE NUMBER,
  HOD NUMBER,
  ADR_CNT NUMBER,
  TTL_CNT NUMBER,
  CREATED DATE,
  MODIFIED DATE
);
CREATE INDEX HOD_D_CODE_IDX ON TRACKING (HOD, D_CODE);
CREATE UNIQUE INDEX TRACKING_PK ON TRACKING (A_ID, D_CODE, HOD);
CREATE INDEX MOD_DATE_IDX ON TRACKING (MODIFIED);
ALTER TABLE TRACKING ADD CONSTRAINT TRACKING_PK PRIMARY KEY (A_ID, D_CODE, HOD);

insert into tracking values (1,2,3,4,5,sysdate,sysdate);
commit;

At first, everything works as expected:

SQL> SELECT * FROM TRACKING;

      A_ID     D_CODE        HOD    ADR_CNT    TTL_CNT CREATED   MODIFIED
---------- ---------- ---------- ---------- ---------- --------- ---------
         1          2          3          4          5 17-JUN-16 17-JUN-16

SQL> SELECT COUNT(1) FROM TRACKING;

  COUNT(1)
----------
         1

Then someone does this:

begin
  sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
    'april_fools',
    'SELECT COUNT(1) FROM TRACKING',
    'SELECT 0 FROM TRACKING WHERE ROWNUM = 1',
    false);
end;
/

Now the results are "wrong":

SQL> ALTER SESSION SET query_rewrite_integrity = trusted;

Session altered.

SQL> SELECT COUNT(1) FROM TRACKING;

  COUNT(1)
----------
         0

This can be probably be detected by looking at the explain plan. In the example below, the Predicate 2 - filter(ROWNUM=1) is a clue that something is wrong, since that predicate is not in the original query. Sometimes the "Notes" section of the explain plan will tell you exactly why it was transformed, but sometimes it only gives clues.

SQL> explain plan for SELECT COUNT(1) FROM TRACKING;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1761840423

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |     2 |     1   (0)| 00:00:01 |
|   1 |  VIEW             |                |     1 |     2 |     1   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY   |                |       |       |            |          |
|   3 |    INDEX FULL SCAN| HOD_D_CODE_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=1)

15 rows selected.

(On an unrelated note - always use COUNT(*) instead of COUNT(1). COUNT(1) is an old myth that looks like cargo cult programming.)