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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.1-incubating
• Fix Version/s: 2.1-incubating
• Component/s: sql-cmp
• Environment: All
• Assignee: Dave Birdsall
• Reporter: Dave Birdsall
• Created: 01/Feb/17 23:45
• Updated: 15/Sep/18 07:30
• Resolved: 02/Feb/17 20:06

Description

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.

Issue Links

• links to: GitHub Pull Request #943