You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:18:13 UTC
[jira] [Created] (TRAFODION-797) LP Bug: 1396793 - Query returned
wrong result due to wrong row counts in indexes
Alice Chen created TRAFODION-797:
------------------------------------
Summary: LP Bug: 1396793 - Query returned wrong result due to wrong row counts in indexes
Key: TRAFODION-797
URL: https://issues.apache.org/jira/browse/TRAFODION-797
Project: Apache Trafodion
Issue Type: Bug
Components: sql-exe
Reporter: Weishiun Tsai
Assignee: Apache Trafodion
Priority: Blocker
Fix For: 1.0 (pre-incubation)
The following query uses QA global tables from g_hcubedb. The tables were not rebuilt between the v1119_0830 build and the v1125_0830 build. The stats information show that the stats were created from the 1st build and remain the same in the 2nd build. However, the plan chosen in the v1119_0830 build was a parallel one and it returned 1000 as the result. The plan chosen in the v1125_0830 build is a serial one and it returns 0, which is a wrong result.
This is a data correctness issue and a regression, so it is marked as Critical.
------------------------------------------------------------------------------
Here is the query to reproduce this problem. It requires QA’s g_hcubedb tables.
prepare s13 from select count(*) from trafodion.g_hcubedb.T1,trafodion.g_hcubedb.T2,trafodion.g_hcubedb.T3,trafodion.g_hcubedb.T4,trafodion.g_hcubedb.T5,trafodion.g_hcubedb.T6,trafodion.g_hcubedb.T7,trafodion.g_hcubedb.T8,trafodion.g_hcubedb.T9,trafodion.g_hcubedb.CUBE1 where
trafodion.g_hcubedb.CUBE1.a=trafodion.g_hcubedb.T9.a and trafodion.g_hcubedb.CUBE1.b=trafodion.g_hcubedb.T8.b and trafodion.g_hcubedb.CUBE1.c=trafodion.g_hcubedb.T7.c and
trafodion.g_hcubedb.T9.b=trafodion.g_hcubedb.T1.b and trafodion.g_hcubedb.T9.c=trafodion.g_hcubedb.T2.c and
trafodion.g_hcubedb.T8.a=trafodion.g_hcubedb.T3.a and trafodion.g_hcubedb.T8.c=trafodion.g_hcubedb.T4.c and
trafodion.g_hcubedb.T7.a=trafodion.g_hcubedb.T5.a and trafodion.g_hcubedb.T7.b=trafodion.g_hcubedb.T6.a;
explain options 'f' s13;
execute s13;
------------------------------------------------------------------------------
Here is the row count information of the tables involved, showstats for CUBE1, and STATS_TIME from SB_HISTOGRAMS showing that the stats have not changed between the two builds.
>>set schema g_hcubedb;
--- SQL operation complete.
>>select count(*) from T1;
(EXPR)
--------------------
10
--- 1 row(s) selected.
>>select count(*) from T2;
(EXPR)
--------------------
10
--- 1 row(s) selected.
>>select count(*) from T3;
(EXPR)
--------------------
10
--- 1 row(s) selected.
>>select count(*) from T4;
(EXPR)
--------------------
10
--- 1 row(s) selected.
>>select count(*) from T5;
(EXPR)
--------------------
10
--- 1 row(s) selected.
>>select count(*) from T6;
(EXPR)
--------------------
100
--- 1 row(s) selected.
>>select count(*) from T7;
(EXPR)
--------------------
100
--- 1 row(s) selected.
>>select count(*) from T8;
(EXPR)
--------------------
1000
--- 1 row(s) selected.
>>select count(*) from T9;
(EXPR)
--------------------
10000
--- 1 row(s) selected.
>>select count(*) from CUBE1;
(EXPR)
--------------------
100000
--- 1 row(s) selected.
>>showstats for table CUBE1 on every column;
Histogram data for Table TRAFODION.G_HCUBEDB.CUBE1
Table ID: 53531328855223099
Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== =========== ===========================
1491884843 8 100000 8 "_SALT_"
1491884838 10 100000 10 A
1491884833 76 100000 100 B
1491884828 76 100000 100 C
1491884823 10 100000 10 D
1491884818 76 100000 100 E
1491884813 76 100000 100 F
1491884808 1 100000 1 TXT
1491884913 1 100000 100000 "_SALT_", A, B, C
1491884908 1 100000 100000 D, "_SALT_", A, B, C
1491884903 1 100000 8000 D, "_SALT_", A, B
1491884898 1 100000 80 D, "_SALT_", A
1491884893 1 100000 80 D, "_SALT_"
1491884888 1 100000 100000 E, "_SALT_", A, B, C
1491884883 1 100000 8000 E, "_SALT_", A, B
1491884878 1 100000 8000 E, "_SALT_", A
1491884873 1 100000 800 E, "_SALT_"
1491884868 1 100000 100000 F, "_SALT_", A, B, C
1491884863 1 100000 100000 F, "_SALT_", A, B
1491884858 1 100000 8000 F, "_SALT_", A
1491884853 1 100000 800 F, "_SALT_"
--- SQL operation complete.
>>select distinct(STATS_TIME) from SB_HISTOGRAMS;
STATS_TIME
-------------------
2014-11-20 06:57:44
2014-11-20 06:58:09
2014-11-20 06:58:16
2014-11-20 06:58:23
2014-11-20 06:58:30
2014-11-20 06:58:37
2014-11-20 06:58:43
2014-11-20 06:59:02
2014-11-20 06:59:10
2014-11-20 06:59:24
2014-11-20 06:59:36
2014-11-20 06:59:49
2014-11-20 07:00:06
2014-11-20 07:00:12
2014-11-20 07:00:19
2014-11-20 07:01:04
2014-11-20 07:01:45
2014-11-20 07:09:58
--- 18 row(s) selected.
------------------------------------------------------------------------------
Here is the execution output showing the in the v1125_0830 build, the query plan is a serial one, and it returns 0, which is a wrong result.
>>prepare s13 from select count(*) from trafodion.g_hcubedb.T1,trafodion.g_hcubedb.T2,trafodion.g_hcubedb.T3,trafodion.g_hcubedb.T4,trafodion.g_hcubedb.T5,trafodion.g_hcubedb.T6,trafodion.g_hcubedb.T7,trafodion.g_hcubedb.T8,trafodion.g_hcubedb.T9,trafodion.g_hcubedb.CUBE1 where
+> trafodion.g_hcubedb.CUBE1.a=trafodion.g_hcubedb.T9.a and trafodion.g_hcubedb.CUBE1.b=trafodion.g_hcubedb.T8.b and trafodion.g_hcubedb.CUBE1.c=trafodion.g_hcubedb.T7.c and
+> trafodion.g_hcubedb.T9.b=trafodion.g_hcubedb.T1.b and trafodion.g_hcubedb.T9.c=trafodion.g_hcubedb.T2.c and
+> trafodion.g_hcubedb.T8.a=trafodion.g_hcubedb.T3.a and trafodion.g_hcubedb.T8.c=trafodion.g_hcubedb.T4.c and
+> trafodion.g_hcubedb.T7.a=trafodion.g_hcubedb.T5.a and trafodion.g_hcubedb.T7.b=trafodion.g_hcubedb.T6.a;
--- SQL command prepared.
>>explain options 'f' s13;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
20 . 21 root 1.00E+000
19 . 20 sort_scalar_aggr 1.00E+000
18 1 19 hybrid_hash_join 1.00E+001
17 2 18 hybrid_hash_join 1.00E+001
16 3 17 hybrid_hash_join 1.00E+003
15 4 16 hybrid_hash_join 1.00E+002
14 5 15 hybrid_hash_join 1.00E+002
13 6 14 hybrid_hash_join 1.00E+003
12 7 13 hybrid_hash_join 1.00E+003
11 8 12 hybrid_hash_join 1.00E+002
10 9 11 hybrid_hash_join 1.00E+002
. . 10 trafodion_scan T9 1.00E+004
. . 9 trafodion_scan T1 1.00E+001
. . 8 trafodion_scan T2 1.00E+001
. . 7 trafodion_index_scan CUBE1 1.00E+005
. . 6 trafodion_scan T7 1.00E+002
. . 5 trafodion_scan T5 1.00E+001
. . 4 trafodion_scan T6 1.00E+002
. . 3 trafodion_scan T8 1.00E+003
. . 2 trafodion_scan T3 1.00E+001
. . 1 trafodion_scan T4 1.00E+001
--- SQL operation complete.
>>execute s13;
(EXPR)
--------------------
0
--- 1 row(s) selected.
------------------------------------------------------------------------------
Here is the execution output showing the in the v1119_0830 build, the query plan was a parallel one, and it returned the correct result 1000.
SQL>prepare s13 from select count(*) from trafodion.g_hcubedb.T1,trafodion.g_hcubedb.T2,trafodion.g_hcubedb.T3,trafodion.g_hcubedb.T4,trafodion.g_hcubedb.T5,trafodion.g_hcubedb.T6,trafodion.g_hcubedb.T7,trafodion.g_hcubedb.T8,trafodion.g_hcubedb.T9,trafodion.g_hcubedb.CUBE1 where
trafodion.g_hcubedb.CUBE1.a=trafodion.g_hcubedb.T9.a and trafodion.g_hcubedb.CUBE1.b=trafodion.g_hcubedb.T8.b and trafodion.g_hcubedb.CUBE1.c=trafodion.g_hcubedb.T7.c and
trafodion.g_hcubedb.T9.b=trafodion.g_hcubedb.T1.b and trafodion.g_hcubedb.T9.c=trafodion.g_hcubedb.T2.c and
trafodion.g_hcubedb.T8.a=trafodion.g_hcubedb.T3.a and trafodion.g_hcubedb.T8.c=trafodion.g_hcubedb.T4.c and
trafodion.g_hcubedb.T7.a=trafodion.g_hcubedb.T5.a and trafodion.g_hcubedb.T7.b=trafodion.g_hcubedb.T6.a;
--- SQL command prepared.
SQL>explain options 'f' s13;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
31 . 32 root 1.00E+000
30 . 31 sort_partial_aggr_ro 1.00E+000
29 . 30 esp_exchange 1:2(hash2) 1.00E+000
28 . 29 sort_partial_aggr_le 1.00E+000
27 2 28 hybrid_hash_join 1.00E+001
26 4 27 hybrid_hash_join 1.00E+001
25 6 26 hybrid_hash_join 1.00E+003
24 8 25 hybrid_hash_join 1.00E+002
23 10 24 hybrid_hash_join 1.00E+002
22 12 23 hybrid_hash_join 1.00E+003
21 14 22 hybrid_hash_join 1.00E+003
20 16 21 hybrid_hash_join 1.00E+002
19 18 20 hybrid_hash_join 1.00E+002
. . 19 trafodion_scan T9 1.00E+004
17 . 18 esp_exchange 2(rep-b):2(hash2) 1.00E+001
. . 17 trafodion_scan T1 1.00E+001
15 . 16 esp_exchange 2(rep-b):2(hash2) 1.00E+001
. . 15 trafodion_scan T2 1.00E+001
13 . 14 esp_exchange 2(hash2):2(hash2) 1.00E+005
. . 13 trafodion_scan CUBE1 1.00E+005
11 . 12 esp_exchange 2(rep-b):2(hash2) 1.00E+002
. . 11 trafodion_scan T7 1.00E+002
9 . 10 esp_exchange 2(rep-b):2(hash2) 1.00E+001
. . 9 trafodion_scan T5 1.00E+001
7 . 8 esp_exchange 2(rep-b):2(hash2) 1.00E+002
. . 7 trafodion_scan T6 1.00E+002
5 . 6 esp_exchange 2(rep-b):2(hash2) 1.00E+003
. . 5 trafodion_scan T8 1.00E+003
3 . 4 esp_exchange 2(rep-b):2(hash2) 1.00E+001
. . 3 trafodion_scan T3 1.00E+001
1 . 2 esp_exchange 2(rep-b):2(hash2) 1.00E+001
. . 1 trafodion_scan T4 1.00E+001
--- SQL operation complete.
SQL>execute s13;
(EXPR)
--------------------
1000
--- 1 row(s) selected.
Assigned to LaunchPad User Mike Hanlon
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)