Re: [sqlite] BETWEEN and explicit collation assignment

On Aug 22, 2013 8:18 PM, "James K. Lowden" wrote: > Collation is not a property of the value. Strings are just strings. > They have an *encoding*, necessarily, but not a collation. > > Collation therefore must, by process of elimination, be a property of > the type.

Re: [sqlite] BETWEEN and explicit collation assignment

I would not either. We can agree to agree. But clarity requires this. It is like "the" in English. From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [i...@tandetnik.org] Sent: Thursday, August 22, 2013

Re: [sqlite] BETWEEN and explicit collation assignment

On 8/22/2013 9:01 PM, Roman Fleysher wrote: create table t(x text collate nocase); insert into t values ('A'); select count(*) from t where x = 'a'; In your opinion, what result should this select statement produce? 1, as it does now? 0, as Simon's approach seems to imply? Something else?

Re: [sqlite] BETWEEN and explicit collation assignment

On Thu, 22 Aug 2013 13:36:00 +0100 Simon Slavin wrote: > > 1. where COLLATE( x AS NOCASE ) > > between COLLATE( 'a' AS NOCASE ) > > and COLLATE( 'b' AS NOCASE ) > > Again, you are trying to apply a collation to a value. And because > BETWEEN takes

Re: [sqlite] BETWEEN and explicit collation assignment

> create table t(x text collate nocase); > insert into t values ('A'); > select count(*) from t where x = 'a'; > In your opinion, what result should this select statement produce? 1, as > it does now? 0, as Simon's approach seems to imply? Something else? Since logically it is the operation

Re: [sqlite] BETWEEN and explicit collation assignment

On 8/22/2013 8:41 PM, Simon Slavin wrote: On 23 Aug 2013, at 1:14am, Igor Tandetnik wrote: Once again, a concrete example: create table t(x text collate nocase); insert into t values ('A'); select count(*) from t where x = 'a'; In your opinion, what result should this

Re: [sqlite] BETWEEN and explicit collation assignment

On 23 Aug 2013, at 1:14am, Igor Tandetnik wrote: > Once again, a concrete example: > > create table t(x text collate nocase); > insert into t values ('A'); > select count(*) from t where x = 'a'; > > In your opinion, what result should this select statement produce? 1, as

Re: [sqlite] BETWEEN and explicit collation assignment

On 8/22/2013 6:59 PM, Roman Fleysher wrote: First, I do not expect any changes to SQLite to be made as a result of this discussion. You might not, but Simon seems to: "It's too late to contrafit this into SQLite3, but I'm wondering whether SQLite4 might have the collation operator rethought

Re: [sqlite] BETWEEN and explicit collation assignment

Dear Igor, First, I do not expect any changes to SQLite to be made as a result of this discussion. You are right, the discussion is largely philosophical. (I do not imply "philosophical" means "impractical", which are sometimes equated.) But the questions you ask are also philosophical: for is

Re: [sqlite] BETWEEN and explicit collation assignment

On 8/22/2013 4:06 PM, Roman Fleysher wrote: Logically, I agree with Simon, collate modifier in table definition describes how indices should be built, nothing more. But for what purpose are indexes built? Isn't it for the purpose of being used to speed up queries? Wouldn't you expect a query

Re: [sqlite] BETWEEN and explicit collation assignment

Dear Igor, I was reading with great interest your debate with Simon. I have come to respect your opinion by reading your comments. I am not a moderator, but trying to imply that many SQLite users are on your side reduced your image in my head. I left USSR (where such arguments were plenty) and

Re: [sqlite] BETWEEN and explicit collation assignment

On 8/22/2013 3:12 PM, Simon Slavin wrote: On 22 Aug 2013, at 8:04pm, Igor Tandetnik wrote: [snip] I pretty much agree with everything you wrote there. But it has nothing to do with my original objection which was the explicit use of a COLLATE operator inside an

Re: [sqlite] BETWEEN and explicit collation assignment

On 8/22/2013 3:13 PM, Niall O'Reilly wrote: But let's bring the thread back to the original problem. What Simon Slavin seems (to me) to be pointing out is that the counter-intuitive behaviour observed by the OP (Clemens Ladisch) needs either to be corrected or

Re: [sqlite] BETWEEN and explicit collation assignment

Niall O'Reilly wrote: > What Simon Slavin seems (to me) to be pointing out is that the > counter-intuitive behaviour observed by the OP (Clemens Ladisch) > needs either to be corrected or explicitly documented It worked correctly (i.e., as documented) before 3.7.15, so it's a bug. Which is

Re: [sqlite] BETWEEN and explicit collation assignment

