Select count() returns 0 but select returns 2 rows

Question

This is very similar to this question with a full minimal example.

I have a simple select query (from a non-empty table) with only left joins. The last left join happens to be with an empty table.

The query returns 2 non-null rows as it should, but simply changing it to a count(*) query makes it return 0 as the count of rows.

The same SQL works properly on both MySQL and MSSQL (after fixing the PK syntax).

Full (re-runnable if uncomented) example:

-- DROP TABLE first;
-- DROP TABLE second;
-- DROP TABLE empty;

CREATE TABLE first (
  pk int,
  fk int
);
ALTER TABLE first
 ADD CONSTRAINT PK_first PRIMARY KEY (pk);

CREATE TABLE second (
  pk int
);
ALTER TABLE second
 ADD CONSTRAINT PK_second PRIMARY KEY (pk);

CREATE TABLE empty (
  pk int
);

ALTER TABLE first ADD CONSTRAINT FK_first FOREIGN KEY (fk)
 REFERENCES second (pk) ENABLE;

INSERT INTO second (pk)
  VALUES (5);
  
INSERT INTO first (pk, fk)
  VALUES (1, 5);
INSERT INTO first (pk, fk)
  VALUES (2, 5);

SELECT
  COUNT(*)
FROM first
LEFT OUTER JOIN second
  ON (first.fk = second.pk)
LEFT OUTER JOIN empty
  ON (1 = 1);

The last query returns 0 on my machine, but changing the count(*) to just * makes it return 2 rows.

Can anyone reproduce this? My db_version is 11.2.0.2.

Explain plan seems to see the 2 rows that should be returned:

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |    13 |            |          |
|   2 |   MERGE JOIN CARTESIAN|          |     2 |    26 |     3   (0)| 00:00:01 |
|   3 |    VIEW               |          |     1 |       |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL | EMPTY    |     1 |       |     2   (0)| 00:00:01 |
|   5 |    BUFFER SORT        |          |     2 |    26 |     3   (0)| 00:00:01 |
|   6 |     INDEX FULL SCAN   | PK_FIRST |     2 |    26 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement (level=2)

I don't know much about dynamic sampling, but if i alter session set OPTIMIZER_DYNAMIC_SAMPLING=0;, then the plan shows 82 rows in each step.

Removing the primary keys fixes the problem on Oracle, but that is hardly a proper solution.

Removing the join into the empty table also fixes the problem, but it is an outer join with tautological filter, so it should be a noop.

Is this actually the intended behavior on Oracle for some reason? Or is my server just bugged? Both MSSQL and MySQL return 2 as the count.

Edit: Round 2

It was enough to add 2 more tables and the bug shows also in 11.2.0.4. Can anyone reproduce it on more current Oracle versions?

An online fiddle here.

CREATE TABLE first (
  pk int,
  fk int
);
ALTER TABLE first
 ADD CONSTRAINT PK_first PRIMARY KEY (pk);

CREATE TABLE second (
  pk int,
  fk int
);
ALTER TABLE second
 ADD CONSTRAINT PK_second PRIMARY KEY (pk);

CREATE TABLE third (
  pk int,
  fk int
);
ALTER TABLE third
 ADD CONSTRAINT PK_third PRIMARY KEY (pk);

CREATE TABLE fourth (
  pk int
);
ALTER TABLE fourth
 ADD CONSTRAINT PK_fourth PRIMARY KEY (pk);

CREATE TABLE empty (
  pk int
);

ALTER TABLE first ADD CONSTRAINT FK_first FOREIGN KEY (fk)
 REFERENCES second (pk) ENABLE;

ALTER TABLE second ADD CONSTRAINT FK_second FOREIGN KEY (fk)
 REFERENCES third (pk) ENABLE;

ALTER TABLE third ADD CONSTRAINT FK_third FOREIGN KEY (fk)
 REFERENCES fourth (pk) ENABLE;

INSERT INTO fourth (pk)
  VALUES (50);
  
INSERT INTO third (pk, fk)
  VALUES (10, 50);
INSERT INTO third (pk, fk)
  VALUES (11, 50);

INSERT INTO second (pk, fk)
  VALUES (5, 10);
