You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by "Xinyi Yan (Jira)" <ji...@apache.org> on 2020/12/02 01:42:00 UTC
[jira] [Commented] (PHOENIX-5571) Incorrect "IS NULL" handling
[ https://issues.apache.org/jira/browse/PHOENIX-5571?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17241982#comment-17241982 ]
Xinyi Yan commented on PHOENIX-5571:
------------------------------------
{code:java}
0: jdbc:phoenix:> select * from product_history_2;
+----------------+--------------------------------------------+-------------+----------+----------+
| TS | ID | MERCHANTID | STOREID | PRODUCT |
+----------------+--------------------------------------------+-------------+----------+----------+
| 1498709133486 | 1491199695565244581-139-1-582-3894176988 | a | | |
| 1498709133486 | 1491199695565244581-139-1-582-3894176988 | | | |
| 1498810584706 | 1491199695565244581-139-1-582-3894176988 | b | | |
| 1498810584706 | 1491199695565244581-139-1-582-3894176988 | | | |
| 1499083503309 | 1491199695565244581-139-1-582-3894176988 | | | |
| 1499170817262 | 1491199695565244581-139-1-582-3894176988 | | | |
| 1499170817262 | 1491199695565244581-139-1-582-3894176988 | | | |
| 1503663938614 | 1491199695565244581-139-1-582-3894176988 | | | |
| 1506596564228 | 1491199695565244581-139-1-582-3894176988 | | | |
| 1509714911254 | 1491199695565244581-139-1-582-3894176988 | | | |
| 1517514069109 | 1491199695565244581-139-1-582-3894176988 | | | |
+----------------+--------------------------------------------+-------------+----------+----------+
11 rows selected (0.032 seconds)
0: jdbc:phoenix:> select count (*) from product_history_2 where merchantId IS not NULL;
+-----------+
| COUNT(1) |
+-----------+
| 2 |
+-----------+
1 row selected (0.018 seconds)
0: jdbc:phoenix:> select count (*) from product_history_2 where merchantId IS NULL;
+-----------+
| COUNT(1) |
+-----------+
| 9 |
+-----------+
{code}
[~0x62ash], [~ckulkarni], [~xucang], can you provide more information to repro this issue?
I created a table with pk (ts,id) and tried to run select count query but cannot repro it on 4.x branch
> Incorrect "IS NULL" handling
> ----------------------------
>
> Key: PHOENIX-5571
> URL: https://issues.apache.org/jira/browse/PHOENIX-5571
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.14.2
> Reporter: Alex Batyrshin
> Priority: Major
>
> {code:java}
> 0: jdbc:phoenix:> SELECT count(*) FROM STG.DAILY_DOCS_20190701;
> +-----------+
> | COUNT(1) |
> +-----------+
> | 242624 |
> +-----------+
> 0: jdbc:phoenix:> SELECT count(*) FROM STG.DAILY_DOCS_20190701 where "ts" IS NOT NULL;
> +-----------+
> | COUNT(1) |
> +-----------+
> | 20099 |
> +-----------+{code}
>
> This should means that count by "ts" IS NULL = 242624 - 20099 = 222525
> But Phoenix returns 0
> {code:java}
> 0: jdbc:phoenix:> SELECT count(*) FROM STG.DAILY_DOCS_20190701 where "ts" IS NULL;
> +-----------+
> | COUNT(1) |
> +-----------+
> | 0 |
> +-----------+ {code}
>
> But if we add some additional AND condition we could get count:
> {code:java}
> 0: jdbc:phoenix:> SELECT count(*) FROM STG.DAILY_DOCS_20190701 where "ts" IS NULL AND "did" is not null;
> +-----------+
> | COUNT(1) |
> +-----------+
> | 222525 |
> +-----------+ {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)