[
https://issues.apache.org/jira/browse/ASTERIXDB-3334?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Peeyush Gupta resolved ASTERIXDB-3334.
--------------------------------------
Resolution: Fixed
> Incorrect answer on a query
> ---------------------------
>
> Key: ASTERIXDB-3334
> URL: https://issues.apache.org/jira/browse/ASTERIXDB-3334
> Project: Apache AsterixDB
> Issue Type: Bug
> Components: COMP - Compiler
> Reporter: Peeyush Gupta
> Assignee: Peeyush Gupta
> Priority: Major
>
> Following are the steps to reproduce the issue.
> Add the following two documents
> {noformat}
> { "Item Count": 1, "Ship Priority": "0", "Order Priority": "1-URGENT",
> "Order Status": "F", "Order Quantity": 38, "Sales Total": 195.19,
> "Discount": 0.08, "Tax Rate": 0.03, "Ship Mode": "REGULAR AIR", "Fill
> Time": 2, "Gross Profit": -71.33, "Price": 5.28, "Ship Handle Cost":
> 5.06, "Employee Name": "Purkey, Jan", "Employee Dept": "1054", "Manager
> Name": "Collins, Babs", "Employee Yrs Exp": 6, "Employee Salary": 104677,
> "Customer Name": "Roy Skaria", "Customer State": "OHIO", "Call Center
> Region": "CENTRAL", "Customer Balance": 6496, "Customer Segment":
> "CONSUMER", "Prod Type1": "OFFICE SUPPLIES", "Prod Type2": "PAPER",
> "Prod Type3": "STATIONARY", "Prod Type4": "BUSINESS STATIONARY", "Product
> Name": "Astroparche® Fine Business Paper", "Product Container": "SMALL
> BOX", "Ship Promo": "REGULAR SHIPPING", "Supplier Name": "Supplier_098",
> "Supplier Balance": 5873, "Supplier Region": "WEST", "Supplier State":
> "CALIFORNIA", "Order ID": "21284", "Order Year": 1997, "Order Month":
> 9, "Order Day": 15, "Order Date": "1997-09-15T00:00:00.000", "Order
> Quarter": "Q3", "Product Base Margin": 0.37, "Product ID": "1811",
> "Receive Time": 3, "Received Date": "1997-09-20T00:00:00.000", "Ship
> Date": "1997-09-17T00:00:00.000", "Ship Charge": 5.06, "Total Cycle
> Time": 5, "Product In Stock": "YES", "PID": 6632, "Market Segment":
> "CONSUMER" }{noformat}
>
> {noformat}
> { "Item Count": 1, "Ship Priority": "0", "Order Priority": "4-NOT
> SPECIFIED", "Order Status": "F", "Order Quantity": 36, "Sales Total":
> 603.9, "Discount": 0.07, "Tax Rate": 0.02, "Ship Mode": "REGULAR AIR",
> "Fill Time": 1, "Gross Profit": 168.93, "Price": 17.52, "Ship Handle
> Cost": 5.6, "Employee Name": "Purkey, Jan", "Employee Dept": "1054",
> "Manager Name": "Collins, Babs", "Employee Yrs Exp": 6, "Employee
> Salary": 104677, "Customer Name": "Nathan Mautz", "Customer State":
> "ILLINOIS", "Call Center Region": "CENTRAL", "Customer Balance": 1820,
> "Customer Segment": "HOME OFFICE", "Prod Type1": "OFFICE SUPPLIES", "Prod
> Type2": "PAPER", "Prod Type3": "WRITINGS PADS", "Prod Type4": "MEMO
> SLIPS", "Product Name": "TOPS Carbonless Receipt Book, Four 2-3/4 x 7-1/4
> Money Receipts per Page", "Product Container": "WRAP BAG", "Ship Promo":
> "REGULAR SHIPPING", "Supplier Name": "Supplier_090", "Supplier Balance":
> 6202, "Supplier Region": "WEST", "Supplier State": "NEW MEXICO", "Order
> ID": "1250", "Order Year": 1997, "Order Month": 9, "Order Day": 29,
> "Order Date": "1997-09-29T00:00:00.000", "Order Quarter": "Q3", "Product
> Base Margin": 0.37, "Product ID": "1860", "Receive Time": 2, "Received
> Date": "1997-10-02T00:00:00.000", "Ship Date": "1997-09-30T00:00:00.000",
> "Ship Charge": 5.6, "Total Cycle Time": 3, "Product In Stock": "YES",
> "PID": 6981, "Market Segment": "HOME OFFICE" }{noformat}
>
> Create Staples view
>
> |CREATE ANALYTICS VIEW `Staples`(`Item Count` BIGINT NOT UNKNOWN, `Ship
> Priority` STRING NOT UNKNOWN, `Order Priority` STRING NOT UNKNOWN, `Order
> Status` STRING NOT UNKNOWN, `Order Quantity` DOUBLE NOT UNKNOWN, `Sales
> Total` DOUBLE NOT UNKNOWN, `Discount` DOUBLE NOT UNKNOWN, `Tax Rate` DOUBLE
> NOT UNKNOWN, `Ship Mode` STRING NOT UNKNOWN, `Fill Time` DOUBLE NOT UNKNOWN,
> `Gross Profit` DOUBLE NOT UNKNOWN, `Price` DOUBLE NOT UNKNOWN, `Ship Handle
> Cost` DOUBLE NOT UNKNOWN, `Employee Name` STRING NOT UNKNOWN, `Employee Dept`
> STRING NOT UNKNOWN, `Manager Name` STRING NOT UNKNOWN, `Employee Yrs Exp`
> DOUBLE NOT UNKNOWN, `Employee Salary` DOUBLE NOT UNKNOWN, `Customer Name`
> STRING NOT UNKNOWN, `Customer State` STRING NOT UNKNOWN, `Call Center Region`
> STRING NOT UNKNOWN, `Customer Balance` DOUBLE NOT UNKNOWN, `Customer Segment`
> STRING NOT UNKNOWN, `Prod Type1` STRING NOT UNKNOWN, `Prod Type2` STRING NOT
> UNKNOWN, `Prod Type3` STRING NOT UNKNOWN, `Prod Type4` STRING NOT UNKNOWN,
> `Product Name` STRING NOT UNKNOWN, `Product Container` STRING NOT UNKNOWN,
> `Ship Promo` STRING NOT UNKNOWN, `Supplier Name` STRING NOT UNKNOWN,
> `Supplier Balance` DOUBLE NOT UNKNOWN, `Supplier Region` STRING NOT UNKNOWN,
> `Supplier State` STRING NOT UNKNOWN, `Order ID` STRING NOT UNKNOWN, `Order
> Year` BIGINT NOT UNKNOWN, `Order Month` BIGINT NOT UNKNOWN, `Order Day`
> BIGINT NOT UNKNOWN, `Order Date` DATETIME NOT UNKNOWN, `Order Quarter` STRING
> NOT UNKNOWN, `Product Base Margin` DOUBLE NOT UNKNOWN, `Product ID` STRING
> NOT UNKNOWN, `Receive Time` DOUBLE NOT UNKNOWN, `Received Date` DATETIME NOT
> UNKNOWN, `Ship Date` DATETIME NOT UNKNOWN, `Ship Charge` DOUBLE NOT UNKNOWN,
> `Total Cycle Time` DOUBLE NOT UNKNOWN, `Product In Stock` STRING NOT UNKNOWN,
> `PID` BIGINT NOT UNKNOWN, `Market Segment` STRING NOT UNKNOWN) default NULL
> AS `_default`|
>
> Run the following query
>
> |SELECT `Staples`.`Employee Name` AS `Employee Name`,|
> | AVG(`Staples`.`Employee Salary`) AS `avg:Employee Salary:ok`|
> |FROM `Staples` `Staples`|
> | INNER JOIN (|
> | SELECT `Staples`.`Call Center Region` AS `Call Center Region`,|
> | `Staples`.`Employee Name` AS `Employee Name`|
> | FROM `Staples` `Staples`|
> | GROUP BY `Staples`.`Call Center Region`,|
> | `Staples`.`Employee Name`|
> | HAVING ((AVG(`Staples`.`Employee Salary`)>=102499.99999999898) AND
> (AVG(`Staples`.`Employee Salary`)<=110000.00000000111))|
> |) `t0` ON ((`Staples`.`Call Center Region` = `t0`.`Call Center Region`) AND
> (`Staples`.`Employee Name` = `t0`.`Employee Name`))|
> |GROUP BY `Staples`.`Employee Name`|
>
> The query returns empty result which is incorrect.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
