Trafodion

Bug #1402031
Comment #3

Comment 3 for bug 1402031

Revision history for this message

Weishiun Tsai (wei-shiun-tsai) wrote on 2014-12-18:

This is seen on the v1217_0830 build again. I am changing this case to critical so the implementation of update stats can be looked at to ensure 100% row count accuracy regardless when it is run, and/or if we need to turn off USTAT_ESTIMATE_HBASE_ROW_COUNT in the upcoming release as the default value if we can't fix this problem in time.

>>set schema g_tpch2x;

--- SQL operation complete.
>>showstats for table CUSTOMER on every column;

Histogram data for Table TRAFODION.G_TPCH2X.CUSTOMER
Table ID: 206656109855119777

Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== =========== ===========================
1139902532 48 1100767 1100767 C_CUSTKEY
1139902527 62 1100767 1100767 C_NAME
1139902522 62 1100767 1100767 C_ADDRESS
1139902517 25 1100767 0 C_NATIONKEY
1139902512 62 1100767 1100767 C_PHONE
1139902507 36 1100767 6 C_ACCTBAL
1139902502 5 1100767 0 C_MKTSEGMENT
1139902497 62 1100767 0 C_COMMENT
1139902492 8 1100767 0 "_SALT_"
1139902537 1 1100767 1100767 "_SALT_", C_CUSTKEY

--- SQL operation complete.
>>showstats for table LINEITEM on every column;

Histogram data for Table TRAFODION.G_TPCH2X.LINEITEM
Table ID: 206656109855120177

Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== =========== ===========================
1309294189 35 12696621 2555377 L_ORDERKEY
1309294184 54 12696621 399594 L_PARTKEY
1309294179 48 12696621 20000 L_SUPPKEY
1309294174 7 12696621 7 L_LINENUMBER
1309294169 50 12696621 50 L_QUANTITY
1309294164 37 12696621 940465 L_EXTENDEDPRICE
1309294159 11 12696621 11 L_DISCOUNT
1309294154 9 12696621 9 L_TAX
1309294149 3 12696621 3 L_RETURNFLAG
1309294144 2 12696621 2 L_LINESTATUS
1309294139 50 12696621 2526 L_SHIPDATE
1309294134 50 12696621 2466 L_COMMITDATE
1309294129 50 12696621 2549 L_RECEIPTDATE
1309294124 4 12696621 4 L_SHIPINSTRUCT
1309294119 7 12696621 7 L_SHIPMODE
1309294114 62 12696621 7448679 L_COMMENT
1309294109 8 12696621 8 "_SALT_"
1309294194 1 12696621 12696621 "_SALT_", L_SHIPDATE, L_ORDERKEY, L_LINENUMBER

--- SQL operation complete.
>>showstats for table NATION on every column;

Histogram data for Table TRAFODION.G_TPCH2X.NATION
Table ID: 206656109855119583

--- SQL operation complete.
>>showstats for table ORDERS on every column;

Histogram data for Table TRAFODION.G_TPCH2X.ORDERS
Table ID: 206656109855120092

Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== =========== ===========================
1227080124 39 4580956 4580956 O_ORDERKEY
1227080119 58 4580956 192533 O_CUSTKEY
1227080114 3 4580956 3 O_ORDERSTATUS
1227080109 54 4580956 4040767 O_TOTALPRICE
1227080104 50 4580956 2406 O_ORDERDATE
1227080099 5 4580956 5 O_ORDERPRIORITY
1227080094 62 4580956 2000 O_CLERK
1227080089 1 4580956 1 O_SHIPPRIORITY
1227080084 62 4580956 4243990 O_COMMENT
1227080079 8 4580956 8 "_SALT_"
1227080129 1 4580956 4580956 "_SALT_", O_ORDERKEY

--- SQL operation complete.
>>showstats for table PART on every column;