INSERT INTO second (pk, fk)
  VALUES (6, 10);

INSERT INTO first (pk, fk)
  VALUES (1, 5);
INSERT INTO first (pk, fk)
  VALUES (2, 5);

SELECT
  COUNT(*)
FROM first
LEFT OUTER JOIN second
  ON (first.fk = second.pk)
LEFT OUTER JOIN third
  ON (first.pk = third.pk)
LEFT OUTER JOIN fourth
  ON (third.fk = fourth.pk)
LEFT OUTER JOIN empty
  ON (1 = 1);

Anyway the consensus seems to be that this is a bug in obsolete Oracle releases.

In response to a comment on the linked question, changing the query to SELECT COUNT(1) OVER () returns 0 rows instantly. — Jakub Fojtik, Aug 19 '20 at 12:59
[Works in Oracle 18](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=07de98b13c733ff1f5a7d668c64b0cff) (and 12.1) - could very well be a bug in your unsupported (and no longer maintained) version. — , Aug 19 '20 at 13:01
Hah.. Just did the same.. @hwnn.. it really is broken on 11.2 - http://sqlfiddle.com/#!4/f91ac/1 — Caius Jard, Aug 19 '20 at 13:04
Just tried it on a second machine and it works correctly. db_version 11.2.0.4 - they fixed it quickly. — Jakub Fojtik, Aug 19 '20 at 13:13
Could anyone test the new code on recent Oracle systems please? I can't make dbfiddle.uk work, says invalid character. — Jakub Fojtik, Aug 19 '20 at 14:02
Second one [works in Oracle 18](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=7b201f05269f361071f5e42cbe536ecd) — , Aug 19 '20 at 14:16
@JakubFojtik I've updated my answer with an additions about Update2 and hints — Sayan Malakshinov, Aug 19 '20 at 18:10

Sayan Malakshinov · Accepted Answer · 2020-08-19T18:08:47.463

2

11.2.0.2 is too old version (EOL already )and looks like it even has never been patched.

The obvious workaroud for your bug is the hint no_query_transformation, try:

SELECT--+ no_query_transformation
  COUNT(*)
FROM first
LEFT OUTER JOIN second
  ON (first.fk = second.pk)
LEFT OUTER JOIN empty
  ON (1 = 1);

Update and addition: you can just disable join elimination using hint NO_ELIMINATE_JOIN: http://sqlfiddle.com/#!4/9cf338/10

SELECT--+ NO_ELIMINATE_JOIN(second)
  COUNT(*)
FROM first
LEFT OUTER JOIN second
  ON (first.fk = second.pk)
LEFT OUTER JOIN empty e
  ON (1 = 1);

or _optimizer_join_elimination_enabled: http://sqlfiddle.com/#!4/9cf338/10

    SELECT--+ opt_param('_optimizer_join_elimination_enabled' 'false')
      COUNT(*)
    FROM first
    LEFT OUTER JOIN second
      ON (first.fk = second.pk)
    LEFT OUTER JOIN third
      ON (first.pk = third.pk)
    LEFT OUTER JOIN fourth
      ON (third.fk = fourth.pk)
    LEFT OUTER JOIN empty
      ON (1 = 1);

edited Aug 19 '20 at 18:08

answered Aug 19 '20 at 13:20

Sayan Malakshinov

8,492
1
19
27

Thanks, that works. Is this usable in production though, since it disables optimizations? Or is there maybe a more specific hint that would have less perf impact? Could help people who cannot upgrade for whatever reason. – Jakub Fojtik Aug 19 '20 at 13:24
1

@JakubFojtik yes, of course, for example the most obvious in your case is `NO_OUTER_JOIN_TO_INNER`, but there are a lot of nuances. It would much better if you provide full query with full output of real execution plan using `dbms_xplan.display_cursor(..,..,'advanced')` – Sayan Malakshinov Aug 19 '20 at 13:30
Thanks, the _optimizer_join_elimination_enabled works even on our older database v11.2.0.2, and seems specific enough to not disable too many optimizations. – Jakub Fojtik Aug 21 '20 at 07:32

Select count(*) returns 0 but select * returns 2 rows

1 Answers1

Select count() returns 0 but select returns 2 rows