[jira] [Commented] (FLINK-29765) SQL query not executing properly

["Martijn Visser (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/FLINK-29765?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17624313#comment-17624313 ] 

Martijn Visser commented on FLINK-29765:
----------------------------------------

[~godfreyhe] [~jark] WDYT? 

> SQL query not executing properly
> --------------------------------
>
>                 Key: FLINK-29765
>                 URL: https://issues.apache.org/jira/browse/FLINK-29765
>             Project: Flink
>          Issue Type: Bug
>          Components: Table SQL / Planner
>    Affects Versions: 1.15.0
>            Reporter: Aqib Mehmood
>            Priority: Major
>
> I am using this query to compare difference between last and second last price of our order sku
> WITH CTE AS (
>     SELECT a.sku, a.name, a.updatedAt, b.price FROM (    
>         SELECT sku, name, max(updatedAt) AS updatedAt from (
>             SELECT sku, name, updatedAt FROM wms.PurchaseOrderProduct
>             WHERE CONCAT(sku, DATE_FORMAT(updatedAt, '%Y-%m-%d %H:%m:%s')) not in (
>                 SELECT CONCAT(sku, DATE_FORMAT(updatedAt, '%Y-%m-%d %H:%m:%s')) FROM (
>                     SELECT sku, max(updatedAt) as updatedAt from wms.PurchaseOrderProduct
>                     GROUP BY sku
>                 ) AS x
>             )
>         ) AS z
>         GROUP BY sku, name
>     ) AS a
>     LEFT JOIN wms.PurchaseOrderProduct b
>     ON a.sku=b.sku AND a.name=b.name and a.updatedAt=b.updatedAt
> )
> SELECT a.sku, a.name, a.updatedAt AS latestupdatedAt, a.price AS latestPrice, b.updatedAt AS lastUpdatedAt, b.price AS lastPrice
> FROM (
>     SELECT a.sku, a.name, a.updatedAt, b.price from (
>         SELECT sku, name, max(updatedAt) as updatedAt from wms.PurchaseOrderProduct
>         GROUP BY sku, name
>     ) AS a
>     LEFT JOIN wms.PurchaseOrderProduct b
>     ON a.sku=b.sku AND a.name=b.name and a.updatedAt=b.updatedAt
> ) AS a
> LEFT JOIN CTE AS b
> ON a.sku=b.sku AND a.name=b.name;
> This issue is that Im getting *NULLs* for columns *lastUpdatedAt* and {*}lastPrice{*}. But when I run the same query on our prod database, I'm getting desired results.I suspect that flink is not processing the entire query before giving the results.
> I get desired results for a couple of rows in while *lastUpdatedAt* and *lastPrice* are not *NULL* in the beginning of the table{*}.{*} But then after that the entire two columns return *NULLs*
> I would like to know why flink is not executing the above query properly?TIA



--
This message was sent by Atlassian Jira
(v8.20.10#820010)