Histogram data for Table TRAFODION.G_TPCH2X.PART
Table ID: 206656109855119862

Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== =========== ===========================
1153067086 48 1725243 1725243 P_PARTKEY
1153067081 62 1725243 1279978 P_NAME
1153067076 5 1725243 0 P_MFGR
1153067071 25 1725243 0 P_BRAND
1153067066 150 1725243 0 P_TYPE
1153067061 50 1725243 0 P_SIZE
1153067056 40 1725243 0 P_CONTAINER
1153067051 55 1725243 0 P_RETAILPRICE
1153067046 76 1725243 0 P_COMMENT
1153067041 8 1725243 0 "_SALT_"
1153067091 1 1725243 1725243 "_SALT_", P_PARTKEY

--- SQL operation complete.
>>showstats for table PARTSUPP on every column;

Histogram data for Table TRAFODION.G_TPCH2X.PARTSUPP
Table ID: 206656109855119976

Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== =========== ===========================
1187049320 48 2974634 0 PS_PARTKEY
1187049315 48 2974634 0 PS_SUPPKEY
1187049310 48 2974634 0 PS_AVAILQTY
1187049305 48 2974634 0 PS_SUPPLYCOST
1187049300 62 2974634 2206974 PS_COMMENT
1187049295 8 2974634 0 "_SALT_"
1187049325 1 2974634 2974634 "_SALT_", PS_PARTKEY, PS_SUPPKEY

--- SQL operation complete.
>>showstats for table REGION on every column;

Histogram data for Table TRAFODION.G_TPCH2X.REGION
Table ID: 206656109855119462

--- SQL operation complete.
>>showstats for table SUPPLIER on every column;

Histogram data for Table TRAFODION.G_TPCH2X.SUPPLIER
Table ID: 206656109855119691

Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== =========== ===========================
1124657212 48 64035 64035 S_SUPPKEY
1124657207 62 64035 64035 S_NAME
1124657202 62 64035 64035 S_ADDRESS
1124657197 25 64035 0 S_NATIONKEY
1124657192 62 64035 64035 S_PHONE
1124657187 56 64035 0 S_ACCTBAL
1124657182 62 64035 47479 S_COMMENT
1124657177 8 64035 0 "_SALT_"
1124657217 1 64035 64035 "_SALT_", S_SUPPKEY

--- SQL operation complete.

This is seen on the v1217_0830 build again.  I am changing this case to critical so the implementation of update stats can be looked at to ensure 100% row count accuracy regardless when it is run, and/or if we need to turn off USTAT_ESTIMATE_HBASE_ROW_COUNT in the upcoming release as the default value if we can't fix this problem in time.

>>set schema g_tpch2x;

--- SQL operation complete.
>>showstats for table CUSTOMER on every column;

Histogram data for Table TRAFODION.G_TPCH2X.CUSTOMER
Table ID: 206656109855119777

Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1139902532     48     1100767     1100767 C_CUSTKEY
1139902527     62     1100767     1100767 C_NAME
1139902522     62     1100767     1100767 C_ADDRESS
1139902517     25     1100767           0 C_NATIONKEY
1139902512     62     1100767     1100767 C_PHONE
1139902507     36     1100767           6 C_ACCTBAL
1139902502      5     1100767           0 C_MKTSEGMENT
1139902497     62     1100767           0 C_COMMENT
1139902492      8     1100767           0 "_SALT_"
1139902537      1     1100767     1100767 "_SALT_", C_CUSTKEY

--- SQL operation complete.
>>showstats for table LINEITEM on every column;

Histogram data for Table TRAFODION.G_TPCH2X.LINEITEM
Table ID: 206656109855120177

Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1309294189     35    12696621     2555377 L_ORDERKEY
1309294184     54    12696621      399594 L_PARTKEY
1309294179     48    12696621       20000 L_SUPPKEY
1309294174      7    12696621           7 L_LINENUMBER
1309294169     50    12696621          50 L_QUANTITY
1309294164     37    12696621      940465 L_EXTENDEDPRICE
1309294159     11    12696621          11 L_DISCOUNT
1309294154      9    12696621           9 L_TAX
1309294149      3    12696621           3 L_RETURNFLAG
1309294144      2    12696621           2 L_LINESTATUS
1309294139     50    12696621        2526 L_SHIPDATE
1309294134     50    12696621        2466 L_COMMITDATE
1309294129     50    12696621        2549 L_RECEIPTDATE
1309294124      4    12696621           4 L_SHIPINSTRUCT
1309294119      7    12696621           7 L_SHIPMODE
1309294114     62    12696621     7448679 L_COMMENT
1309294109      8    12696621           8 "_SALT_"
1309294194      1    12696621    12696621 "_SALT_", L_SHIPDATE, L_ORDERKEY, L_LINENUMBER

