Re: [sqlite] Collation with concatenation

[Dan Kennedy]

On 12/15/2011 11:59 PM, Jeff Matthews wrote:

This is in the documentation:



CREATE TABLE t1(
 x INTEGER PRIMARY KEY,
 a, /* collating sequence BINARY */
 b COLLATE BINARY,  /* collating sequence BINARY */
 c COLLATE RTRIM,   /* collating sequence RTRIM  */
 d COLLATE NOCASE   /* collating sequence NOCASE */
);


/* x   a b c   d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc');

/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4

The above example is intuitive to me.

But what about this one?

/* Grouping is performed using the BINARY collating sequence.  'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2

First, if d is collated on NOCASE, then, why does the second operation use
BINARY?   Is it because of this rule as stated in the documentation:   "The
collating sequence set by the COLLATE operator overrides the collating
sequence determined by the COLLATE clause in a table column definition
  ."


It works the same way as ORDER BY:

  "If the expression is not a column and has no COLLATE clause, then
   the BINARY collating sequence is used"

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Collation with concatenation

[Jeff Matthews]

This is in the documentation:

 

CREATE TABLE t1(
x INTEGER PRIMARY KEY,
a, /* collating sequence BINARY */
b COLLATE BINARY,  /* collating sequence BINARY */
c COLLATE RTRIM,   /* collating sequence RTRIM  */
d COLLATE NOCASE   /* collating sequence NOCASE */
);
   
 
   /* x   a b c   d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc');
 
/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4
 
The above example is intuitive to me.
 
But what about this one?
 
/* Grouping is performed using the BINARY collating sequence.  'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2
 
First, if d is collated on NOCASE, then, why does the second operation use
BINARY?   Is it because of this rule as stated in the documentation:   "The
collating sequence set by the COLLATE operator overrides the collating
sequence determined by the COLLATE clause in a table column definition
 ." 
 

Second, was the example of adding concatenation of the ' ' supposed to have
any impact on the result other than showing that the collation is now done
on binary?   If I concatenate anything (e.g., 'bigcheeseburger'), it doesn't
make a difference.   So, I think the answer is that the concatenation
example is just to demonstrate that binary takes precedence over the
collation setting for the column.

 

Thanks for advice.

 

 

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users