Re: [sqlite] BETWEEN and explicit collation assignment

On Tue, 3 Sep 2013 18:37:42 -0500 Nico Williams wrote: > > There's no need to qualify string literals, as it turns out. SQLite > > makes a reasonable choice in that context. When comparing a string > > literal to a column, the literal (in effect) takes on the collation >

Re: [sqlite] BETWEEN and explicit collation assignment

On Thu, Aug 29, 2013 at 6:35 PM, James K. Lowden wrote: > On Tue, 27 Aug 2013 21:00:50 -0500 > Nico Williams wrote: > >> Of course, lacking a syntax for associating collations with string >> literals there will be times when some, or even all of

Re: [sqlite] BETWEEN and explicit collation assignment

On Tue, 27 Aug 2013 21:00:50 -0500 Nico Williams wrote: > Of course, lacking a syntax for associating collations with string > literals there will be times when some, or even all of the operands to > an operation that needs collation information, is missing. In such >

Re: [sqlite] BETWEEN and explicit collation assignment

> -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf > Of James K. Lowden > Sent: Tuesday, August 27, 2013 8:11 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] BETWEEN and explicit collation assig

Re: [sqlite] BETWEEN and explicit collation assignment

On Mon, Aug 26, 2013 at 2:03 PM, Roman Fleysher wrote: Certainly associating a type conversion with a lexical instance of an operator, applying to all of the operator's operands (or perhaps even just to some, while having to explicitly cast others) would work, at

Re: [sqlite] BETWEEN and explicit collation assignment

On Mon, 26 Aug 2013 19:03:39 + Roman Fleysher wrote: > However, sometimes, in comparison we want to ignore some of the > attributes, or compare derived ones. Many busses can carry 25 people, > and may be considered equal if we simply need to transport people.

Re: [sqlite] BETWEEN and explicit collation assignment

> What is relevant is not determined by the type of the object, but by > the task at hand, in other words by the comparator. No. There is only one meaning of "equal". Two things are either equal or not. How to compare them is determined by only their type. You are correct, James, types are

Re: [sqlite] BETWEEN and explicit collation assignment

On Fri, 23 Aug 2013 17:08:59 + Roman Fleysher wrote: > This leads to enormous growth in number of types. Another solution is > to realize that the comparator (the thing that makes comparisons) is > actually an object of its own An operation is not an object.

Re: [sqlite] BETWEEN and explicit collation assignment

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of James K. Lowden [jklow...@schemamania.org] Sent: Thursday, August 22, 2013 9:18 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] BETWEEN and explicit collation assignment

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

22, 2013 9:37 PM To: sqlite-users@sqlite.org Subject: 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'; > >>

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

rs@sqlite.org Subject: 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 int

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

onsequence. Roman From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [i...@tandetnik.org] Sent: Thursday, August 22, 2013 5:26 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] BETWEEN and explicit

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

PM To: sqlite-users@sqlite.org Subject: 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 <i...@tandetnik.org> wrote: >> [snip] > > I pretty much agree with everything you wrote there.

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] 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] BETWEEN and explicit collation assignment

On Wed, 21 Aug 2013 20:26:30 +0100 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 the documention says that something like this > >

Re: [sqlite] BETWEEN and explicit collation assignment

On 8/21/2013 3:26 PM, 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 the documention says that something like this "ABC" COLLATE COL1 = "3F" COLLATE COL2

Re: [sqlite] BETWEEN and explicit collation assignment

On 21 Aug 2013, at 8:22pm, Igor Tandetnik wrote: > On 8/21/2013 2:55 PM, Simon Slavin wrote: >> >> On 21 Aug 2013, at 5:02pm, Igor Tandetnik wrote: >> >>> I imagine you'd still want to be able to put COLLATE clause on the column >>> definition, as in

Re: [sqlite] BETWEEN and explicit collation assignment

On 8/21/2013 2:55 PM, Simon Slavin wrote: On 21 Aug 2013, at 5:02pm, Igor Tandetnik wrote: I imagine you'd still want to be able to put COLLATE clause on the column definition, as in "create table t (x collate NOCASE);". How is this supposed to work in your hypothetical

Re: [sqlite] BETWEEN and explicit collation assignment

On 21 Aug 2013, at 5:02pm, Igor Tandetnik wrote: > I imagine you'd still want to be able to put COLLATE clause on the column > definition, as in "create table t (x collate NOCASE);". How is this supposed > to work in your hypothetical new world? It works the same as it

Re: [sqlite] BETWEEN and explicit collation assignment

On 08/21/2013 11:02 PM, Igor Tandetnik wrote: On 8/21/2013 11:17 AM, Simon Slavin wrote: Given that this level of implementation is correct I feel that any error in SQLite's handling of COLLATE is closer to the expression parsing level than the low level implementation. The low level

Re: [sqlite] BETWEEN and explicit collation assignment

On 8/21/2013 11:17 AM, Simon Slavin wrote: Given that this level of implementation is correct I feel that any error in SQLite's handling of COLLATE is closer to the expression parsing level than the low level implementation. The low level implementation is fine. The problem would appear to

Re: [sqlite] BETWEEN and explicit collation assignment

Problem --- I feel that Clemens has brought up an important point, but I feel that rather than notice a specific error in the implementation of BETWEEN he has highlighted a conceptual error in SQLite. Consider this comparison: "Albert" > "albert" It would be inappropriate to