You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Kevin Liew (JIRA)" <ji...@apache.org> on 2016/12/12 19:24:58 UTC
[jira] [Updated] (PHOENIX-3530) Queries with 'like/wildcard %' in
the predicate not returning all rows
[ https://issues.apache.org/jira/browse/PHOENIX-3530?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kevin Liew updated PHOENIX-3530:
--------------------------------
Description:
Here is the background:
This query returns back 18 rows:
{code:sql}
select rowkey_i, evt_d, rawevt_fk from tep_own.event_trace where rowkey_i like '2016102709%CSXT000102%_GPS_ATS';
{code:sql}
If I look at the unique rawevt_fks it returns back 6 unique rows
{code:sql}
select distinct rawevt_fk from tep_own.event_trace where rowkey_i like '2016102709%CSXT000102%_GPS_ATS';
{code:sql}
If I query the table with a wildcard it returns back only 2 rows when it should be 5 (one of the 6 has a row key with the hour as 08 instead of 09)
{code:sql}
select * from tep_own.raw_event where rowkey_i like '2016102709%_CSXT000102_GPS_ATS';
{code:sql}
If I query one of those rows comparing the rowkey directly using the equals sign I get the row back as I should.
{code:sql}
select * from tep_own.raw_event where rowkey_i = '20161027095735327_CSXT000102_GPS_ATS';
{code:sql}
However, if I do the same query with a wildcard nothing comes back:
{code:sql}
select * from tep_own.raw_event where rowkey_i like '20161027095735327_CSXT000102_GPS_%';
{code:sql}
If I use the in clause to list each of the rawevt fks all rows are returned:
{code:sql}
select * from tep_own.raw_event where rowkey_i in
( '20161027085735178_CSXT000102_GPS_ATS',
'20161027091035210_CSXT000102_GPS_ATS',
'20161027092736742_CSXT000102_GPS_ATS',
'20161027093835903_CSXT000102_GPS_ATS',
'20161027094724722_CSXT000102_GPS_ATS',
'20161027095735327_CSXT000102_GPS_ATS') ;
{code:sql}
So, it looks like the data is there as long as you query it directly without using a wildcard. When using a wildcard we are getting mixed results in what is returned.
was:
Here is the background:
This query returns back 18 rows
.
select rowkey_i, evt_d, rawevt_fk from tep_own.event_trace where rowkey_i like '2016102709%CSXT000102%_GPS_ATS';
If I look at the unique rawevt_fks it returns back 6 unique rows
select distinct rawevt_fk from tep_own.event_trace where rowkey_i like '2016102709%CSXT000102%_GPS_ATS';
If I query the table with a wildcard it returns back only 2 rows when it should be 5 (one of the 6 has a row key with the hour as 08 instead of 09)
select * from tep_own.raw_event where rowkey_i like '2016102709%_CSXT000102_GPS_ATS';
If I query one of those rows comparing the rowkey directly using the equals sign I get the row back as I should.
select * from tep_own.raw_event where rowkey_i = '20161027095735327_CSXT000102_GPS_ATS';
However, if I do the same query with a wildcard nothing comes back:
select * from tep_own.raw_event where rowkey_i like '20161027095735327_CSXT000102_GPS_%';
If I use the in clause to list each of the rawevt fks all rows are returned:
select * from tep_own.raw_event where rowkey_i in
( '20161027085735178_CSXT000102_GPS_ATS',
'20161027091035210_CSXT000102_GPS_ATS',
'20161027092736742_CSXT000102_GPS_ATS',
'20161027093835903_CSXT000102_GPS_ATS',
'20161027094724722_CSXT000102_GPS_ATS',
'20161027095735327_CSXT000102_GPS_ATS') ;
So, it looks like the data is there as long as you query it directly without using a wildcard. When using a wildcard we are getting mixed results in what is returned.
> Queries with 'like/wildcard %' in the predicate not returning all rows
> ----------------------------------------------------------------------
>
> Key: PHOENIX-3530
> URL: https://issues.apache.org/jira/browse/PHOENIX-3530
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.4.0
> Environment: Red Hat Enterprise Linux Server release 6.5
> Reporter: Duane Lupinski
>
> Here is the background:
> This query returns back 18 rows:
> {code:sql}
> select rowkey_i, evt_d, rawevt_fk from tep_own.event_trace where rowkey_i like '2016102709%CSXT000102%_GPS_ATS';
> {code:sql}
> If I look at the unique rawevt_fks it returns back 6 unique rows
> {code:sql}
> select distinct rawevt_fk from tep_own.event_trace where rowkey_i like '2016102709%CSXT000102%_GPS_ATS';
> {code:sql}
> If I query the table with a wildcard it returns back only 2 rows when it should be 5 (one of the 6 has a row key with the hour as 08 instead of 09)
>
> {code:sql}
> select * from tep_own.raw_event where rowkey_i like '2016102709%_CSXT000102_GPS_ATS';
> {code:sql}
> If I query one of those rows comparing the rowkey directly using the equals sign I get the row back as I should.
> {code:sql}
> select * from tep_own.raw_event where rowkey_i = '20161027095735327_CSXT000102_GPS_ATS';
> {code:sql}
> However, if I do the same query with a wildcard nothing comes back:
> {code:sql}
> select * from tep_own.raw_event where rowkey_i like '20161027095735327_CSXT000102_GPS_%';
> {code:sql}
> If I use the in clause to list each of the rawevt fks all rows are returned:
>
> {code:sql}
> select * from tep_own.raw_event where rowkey_i in
> ( '20161027085735178_CSXT000102_GPS_ATS',
> '20161027091035210_CSXT000102_GPS_ATS',
> '20161027092736742_CSXT000102_GPS_ATS',
> '20161027093835903_CSXT000102_GPS_ATS',
> '20161027094724722_CSXT000102_GPS_ATS',
> '20161027095735327_CSXT000102_GPS_ATS') ;
> {code:sql}
> So, it looks like the data is there as long as you query it directly without using a wildcard. When using a wildcard we are getting mixed results in what is returned.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)