You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cassandra.apache.org by "Sylvain Lebresne (JIRA)" <ji...@apache.org> on 2012/05/25 16:36:22 UTC
[jira] [Created] (CASSANDRA-4283) CQL3: dates are not handled
correctly in slices
Sylvain Lebresne created CASSANDRA-4283:
-------------------------------------------
Summary: CQL3: dates are not handled correctly in slices
Key: CASSANDRA-4283
URL: https://issues.apache.org/jira/browse/CASSANDRA-4283
Project: Cassandra
Issue Type: Bug
Components: API
Affects Versions: 1.1.0
Reporter: Sylvain Lebresne
Assignee: Sylvain Lebresne
Fix For: 1.1.2
Our timestamp type allows to input timestamp as dates like '2012-06-06'. However, those don't work as expected in slice queries, as for instance:
{noformat}
SELECT * FROM timeline
WHERE k = ...
AND time > '2012-06-06'
AND time <= '2012-06-09'
{noformat}
will return timestamps from '2012-06-06' and not those from '2012-06-09'. The reason being of course that we always translate a date the same way, using 0 for whichever part is not precised.
A reasonably simple fix could be to add a new fromString(String s, boolean gt) method to AbstractType that is used when the the string should be interpreted in an inequality (the boolean gt would then say which kind of inequality).
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (CASSANDRA-4283) CQL3: dates are not handled
correctly in slices
Posted by "Sylvain Lebresne (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CASSANDRA-4283?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13283591#comment-13283591 ]
Sylvain Lebresne commented on CASSANDRA-4283:
---------------------------------------------
To be clear, I did not pretended you were wrong on how SQL works and I'm on board with the argument of "let's continue to do what SQL defines". I was just finding it a bit unfair to pretend that, taking aside what SQL actually defines, to expect {{SELECT * FROM timeline WHERE time > '2012-06-06'}} not to return value dating of the 6 June amounts to "guess what the user wants".
> CQL3: dates are not handled correctly in slices
> ------------------------------------------------
>
> Key: CASSANDRA-4283
> URL: https://issues.apache.org/jira/browse/CASSANDRA-4283
> Project: Cassandra
> Issue Type: Bug
> Components: API
> Affects Versions: 1.1.0
> Reporter: Sylvain Lebresne
> Assignee: Sylvain Lebresne
> Labels: cql3
> Fix For: 1.1.2
>
>
> Our timestamp type allows to input timestamp as dates like '2012-06-06'. However, those don't work as expected in slice queries, as for instance:
> {noformat}
> SELECT * FROM timeline
> WHERE k = ...
> AND time > '2012-06-06'
> AND time <= '2012-06-09'
> {noformat}
> will return timestamps from '2012-06-06' and not those from '2012-06-09'. The reason being of course that we always translate a date the same way, using 0 for whichever part is not precised.
> A reasonably simple fix could be to add a new fromString(String s, boolean gt) method to AbstractType that is used when the the string should be interpreted in an inequality (the boolean gt would then say which kind of inequality).
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (CASSANDRA-4283) CQL3: dates are not handled
correctly in slices
Posted by "Sylvain Lebresne (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CASSANDRA-4283?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13283576#comment-13283576 ]
Sylvain Lebresne commented on CASSANDRA-4283:
---------------------------------------------
bq. First, I don't think trying to guess what the user wants here is the right solution.
Maybe I'm wrong on what the SQL standard actually does and that's another problem, but at least in principle I really don't think that what I was suggesting is in any way ambiguous. If you use '2012-06-06', it feels natural and straightforward that you expect a day precision. And that if you want any time after '2012-06-06 00:00:00' then you'd use {{time > '2012-06-06 00:00:00'}}. And if you say, {{time > '2012'}}, likely you want something after 2012. I mean, it reads like that.
That being said, I haven't checked what the SQL standard and if it prescribes that the query in the description of this ticket is equivalent to:
{noformat}
SELECT * FROM timeline
WHERE k = ...
AND time > '2012-06-06 00:00:00'
AND time <= '2012-06-09 00:00:00'
{noformat}
and thus will return everything done the 6 june 2012, then so be it. That's not the most natural definition but so be it.
> CQL3: dates are not handled correctly in slices
> ------------------------------------------------
>
> Key: CASSANDRA-4283
> URL: https://issues.apache.org/jira/browse/CASSANDRA-4283
> Project: Cassandra
> Issue Type: Bug
> Components: API
> Affects Versions: 1.1.0
> Reporter: Sylvain Lebresne
> Assignee: Sylvain Lebresne
> Labels: cql3
> Fix For: 1.1.2
>
>
> Our timestamp type allows to input timestamp as dates like '2012-06-06'. However, those don't work as expected in slice queries, as for instance:
> {noformat}
> SELECT * FROM timeline
> WHERE k = ...
> AND time > '2012-06-06'
> AND time <= '2012-06-09'
> {noformat}
> will return timestamps from '2012-06-06' and not those from '2012-06-09'. The reason being of course that we always translate a date the same way, using 0 for whichever part is not precised.
> A reasonably simple fix could be to add a new fromString(String s, boolean gt) method to AbstractType that is used when the the string should be interpreted in an inequality (the boolean gt would then say which kind of inequality).
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (CASSANDRA-4283) CQL3: dates are not handled
correctly in slices
Posted by "Jonathan Ellis (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CASSANDRA-4283?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13283529#comment-13283529 ]
Jonathan Ellis commented on CASSANDRA-4283:
-------------------------------------------
Is the problem that it doesn't use the correct operation marker at the end of the CompositeType?
> CQL3: dates are not handled correctly in slices
> ------------------------------------------------
>
> Key: CASSANDRA-4283
> URL: https://issues.apache.org/jira/browse/CASSANDRA-4283
> Project: Cassandra
> Issue Type: Bug
> Components: API
> Affects Versions: 1.1.0
> Reporter: Sylvain Lebresne
> Assignee: Sylvain Lebresne
> Labels: cql3
> Fix For: 1.1.2
>
>
> Our timestamp type allows to input timestamp as dates like '2012-06-06'. However, those don't work as expected in slice queries, as for instance:
> {noformat}
> SELECT * FROM timeline
> WHERE k = ...
> AND time > '2012-06-06'
> AND time <= '2012-06-09'
> {noformat}
> will return timestamps from '2012-06-06' and not those from '2012-06-09'. The reason being of course that we always translate a date the same way, using 0 for whichever part is not precised.
> A reasonably simple fix could be to add a new fromString(String s, boolean gt) method to AbstractType that is used when the the string should be interpreted in an inequality (the boolean gt would then say which kind of inequality).
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (CASSANDRA-4283) CQL3: dates are not handled
correctly in slices
Posted by "Sylvain Lebresne (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CASSANDRA-4283?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13283538#comment-13283538 ]
Sylvain Lebresne commented on CASSANDRA-4283:
---------------------------------------------
No, the problem is that '2012-06-06' is always translated as '2012-06-06 00:00:00', but when you ask for {{time > '2012-06-06'}} you clearly don't expect to get something dating of the 6 June at midnigt and 1 minute. Or in other words, '2012-06-06' means a time range, more than a precise timestamp, which we don't handle.
> CQL3: dates are not handled correctly in slices
> ------------------------------------------------
>
> Key: CASSANDRA-4283
> URL: https://issues.apache.org/jira/browse/CASSANDRA-4283
> Project: Cassandra
> Issue Type: Bug
> Components: API
> Affects Versions: 1.1.0
> Reporter: Sylvain Lebresne
> Assignee: Sylvain Lebresne
> Labels: cql3
> Fix For: 1.1.2
>
>
> Our timestamp type allows to input timestamp as dates like '2012-06-06'. However, those don't work as expected in slice queries, as for instance:
> {noformat}
> SELECT * FROM timeline
> WHERE k = ...
> AND time > '2012-06-06'
> AND time <= '2012-06-09'
> {noformat}
> will return timestamps from '2012-06-06' and not those from '2012-06-09'. The reason being of course that we always translate a date the same way, using 0 for whichever part is not precised.
> A reasonably simple fix could be to add a new fromString(String s, boolean gt) method to AbstractType that is used when the the string should be interpreted in an inequality (the boolean gt would then say which kind of inequality).
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (CASSANDRA-4283) CQL3: dates are not handled
correctly in slices
Posted by "Jonathan Ellis (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CASSANDRA-4283?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13283555#comment-13283555 ]
Jonathan Ellis commented on CASSANDRA-4283:
-------------------------------------------
Two points.
First, I don't think trying to guess what the user wants here is the right solution. They could mean:
# "any time after 2012-06-06 00:00:00"
# "at least a day after time Y"
# "time X is a *different* day than time Y" (different from #2 when Y is not 00:00:00)
Second, for the query you give, #1 is normal and expected behavior for every relational database.
The SQL standard specifies an {{interval}} type that can be used to solve #2:
{{SELECT x FROM foo WHERE x > y + '1 day'::interval}}
The easiest way to do #3 is to force everything to truncate to the {{date}} type:
{{SELECT x FROM foo WHERE x::date > y::date}}
> CQL3: dates are not handled correctly in slices
> ------------------------------------------------
>
> Key: CASSANDRA-4283
> URL: https://issues.apache.org/jira/browse/CASSANDRA-4283
> Project: Cassandra
> Issue Type: Bug
> Components: API
> Affects Versions: 1.1.0
> Reporter: Sylvain Lebresne
> Assignee: Sylvain Lebresne
> Labels: cql3
> Fix For: 1.1.2
>
>
> Our timestamp type allows to input timestamp as dates like '2012-06-06'. However, those don't work as expected in slice queries, as for instance:
> {noformat}
> SELECT * FROM timeline
> WHERE k = ...
> AND time > '2012-06-06'
> AND time <= '2012-06-09'
> {noformat}
> will return timestamps from '2012-06-06' and not those from '2012-06-09'. The reason being of course that we always translate a date the same way, using 0 for whichever part is not precised.
> A reasonably simple fix could be to add a new fromString(String s, boolean gt) method to AbstractType that is used when the the string should be interpreted in an inequality (the boolean gt would then say which kind of inequality).
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (CASSANDRA-4283) CQL3: dates are not handled
correctly in slices
Posted by "Jonathan Ellis (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CASSANDRA-4283?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13283581#comment-13283581 ]
Jonathan Ellis commented on CASSANDRA-4283:
-------------------------------------------
Example from postgresql:
{code}
# select '2012-06-06 00:00:01'::timestamp > '2012-06-06'::timestamp;
?column?
----------
t
(1 row)
# select '2012-06-06 00:00:01'::date > '2012-06-06'::date;
?column?
----------
f
(1 row)
{code}
> CQL3: dates are not handled correctly in slices
> ------------------------------------------------
>
> Key: CASSANDRA-4283
> URL: https://issues.apache.org/jira/browse/CASSANDRA-4283
> Project: Cassandra
> Issue Type: Bug
> Components: API
> Affects Versions: 1.1.0
> Reporter: Sylvain Lebresne
> Assignee: Sylvain Lebresne
> Labels: cql3
> Fix For: 1.1.2
>
>
> Our timestamp type allows to input timestamp as dates like '2012-06-06'. However, those don't work as expected in slice queries, as for instance:
> {noformat}
> SELECT * FROM timeline
> WHERE k = ...
> AND time > '2012-06-06'
> AND time <= '2012-06-09'
> {noformat}
> will return timestamps from '2012-06-06' and not those from '2012-06-09'. The reason being of course that we always translate a date the same way, using 0 for whichever part is not precised.
> A reasonably simple fix could be to add a new fromString(String s, boolean gt) method to AbstractType that is used when the the string should be interpreted in an inequality (the boolean gt would then say which kind of inequality).
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Resolved] (CASSANDRA-4283) CQL3: dates are not handled
correctly in slices
Posted by "Sylvain Lebresne (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CASSANDRA-4283?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sylvain Lebresne resolved CASSANDRA-4283.
-----------------------------------------
Resolution: Not A Problem
> CQL3: dates are not handled correctly in slices
> ------------------------------------------------
>
> Key: CASSANDRA-4283
> URL: https://issues.apache.org/jira/browse/CASSANDRA-4283
> Project: Cassandra
> Issue Type: Bug
> Components: API
> Affects Versions: 1.1.0
> Reporter: Sylvain Lebresne
> Assignee: Sylvain Lebresne
> Labels: cql3
> Fix For: 1.1.2
>
>
> Our timestamp type allows to input timestamp as dates like '2012-06-06'. However, those don't work as expected in slice queries, as for instance:
> {noformat}
> SELECT * FROM timeline
> WHERE k = ...
> AND time > '2012-06-06'
> AND time <= '2012-06-09'
> {noformat}
> will return timestamps from '2012-06-06' and not those from '2012-06-09'. The reason being of course that we always translate a date the same way, using 0 for whichever part is not precised.
> A reasonably simple fix could be to add a new fromString(String s, boolean gt) method to AbstractType that is used when the the string should be interpreted in an inequality (the boolean gt would then say which kind of inequality).
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira