[jira] [Created] (TRAFODION-2470) Warning 6004 on histograms for NUMERIC(38,12) column

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

David Wayne Birdsall created TRAFODION-2470:
-----------------------------------------------

             Summary: Warning 6004 on histograms for NUMERIC(38,12) column
                 Key: TRAFODION-2470
                 URL: https://issues.apache.org/jira/browse/TRAFODION-2470
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
    Affects Versions: 2.1-incubating
         Environment: All
            Reporter: David Wayne Birdsall
            Assignee: David Wayne Birdsall


The following sqlci session log demonstrates the problem:

>>drop table if exists T1;

--- SQL operation complete.
>>
>>CREATE TABLE T1
+>  (
+>    C1 LARGEINT NO DEFAULT NOT NULL NOT SERIALIZED
+>  , C2 LARGEINT NO DEFAULT NOT NULL NOT SERIALIZED
+>  , C3 NUMERIC(38, 12) DEFAULT NULL NOT SERIALIZED
+>  , C4 LARGEINT DEFAULT NULL NOT SERIALIZED
+>  , PRIMARY KEY (C2 ASC, C1 ASC)
+>  )
+>;

--- SQL operation complete.
>>
>>insert into T1 values
+>( 1,1, 1, 1), 
+>(1,2,9696239.280000000000, 3),
+>(2,3, 9696240.0, 4),
+>(2,5, 9698840.0, 6),
+>(3,1, 9996240.0, 7),
+>(3,2, 9999999.99, 8),
+>(3,3, 10000000.0, 9),
+>(4,1, 10229050.000000000000, 10);

--- 8 row(s) inserted.
>>
>>update statistics for table T1 on every column;

--- SQL operation complete.
>>
>>showstats for table T1 on C3 detail;

Detailed Histogram data for Table TRAFODION.SEABASE.T1
Table ID: 8707267549712621116

Hist ID:    333471856
Column(s):  C3
Total Rows: 8
Total UEC:  8
Low Value:  (1.000000000000)
High Value: (10229050.000000000000)
Intervals:  8

Number    Rowcount         UEC Boundary
====== =========== =========== ======================================
     0           0           0 (1.000000000000)
     1           1           1 (1.000000000000)
     2           1           1 (9696239.280000000000)
     3           1           1 (9696240.000000000000)
     4           1           1 (9698840.000000000000)
     5           1           1 (9996240.000000000000)
     6           1           1 (9999999.990000000000)
     7           1           1 (10000000.000000000000)
     8           1           1 (10229050.000000000000)


--- SQL operation complete.
>>
>>prepare S1 from select * from T1 
+> where C3 > 10229050;

*** WARNING[6004] The metadata table HISTOGRAM_INTERVALS for column TRAFODION.SEABASE.T1.C3 do not have intervals 6 and 7 in order. If you have manually modified the metadata table, then you should undo your changes using the CLEAR option in UPDATE STATISTICS and regenerate the statistics.

--- SQL command prepared.
>>

Note that intervals 6 and 7 in the histograms are in fact in the proper order. The problem seems to be that the character string representation of the value for interval 7 is 20 characters long.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)