[
https://issues.apache.org/jira/browse/DRILL-5115?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15733645#comment-15733645
]
Rahul Challapalli commented on DRILL-5115:
------------------------------------------
The same thing can be observed for the below query. Filter is removed and is
pushed down into the scan. However the scan fails to apply the filter.
Query
{code}
select num, let, `day`, l_orderkey from ld5 where num=2 and substring(let, 1,
1)='o' and `day` = 12 and `year`=2015 and `month` in (7,9) order by l_orderkey
limit 10;
{code}
Wong Plan :
{code}
00-00 Screen : rowType = RecordType(ANY num, ANY let, BIGINT day, ANY
l_orderkey): rowcount = 10.0, cumulative cost = {1261.0 rows, 8200.19083766033
cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 7786
00-01 Project(num=[$0], let=[$1], day=[$2], l_orderkey=[$3]) : rowType =
RecordType(ANY num, ANY let, BIGINT day, ANY l_orderkey): rowcount = 10.0,
cumulative cost = {1260.0 rows, 8199.19083766033 cpu, 0.0 io, 0.0 network, 0.0
memory}, id = 7785
00-02 SelectionVectorRemover : rowType = RecordType(ANY num, ANY let,
BIGINT day, ANY l_orderkey): rowcount = 10.0, cumulative cost = {1260.0 rows,
8199.19083766033 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 7784
00-03 Limit(fetch=[10]) : rowType = RecordType(ANY num, ANY let,
BIGINT day, ANY l_orderkey): rowcount = 10.0, cumulative cost = {1250.0 rows,
8189.19083766033 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 7783
00-04 SelectionVectorRemover : rowType = RecordType(ANY num, ANY
let, BIGINT day, ANY l_orderkey): rowcount = 310.0, cumulative cost = {1240.0
rows, 8149.19083766033 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 7782
00-05 TopN(limit=[10]) : rowType = RecordType(ANY num, ANY let,
BIGINT day, ANY l_orderkey): rowcount = 310.0, cumulative cost = {930.0 rows,
7839.19083766033 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 7781
00-06 Project(num=[$0], let=[SUBSTR($1, 1, 2)],
day=[EXTRACT(FLAG(DAY), $2)], l_orderkey=[$3]) : rowType = RecordType(ANY num,
ANY let, BIGINT day, ANY l_orderkey): rowcount = 310.0, cumulative cost =
{620.0 rows, 3720.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 7780
00-07 Scan(groupscan=[ParquetGroupScan
[entries=[ReadEntryWithPath
[path=maprfs:/drill/testdata/metadata_caching_pp/l_3level/2/one/2015-7-12/50.parquet],
ReadEntryWithPath
[path=maprfs:/drill/testdata/metadata_caching_pp/l_3level/2/one/2015-9-12/20.parquet]],
selectionRoot=maprfs:/drill/testdata/metadata_caching_pp/l_3level, numFiles=2,
usedMetadataFile=true,
cacheFileRoot=/drill/testdata/metadata_caching_pp/l_3level, columns=[`dir0`,
`dir1`, `dir2`, `l_orderkey`]]]) : rowType = RecordType(ANY dir0, ANY dir1, ANY
dir2, ANY l_orderkey): rowcount = 310.0, cumulative cost = {310.0 rows, 1240.0
cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 7779
{code}
Correct Plan :
{code}
00-00 Screen : rowType = RecordType(ANY num, ANY let, BIGINT day, ANY
l_orderkey): rowcount = 10.0, cumulative cost = {165.0 rows, 683.7098998795495
cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 24930
00-01 Project(num=[$0], let=[$1], day=[$2], l_orderkey=[$3]) : rowType =
RecordType(ANY num, ANY let, BIGINT day, ANY l_orderkey): rowcount = 10.0,
cumulative cost = {164.0 rows, 682.7098998795495 cpu, 0.0 io, 0.0 network, 0.0
memory}, id = 24929
00-02 SelectionVectorRemover : rowType = RecordType(ANY num, ANY let,
BIGINT day, ANY l_orderkey): rowcount = 10.0, cumulative cost = {164.0 rows,
682.7098998795495 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 24928
00-03 Limit(fetch=[10]) : rowType = RecordType(ANY num, ANY let,
BIGINT day, ANY l_orderkey): rowcount = 10.0, cumulative cost = {154.0 rows,
672.7098998795495 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 24927
00-04 SelectionVectorRemover : rowType = RecordType(ANY num, ANY
let, BIGINT day, ANY l_orderkey): rowcount = 1.0, cumulative cost = {144.0
rows, 632.7098998795495 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 24926
00-05 TopN(limit=[10]) : rowType = RecordType(ANY num, ANY let,
BIGINT day, ANY l_orderkey): rowcount = 1.0, cumulative cost = {143.0 rows,
631.7098998795495 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 24925
00-06 Project(num=[$0], let=[SUBSTR($1, 1, 2)],
day=[EXTRACT(FLAG(DAY), $2)], l_orderkey=[$3]) : rowType = RecordType(ANY num,
ANY let, BIGINT day, ANY l_orderkey): rowcount = 1.0, cumulative cost = {142.0
rows, 618.4221875000001 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 24924
00-07 SelectionVectorRemover : rowType = RecordType(ANY dir0,
ANY dir1, ANY dir2, ANY l_orderkey): rowcount = 1.0, cumulative cost = {141.0
rows, 610.4221875000001 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 24923
00-08 Filter(condition=[AND(=($0, 2), =(SUBSTRING(SUBSTR($1,
1, 2), 1, 1), 'o'), =(EXTRACT(FLAG(DAY), $2), 12), =(EXTRACT(FLAG(YEAR), $2),
2015), OR(=(EXTRACT(FLAG(MONTH), $2), 7), =(EXTRACT(FLAG(MONTH), $2), 9)))]) :
rowType = RecordType(ANY dir0, ANY dir1, ANY dir2, ANY l_orderkey): rowcount =
1.0, cumulative cost = {140.0 rows, 609.4221875000001 cpu, 0.0 io, 0.0 network,
0.0 memory}, id = 24922
00-09 Scan(groupscan=[ParquetGroupScan
[entries=[ReadEntryWithPath
[path=/drill/testdata/metadata_caching_pp/l_3level/2/one/2015-9-12/20.parquet],
ReadEntryWithPath
[path=/drill/testdata/metadata_caching_pp/l_3level/2/one/2015-7-12/50.parquet]],
selectionRoot=/drill/testdata/metadata_caching_pp/l_3level, numFiles=2,
usedMetadataFile=true,
cacheFileRoot=/drill/testdata/metadata_caching_pp/l_3level/2/one,
columns=[`dir0`, `dir1`, `dir2`, `l_orderkey`]]]) : rowType = RecordType(ANY
dir0, ANY dir1, ANY dir2, ANY l_orderkey): rowcount = 70.0, cumulative cost =
{70.0 rows, 280.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 24921
{code}
> Metadata Cache Pruning randomly returns wrong results at higher concurrencies
> -----------------------------------------------------------------------------
>
> Key: DRILL-5115
> URL: https://issues.apache.org/jira/browse/DRILL-5115
> Project: Apache Drill
> Issue Type: Bug
> Components: Metadata, Query Planning & Optimization
> Affects Versions: 1.8.0, 1.9.0, 1.10
> Reporter: Rahul Challapalli
> Attachments: correctdata_profile.txt, drillbit1.log, drillbit2.log,
> l_3level.tgz, wrongdata_profile.txt
>
>
> git.commit.id.abbrev=4312d65
> When multiple queries are updating the metadata cache simultaneously the
> below query randomly returns wrong results.
> A single run includes executing a suite of 90 tests at a concurrency of 50. I
> encountered a wrong data scenario in my 10th run.
>
> Query :
> {code}
> select l_orderkey from l_3level where dir0=1 and ((dir1='one' and dir2 IN
> ('2015-7-12', '2015-7-13')) or (dir1='two' and dir2='2015-8-12'))
> {code}
> Wrong Result Plan (based on the profile) :
> {code}
> 00-00 Screen : rowType = RecordType(ANY l_orderkey): rowcount = 310.0,
> cumulative cost = {341.0 rows, 341.0 cpu, 0.0 io, 0.0 network, 0.0 memory},
> id = 205721
> 00-01 Project(l_orderkey=[$0]) : rowType = RecordType(ANY l_orderkey):
> rowcount = 310.0, cumulative cost = {310.0 rows, 310.0 cpu, 0.0 io, 0.0
> network, 0.0 memory}, id = 205720
> 00-02 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
> [path=maprfs:/drill/testdata/metadata_caching_pp/l_3level/1/one/2015-7-13/20.parquet],
> ReadEntryWithPath
> [path=maprfs:/drill/testdata/metadata_caching_pp/l_3level/1/two/2015-8-12/30.parquet],
> ReadEntryWithPath
> [path=maprfs:/drill/testdata/metadata_caching_pp/l_3level/1/one/2015-7-12/10.parquet]],
> selectionRoot=maprfs:/drill/testdata/metadata_caching_pp/l_3level,
> numFiles=3, usedMetadataFile=true,
> cacheFileRoot=/drill/testdata/metadata_caching_pp/l_3level,
> columns=[`l_orderkey`]]]) : rowType = RecordType(ANY l_orderkey): rowcount =
> 310.0, cumulative cost = {310.0 rows, 310.0 cpu, 0.0 io, 0.0 network, 0.0
> memory}, id = 205719
> {code}
> Correct Result Plan (based on the profile):
> {code}
> 00-00 Screen : rowType = RecordType(ANY l_orderkey): rowcount = 2.25,
> cumulative cost = {122.475 rows, 527.475 cpu, 0.0 io, 0.0 network, 0.0
> memory}, id = 226849
> 00-01 Project(l_orderkey=[$3]) : rowType = RecordType(ANY l_orderkey):
> rowcount = 2.25, cumulative cost = {122.25 rows, 527.25 cpu, 0.0 io, 0.0
> network, 0.0 memory}, id = 226848
> 00-02 SelectionVectorRemover : rowType = RecordType(ANY dir0, ANY
> dir1, ANY dir2, ANY l_orderkey): rowcount = 2.25, cumulative cost = {122.25
> rows, 527.25 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 226847
> 00-03 Filter(condition=[AND(=($0, 1), OR(AND(=($1, 'one'), OR(=($2,
> '2015-7-12'), =($2, '2015-7-13'))), AND(=($1, 'two'), =($2, '2015-8-12'))))])
> : rowType = RecordType(ANY dir0, ANY dir1, ANY dir2, ANY l_orderkey):
> rowcount = 2.25, cumulative cost = {120.0 rows, 525.0 cpu, 0.0 io, 0.0
> network, 0.0 memory}, id = 226846
> 00-04 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
> [path=/drill/testdata/metadata_caching_pp/l_3level/1/one/2015-7-13/20.parquet],
> ReadEntryWithPath
> [path=/drill/testdata/metadata_caching_pp/l_3level/1/two/2015-8-12/30.parquet],
> ReadEntryWithPath
> [path=/drill/testdata/metadata_caching_pp/l_3level/1/one/2015-7-12/10.parquet]],
> selectionRoot=/drill/testdata/metadata_caching_pp/l_3level, numFiles=3,
> usedMetadataFile=true,
> cacheFileRoot=/drill/testdata/metadata_caching_pp/l_3level/1,
> columns=[`dir0`, `dir1`, `dir2`, `l_orderkey`]]]) : rowType = RecordType(ANY
> dir0, ANY dir1, ANY dir2, ANY l_orderkey): rowcount = 60.0, cumulative cost =
> {60.0 rows, 240.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 226845
> {code}
> I attached the data set, log files and the query profiles. Let me know if you
> need anything
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
