Re: [sqlite] Query problems

[Keith Medcalf]

> 1.  If you define a column with NOCASE and later an index without, it
> won't be possible to insert two values differing only by case,
> because the column will reject it.

Of course it will accept the value, unless you declared the column unique so 
that a unique index is created using the nocase collation.  Whether or not the 
index with the binary collation is unique is irrelevant since the two values 
are different, as far as that index is concerned.

> 2.  If you define a column with default collation and later an index
> with NOCASE (successfully, because at the time of creation no two
> values differed only by case) then henceforward it *also* won't be
> possible to insert two values differing only by case, because the
> index will reject it.

This would be true only if the index created with collation nocase were a 
unique index.  Otherwise, it would not care that you inserted rows with 
duplicate case-folded values.

> I suppose the current state of affairs -- where indexes have collation
> properties independent of the columns they index -- is more
> historical anomaly than design.  It's hard to see a reason to carry it
> forward in SQLite4.

With this I agree but generally because I do not see any purpose in having 
multiple collations apply to the same attribute under different circumstances.  
Perhaps there may be circumstances where it is useful, but I have never ever 
seen one.




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

Re: [sqlite] Query problems

[James K. Lowden]

On Tue, 3 Sep 2013 23:50:09 +0200
Eduardo Morras  wrote:

> Don't know if column collation overrides index collation or viceversa.

It's probably simpler to think of them as two things, table and index.
Neither "overrides" the other.  

Ideally, they use the same collation.  In the event not, I would think
insertion would fail for any row not meeting both criteria.  That is,

1.  If you define a column with NOCASE and later an index without, it
won't be possible to insert two values differing only by case,
because the column will reject it.  

2.  If you define a column with default collation and later an index
with NOCASE (successfully, because at the time of creation no two
values differed only by case) then henceforward it *also* won't be
possible to insert two values differing only by case, because the
index will reject it.  

I suppose the current state of affairs -- where indexes have collation
properties independent of the columns they index -- is more
historical anomaly than design.  It's hard to see a reason to carry it
forward in SQLite4.  

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

Re: [sqlite] Query problems

[Eduardo Morras]

On Mon, 2 Sep 2013 13:48:02 +0100
Simon Slavin  wrote:

> 
> On 2 Sep 2013, at 8:25am, Eduardo Morras  wrote:
> 
> > Or create the index with collate
> > 
> > CREATE INDEX idx_collated_column ON myTable ( column COLLATE NOCASE )
> 
> The problem with doing it in the index is that it's hard to predict when 
> SQLite will use a particular index. Better to think about the nature of your 
> data when you create the table.

