OraFAQ Forum: SQL & PL/SQL » does any one use oracle v12?

Home » SQL & PL/SQL » SQL & PL/SQL » does any one use oracle v12? (oracle v12)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

does any one use oracle v12? [message #637647]

Thu, 21 May 2015 06:03

LargeHadronCollider
Messages: 7
Registered: May 2015
Location: Belarus

Junior Member

hi!
i looking for any one, who have oracle v12 and can try and comfirm (or not) it bug (google not helps me)
so bug: grouping not work properly on subqueries
reproducing:

//table
CREATE TABLE SCOTT.TEST
(
  TIME   DATE,
  TEXT   CHAR(10 BYTE),
  VALUE  NUMBER
);

//filling
INSERT INTO SCOTT.TEST (TIME, TEXT, VALUE) VALUES (to_date('2015052101','YYYYMMDDHH24'), 'aaa', 123.2);
INSERT INTO SCOTT.TEST (TIME, TEXT, VALUE) VALUES (to_date('2015052102','YYYYMMDDHH24'), 'aaa', 456.25);
INSERT INTO SCOTT.TEST (TIME, TEXT, VALUE) VALUES (to_date('2015052101','YYYYMMDDHH24'), 'bbb', 12345);
INSERT INTO SCOTT.TEST (TIME, TEXT, VALUE) VALUES (to_date('2015052102','YYYYMMDDHH24'), 'bbb', 784);

//Query1 (result is ok)
select trunc(TIME),TEXT,avg(VALUE) VALUE from SCOTT.test group by trunc(TIME),TEXT;

//Query2 (wrong result)
select trunc(TIME)
      ,TEXT
      ,avg(VALUE)        
from (select TIME        
            ,TEXT
            ,max(VALUE) VALUE     
      from SCOTT.test
      group by TIME,TEXT) t1
group by trunc(TIME),TEXT;

//Query3 (result is ok)
select trunc(TIME)
      ,TEXT
      ,avg(VALUE)        
from (select TIME        
            ,to_char(TEXT) text
            ,max(VALUE) VALUE     
      from SCOTT.test
      group by TIME,TEXT) t1
group by trunc(TIME),TEXT;

Query1 result (like expect)

TRUNC(TIME)	TEXT	AVG(VALUE)
5.21.2015	bbb	6564.5
5.21.2015	aaa	289.725

Query2 result (unexpect Shocked

, grouping is does not work)

TRUNC(TIME)	TEXT	AVG(VALUE)
5.21.2015	aaa	123.2
5.21.2015	bbb	784
5.21.2015	aaa	456.25
5.21.2015	bbb	12345

Query3 result is same for Query1
Query2 & Query3 logically is same but result is different

oracle v11 for all three queries shows same results, like for query1

please any one, to try reproduce it...
maybe i fool..

PS: maybe wrong topic... sorry...

[Updated on: Thu, 21 May 2015 06:04]

Report message to a moderator

Re: does any one use oracle v12? [message #637648 is a reply to message #637647]

Thu, 21 May 2015 06:30

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

No, it is not a bug. Different queries, different output.

All the queries would result the same output. Your posted output is not the output of Query2, but the output of the subquery of Query2.

Understand that, GROUP BY TRUNC(TIME) and GROUP BY TIME are different. TRUNC on DATE truncates the time portion, thus in your sample data, after truncating the time portion all the 4 rows have same date. Thus, average is done on the aggregate.

SQL> SELECT TIME ,TEXT ,MAX(VALUE) VALUE FROM test GROUP BY TIME,TEXT;

TIME      TEXT            VALUE
--------- ---------- ----------
21-MAY-15 aaa             123.2
21-MAY-15 bbb               784
21-MAY-15 aaa            456.25
21-MAY-15 bbb             12345

SQL> SELECT TRUNC(TIME),TEXT,AVG(VALUE) VALUE FROM test GROUP BY TRUNC(TIME),TEXT;

TRUNC(TIM TEXT            VALUE
--------- ---------- ----------
21-MAY-15 bbb            6564.5
21-MAY-15 aaa           289.725

Testing your 3 queries, here are the result on 12.1.0.1:

SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> SELECT TRUNC(TIME),TEXT,AVG(VALUE) VALUE FROM test GROUP BY TRUNC(TIME),TEXT;

TRUNC(TIM TEXT            VALUE
--------- ---------- ----------
21-MAY-15 bbb            6564.5
21-MAY-15 aaa           289.725

SQL>
SQL> SELECT TRUNC(TIME) ,
  2    TEXT ,
  3    AVG(VALUE)
  4  FROM
  5    (SELECT TIME ,TEXT ,MAX(VALUE) VALUE FROM test GROUP BY TIME,TEXT
  6    ) t1
  7  GROUP BY TRUNC(TIME),
  8    TEXT;

TRUNC(TIM TEXT       AVG(VALUE)
--------- ---------- ----------
21-MAY-15 bbb            6564.5
21-MAY-15 aaa           289.725

SQL>
SQL>
SQL> SELECT TRUNC(TIME) ,
  2    TEXT ,
  3    AVG(VALUE)
  4  FROM
  5    (SELECT TIME ,
  6      TO_CHAR(TEXT) text ,
  7      MAX(VALUE) VALUE
  8    FROM test
  9    GROUP BY TIME,
 10      TEXT
 11    ) t1
 12  GROUP BY TRUNC(TIME),
 13    TEXT;

TRUNC(TIM TEXT       AVG(VALUE)
--------- ---------- ----------
21-MAY-15 bbb            6564.5
21-MAY-15 aaa           289.725

SQL>

Regards,
Lalit

Report message to a moderator

Re: does any one use oracle v12? [message #637651 is a reply to message #637648]

Thu, 21 May 2015 06:50

LargeHadronCollider
Messages: 7
Registered: May 2015
Location: Belarus

Junior Member

Lalit Kumar B wrote on Thu, 21 May 2015 14:30

No, it is not a bug. Different queries, different output.

All the queries would result the same output. Your posted output is not the output of Query2, but the output of the subquery of Query2.

Understand that, GROUP BY TRUNC(TIME) and GROUP BY TIME are different. TRUNC on DATE truncates the time portion, thus in your sample data, after truncating the time portion all the 4 rows have same date. Thus, average is done on the aggregate.

yes queries is different, but output must be same, 2 record only

quey2 use subselect, and yes fist subselect use GROUP BY TIME, but level-up select use GROUP BY TRUNC(TIME) (by days)!!! and output must have 2 records, not 4!!!
it is totally simplified select for ???bug??? reproducing..

Report message to a moderator

Re: does any one use oracle v12? [message #637652 is a reply to message #637648]

Thu, 21 May 2015 06:51

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Lalit Kumar B wrote on Thu, 21 May 2015 12:30

Your posted output is not the output of Query2, but the output of the subquery of Query2.

You assume that why?

@LargeHadronCollider - what is your exact version?

Report message to a moderator

Re: does any one use oracle v12? [message #637653 is a reply to message #637652]

Thu, 21 May 2015 06:56

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

cookiemonster wrote on Thu, 21 May 2015 17:21

Lalit Kumar B wrote on Thu, 21 May 2015 12:30
Your posted output is not the output of Query2, but the output of the subquery of Query2.

You assume that why?

Wrong wording. I mean the output shown for Query2 could be the output of the subquery but no the actual query. Anyway, waiting for OP's version.

Report message to a moderator

Re: does any one use oracle v12? [message #637654 is a reply to message #637651]

Thu, 21 May 2015 06:57

LargeHadronCollider
Messages: 7
Registered: May 2015
Location: Belarus

Junior Member

in any case next following queries must have same outputs

//Query2 (wrong result)
select trunc(TIME)
      ,TEXT
      ,avg(VALUE)        
from (select TIME        
            ,TEXT
            ,max(VALUE) VALUE     
      from SCOTT.test
      group by TIME,TEXT) t1
group by trunc(TIME),TEXT;

//Query3 (result is ok)
select trunc(TIME)
      ,TEXT
      ,avg(VALUE)        
from (select TIME        
            ,to_char(TEXT) text
            ,max(VALUE) VALUE     
      from SCOTT.test
      group by TIME,TEXT) t1
group by trunc(TIME),TEXT;

yes???

but is not!

for first 4 records (second grouping does not work), for second 2 record

Report message to a moderator

Re: does any one use oracle v12? [message #637655 is a reply to message #637647]

Thu, 21 May 2015 06:58

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I get the same anomalous result as you. If you look at the exec plans, you can see that te query2 is doing only one aggregation:

orclz>
orclz> set autot on exp
orclz>
orclz> --query 2
orclz> select trunc(TIME)
  2        ,TEXT
  3        ,avg(VALUE)
  4  from (select TIME
  5              ,TEXT
  6              ,max(VALUE) VALUE
  7        from SCOTT.test
  8        group by TIME,TEXT) t1
  9  group by trunc(TIME),TEXT;

TRUNC(TIME)         TEXT       AVG(VALUE)
------------------- ---------- ----------
2015-05-21:00:00:00 aaa             123.2
2015-05-21:00:00:00 bbb               784
2015-05-21:00:00:00 aaa            456.25
2015-05-21:00:00:00 bbb             12345


Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   136 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |     4 |   136 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST |     4 |   136 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=4)

orclz>
orclz> --query 3
orclz> select trunc(TIME)
  2        ,TEXT
  3        ,avg(VALUE)
  4  from (select TIME
  5              ,to_char(TEXT) text
  6              ,max(VALUE) VALUE
  7        from SCOTT.test
  8        group by TIME,TEXT) t1
  9  group by trunc(TIME),TEXT;

TRUNC(TIME)         TEXT       AVG(VALUE)
------------------- ---------- ----------
2015-05-21:00:00:00 bbb            6564.5
2015-05-21:00:00:00 aaa           289.725


Execution Plan
----------------------------------------------------------
Plan hash value: 1804376136

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     4 |   136 |     5  (40)| 00:00:01 |
|   1 |  HASH GROUP BY       |      |     4 |   136 |     5  (40)| 00:00:01 |
|   2 |   VIEW               |      |     4 |   136 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     4 |   136 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TEST |     4 |   136 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=4)

orclz>

Same result if I prevent the view merging. It materializes te subqyery, but still does not do the outer aggregation:

orclz> ed
Wrote file afiedt.buf

  1  select /*+ no_merge(t1) */ trunc(TIME)
  2        ,TEXT
  3        ,avg(VALUE)
  4  from (select TIME
  5              ,TEXT
  6              ,max(VALUE) VALUE
  7        from SCOTT.test
  8        group by TIME,TEXT) t1
  9* group by trunc(TIME),TEXT
orclz> /

TRUNC(TIME)         TEXT       AVG(VALUE)
------------------- ---------- ----------
2015-05-21:00:00:00 aaa             123.2
2015-05-21:00:00:00 bbb               784
2015-05-21:00:00:00 aaa            456.25
2015-05-21:00:00:00 bbb             12345


Execution Plan
----------------------------------------------------------
Plan hash value: 887011165

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     4 |   136 |     4  (25)| 00:00:01 |
|   1 |  VIEW               |      |     4 |   136 |     4  (25)| 00:00:01 |
|   2 |   HASH GROUP BY     |      |     4 |   136 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST |     4 |   136 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=4)

orclz>

This is 12.1.0.2 on Windows, no PSU applied.

[Updated on: Thu, 21 May 2015 06:59]

Report message to a moderator

Re: does any one use oracle v12? [message #637656 is a reply to message #637654]

Thu, 21 May 2015 07:00

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

@OP, Your exact version please?

Post the output of:

select * from v$version;

[Updated on: Thu, 21 May 2015 07:00]

Report message to a moderator

Re: does any one use oracle v12? [message #637657 is a reply to message #637656]

Thu, 21 May 2015 07:04

LargeHadronCollider
Messages: 7
Registered: May 2015
Location: Belarus

Junior Member

week ago downloaded..

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production	0
PL/SQL Release 12.1.0.2.0 - Production	0
CORE	12.1.0.2.0	Production	0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production	0
NLSRTL Version 12.1.0.2.0 - Production	0

[Updated on: Thu, 21 May 2015 07:04]

Report message to a moderator

Re: does any one use oracle v12? [message #637658 is a reply to message #637655]

Thu, 21 May 2015 07:06

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Seems like a bug, I get same output for both the queries. The same execution plan is used for both the queries.

SQL> SELECT banner FROM v$version WHERE ROWNUM = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL>
SQL> SET autot ON exp
SQL>
SQL> select trunc(TIME)
  2        ,TEXT
  3        ,avg(VALUE)
  4  from (select TIME
  5              ,TEXT
  6              ,max(VALUE) VALUE
  7        from test
  8        group by TIME,TEXT) t1
  9  group by trunc(TIME),TEXT;

TRUNC(TIM TEXT       AVG(VALUE)
--------- ---------- ----------
21-MAY-15 bbb            6564.5
21-MAY-15 aaa           289.725


Execution Plan
----------------------------------------------------------
Plan hash value: 1804376136

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     4 |   136 |     3   (0)| 00:00:01 |
|   1 |  HASH GROUP BY       |      |     4 |   136 |     3   (0)| 00:00:01 |
|   2 |   VIEW               |      |     4 |   136 |     3   (0)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     4 |   136 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TEST |     4 |   136 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL>
SQL> select trunc(TIME)
  2        ,TEXT
  3        ,avg(VALUE)
  4  from (select TIME
  5              ,to_char(TEXT) text
  6              ,max(VALUE) VALUE
  7        from test
  8        group by TIME,TEXT) t1
  9  group by trunc(TIME),TEXT;

TRUNC(TIM TEXT       AVG(VALUE)
--------- ---------- ----------
21-MAY-15 bbb            6564.5
21-MAY-15 aaa           289.725


Execution Plan
----------------------------------------------------------
Plan hash value: 1804376136

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     4 |   136 |     3   (0)| 00:00:01 |
|   1 |  HASH GROUP BY       |      |     4 |   136 |     3   (0)| 00:00:01 |
|   2 |   VIEW               |      |     4 |   136 |     3   (0)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     4 |   136 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TEST |     4 |   136 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL>

Report message to a moderator

Re: does any one use oracle v12? [message #637660 is a reply to message #637657]

Thu, 21 May 2015 07:06

LargeHadronCollider
Messages: 7
Registered: May 2015
Location: Belarus

Junior Member

John Watson, thanks for comfirm!

Report message to a moderator

Re: does any one use oracle v12? [message #637661 is a reply to message #637660]

Thu, 21 May 2015 07:10

LargeHadronCollider
Messages: 7
Registered: May 2015
Location: Belarus

Junior Member

Lalit Kumar B, thanks too

so, now need to tell to oracle...

Report message to a moderator

Re: does any one use oracle v12? [message #637662 is a reply to message #637661]

Thu, 21 May 2015 07:18

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Better apply the April PSU first, it miht be fixed already.

Just for completeness:

orclz> alter session set optimizer_features_enable='12.1.0.1';

Session altered.

orclz>  select trunc(TIME)
  2         ,TEXT
  3         ,avg(VALUE)
  4   from (select TIME
  5               ,TEXT
  6               ,max(VALUE) VALUE
  7         from SCOTT.test
  8         group by TIME,TEXT) t1
  9   group by trunc(TIME),TEXT;

TRUNC(TIME)         TEXT       AVG(VALUE)
------------------- ---------- ----------
2015-05-21:00:00:00 bbb            6564.5
2015-05-21:00:00:00 aaa           289.725

orclz> alter session set optimizer_features_enable='12.1.0.2';

Session altered.

orclz>   select trunc(TIME)
  2        ,TEXT
  3        ,avg(VALUE)
  4  from (select TIME
  5              ,TEXT
  6              ,max(VALUE) VALUE
  7        from SCOTT.test
  8        group by TIME,TEXT) t1
  9  group by trunc(TIME),TEXT;

TRUNC(TIME)         TEXT       AVG(VALUE)
------------------- ---------- ----------
2015-05-21:00:00:00 aaa             123.2
2015-05-21:00:00:00 bbb               784
2015-05-21:00:00:00 aaa            456.25
2015-05-21:00:00:00 bbb             12345

orclz>

Report message to a moderator

Re: does any one use oracle v12? [message #637664 is a reply to message #637662]

Thu, 21 May 2015 07:26

LargeHadronCollider
Messages: 7
Registered: May 2015
Location: Belarus

Junior Member

John Watson wrote on Thu, 21 May 2015 15:18

orclz> alter session set optimizer_features_enable='12.1.0.1';

it funny and it works...

thanks

Report message to a moderator

Previous Topic:	Error using INTO clause in execute immediate
Next Topic:	Error in execution of Trigger

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 04:55:50 CDT 2024