You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by "cody81314 (via GitHub)" <gi...@apache.org> on 2023/06/29 08:57:52 UTC

[GitHub] [pinot] cody81314 opened a new issue, #11002: Incorrect Results when using OR Operator in WHERE Clause with Pinot Connector on Trino

cody81314 opened a new issue, #11002:
URL: https://github.com/apache/pinot/issues/11002

   Trino version: v419.
   Pinot version: v0.12.1
   
   I am using Trino to query Pinot, and I have encountered an issue with the results when using the `OR` operator in the `WHERE` clause. The specific query is as follows:
   
   ```sql
   WITH fd AS (
     SELECT user_id, affiliate
     FROM (
       SELECT user_id, affiliate, row_number() OVER (PARTITION BY user_id ORDER BY count(0) DESC) AS rn
       FROM sensitive_table
       GROUP BY user_id, affiliate
     ) ac
     WHERE rn = 1
   )
   
   SELECT fd.user_id, s2.account_user_id, fd.affiliate
   FROM sensitive_table_2 s2
   RIGHT JOIN fd ON fd.user_id = s2.account_user_id
   WHERE fd.user_id = 'sensitive_user_id' OR s2.account_user_id = 'sensitive_user_id'
   ```
   
   When I modify the condition to use the `OR` operator 
   ```sql 
   WHERE fd.user_id = 'sensitive_user_id' OR s2.account_user_id = 'sensitive_user_id'
   ```
   , the query fails to retrieve any data. However, when I use the `AND `operator 
   ```sql
   WHERE fd.user_id = 'sensitive_user_id' AND s2.account_user_id = 'sensitive_user_id'
   ```
   , the query returns the expected results.
   
   This behavior seems to indicate an issue with the Pinot connector in Trino when handling `OR `conditions in the `WHERE `clause. The connector might not be correctly evaluating the `OR `operator in this scenario, leading to incorrect results.
   
   I kindly request the Trino community to investigate and address this issue with the Pinot connector, as it hinders the ability to construct queries with flexible conditions. Thank you for your attention to this matter.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org

[GitHub] [pinot] cody81314 commented on issue #11002: Incorrect Results when using OR Operator in WHERE Clause with Pinot Connector on Trino

Posted by "cody81314 (via GitHub)" <gi...@apache.org>.
cody81314 commented on issue #11002:
URL: https://github.com/apache/pinot/issues/11002#issuecomment-1619726488

   Hello Xiang Fu,
   
   We have discovered that the issue was caused by exceeding the default limit of `100,000` for `pinot.server.query.executor.num.groups.limit` when grouping the query results. This resulted in incorrect results.
   
   Thank you for your previous response!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org

[GitHub] [pinot] Jackie-Jiang commented on issue #11002: Incorrect Results when using OR Operator in WHERE Clause with Pinot Connector on Trino

Posted by "Jackie-Jiang (via GitHub)" <gi...@apache.org>.
Jackie-Jiang commented on issue #11002:
URL: https://github.com/apache/pinot/issues/11002#issuecomment-1615225342

   cc @elonazoulay @xiangfu0 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org

[GitHub] [pinot] xiangfu0 commented on issue #11002: Incorrect Results when using OR Operator in WHERE Clause with Pinot Connector on Trino

Posted by "xiangfu0 (via GitHub)" <gi...@apache.org>.
xiangfu0 commented on issue #11002:
URL: https://github.com/apache/pinot/issues/11002#issuecomment-1615274498

   I think the valid query should be 
   ```WITH fd AS (
     SELECT user_id, affiliate
     FROM (
       SELECT user_id, affiliate, row_number() OVER (PARTITION BY user_id ORDER BY count(0) DESC) AS rn
       FROM sensitive_table
       GROUP BY user_id, affiliate
     ) ac
     WHERE rn = 1
   )
   
   SELECT fd.user_id, s2.account_user_id, fd.affiliate
   FROM sensitive_table_2 s2
   RIGHT JOIN fd ON fd.user_id = s2.account_user_id
   WHERE fd.user_id = 'sensitive_user_id'
   ```
   For RIGHT JOIN, you don't need `fd.user_id = 'sensitive_user_id' OR s2.account_user_id = 'sensitive_user_id'`


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org

[GitHub] [pinot] cody81314 closed issue #11002: Incorrect Results when using OR Operator in WHERE Clause with Pinot Connector on Trino

Posted by "cody81314 (via GitHub)" <gi...@apache.org>.
cody81314 closed issue #11002: Incorrect Results when using OR Operator in WHERE Clause with Pinot Connector on Trino
URL: https://github.com/apache/pinot/issues/11002


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org