But if you define it at table/column level, the collate will be applied to all 
operations that use the column, like group by or may altere other indexes 
(making "aAa" and "aaa" equal column has less distinct values and index stats 
aren't real ). Don't know if column collation overrides index collation or 
viceversa.

I see it as one of the reduced list of cases where developer should send hints 
or suggestions (mafia style) to sqlite to use a defined index, if more than one 
is defined on same columns with different collation.

> 
> On the other hand if you have already created your table and have lots of 
> foreign keys it can be a pain to try to reconstruct your data by DROPping and 
> recreating tables.  It may be easier just to add a new index.
> 
> Simon.

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

Re: [sqlite] Query problems

[Joseph L. Casale]

> Plus, of course, index will only ever be used for operations where you have
> overridden the default collating sequence for the operation, for example by
> specifying collate nocase in the join expression, or adding the collate 
> nocase to
> the order by or group by.

I assume this explains why the change in the table def made a difference from
not specifying the collation whereas the index did. I did not override the 
default
of the table in the query so the index was not used.

I've encountered another issue as I was running my tests in sqlitestudio when I
realized the query against the tables with the collation specified returned all 
rows
in less than a minute. Running the query against the db in the sqlite shell is 
still bad.
I know sqlitestudio enables certain non-default pragmas, but I wonder which ones
could result in this speed difference.

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

Re: [sqlite] Query problems

[Keith Medcalf]

Plus, of course, index will only ever be used for operations where you have 
overridden the default collating sequence for the operation, for example by 
specifying collate nocase in the join expression, or adding the collate nocase 
to the order by or group by.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Monday, 2 September, 2013 06:48
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Query problems
> 
> 
> On 2 Sep 2013, at 8:25am, Eduardo Morras <emorr...@yahoo.es> wrote:
> 
> > Or create the index with collate
> >
> > CREATE INDEX idx_collated_column ON myTable ( column COLLATE NOCASE )
> 
> The problem with doing it in the index is that it's hard to predict when
> SQLite will use a particular index. Better to think about the nature of
> your data when you create the table.
> 
> On the other hand if you have already created your table and have lots
> of foreign keys it can be a pain to try to reconstruct your data by
> DROPping and recreating tables.  It may be easier just to add a new
> index.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Query problems

[Simon Slavin]

On 2 Sep 2013, at 8:25am, Eduardo Morras  wrote:

> Or create the index with collate
> 
> CREATE INDEX idx_collated_column ON myTable ( column COLLATE NOCASE )

The problem with doing it in the index is that it's hard to predict when SQLite 
will use a particular index. Better to think about the nature of your data when 
you create the table.

On the other hand if you have already created your table and have lots of 
foreign keys it can be a pain to try to reconstruct your data by DROPping and 
recreating tables.  It may be easier just to add a new index.

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

Re: [sqlite] Query problems

[Eduardo Morras]

On Mon, 2 Sep 2013 02:12:05 +0100
Simon Slavin  wrote:

> 
> On 2 Sep 2013, at 2:03am, Joseph L. Casale  wrote:
> 
> > I am using LIKE as the columns are indexed NOCASE and I need the
> > comparison case insensitive.
> 
> Have you tried using '=' ?
> 
> Also if you declare the columns as COLLATE NOCASE in your table definition, 
> then using '=' will definitely work the way you want it to.  An example would 
> be
> 
> CREATE TABLE myTable (myName TEXT COLLATE NOCASE)

Or create the index with collate

CREATE INDEX idx_collated_column ON myTable ( column COLLATE NOCASE )

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


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

Re: [sqlite] Query problems

[Joseph L. Casale]

> Have you tried using '=' ?
> 
> Also if you declare the columns as COLLATE NOCASE in your table definition,
> then using '=' will definitely work the way you want it to.  An example would 
> be
> 
> CREATE TABLE myTable (myName TEXT COLLATE NOCASE)

Simon,
That took this query from not finishing in 5 hours to producing results in 
under a
minute, many thanks for everyone's guidance!

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

Re: [sqlite] Query problems

[Joseph L. Casale]

> > 0   0   1   SCAN TABLE d_table_b AS da (~10 rows)
> >
> 
> Is this the index you referenced in you reply to Simon?
> Maybe you are using wrong index/column?

I'll recheck, I am also reading up on indexes as they relate to optimizing
queries. Could be I made a mistake.

> I had the same problem (kind of) and got the answer here to create a
> different index...
> 
> Thank you.
> 
> Can you post you schema?

Sure, it's not mine technically so I have to sanitize portions.

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

Re: [sqlite] Query problems

[Joseph L. Casale]

> LIKE is used when comparing strings with wildcards.  For example, val LIKE
> 'abra%' (which will match 'abraCaDAbra' and 'abrakadee'.
> 
> If there are no wildcards you should be using =, not LIKE.  LIKE will/should
> always indicate that a table or index scan is required, perhaps of the whole
> table/index if the like expression is not a constant (there is no other 
> choice since
> the wildcarded expression could evaluate to '%d%' which would return every
> row with a 'd' anywhere in the value.  This means that the query planner must
> assume that this join will require a full table/index scan for each 
> inner-loop and
> may return all rows because no other plan assumption would be valid.  This 
> will
> result in really crappy performance.
> 
> Are the columns declared as COLLATE NOCASE, or just the index?  If just the
> index, why?

Was just the index as I didn't know better, but its corrected now.

> If there is some (really strange) reason why the table column is not declared
> with COLLATE NOCASE, then you can always override the collation of the
> column in the expression itself:
> 
> CollateBinaryColumn COLLATE NOCASE =
> SomeOtherColumnCollationDoesNotMatter

This insight is much appreciated, thanks!
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query problems

[Igor Korot]

Hi, Joseph,

On Sun, Sep 1, 2013 at 6:21 PM, Joseph L. Casale
wrote:

> > Hi,
> > Can you do "DESCRIBE QUERY PLAN " and post results here?
> >
> > Also, what do you mean by "unbearable at scale"? Did you measure it? What
> > is the result?
> >
> > Thank you.
>
> It doesn't finish with maybe 4 or 5 hours run time.
>
> Sorry, do you mean "explain query plan ..."?
> 0   0   1   SCAN TABLE d_table_b AS da (~10 rows)
>

Is this the index you referenced in you reply to Simon?
Maybe you are using wrong index/column?

I had the same problem (kind of) and got the answer here to create a
different index...

Thank you.

Can you post you schema?

> 0   1   3   SEARCH TABLE d_table_a AS d USING INTEGER PRIMARY
> KEY (rowid=?) (~1 rows)
> 0   2   0   SEARCH TABLE s_table_b AS sa USING AUTOMATIC
> COVERING INDEX (key=?) (~7 rows)
> 0   3   2   SEARCH TABLE s_table_a AS s USING INTEGER PRIMARY
> KEY (rowid=?) (~1 rows)
>
> Thanks,
> jlc
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query problems

[Joseph L. Casale]

> Have you tried using '=' ?
> 
> Also if you declare the columns as COLLATE NOCASE in your table definition,
> then using '=' will definitely work the way you want it to.  An example would 
> be
> 
> CREATE TABLE myTable (myName TEXT COLLATE NOCASE)
> 
> Simon.

I did and it excluded the comparisons whose case only differed, I only defined
COLLATE NOCASE in the index so I guess it wasn't being used.

I just changed the table defs to use this and am reloading the data.

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

Re: [sqlite] Query problems

[Keith Medcalf]

> I am using LIKE as the columns are indexed NOCASE and I need the
> comparison case insensitive. I suspect this is where is breaks down
> but I don't know enough sql to really appreciate the ways I could
> approach this better.

LIKE is used when comparing strings with wildcards.  For example, val LIKE 
'abra%' (which will match 'abraCaDAbra' and 'abrakadee'.  

If there are no wildcards you should be using =, not LIKE.  LIKE will/should 
always indicate that a table or index scan is required, perhaps of the whole 
table/index if the like expression is not a constant (there is no other choice 
since the wildcarded expression could evaluate to '%d%' which would return 
every row with a 'd' anywhere in the value.  This means that the query planner 
must assume that this join will require a full table/index scan for each 
inner-loop and may return all rows because no other plan assumption would be 
valid.  This will result in really crappy performance.

Are the columns declared as COLLATE NOCASE, or just the index?  If just the 
index, why?

If there is some (really strange) reason why the table column is not declared 
with COLLATE NOCASE, then you can always override the collation of the column 
in the expression itself:

CollateBinaryColumn COLLATE NOCASE = SomeOtherColumnCollationDoesNotMatter





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

Re: [sqlite] Query problems

[Joseph L. Casale]

> Hi,
> Can you do "DESCRIBE QUERY PLAN " and post results here?
> 
> Also, what do you mean by "unbearable at scale"? Did you measure it? What
> is the result?
> 
> Thank you.

It doesn't finish with maybe 4 or 5 hours run time.

Sorry, do you mean "explain query plan ..."?
0   0   1   SCAN TABLE d_table_b AS da (~10 rows)
0   1   3   SEARCH TABLE d_table_a AS d USING INTEGER PRIMARY KEY 
(rowid=?) (~1 rows)
0   2   0   SEARCH TABLE s_table_b AS sa USING AUTOMATIC COVERING 
INDEX (key=?) (~7 rows)
0   3   2   SEARCH TABLE s_table_a AS s USING INTEGER PRIMARY KEY 
(rowid=?) (~1 rows)

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

Re: [sqlite] Query problems

[Simon Slavin]

On 2 Sep 2013, at 2:03am, Joseph L. Casale  wrote:

> I am using LIKE as the columns are indexed NOCASE and I need the
> comparison case insensitive.

Have you tried using '=' ?

Also if you declare the columns as COLLATE NOCASE in your table definition, 
then using '=' will definitely work the way you want it to.  An example would be

CREATE TABLE myTable (myName TEXT COLLATE NOCASE)

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

Re: [sqlite] Query problems

[Igor Korot]

Hi,
Can you do "DESCRIBE QUERY PLAN " and post results here?

Also, what do you mean by "unbearable at scale"? Did you measure it? What
is the result?

Thank you.

On Sun, Sep 1, 2013 at 6:03 PM, Joseph L. Casale
wrote:

> I have a query that is unbearable at scale, for example when
> s_table_a and s_table_b have 70k and 1.25M rows.
>
> SELECT s.id AS s_id
>,s.lid AS s_lid
>,sa.val AS s_sid
>,d.id AS d_id
>,d.lid AS d_lid
>   FROM s_table_b sa
>   JOIN d_table_b da ON
>(
>  da.key=sa.key
>  AND da.key='unique_string'
>  AND da.val LIKE sa.val
>)
>   JOIN s_table_a s ON
>s.id=sa.id
>   JOIN d_table_a d ON
>(
>  d.id=da.id
>  AND NOT d.lid LIKE s.lid
>)
>
> I am using LIKE as the columns are indexed NOCASE and I need the
> comparison case insensitive. I suspect this is where is breaks down
> but I don't know enough sql to really appreciate the ways I could
> approach this better.
>
> Both {s|d}_table_a have 2 columns, id, lid where id is PK.
> Both {s|d}_table_b have 4 columns, seqno, id, key, val where seqno is PK,
> id is a FK ref to {s|d}_table_a.id, and several key/val pairs are
> inserted to correspond
> to the associated PK id from {s|d}_table_a.
>
> I'd be grateful for any suggestions or hints to improve this.
> Thanks,
> jlc
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query problems

[Joseph L. Casale]

I have a query that is unbearable at scale, for example when
s_table_a and s_table_b have 70k and 1.25M rows.

SELECT s.id AS s_id
   ,s.lid AS s_lid
   ,sa.val AS s_sid
   ,d.id AS d_id
   ,d.lid AS d_lid
  FROM s_table_b sa
  JOIN d_table_b da ON
   (
 da.key=sa.key
 AND da.key='unique_string'
 AND da.val LIKE sa.val
   )
  JOIN s_table_a s ON
   s.id=sa.id
  JOIN d_table_a d ON
   (
 d.id=da.id
 AND NOT d.lid LIKE s.lid
   )

I am using LIKE as the columns are indexed NOCASE and I need the
comparison case insensitive. I suspect this is where is breaks down
but I don't know enough sql to really appreciate the ways I could
approach this better.

Both {s|d}_table_a have 2 columns, id, lid where id is PK.
Both {s|d}_table_b have 4 columns, seqno, id, key, val where seqno is PK,
id is a FK ref to {s|d}_table_a.id, and several key/val pairs are inserted to 
correspond
to the associated PK id from {s|d}_table_a.

I'd be grateful for any suggestions or hints to improve this.
Thanks,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Problems - Keyword? Encoding?

[Eric Anderson]

On Thu, Sep 29, 2011 at 8:42 PM, David Garfield
 wrote:
> If the value in key is a blob, then like matches it and = does not.

Thanks. This suggestion helped me track down the issue. I was actually
going through ActiveRecord (the ORM for Ruby on Rails). Was upgrading
my app for Ruby 1.9 and my unit tests were not passing. Your
suggestion lead me to the following page which gave me the solution:

http://stackoverflow.com/questions/7437944/sqlite3-varchar-matching-with-like-but-not

-- 
http://saveyourcall.com - Easily record phone calls from any phone
http://pixelwareinc.com - Atlanta-based web development and design
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Problems - Keyword? Encoding?

[Jay A. Kreibich]

On Thu, Sep 29, 2011 at 08:32:04PM -0400, Eric Anderson scratched on the wall:
> The below statement returns records:
> 
> SELECT * FROM recordings WHERE "key" LIKE
> '4df0247ce1a97685a782d2cb051b48ed952e666c';
> 
> But this one does not:
> 
> SELECT * FROM recordings WHERE "key" =
> '4df0247ce1a97685a782d2cb051b48ed952e666c';

> The only thing I can think of is some sort of encoding
> issue that the LIKE operator is getting around somehow. Or perhaps the
> fact that it is a keyword?

  = is case sensitive, LIKE is not (by default).

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Problems - Keyword? Encoding?

[David Garfield]

If the value in key is a blob, then like matches it and = does not.

Because like has to do a string conversion on key, it also doesn't use
the index.

Try:

SELECT * FROM recordings WHERE "key" =
cast('4df0247ce1a97685a782d2cb051b48ed952e666c' as blob);

Or try inserting the key as text in the first place.

Or use a binary blob instead of a hex dump of it, and use (note the
"x"):

SELECT * FROM recordings WHERE "key" =
x'4df0247ce1a97685a782d2cb051b48ed952e666c';

--David Garfield

Eric Anderson writes:
> The below statement returns records:
> 
> SELECT * FROM recordings WHERE "key" LIKE
> '4df0247ce1a97685a782d2cb051b48ed952e666c';
> 
> But this one does not:
> 
> SELECT * FROM recordings WHERE "key" =
> '4df0247ce1a97685a782d2cb051b48ed952e666c';
> 
> The only difference is that = and LIKE have been swapped.
> 
> I realize "key" is a keyword but it is quoted properly from what I can
> tell. I'm on sqlite version 3.7.4 (standard one with Mint 11 Linux).
> Any ideas? The only thing I can think of is some sort of encoding
> issue that the LIKE operator is getting around somehow. Or perhaps the
> fact that it is a keyword?
> 
> Any ideas on how to debug?
> 
> -- 
> http://saveyourcall.com - Easily record phone calls from any phone
> http://pixelwareinc.com - Atlanta-based web development and design
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Query Problems - Keyword? Encoding?

[Eric Anderson]

The below statement returns records:

SELECT * FROM recordings WHERE "key" LIKE
'4df0247ce1a97685a782d2cb051b48ed952e666c';

But this one does not:

SELECT * FROM recordings WHERE "key" =
'4df0247ce1a97685a782d2cb051b48ed952e666c';

The only difference is that = and LIKE have been swapped.

I realize "key" is a keyword but it is quoted properly from what I can
tell. I'm on sqlite version 3.7.4 (standard one with Mint 11 Linux).
Any ideas? The only thing I can think of is some sort of encoding
issue that the LIKE operator is getting around somehow. Or perhaps the
fact that it is a keyword?

Any ideas on how to debug?

-- 
http://saveyourcall.com - Easily record phone calls from any phone
http://pixelwareinc.com - Atlanta-based web development and design
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users