Advertisement
ExaGridDba

Incorrect results 12c global index

May 30th, 2016
378
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.56 KB | None | 0 0
  1. Incorrect results from Oracle 12c.
  2. ==================================
  3.  
  4. This is a simpler case than found in
  5. https://antognini.ch/2016/04/wrong-results-involving-index-full-scan-minmax-in-12-1-0-2/
  6.  
  7. [oracle@stormking cdb12102 partition]$ sqlplus /nolog @ conn.u.sql
  8.  
  9. SQL*Plus: Release 12.1.0.2.0 Production on Mon May 30 13:22:18 2016
  10.  
  11. Copyright (c) 1982, 2014, Oracle. All rights reserved.
  12.  
  13. Connected.
  14. SQL> host cat results2.sql
  15. drop table t ;
  16. create table t (
  17. p number,
  18. i date
  19. )
  20. partition by list (p) (
  21. partition p1 values (1),
  22. partition p2 values (2),
  23. partition p3 values (3),
  24. partition p4 values (4),
  25. partition p5 values (5),
  26. partition p6 values (6),
  27. partition p7 values (7)
  28. );
  29.  
  30. insert into t ( p, i)
  31. select mod(rownum,7)+1,
  32. trunc(sysdate+mod(rownum,7))
  33. from dual
  34. connect by level <= 365;
  35.  
  36. select p, min(i)
  37. from t
  38. group by p;
  39.  
  40. select min(i) right from t where p = 3;
  41.  
  42. create index i on t (i);
  43.  
  44. select min(i) wrong from t where p = 3;
  45.  
  46. alter session set "_fix_control" = '16346018:off';
  47. select min(i) from t where p = 3;
  48.  
  49. SQL> @ results2.sql
  50.  
  51. Table dropped.
  52.  
  53.  
  54. Table created.
  55.  
  56.  
  57. 365 rows created.
  58.  
  59.  
  60. P MIN(I)
  61. ---------- ----------
  62. 1 2016-05-30
  63. 2 2016-05-31
  64. 3 2016-06-01
  65. 4 2016-06-02
  66. 5 2016-06-03
  67. 6 2016-06-04
  68. 7 2016-06-05
  69.  
  70. 7 rows selected.
  71.  
  72.  
  73. RIGHT
  74. ----------
  75. 2016-06-01
  76.  
  77.  
  78. Index created.
  79.  
  80.  
  81. WRONG
  82. ----------
  83. 2016-05-30
  84.  
  85.  
  86. Session altered.
  87.  
  88.  
  89. MIN(I)
  90. ----------
  91. 2016-06-01
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement