You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Gabriel Reid (JIRA)" <ji...@apache.org> on 2014/03/16 08:20:13 UTC
[jira] [Resolved] (PHOENIX-651) Query with group by fails when the
column for secondary index is included in the select clause.
[ https://issues.apache.org/jira/browse/PHOENIX-651?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Gabriel Reid resolved PHOENIX-651.
----------------------------------
Resolution: Fixed
Bulk resolve of closed issues imported from GitHub. This status was reached by first re-opening all closed imported issues and then resolving them in bulk.
> Query with group by fails when the column for secondary index is included in the select clause.
> -----------------------------------------------------------------------------------------------
>
> Key: PHOENIX-651
> URL: https://issues.apache.org/jira/browse/PHOENIX-651
> Project: Phoenix
> Issue Type: Task
> Affects Versions: 2.1.1
> Reporter: Samarth Jain
> Assignee: Samarth Jain
>
> </br>
> select distinct cls from new_version_course where cls<100 - this looks like another bug in the newly added secondary index code.
> </br>
> Cases I tried on Phoenix 2.1.0 and INTEGER data type so that we don't end up hitting the existing bugs with UNSIGNED_INT :
> </br>
> <b>Case 1 - index on cls, select and group by cls:</b>
> create table if not exists new_version_course(id char(13) not null primary key, cls INTEGER, math INTEGER,os INTEGER,ds INTEGER,c INTEGER, java INTEGER,db INTEGER,summary varchar(1000)) IMMUTABLE_ROWS=true split on ('06-250','06-500', '06-750');
> </br>
> create index new_version_course_cls_idx on new_version_course(cls) include(math,os,ds,c,java,db,summary);
> </br>
> select cls, count(*) from new_version_course group by cls - <b>FAILS</b>
> </br>
> <b>Case 2 - no index on cls:</b>
> </br>
> create table if not exists new_version_course(id char(13) not null primary key, cls INTEGER, math INTEGER,os INTEGER,ds INTEGER,c INTEGER, java INTEGER,db INTEGER,summary varchar(1000)) IMMUTABLE_ROWS=true split on ('06-250','06-500', '06-750');
> </br>
> select cls, count(*) from new_version_course group by cls - <b>WORKS</b>
> </br>
> <b>Case 3 - index on cls, select and group by math:</b>
> </br>
> create table if not exists new_version_course(id char(13) not null primary key, cls INTEGER, math INTEGER,os INTEGER,ds INTEGER,c INTEGER, java INTEGER,db INTEGER,summary varchar(1000)) IMMUTABLE_ROWS=true split on ('06-250','06-500', '06-750');
> </br>
> create index new_version_course_cls_idx on new_version_course(cls) include(math,os,ds,c,java,db,summary);
> </br>
> select math, count(*) from new_version_course group by math - <b>WORKS</b>
> </br>
> <b>Case 4 - index on math, select and group by cls:</b>
> </br>
> create table if not exists new_version_course(id char(13) not null primary key, cls INTEGER, math INTEGER,os INTEGER,ds INTEGER,c INTEGER, java INTEGER,db INTEGER,summary varchar(1000)) IMMUTABLE_ROWS=true split on ('06-250','06-500', '06-750');
> </br>
> create index new_version_course_math_idx on table new_version_course(math) include(cls,os,ds,c,java,db,summary);
> </br>
> select cls, count(*) from new_version_course group by cls - <b>WORKS</b>
> </br>
> <b>Case 5 - indexes on math and cls, select and group by cls:</b>
> </br>
> create table if not exists new_version_course(id char(13) not null primary key, cls INTEGER, math INTEGER,os INTEGER,ds INTEGER,c INTEGER, java INTEGER,db INTEGER,summary varchar(1000)) IMMUTABLE_ROWS=true split on ('06-250','06-500', '06-750');
> </br>
> create index new_version_course_math_idx on table new_version_course(math) include(cls,os,ds,c,java,db,summary);
> </br>
> create index new_version_course_cls_idx on new_version_course(cls) include(math,os,ds,c,java,db,summary);
> </br>
> select cls, count(*) from new_version_course group by cls - <b>FAILS</b>
> </br>
> <b>Case 6 - index on cls, group by cls:</b>
> </br>
> create table if not exists new_version_course(id char(13) not null primary key, cls INTEGER, math INTEGER,os INTEGER,ds INTEGER,c INTEGER, java INTEGER,db INTEGER,summary varchar(1000)) IMMUTABLE_ROWS=true split on ('06-250','06-500', '06-750');
> </br>
> create index new_version_course_cls_idx on new_version_course(cls) include(math,os,ds,c,java,db,summary);
> </br>
> select count(*) from new_version_course group by cls - <b>WORKS</b>
> </br>
--
This message was sent by Atlassian JIRA
(v6.2#6252)