[ https://issues.apache.org/jira/browse/DRILL-8331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Anshuman Mishra updated DRILL-8331: ----------------------------------- Description: Hi All, I am facing an issue regarding a query I am firing up in drill and not getting the expected result set. Basically what I am doing is merging the data from three sheets and eliminating only those records present in the 4th sheet. The query: {quote}{_}select * from (SELECT EmpId,Test FROM TABLE(dfs.{_}{{{}_[C://TestingMerge.xlsx|file:///C://ATOP//all_files//XLS_XLSX//TestingMerge.xlsx]_{}}}{_}(type => 'excel', sheetName =>'Sheet1')) UNION ALL SELECT EmpId,Test FROM TABLE(dfs.{_}{{{}_[C://TestingMerge.xlsx|file:///C://ATOP//all_files//XLS_XLSX//TestingMerge.xlsx]_{}}}{_}(type => 'excel', sheetName =>'Sheet2')) UNION ALL SELECT EmpId,Test FROM TABLE(dfs.{_}{{{}_[C://TestingMerge.xlsx|file:///C://ATOP//all_files//XLS_XLSX//TestingMerge.xlsx]_{}}}{_}(type => 'excel', sheetName =>'Sheet3'))) where EmpID not in (select EmpID from TABLE(dfs.{_}{{{}_[C://TestingMerge.xlsx|file:///C://ATOP//all_files//XLS_XLSX//TestingMerge.xlsx]_{}}}{_}(type => 'excel', sheetName =>'Sheet4'))){_} {quote} The query when we execute it, returns no data, when it should just eliminate the data present in sheet4 from the excel sheet. Strangely enough when we do a union All on two sheets this query works perfectly fine and returns the expected result. One more finding is, if we use static values in 'not in' instead of 'subquery', the query works fine with all the 3 sheets as union all. I even gave a null check in the subquery used in 'not in' as it might consider null values as well, still no luck. NOTE: Instead of UNION ALL if UNION is applied then it is giving the expected result set. I have attached a sample file I am using to recreate the issue. [^TestingMerge.xlsx] was: Hi All, I am having a query regarding a query I am firing up in drill and not getting the expected result set. Basically what I am doing is merging the data from three sheets and eliminating only those records present in the 4th sheet. The query: {quote}{_}select * from (SELECT EmpId,Test FROM TABLE(dfs.{_}{{{}_[C://TestingMerge.xlsx|file:///C://ATOP//all_files//XLS_XLSX//TestingMerge.xlsx]_{}}}{_}(type => 'excel', sheetName =>'Sheet1')) UNION ALL SELECT EmpId,Test FROM TABLE(dfs.{_}{{{}_[C://TestingMerge.xlsx|file:///C://ATOP//all_files//XLS_XLSX//TestingMerge.xlsx]_{}}}{_}(type => 'excel', sheetName =>'Sheet2')) UNION ALL SELECT EmpId,Test FROM TABLE(dfs.{_}{{{}_[C://TestingMerge.xlsx|file:///C://ATOP//all_files//XLS_XLSX//TestingMerge.xlsx]_{}}}{_}(type => 'excel', sheetName =>'Sheet3'))) where EmpID not in (select EmpID from TABLE(dfs.{_}{{{}_[C://TestingMerge.xlsx|file:///C://ATOP//all_files//XLS_XLSX//TestingMerge.xlsx]_{}}}{_}(type => 'excel', sheetName =>'Sheet4'))){_} {quote} The query when we execute it, returns no data, when it should just eliminate the data present in sheet4 from the excel sheet. Strangely enough when we do a union All on two sheets this query works perfectly fine and returns the expected result. One more finding is, if we use static values in 'not in' instead of 'subquery', the query works fine with all the 3 sheets as union all. I even gave a null check in the subquery used in 'not in' as it might consider null values as well, still no luck. NOTE: Instead of UNION ALL if UNION is applied then it is giving the expected result set. I have attached a sample file I am using to recreate the issue. [^TestingMerge.xlsx] > UNION ALL for multiple sheets in excel giving unexpected result when used > with a subquery in not in > --------------------------------------------------------------------------------------------------- > > Key: DRILL-8331 > URL: https://issues.apache.org/jira/browse/DRILL-8331 > Project: Apache Drill > Issue Type: Bug > Components: Storage - Excel > Affects Versions: 1.20.2 > Reporter: Anshuman Mishra > Priority: Major > Attachments: TestingMerge.xlsx > > > Hi All, > I am facing an issue regarding a query I am firing up in drill and not > getting the expected result set. Basically what I am doing is merging the > data from three sheets and eliminating only those records present in the 4th > sheet. > The query: > {quote}{_}select * from (SELECT EmpId,Test FROM > TABLE(dfs.{_}{{{}_[C://TestingMerge.xlsx|file:///C://ATOP//all_files//XLS_XLSX//TestingMerge.xlsx]_{}}}{_}(type > => 'excel', sheetName =>'Sheet1')) UNION ALL SELECT EmpId,Test FROM > TABLE(dfs.{_}{{{}_[C://TestingMerge.xlsx|file:///C://ATOP//all_files//XLS_XLSX//TestingMerge.xlsx]_{}}}{_}(type > => 'excel', sheetName =>'Sheet2')) UNION ALL SELECT EmpId,Test FROM > TABLE(dfs.{_}{{{}_[C://TestingMerge.xlsx|file:///C://ATOP//all_files//XLS_XLSX//TestingMerge.xlsx]_{}}}{_}(type > => 'excel', sheetName =>'Sheet3'))) where EmpID not in (select EmpID from > TABLE(dfs.{_}{{{}_[C://TestingMerge.xlsx|file:///C://ATOP//all_files//XLS_XLSX//TestingMerge.xlsx]_{}}}{_}(type > => 'excel', sheetName =>'Sheet4'))){_} > {quote} > The query when we execute it, returns no data, when it should just eliminate > the data present in sheet4 from the excel sheet. Strangely enough when we do > a union All on two sheets this query works perfectly fine and returns the > expected result. One more finding is, if we use static values in 'not in' > instead of 'subquery', the query works fine with all the 3 sheets as union > all. I even gave a null check in the subquery used in 'not in' as it might > consider null values as well, still no luck. > NOTE: Instead of UNION ALL if UNION is applied then it is giving the expected > result set. > I have attached a sample file I am using to recreate the issue. > [^TestingMerge.xlsx] -- This message was sent by Atlassian Jira (v8.20.10#820010)