[20220124]group by bug.txt

--//https://jonathanlewis.wordpress.com/2022/01/01/happy-new-year/,重复测试:

1.环境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t as (
        select date'2021-12-30' d from dual
        union all
        select date'2021-12-31'   from dual
);

select extract(year from d), count(*) from t group by extract(year from d);
alter table t add primary key(d);
select extract(year from d), count(*) from t group by extract(year from d);

--//测试结果.
SCOTT@book> select extract(year from d), count(*) from t group by extract(year from d);
EXTRACT(YEARFROMD)   COUNT(*)
------------------ ----------
              2021          2

SCOTT@book> alter table t add primary key(d);
Table altered.

SCOTT@book> select extract(year from d), count(*) from t group by extract(year from d);
EXTRACT(YEARFROMD)   COUNT(*)
------------------ ----------
              2021          2
--//无法测试出来.主要问题在于志行计划id=2 走HASH GROUP BY
Plan hash value: 1082526398
-------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |        |       |     2 (100)|          |       |       |          |
|   1 |  HASH GROUP BY   |              |      2 |    16 |     2  (50)| 00:00:01 |  1558K|  1558K|  489K (0)|
|   2 |   INDEX FULL SCAN| SYS_C0012226 |      2 |    16 |     1   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------------

SCOTT@book> select /*+ NO_USE_HASH_AGGREGATION(@SEL$1) */ extract(year from d), count(*) from t group by extract(year from d);
EXTRACT(YEARFROMD)   COUNT(*)
------------------ ----------
              2021          2

Plan hash value: 1136141978
-------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |        |       |     2 (100)|          |       |       |          |
|   1 |  SORT GROUP BY   |              |      2 |    16 |     2  (50)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   2 |   INDEX FULL SCAN| SYS_C0012226 |      2 |    16 |     1   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------------


Matthias Rogel supplied a follow-up demonstrating the problem with to_char(), which prompted me to create an example
showing that it wasn't just about dates – which I've tidied up below (reminder, all results on this page are from
12.2.0.1):

create  table t1 as
select  round(rownum/10,1) n1
from    all_objects
where   rownum <= 10
;


select n1 from t1 order by n1;
select n1, count(*) from t1 group by n1 order by n1;

column nch format A3

select to_char(n1,'99') nch, count(*) from t1 group by to_char(n1,'99') order by 1,2;

select * from table(dbms_xplan.display_cursor(format =>'outline'));

alter table t1 add constraint t1_pk primary key(n1);
select to_char(n1,'99') nch , count(*) from t1 group by to_char(n1,'99') order by 1,2;

select * from table(dbms_xplan.display_cursor(format =>'outline'));

--//重复测试在11.2.0.4没有这个问题.不过这个问题在19c上再现.

> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

create table t as (
        select date'2021-12-30' d from dual
        union all
        select date'2021-12-31'   from dual
  5  );

Table created.

> select extract(year from d), count(*) from t group by extract(year from d);
EXTRACT(YEARFROMD)   COUNT(*)
------------------ ----------
              2021          2

> alter table t add primary key(d);
Table altered.

> select extract(year from d), count(*) from t group by extract(year from d);
EXTRACT(YEARFROMD)   COUNT(*)
------------------ ----------
              2021          1
              2021          1

> @ sl all
alter session set statistics_level = all;
Session altered.

> select extract(year from d), count(*) from t group by extract(year from d)  order by 2;
EXTRACT(YEARFROMD)   COUNT(*)
------------------ ----------
              2021          1
              2021          1

> @ dpc '' 'projection outline' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  b0yb7wmdfq4jk, child number 0
-------------------------------------
select extract(year from d), count(*) from t group by extract(year from
d)  order by 2
Plan hash value: 3804481363
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |      1 |        |       |     1 (100)|          |      2 |00:00:00.01 |       2 |
|   1 |  INDEX FULL SCAN | SYS_C0065438 |      1 |      2 |    16 |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$9BB7A81A / T@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$9BB7A81A")
      ELIM_GROUPBY(@"SEL$47952E7A")
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      OUTLINE(@"SEL$47952E7A")
      ELIM_GROUPBY(@"SEL$1")
      OUTLINE(@"SEL$1")
      INDEX(@"SEL$9BB7A81A" "T"@"SEL$1" ("T"."D"))
      END_OUTLINE_DATA
  */
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "D"[DATE,7]

--//可以发现执行计划取消group by.

> select /*+ NO_ELIM_GROUPBY(@"SEL$47952E7A") */ extract(year from d), count(*) from t group by extract(year from d)  ;
EXTRACT(YEARFROMD)   COUNT(*)
------------------ ----------
              2021          1
              2021          1
--//加入提示NO_ELIM_GROUPBY无效.执行计划里面提示

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   1 -  SEL$9BB7A81A
         U -  NO_ELIM_GROUPBY(@"SEL$47952E7A")
--//应该使用SEL$9BB7A81A

SCOTT@78> select /*+ NO_ELIM_GROUPBY(@SEL$9BB7A81A */ extract(year from d), count(*) from t group by extract(year from d)  ;
EXTRACT(YEARFROMD)   COUNT(*)
------------------ ----------
              2021          2
--//昏,提示后少1个括号也是可以生效的.

Elapsed: 00:00:00.02
SCOTT@78> @ dpc '' 'outline projection' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  aur3jwvkg70cb, child number 0
-------------------------------------
select /*+ NO_ELIM_GROUPBY(@SEL$9BB7A81A */ extract(year from d),
count(*) from t group by extract(year from d)
Plan hash value: 47235625
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     4 (100)|          |      1 |00:00:00.01 |       2 |       |       |          |
|   1 |  HASH GROUP BY     |      |      1 |      2 |    16 |     4  (25)| 00:00:01 |      1 |00:00:00.01 |       2 |  1558K|  1558K|  482K (0)|
|   2 |   TABLE ACCESS FULL| T    |      1 |      2 |    16 |     3   (0)| 00:00:01 |      2 |00:00:00.01 |       2 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$1")
      END_OUTLINE_DATA
  */
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - EXTRACT(YEAR FROM INTERNAL_FUNCTION("D"))[22], COUNT(*)[22]