You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by "Deeter, Derek" <de...@digitalinsight.com> on 2011/08/16 01:45:49 UTC
CQL query using 'OR' in WHERE clause
Hi,
We are using CQL to obtain data from Cassandra 0.8.1 using Hector and
getting an error when using 'OR' on a secondary index. I get the same
error when using CQL 1.0.3. All the items in the WHERE clause are
secondary indices and they are all UTF8Type validation. The query works
when leaving out everything from 'OR' onwards. Example:
cqlsh> SELECT '.id' , '.ipAddress' , '.userProduct' , '.offeringId' ,
'.appId', 'timeStamp', '.logType', '.tzOffset' , '.id' , 'member' ,
'mfaEnrolled' , 'sessionId' , 'startPage' , 'timeStamp' FROM Audit_Log
USING CONSISTENCY ONE WHERE '.bcId' = '01112' AND '.userProduct' =
'IB' AND 'timeStamp' >= 1312182000000 AND '.logType' = 'login' OR
'.logType' = 'badLogin';
Bad Request: line 1:336 mismatched input 'OR' expecting EOF
I also tried to use the 'IN' keyword to no avail:
cqlsh> SELECT '.id' , '.ipAddress' , '.userProduct' , '.offeringId' ,
'.appId', 'timeStamp', '.logType', '.tzOffset' , '.id' , 'member' ,
'mfaEnrolled' , 'sessionId' , 'startPage' , 'timeStamp' FROM Audit_Log
USING CONSISTENCY ONE WHERE '.bcId' = '01112' AND '.userProduct' =
'IB' AND 'timeStamp' >= 1312182000000 AND '.logType' IN ( 'login',
'badLogin');
Bad Request: line 1:326 mismatched input 'IN' expecting set null
I also tried simplifying the query WHERE clause to only "WHERE
'.logType' = 'login' OR '.logType' = 'badLogin';" but get the same
'mismatched input' error. Is there any way to set up a query on a set
of values such as the above? Or do I have the syntax wrong?
Thanks in advance,
-Derek
Derek Deeter
Software Engineer, Sr
<http://ifs.intuit.com/>
o: 818-597-5932 | m: 661-645-7842 | f: 818-878-7555
This email may contain confidential and privileged material for the sole
use of the intended recipient. Any review or distribution by others is
strictly prohibited. If you are not the intended recipient, please
contact the sender and delete all copies.
RE: CQL query using 'OR' in WHERE clause
Posted by "Deeter, Derek" <de...@digitalinsight.com>.
Thanks, Jonathan!
-Derek
--
Derek Deeter, Sr. Software Engineer Intuit Financial Services
(818) 597-5932 (x76932) 5601 Lindero Canyon Rd.
derek.deeter@digitalinsight.com Westlake, CA 91362
-----Original Message-----
From: Jonathan Ellis [mailto:jbellis@gmail.com]
Sent: Monday, August 15, 2011 7:37 PM
To: user@cassandra.apache.org
Subject: Re: CQL query using 'OR' in WHERE clause
Disjunctions are not yet supported and probably will not be until after 1.0.
On Mon, Aug 15, 2011 at 6:45 PM, Deeter, Derek
<de...@digitalinsight.com> wrote:
> Hi,
>
> We are using CQL to obtain data from Cassandra 0.8.1 using Hector and
> getting an error when using 'OR' on a secondary index. I get the same error
> when using CQL 1.0.3. All the items in the WHERE clause are secondary
> indices and they are all UTF8Type validation. The query works when leaving
> out everything from 'OR' onwards. Example:
>
> cqlsh> SELECT '.id' , '.ipAddress' , '.userProduct' , '.offeringId' ,
> '.appId', 'timeStamp', '.logType', '.tzOffset' , '.id' , 'member' ,
> 'mfaEnrolled' , 'sessionId' , 'startPage' , 'timeStamp' FROM Audit_Log USING
> CONSISTENCY ONE WHERE '.bcId' = '01112' AND '.userProduct' = 'IB' AND
> 'timeStamp' >= 1312182000000 AND '.logType' = 'login' OR '.logType' =
> 'badLogin';
>
> Bad Request: line 1:336 mismatched input 'OR' expecting EOF
>
> I also tried to use the 'IN' keyword to no avail:
>
> cqlsh> SELECT '.id' , '.ipAddress' , '.userProduct' , '.offeringId' ,
> '.appId', 'timeStamp', '.logType', '.tzOffset' , '.id' , 'member' ,
> 'mfaEnrolled' , 'sessionId' , 'startPage' , 'timeStamp' FROM Audit_Log USING
> CONSISTENCY ONE WHERE '.bcId' = '01112' AND '.userProduct' = 'IB' AND
> 'timeStamp' >= 1312182000000 AND '.logType' IN ( 'login', 'badLogin');
>
> Bad Request: line 1:326 mismatched input 'IN' expecting set null
>
> I also tried simplifying the query WHERE clause to only "WHERE '.logType' =
> 'login' OR '.logType' = 'badLogin';" but get the same 'mismatched input'
> error. Is there any way to set up a query on a set of values such as the
> above? Or do I have the syntax wrong?
>
> Thanks in advance,
>
> -Derek
>
> Derek Deeter
> Software Engineer, Sr
>
> o: 818-597-5932 | m: 661-645-7842 | f: 818-878-7555
>
> This email may contain confidential and privileged material for the sole use
> of the intended recipient. Any review or distribution by others is strictly
> prohibited. If you are not the intended recipient, please contact the sender
> and delete all copies.
--
Jonathan Ellis
Project Chair, Apache Cassandra
co-founder of DataStax, the source for professional Cassandra support
http://www.datastax.com
Re: CQL query using 'OR' in WHERE clause
Posted by Jonathan Ellis <jb...@gmail.com>.
Disjunctions are not yet supported and probably will not be until after 1.0.
On Mon, Aug 15, 2011 at 6:45 PM, Deeter, Derek
<de...@digitalinsight.com> wrote:
> Hi,
>
> We are using CQL to obtain data from Cassandra 0.8.1 using Hector and
> getting an error when using ‘OR’ on a secondary index. I get the same error
> when using CQL 1.0.3. All the items in the WHERE clause are secondary
> indices and they are all UTF8Type validation. The query works when leaving
> out everything from ‘OR’ onwards. Example:
>
> cqlsh> SELECT '.id' , '.ipAddress' , '.userProduct' , '.offeringId' ,
> '.appId', 'timeStamp', '.logType', '.tzOffset' , '.id' , 'member' ,
> 'mfaEnrolled' , 'sessionId' , 'startPage' , 'timeStamp' FROM Audit_Log USING
> CONSISTENCY ONE WHERE '.bcId' = '01112' AND '.userProduct' = 'IB' AND
> 'timeStamp' >= 1312182000000 AND '.logType' = 'login' OR '.logType' =
> 'badLogin';
>
> Bad Request: line 1:336 mismatched input 'OR' expecting EOF
>
> I also tried to use the ‘IN’ keyword to no avail:
>
> cqlsh> SELECT '.id' , '.ipAddress' , '.userProduct' , '.offeringId' ,
> '.appId', 'timeStamp', '.logType', '.tzOffset' , '.id' , 'member' ,
> 'mfaEnrolled' , 'sessionId' , 'startPage' , 'timeStamp' FROM Audit_Log USING
> CONSISTENCY ONE WHERE '.bcId' = '01112' AND '.userProduct' = 'IB' AND
> 'timeStamp' >= 1312182000000 AND '.logType' IN ( 'login', 'badLogin');
>
> Bad Request: line 1:326 mismatched input 'IN' expecting set null
>
> I also tried simplifying the query WHERE clause to only “WHERE '.logType' =
> 'login' OR '.logType' = 'badLogin';” but get the same ‘mismatched input’
> error. Is there any way to set up a query on a set of values such as the
> above? Or do I have the syntax wrong?
>
> Thanks in advance,
>
> -Derek
>
> Derek Deeter
> Software Engineer, Sr
>
> o: 818-597-5932 | m: 661-645-7842 | f: 818-878-7555
>
> This email may contain confidential and privileged material for the sole use
> of the intended recipient. Any review or distribution by others is strictly
> prohibited. If you are not the intended recipient, please contact the sender
> and delete all copies.
--
Jonathan Ellis
Project Chair, Apache Cassandra
co-founder of DataStax, the source for professional Cassandra support
http://www.datastax.com