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:17:16 UTC
[jira] [Created] (TRAFODION-527) LP Bug: 1354619 - select on hive
tables does not return all the rows
Alice Chen created TRAFODION-527:
------------------------------------
Summary: LP Bug: 1354619 - select on hive tables does not return all the rows
Key: TRAFODION-527
URL: https://issues.apache.org/jira/browse/TRAFODION-527
Project: Apache Trafodion
Issue Type: Bug
Components: sql-exe
Reporter: Apache Trafodion
Assignee: Apache Trafodion
Priority: Blocker
Fix For: 0.9 (pre-incubation)
select on hive tables does not retun the the correct number of rows. This issue happens with select count(*) from hive table amd also with select col1, coln from hive table.
We saw the issue on two clusters so far . the oss poc cluster and lava-27 cluster.
here is an example:
-----------------------------------
>>explain options 'f' select count(*) from backlog_hdr_traf ;
..
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
4 . 5 root 1.00E+000
3 . 4 sort_partial_aggr_ro 1.00E+000
2 . 3 esp_exchange 1:8(hash2) 1.00E+000
1 . 2 sort_partial_aggr_le 1.00E+000
. . 1 hive_scan BACKLOG_HDR_TRAF 7.06E+006
--- SQL operation complete.
>>select count(*) from backlog_hdr_traf ;
(EXPR)
--------------------
7112675
--- 1 row(s) selected.
Start Time 2014/08/08 23:39:35.705985
End Time 2014/08/08 23:39:49.291769
Elapsed Time 00:00:13.585784
Compile Time 00:00:00.438265
Execution Time 00:00:13.147418
Table Name Records Records Hdfs Hdfs I/O Hdfs Access
Accessed Used I/Os Bytes Time(usec)
HIVE.BACKLOG_HDR_TRAF
7112675 7112675 132 8383405562 85829028
--- SQL operation complete.
>>cqd attempt_esp_parallelism 'off' ;
--- SQL operation complete.
>>fc select
>>select count(*) from backlog_hdr_traf ;
..
(EXPR)
--------------------
3395185
--- 1 row(s) selected.
Start Time 2014/08/08 23:41:24.112849
End Time 2014/08/08 23:41:51.491763
Elapsed Time 00:00:27.378914
Compile Time 00:00:00.396360
Execution Time 00:00:26.982448
Table Name Records Records Hdfs Hdfs I/O Hdfs Access
Accessed Used I/Os Bytes Time(usec)
HIVE.BACKLOG_HDR_TRAF
3395185 3395185 65 4088339963 26011247
--- SQL operation complete.
>>explain options 'f' select count(*) from backlog_hdr_traf ;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
2 . 3 root 1.00E+000
1 . 2 sort_scalar_aggr 1.00E+000
. . 1 hive_scan BACKLOG_HDR_TRAF 7.06E+006
--- SQL operation complete.
>>showplan option 't' select count(*) from backlog_hdr_traf ;
MASTER Executor fragment
========================
Fragment ID: 0, Length: 9744
Contents of EX_ROOT [3]:
------------------------
For ComTdb :
Class Version = 1, Class Size = 512
InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
queueResizeLimit = 9, queueResizeFactor = 4
queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0
estimatedRowUsed = 0, estimatedRowsAccessed = 0, expressionMode = 0
Flag = 0000000010101001
For ComTdbRoot :
FirstNRows = -1, baseTablenamePosition = -1
queryType_ = 2, planVersion_ = 2600
rtFlags1_ = 00100000001100000000101000000000
rtFlags2_ = 00000000000000000000000001001000
rtFlags3_ = 0000000000000000
rtFlags4_ = 00000000000000000000011000000000
rtFlags5_ = 00000000000000000000000000000000
queryType_ = 2
inputVarsSize_ = 0
# of Expressions = 3
Expression: inputExpr_ is NULL
Expression: outputExpr_ is not NULL
Expression: pkeyExpr_ is NULL
Contents of EX_SORT_GRBY [2]:
-----------------------------
For ComTdb :
Class Version = 1, Class Size = 248
InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
queueResizeLimit = 9, queueResizeFactor = 4
queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 5, bufferSize = 5120
estimatedRowUsed = 1, estimatedRowsAccessed = 0, expressionMode = 0
Flag = 0000000000001001
For ComTdbSortGrby :
Flags = 0000000000000000, recLen = 8
# of Expressions = 4
Expression: aggrExpr_ is not NULL
Expression: grbyExpr_ is NULL
Expression: moveExpr_ is NULL
Expression: havingExpr_ is NULL
Contents of EX_HDFS_SCAN [1]:
-----------------------------
For ComTdb :
Class Version = 1, Class Size = 344
InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
queueResizeLimit = 9, queueResizeFactor = 4
queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 32840
estimatedRowUsed = 0, estimatedRowsAccessed = 0, expressionMode = 0
Flag = 0000000000001001
For ComTdbHdfsScan :
tableName_ = HIVE.BACKLOG_HDR_TRAF
hostName_ = d9t0076c.houston.hp.com, port_ = 8020
recordDelimiter_ = 10, columnDelimiter_ = 124
hdfsBufSize_ = 67108864, rangeTailIOSize_ = 16384, hdfsSqlMaxRecLen_ = 4
tuppIndex_ = 3, workAtpIndex_ = 3
asciiTuppIndex_ = 4, asciiRowLen_ = 0
moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 4
convertSkipListSize_ = 320, convertSkipList_ = 0
outputRowLength_ = 4
Flag = 0000000000011110
Number of ranges to scan: 5
Number of esps to scan: 1
Esp# Range# StartOffset BytesRead FileName
====== ====== ============ ============ ==============================
0 0 0 0 backlog_hdr_traf/part-m-00000
0 1 0 1733114767 backlog_hdr_traf/part-m-00001
0 2 0 6276819220 backlog_hdr_traf/part-m-00002
0 3 0 373281377 backlog_hdr_traf/part-m-00003
0 4 0 75510 backlog_hdr_traf/part-m-00004
Summary of bytes read per ESP (8383290874 = 100 percent):
ESP 0 reads 8383290874 bytes ( 100 percent of avg)
# of Expressions = 4
Expression: selectExpr_ is NULL
Expression: moveExpr_ is NULL
Expression: convertExpr_ is NULL
Expression: moveColsConvertExpr_ is NULL
--- SQL operation complete.
Start Time 2014/08/08 23:44:37.301892
End Time 2014/08/08 23:44:42.067258
Elapsed Time 00:00:04.765366
Compile Time 00:00:00.001900
Execution Time 00:00:04.763392
--- SQL operation complete.
>>cqd attempt_esp_parallelism 'off' ;
--- SQL operation complete.
>>fc
>>cqd attempt_esp_parallelism 'off' ;
.. reset
>>cqd attempt_esp_parallelism eset' ;
.. ir
>>cqd attempt_esp_parallelism reset' ;
..
*** ERROR[15005] Unmatched quote in input (unterminated string):
cqd attempt_esp_parallelism reset' ;
^ (34 characters from start of SQL statement)
>>fc
>>cqd attempt_esp_parallelism reset' ;
.. d
>>cqd attempt_esp_parallelism reset ;
..
--- SQL operation complete.
>>fc showplan ;
>>showplan option 't' select count(*) from backlog_hdr_traf ;
..
MASTER Executor fragment
========================
Fragment ID: 0, Length: 8424
Contents of EX_ROOT [5]:
------------------------
For ComTdb :
Class Version = 1, Class Size = 512
InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
queueResizeLimit = 9, queueResizeFactor = 4
queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0
estimatedRowUsed = 0, estimatedRowsAccessed = 0, expressionMode = 0
Flag = 0000000010101001
For ComTdbRoot :
FirstNRows = -1, baseTablenamePosition = -1
queryType_ = 2, planVersion_ = 2600
rtFlags1_ = 00100000001100000000101000000000
rtFlags2_ = 00000000000000000000000001001000
rtFlags3_ = 0000000000000000
rtFlags4_ = 00000000000000000000011000000000
rtFlags5_ = 00000000000000000000000000000000
queryType_ = 2
inputVarsSize_ = 0
# of Expressions = 3
Expression: inputExpr_ is NULL
Expression: outputExpr_ is not NULL
Expression: pkeyExpr_ is NULL
Contents of EX_SORT_GRBY [4]:
-----------------------------
For ComTdb :
Class Version = 1, Class Size = 248
InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
queueResizeLimit = 9, queueResizeFactor = 4
queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 5, bufferSize = 5120
estimatedRowUsed = 1, estimatedRowsAccessed = 0, expressionMode = 0
Flag = 0000000000001001
For ComTdbSortGrby :
Flags = 0000000000000000, recLen = 8
# of Expressions = 4
Expression: aggrExpr_ is not NULL
Expression: grbyExpr_ is NULL
Expression: moveExpr_ is NULL
Expression: havingExpr_ is NULL
Contents of EX_SPLIT_TOP [3]:
-----------------------------
For ComTdb :
Class Version = 1, Class Size = 280
InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
queueResizeLimit = 9, queueResizeFactor = 4
queueSizeDown = 2, queueSizeUp = 2048, numBuffers = 4, bufferSize = 1024
estimatedRowUsed = 1, estimatedRowsAccessed = 0, expressionMode = 0
Flag = 0000000000001001
For ComTdbSplitTop :
Flags = 0000000000001001
splitTopFlags_ = 0000000000000000
bottomNumParts_ = 8
Exchange uses SeaMonster: no
# of Expressions = 2
Expression: childInputPartFunction_ is NULL
Expression: mergeKeyExpr_ is NULL
Contents of EX_SEND_TOP [3]:
----------------------------
For ComTdb :
Class Version = 1, Class Size = 296
InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
queueResizeLimit = 9, queueResizeFactor = 4
queueSizeDown = 4, queueSizeUp = 128, numBuffers = 0, bufferSize = 0
estimatedRowUsed = 1, estimatedRowsAccessed = 0, expressionMode = 0
Flag = 0000000000001001
For ComTdbSendTop :
childFragId_ = 2, sendTopFlags_ = 0000000000100000
downRecordLength_ = 0, upRecordLength_ = 16
sendBufferSize_ = 144, recvBufferSize_ = 12500
Exchange uses SeaMonster: no
# of Expressions = 1
Expression: moveInputValues_ is NULL
ESP fragment
===========
Fragment ID: 2, Parent ID: 0, Length: 11544
Contents of EX_SPLIT_BOTTOM [3]:
--------------------------------
For ComTdb :
Class Version = 1, Class Size = 280
InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
queueResizeLimit = 9, queueResizeFactor = 4
queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0
estimatedRowUsed = 1, estimatedRowsAccessed = 0, expressionMode = 0
Flag = 0000000010101001
For ComTdbSplitBottom :
splitBottomFlags_ = 0000000000000100
topNumESPs = 1, bottomNumESPs = 8
partInputDataLen = 8, partInputATPIndex = 2, combineRequests = 1
Query uses SeaMonster: no
Exchange uses SeaMonster: no
# of Expressions = 1
Expression: partFunction_ is NULL
Contents of EX_SORT_GRBY [2]:
-----------------------------
For ComTdb :
Class Version = 1, Class Size = 248
InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
queueResizeLimit = 9, queueResizeFactor = 4
queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 5, bufferSize = 5120
estimatedRowUsed = 1, estimatedRowsAccessed = 0, expressionMode = 0
Flag = 0000000000001001
For ComTdbSortGrby :
Flags = 0000000000000000, recLen = 8
# of Expressions = 4
Expression: aggrExpr_ is not NULL
Expression: grbyExpr_ is NULL
Expression: moveExpr_ is NULL
Expression: havingExpr_ is NULL
Contents of EX_HDFS_SCAN [1]:
-----------------------------
For ComTdb :
Class Version = 1, Class Size = 344
InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
queueResizeLimit = 9, queueResizeFactor = 4
queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 32840
estimatedRowUsed = 0, estimatedRowsAccessed = 0, expressionMode = 0
Flag = 0000000000001001
For ComTdbHdfsScan :
tableName_ = HIVE.BACKLOG_HDR_TRAF
hostName_ = d9t0076c.houston.hp.com, port_ = 8020
recordDelimiter_ = 10, columnDelimiter_ = 124
hdfsBufSize_ = 67108864, rangeTailIOSize_ = 16384, hdfsSqlMaxRecLen_ = 4
tuppIndex_ = 5, workAtpIndex_ = 3
asciiTuppIndex_ = 4, asciiRowLen_ = 0
moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 4
convertSkipListSize_ = 320, convertSkipList_ = 0
outputRowLength_ = 4
Flag = 0000000000011110
Number of ranges to scan: 12
Number of esps to scan: 8
Esp# Range# StartOffset BytesRead FileName
====== ====== ============ ============ ==============================
0 0 0 0 backlog_hdr_traf/part-m-00000
0 1 0 1047911360 backlog_hdr_traf/part-m-00001 (split_e)
1 2 1047911360 685203407 backlog_hdr_traf/part-m-00001 (split_b)
1 3 0 362707953 backlog_hdr_traf/part-m-00002 (split_e)
2 4 362707953 1047911360 backlog_hdr_traf/part-m-00002 (split_b/e)
3 5 1410619313 1047911360 backlog_hdr_traf/part-m-00002 (split_b/e)
4 6 2458530673 1047911360 backlog_hdr_traf/part-m-00002 (split_b/e)
5 7 3506442033 1047911360 backlog_hdr_traf/part-m-00002 (split_b/e)
6 8 4554353393 1047911360 backlog_hdr_traf/part-m-00002 (split_b/e)
7 9 5602264753 674554467 backlog_hdr_traf/part-m-00002 (split_b)
7 10 0 373281377 backlog_hdr_traf/part-m-00003
7 11 0 75510 backlog_hdr_traf/part-m-00004
Summary of bytes read per ESP (8383290874 = 100 percent):
ESP 0 reads 1047911360 bytes ( 100 percent of avg)
ESP 1 reads 1047911360 bytes ( 100 percent of avg)
ESP 2 reads 1047911360 bytes ( 100 percent of avg)
ESP 3 reads 1047911360 bytes ( 100 percent of avg)
ESP 4 reads 1047911360 bytes ( 100 percent of avg)
ESP 5 reads 1047911360 bytes ( 100 percent of avg)
ESP 6 reads 1047911360 bytes ( 100 percent of avg)
ESP 7 reads 1047911354 bytes ( 99 percent of avg)
# of Expressions = 4
Expression: selectExpr_ is NULL
Expression: moveExpr_ is NULL
Expression: convertExpr_ is NULL
Expression: moveColsConvertExpr_ is NULL
Contents of EX_SEND_BOTTOM [3]:
-------------------------------
For ComTdb :
Class Version = 1, Class Size = 256
InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
queueResizeLimit = 9, queueResizeFactor = 4
queueSizeDown = 4, queueSizeUp = 128, numBuffers = 0, bufferSize = 0
estimatedRowUsed = 1, estimatedRowsAccessed = 0, expressionMode = 0
Flag = 0000000010001001
For ComTdbSendBottom :
sendBottomFlags_ = 0000000000000000
downRecordLength_ = 0, upRecordLength_ = 16
requestBufferSize_ = 144, replyBufferSize_ = 12500
numRequestBuffers_ = 2, numReplyBuffers_ = 4
Exchange uses SeaMonster: no
# of Expressions = 1
Expression: moveOutputValues_ is not NULL
--- SQL operation complete.
Start Time 2014/08/08 23:46:54.371753
End Time 2014/08/08 23:46:54.879475
Elapsed Time 00:00:00.507722
Compile Time 00:00:00.002453
Execution Time 00:00:00.505177
--- SQL operation complete.
Assigned to LaunchPad User khaled Bouaziz
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)