You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@phoenix.apache.org by Venkat Raman <vr...@gmail.com> on 2016/01/23 08:13:58 UTC

Question regarding secondary indexes

Hi All,

We are using secondary indexes and see the following issue. Consider main
table ACT with primary key (K1, K2, K3) and column M1 and secondary index
of form -

CREATE INDEX IF NOT EXISTS act_idx ON ACT(K1, K3, K2);

Then query of the following form gives incorrect results -

select /*+ INDEX(ACT act_idx) */ K1, K2, K3 from ACT where K1 = ? and K3 =
? and M1 = ? order by K1,K3 limit 10;

What seems to happen is filter on M1 is applied after the limit is applied
rather than limit being applied after all the filters have applied. Is this
an expected issue?

I apologize if this question has been asked before.

Regards,
Venkat

Re: Question regarding secondary indexes

Posted by Venkat Raman <vr...@gmail.com>.

Hi James,

Thanks. This is what i was looking for, guess i have not looked recently.

Venkat

On Sat, Jan 23, 2016 at 8:37 AM, James Taylor <ja...@apache.org>
wrote:

> Hi Venkat,
> I believe this issue has been fixed in PHOENIX-2601. Please give our
> 4.7.0 a try as this fix is included there.
> Thanks,
> James
>
> On Friday, January 22, 2016, Venkat Raman <vr...@gmail.com> wrote:
>
>> Hi All,
>>
>> We are using secondary indexes and see the following issue. Consider main
>> table ACT with primary key (K1, K2, K3) and column M1 and secondary index
>> of form -
>>
>> CREATE INDEX IF NOT EXISTS act_idx ON ACT(K1, K3, K2);
>>
>> Then query of the following form gives incorrect results -
>>
>> select /*+ INDEX(ACT act_idx) */ K1, K2, K3 from ACT where K1 = ? and K3
>> = ? and M1 = ? order by K1,K3 limit 10;
>>
>> What seems to happen is filter on M1 is applied after the limit is
>> applied rather than limit being applied after all the filters have applied.
>> Is this an expected issue?
>>
>> I apologize if this question has been asked before.
>>
>> Regards,
>> Venkat
>>
>

Re: Question regarding secondary indexes

Posted by James Taylor <ja...@apache.org>.

Hi Venkat,
I believe this issue has been fixed in PHOENIX-2601. Please give our 4.7.0
a try as this fix is included there.
Thanks,
James

On Friday, January 22, 2016, Venkat Raman <vr...@gmail.com> wrote:

> Hi All,
>
> We are using secondary indexes and see the following issue. Consider main
> table ACT with primary key (K1, K2, K3) and column M1 and secondary index
> of form -
>
> CREATE INDEX IF NOT EXISTS act_idx ON ACT(K1, K3, K2);
>
> Then query of the following form gives incorrect results -
>
> select /*+ INDEX(ACT act_idx) */ K1, K2, K3 from ACT where K1 = ? and K3 =
> ? and M1 = ? order by K1,K3 limit 10;
>
> What seems to happen is filter on M1 is applied after the limit is applied
> rather than limit being applied after all the filters have applied. Is this
> an expected issue?
>
> I apologize if this question has been asked before.
>
> Regards,
> Venkat
>