lrao-stripe opened a new issue, #13749:
URL: https://github.com/apache/pinot/issues/13749
We have a query on a realtime table with filter expressions and count(*)
giving inconsistent query results. In all cases we order in descending order by
count.
## [Fails] Case 1: Multiple filter expressions with some evaluation to 0
rows, and count(*) outside filter
```
select
col_abc,
count(*) as count,
count(*) FILTER (
WHERE
col_type = ‘typeA’
) as typeA_count,
count(*) FILTER (
WHERE
col_type = ‘typeB’
) as typeB_count,
count(*) FILTER (
WHERE
col_type = ‘typeC’
) as typeC_count
from
table_t
where
created > 1718841600000.0
and created < 1719532800000.0
and col_abc != ''
group by
1
order by
count desc
limit
15
```
With this query we get inconsistent responses when we run this query
multiple times.
Response 1: As you can tell the responses are also not sorted.
```
| col_abc | count | typeA_count | typeB_count | typeC_count |
|:----------|------:|------------:|------------:|------------:|
| January | 2 | 2 | 0 | 0 |
| February | 1 | 1 | 0 | 0 |
| March | 3 | 3 | 0 | 0 |
```
Response 2: Results are sorted (and appear to be correct)
```
| col_abc | count | typeA_count | typeB_count | typeC_count |
|:----------|-------:|------------:|------------:|------------:|
| January | 108440 | 108440 | 0 | 0 |
| February | 12044 | 12044 | 0 | 0 |
| March | 3234 | 3234 | 0 | 0 |
```
## [Passes] Case 2: Multiple FILTER expressions and count(*) also as FILTER
expr
Modifying the query to treat count(*) also as a FILTER expr appears to fix
the issue and we get consistent, sorted responses even after running the query
multiple times.
```
select
col_abc,
count(*) FILTER(WHERE true) as count,
count(*) FILTER (
WHERE
col_type = ‘typeA’
) as typeA_count,
count(*) FILTER (
WHERE
col_type = ‘typeB’
) as typeB_count,
count(*) FILTER (
WHERE
col_type = ‘typeC’
) as typeC_count
from
table_t
where
created > 1718841600000.0
and created < 1719532800000.0
and col_abc != ''
group by
1
order by
count desc
limit
15
```
## [Passes] Case 3: Single FILTER expression that evaluates to non-zero rows
and count(*) outside filter expr
This gives consistent responses in a sorted order by count.
```
select
col_abc,
count(*) as count,
count(*) FILTER (
WHERE
col_type = ‘typeA’
) as typeA_count
from
table_t
where
created > 1718841600000.0
and created < 1719532800000.0
and col_abc != ''
group by
1
order by
count desc
limit
15
```
## [Fails] Case 4: Single FILTER expression that evaluates to zero rows and
count(*) outside filter expr
This gives inconsistent responses (similar to case 1) and not sorted by count
```
select
col_abc,
count(*) as count,
count(*) FILTER (
WHERE
col_type = ‘typeA’
) as typeA_count
from
table_t
where
created > 1718841600000.0
and created < 1719532800000.0
and col_abc != ''
group by
1
order by
count desc
limit
15
```
--
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-unsubscr...@pinot.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For additional commands, e-mail: commits-h...@pinot.apache.org
