Peeyush Gupta created ASTERIXDB-3334:
----------------------------------------
Summary: 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
Following are the steps to reproduce the issue.
Add the following two documents
|{|
| "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"|
|}|
|{|
| "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"|
|}|
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)
