You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Eric Czech <er...@nextbigsound.com> on 2012/05/03 19:46:11 UTC
odd CQL behavior
I can't believe I have to ask this but I have a CF with about 10 rows and
the keys are literally 1 through 9.
Why does this not work if I want the row where the key is ascii('5')?
cqlsh:Keyspace1> select first 1 * from CF where key = '5';
KEY
-----
05
* I saw the Jira about the sort of phantom row with no values so I know why
that's there
Listing the keys shows something like this:
cqlsh:Keyspace1> select key from CF ;
key
------
33
36
35
38
32
31
39
34
37
If I prepend a '3' to my key queries it works but I can't possibly see why
I would have to do that.
cqlsh:Keyspace1> select first 1 * from CF where key = '35';
( Returns the right rows for key '5')
Changing the validator appears to make no material difference beyond the
key listing:
cqlsh:Keyspace1> assume CF(key) values are text;
cqlsh:Keyspace1> select key from CF ;
key
-----
3
6
5
8
2
1
9
4
7
cqlsh:Keyspace1> select first 1 * from CF where key = '5';
KEY
-----
05
What's going on here?
Re: odd CQL behavior
Posted by Eric Czech <er...@nextbigsound.com>.
Alright, thanks again!
I guess I'll stick to the CLI since I'd rather not make schema changes for
the sake of querying (we have hundreds of column families and I don't want
to make that change everywhere).
On Thu, May 3, 2012 at 2:41 PM, paul cannon <pa...@datastax.com> wrote:
> Very sadly, no. It's not (yet?) meant to be a particularly good
> interactive language, so the only "function" in cql is COUNT(), and that's
> really just special syntax. It seems, though, that you might actually be
> better off with a key of type 'ascii' or 'text', if that's how you expect
> to work with it. Would it be an option to adjust that table?
>
> The ASSUME-changes-outgoing-cql ticket (CASSANDRA-3799) would also help,
> so maybe keep an eye on that.
>
> p
>
>
> On Thu, May 3, 2012 at 1:22 PM, Eric Czech <er...@nextbigsound.com> wrote:
>
>> Gotcha, I probably should have guessed that much. Does CQL have any
>> functions to convert ascii to hex so that I don't have to do that
>> conversion elsewhere (I don't see one in the docs)?
>>
>>
>> On Thu, May 3, 2012 at 2:09 PM, paul cannon <pa...@datastax.com> wrote:
>>
>>> On Thu, May 3, 2012 at 12:46 PM, Eric Czech <er...@nextbigsound.com>wrote:
>>>
>>>> I can't believe I have to ask this but I have a CF with about 10 rows
>>>> and the keys are literally 1 through 9.
>>>>
>>>> Why does this not work if I want the row where the key is ascii('5')?
>>>>
>>>> cqlsh:Keyspace1> select first 1 * from CF where key = '5';
>>>> KEY
>>>> -----
>>>> 05
>>>>
>>>
>>> This depends on the configured type (key_validation) of the key. From
>>> what you've posted, it looks like CQL is treating it as 'blob', not 'ascii'.
>>>
>>> * I saw the Jira about the sort of phantom row with no values so I know
>>>> why that's there
>>>>
>>>> Listing the keys shows something like this:
>>>>
>>>> cqlsh:Keyspace1> select key from CF ;
>>>> key
>>>> ------
>>>> 33
>>>> 36
>>>> 35
>>>> 38
>>>> 32
>>>> 31
>>>> 39
>>>> 34
>>>> 37
>>>>
>>>> If I prepend a '3' to my key queries it works but I can't possibly see
>>>> why I would have to do that.
>>>>
>>>> cqlsh:Keyspace1> select first 1 * from CF where key = '35';
>>>> ( Returns the right rows for key '5')
>>>>
>>>
>>> Cause 35 is hex for ascii(5), as you pointed out.
>>>
>>> Changing the validator appears to make no material difference beyond the
>>>> key listing:
>>>>
>>>
>>> This is because ASSUME is only a cqlsh feature, and only affects how
>>> data is deserialized. There is a ticket out for cqlsh also to mangle your
>>> outgoing CQL statements to match ASSUMEd types too, but that's not there
>>> yet.
>>>
>>> HTH,
>>> p
>>>
>>
>>
>
Re: odd CQL behavior
Posted by paul cannon <pa...@datastax.com>.
Very sadly, no. It's not (yet?) meant to be a particularly good
interactive language, so the only "function" in cql is COUNT(), and that's
really just special syntax. It seems, though, that you might actually be
better off with a key of type 'ascii' or 'text', if that's how you expect
to work with it. Would it be an option to adjust that table?
The ASSUME-changes-outgoing-cql ticket (CASSANDRA-3799) would also help, so
maybe keep an eye on that.
p
On Thu, May 3, 2012 at 1:22 PM, Eric Czech <er...@nextbigsound.com> wrote:
> Gotcha, I probably should have guessed that much. Does CQL have any
> functions to convert ascii to hex so that I don't have to do that
> conversion elsewhere (I don't see one in the docs)?
>
>
> On Thu, May 3, 2012 at 2:09 PM, paul cannon <pa...@datastax.com> wrote:
>
>> On Thu, May 3, 2012 at 12:46 PM, Eric Czech <er...@nextbigsound.com>wrote:
>>
>>> I can't believe I have to ask this but I have a CF with about 10 rows
>>> and the keys are literally 1 through 9.
>>>
>>> Why does this not work if I want the row where the key is ascii('5')?
>>>
>>> cqlsh:Keyspace1> select first 1 * from CF where key = '5';
>>> KEY
>>> -----
>>> 05
>>>
>>
>> This depends on the configured type (key_validation) of the key. From
>> what you've posted, it looks like CQL is treating it as 'blob', not 'ascii'.
>>
>> * I saw the Jira about the sort of phantom row with no values so I know
>>> why that's there
>>>
>>> Listing the keys shows something like this:
>>>
>>> cqlsh:Keyspace1> select key from CF ;
>>> key
>>> ------
>>> 33
>>> 36
>>> 35
>>> 38
>>> 32
>>> 31
>>> 39
>>> 34
>>> 37
>>>
>>> If I prepend a '3' to my key queries it works but I can't possibly see
>>> why I would have to do that.
>>>
>>> cqlsh:Keyspace1> select first 1 * from CF where key = '35';
>>> ( Returns the right rows for key '5')
>>>
>>
>> Cause 35 is hex for ascii(5), as you pointed out.
>>
>> Changing the validator appears to make no material difference beyond the
>>> key listing:
>>>
>>
>> This is because ASSUME is only a cqlsh feature, and only affects how data
>> is deserialized. There is a ticket out for cqlsh also to mangle your
>> outgoing CQL statements to match ASSUMEd types too, but that's not there
>> yet.
>>
>> HTH,
>> p
>>
>
>
Re: odd CQL behavior
Posted by Eric Czech <er...@nextbigsound.com>.
Gotcha, I probably should have guessed that much. Does CQL have any
functions to convert ascii to hex so that I don't have to do that
conversion elsewhere (I don't see one in the docs)?
On Thu, May 3, 2012 at 2:09 PM, paul cannon <pa...@datastax.com> wrote:
> On Thu, May 3, 2012 at 12:46 PM, Eric Czech <er...@nextbigsound.com> wrote:
>
>> I can't believe I have to ask this but I have a CF with about 10 rows and
>> the keys are literally 1 through 9.
>>
>> Why does this not work if I want the row where the key is ascii('5')?
>>
>> cqlsh:Keyspace1> select first 1 * from CF where key = '5';
>> KEY
>> -----
>> 05
>>
>
> This depends on the configured type (key_validation) of the key. From what
> you've posted, it looks like CQL is treating it as 'blob', not 'ascii'.
>
> * I saw the Jira about the sort of phantom row with no values so I know
>> why that's there
>>
>> Listing the keys shows something like this:
>>
>> cqlsh:Keyspace1> select key from CF ;
>> key
>> ------
>> 33
>> 36
>> 35
>> 38
>> 32
>> 31
>> 39
>> 34
>> 37
>>
>> If I prepend a '3' to my key queries it works but I can't possibly see
>> why I would have to do that.
>>
>> cqlsh:Keyspace1> select first 1 * from CF where key = '35';
>> ( Returns the right rows for key '5')
>>
>
> Cause 35 is hex for ascii(5), as you pointed out.
>
> Changing the validator appears to make no material difference beyond the
>> key listing:
>>
>
> This is because ASSUME is only a cqlsh feature, and only affects how data
> is deserialized. There is a ticket out for cqlsh also to mangle your
> outgoing CQL statements to match ASSUMEd types too, but that's not there
> yet.
>
> HTH,
> p
>
Re: odd CQL behavior
Posted by paul cannon <pa...@datastax.com>.
On Thu, May 3, 2012 at 12:46 PM, Eric Czech <er...@nextbigsound.com> wrote:
> I can't believe I have to ask this but I have a CF with about 10 rows and
> the keys are literally 1 through 9.
>
> Why does this not work if I want the row where the key is ascii('5')?
>
> cqlsh:Keyspace1> select first 1 * from CF where key = '5';
> KEY
> -----
> 05
>
This depends on the configured type (key_validation) of the key. From what
you've posted, it looks like CQL is treating it as 'blob', not 'ascii'.
* I saw the Jira about the sort of phantom row with no values so I know why
> that's there
>
> Listing the keys shows something like this:
>
> cqlsh:Keyspace1> select key from CF ;
> key
> ------
> 33
> 36
> 35
> 38
> 32
> 31
> 39
> 34
> 37
>
> If I prepend a '3' to my key queries it works but I can't possibly see why
> I would have to do that.
>
> cqlsh:Keyspace1> select first 1 * from CF where key = '35';
> ( Returns the right rows for key '5')
>
Cause 35 is hex for ascii(5), as you pointed out.
Changing the validator appears to make no material difference beyond the
> key listing:
>
This is because ASSUME is only a cqlsh feature, and only affects how data
is deserialized. There is a ticket out for cqlsh also to mangle your
outgoing CQL statements to match ASSUMEd types too, but that's not there
yet.
HTH,
p