[
https://issues.apache.org/jira/browse/ASTERIXDB-3334?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Peeyush Gupta updated ASTERIXDB-3334:
-------------------------------------
Description:
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.
was:
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.
> 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
> 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)