--- SQL operation complete.
>>showstats for table NATION on every column;

Histogram data for Table TRAFODION.G_TPCH2X.NATION
Table ID: 206656109855119583

Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1115960438     25          25          25 N_NATIONKEY
1115960433     25          25          25 N_NAME
1115960428      5          25           5 N_REGIONKEY
1115960423     25          25          25 N_COMMENT
1115960418      8          25           8 "_SALT_"
1115960443      1          25          25 "_SALT_", N_NATIONKEY

--- SQL operation complete.
>>showstats for table ORDERS on every column;

Histogram data for Table TRAFODION.G_TPCH2X.ORDERS
Table ID: 206656109855120092

Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1227080124     39     4580956     4580956 O_ORDERKEY
1227080119     58     4580956      192533 O_CUSTKEY
1227080114      3     4580956           3 O_ORDERSTATUS
1227080109     54     4580956     4040767 O_TOTALPRICE
1227080104     50     4580956        2406 O_ORDERDATE
1227080099      5     4580956           5 O_ORDERPRIORITY
1227080094     62     4580956        2000 O_CLERK
1227080089      1     4580956           1 O_SHIPPRIORITY
1227080084     62     4580956     4243990 O_COMMENT
1227080079      8     4580956           8 "_SALT_"
1227080129      1     4580956     4580956 "_SALT_", O_ORDERKEY

--- SQL operation complete.
>>showstats for table PART on every column;

Histogram data for Table TRAFODION.G_TPCH2X.PART
Table ID: 206656109855119862

Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1153067086     48     1725243     1725243 P_PARTKEY
1153067081     62     1725243     1279978 P_NAME
1153067076      5     1725243           0 P_MFGR
1153067071     25     1725243           0 P_BRAND
1153067066    150     1725243           0 P_TYPE
1153067061     50     1725243           0 P_SIZE
1153067056     40     1725243           0 P_CONTAINER
1153067051     55     1725243           0 P_RETAILPRICE
1153067046     76     1725243           0 P_COMMENT
1153067041      8     1725243           0 "_SALT_"
1153067091      1     1725243     1725243 "_SALT_", P_PARTKEY

--- SQL operation complete.
>>showstats for table PARTSUPP on every column;

Histogram data for Table TRAFODION.G_TPCH2X.PARTSUPP
Table ID: 206656109855119976

Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1187049320     48     2974634           0 PS_PARTKEY
1187049315     48     2974634           0 PS_SUPPKEY
1187049310     48     2974634           0 PS_AVAILQTY
1187049305     48     2974634           0 PS_SUPPLYCOST
1187049300     62     2974634     2206974 PS_COMMENT
1187049295      8     2974634           0 "_SALT_"
1187049325      1     2974634     2974634 "_SALT_", PS_PARTKEY, PS_SUPPKEY

--- SQL operation complete.
>>showstats for table REGION on every column;

Histogram data for Table TRAFODION.G_TPCH2X.REGION
Table ID: 206656109855119462

Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1106768700      5           5           5 R_REGIONKEY
1106768695      5           5           5 R_NAME
1106768690      5           5           5 R_COMMENT
1106768685      3           5           3 "_SALT_"
1106768705      1           5           5 "_SALT_", R_REGIONKEY

--- SQL operation complete.
>>showstats for table SUPPLIER on every column;

Histogram data for Table TRAFODION.G_TPCH2X.SUPPLIER
Table ID: 206656109855119691

Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1124657212     48       64035       64035 S_SUPPKEY
1124657207     62       64035       64035 S_NAME
1124657202     62       64035       64035 S_ADDRESS
1124657197     25       64035           0 S_NATIONKEY
1124657192     62       64035       64035 S_PHONE
1124657187     56       64035           0 S_ACCTBAL
1124657182     62       64035       47479 S_COMMENT
1124657177      8       64035           0 "_SALT_"
1124657217      1       64035       64035 "_SALT_", S_SUPPKEY

--- SQL operation complete.