On Thu, 22 Aug 2013 13:15:19 -0400 Igor Tandetnik wrote: > On 8/22/2013 11:49 AM, Simon Slavin wrote: > > Step 1 would be remove all ability to specify collation applying to a > > single value. > > But it already applies, implicitly, to a single value that happens to be >

Re: [sqlite] BETWEEN and explicit collation assignment

On 22 Aug 2013, at 8:04pm, Igor Tandetnik wrote: > [snip] I pretty much agree with everything you wrote there. But it has nothing to do with my original objection which was the explicit use of a COLLATE operator inside an expression. Simon.

Re: [sqlite] BETWEEN and explicit collation assignment

On 8/22/2013 2:10 PM, Simon Slavin wrote: Sorry, hit 'send' too early. On 22 Aug 2013, at 6:15pm, Igor Tandetnik wrote: But again, by what formal mechanism does a property of the column affect the behavior of the operator? I see no reason for it to do that. So to be

Re: [sqlite] BETWEEN and explicit collation assignment

Sorry, hit 'send' too early. On 22 Aug 2013, at 6:15pm, Igor Tandetnik wrote: > But again, by what formal mechanism does a property of the column affect the > behavior of the operator? I see no reason for it to do that. I can see why the property of the column might

Re: [sqlite] BETWEEN and explicit collation assignment

On 22 Aug 2013, at 6:15pm, Igor Tandetnik wrote: > But again, by what formal mechanism does a property of the column affect the > behavior of the operator? I see no reason for it to do that. I can see why the property of the column might affect the behaviour of an index

Re: [sqlite] BETWEEN and explicit collation assignment

On 8/22/2013 11:49 AM, Simon Slavin wrote: On 22 Aug 2013, at 2:36pm, Igor Tandetnik wrote: On 8/22/2013 8:52 AM, Simon Slavin wrote: Nevertheless do you understand the point I'm trying to make -- that collations are a modifier for comparisons not individual values ?

Re: [sqlite] sylinks to db files?

On Thu, Aug 22, 2013 at 4:46 PM, Richard Hipp wrote: > See http://www.sqlite.org/howtocorrupt.html#alias for the explanation of > why this leads to database corruption. > Thank you :). i turned out i just needed to symlink the repo dir at fs/apache level and change my CGI

Re: [sqlite] sylinks to db files?

On Thu, Aug 22, 2013 at 9:53 AM, Stephan Beal wrote: > Hi, all, > > i'm renaming a fossil repository db and i'm wondering if it's legal to use > a symlink to point the old db to the new one while i get all the docs/URLs > rewritten? > See

[sqlite] sylinks to db files?

Hi, all, i'm renaming a fossil repository db and i'm wondering if it's legal to use a symlink to point the old db to the new one while i get all the docs/URLs rewritten? e.g. mv old.fsl new.fsl ln -s new.fsl old.fsl where my "old" CGI would then point to 'old.fsl'. Or am i just asking for

Re: [sqlite] BETWEEN and explicit collation assignment

On 8/22/2013 8:52 AM, Simon Slavin wrote: Nevertheless do you understand the point I'm trying to make -- that collations are a modifier for comparisons not individual values ? I do understand your point. I still don't understand how it's supposed to apply in practice to a situation like

Re: [sqlite] BETWEEN and explicit collation assignment

On Thu, Aug 22, 2013 at 8:52 AM, Simon Slavin wrote: > > Nevertheless do you understand the point I'm trying to make -- that > collations are a modifier for comparisons not individual values ? > > The collation is a property of the value. It is part of the datatype of the

Re: [sqlite] BETWEEN and explicit collation assignment

On 22 Aug 2013, at 1:47pm, Igor Tandetnik wrote: > You can, actually: hulkify(x) := (case when x = 'the hulk' then 'B' else 'A' > end) . With such a function, "a = b collate muchStronger" is equivalent to > "hulkify(a) = hulkify(b) collate BINARY". In other words,

Re: [sqlite] BETWEEN and explicit collation assignment

On 8/22/2013 8:36 AM, Simon Slavin wrote: Collation 'muchStronger': If a = b return 0, else ... If b is 'the hulk' return -1, else ... If a is 'the hulk' return 1, else ... return 0 (translation: people are roughly as strong as one-another, except for The Hulk

Re: [sqlite] BETWEEN and explicit collation assignment

On 22 Aug 2013, at 2:55am, James K. Lowden wrote: > Simon Slavin wrote: > >> My problem is not with COLLATE in general. It's with expressions. >> Table definitions are fine. Index definitions are fine. It's purely >> that my understanding of

Re: [sqlite] BLOB & Other Data Type

Your declarations affect the AFFINITY of the columns (integer, text, integer) vs. (none, none, none). In both cases the actual types are (integer, text, integer) and will be stored exactly thus. The difference can be seen only when the declared type of the column and the actual type of the data