subject:"\[sqlite\] SQL Crash with sqlite 3.6.22 commandline"

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

2010-01-22 Thread ve3meo

That's great! I'm an end-user and cannot compile it to check myself. Thanks 
to shane, drh, Hub Dog for the rapid fix. I will have to dig around to 
figure out how/when that shows up in a release.

That leaves the question about the change of syntax between 3.5.4 and 
3.6.17. In the earlier it was sufficient to override the unavailable 
collation with a COLLATE clause at the first instance of the field if it is 
to be later compared in a WHERE clause, e.g.:

SELECT  Name COLLATE NOCASE
FROM AddressTable
WHERE  Name LIKE '%_';

By release 3.6.17, it was necessary to put the COLLATE clause where the 
comparison is made, e.g.:

SELECT  Name
FROM AddressTable
WHERE  Name COLLATE NOCASE LIKE '%_';

3.5.4 was happy either way.

However, if this latter SELECT is a virtual table the subject of another 
SELECT stmt, then both 3.5.4 and 3.6.17+ throw up an error: "No such 
collation sequence: x" whereas 3.5.4 is happy if the first statement is 
the virtual table.

For both 3.5.4 and 3.6.17 to be happy, COLLATE NOCASE must be used twice:

SELECT * FROM
 (SELECT  Name COLLATE NOCASE
  FROM AddressTable
  WHERE  Name COLLATE NOCASE LIKE '%_');

OR the collated field must be renamed in the form of the first statement:

SELECT * FROM
 (SELECT  Name COLLATE NOCASE AS Nuts
  FROM AddressTable
  WHERE  Nuts LIKE '%_');

The ORDER BY clause operated differently in 3.5.4, requiring the COLLATE 
clause within, despite its earlier use in the SELECT expr. Leading to

SELECT  Name COLLATE NOCASE
FROM AddressTable
WHERE  Name LIKE '%_'
ORDER BY Name COLLATE NOCASE;
as sufficient for 3.5.4

SELECT  Name
FROM AddressTable
WHERE  Name COLLATE NOCASE LIKE '%_'
ORDER BY Name COLLATE NOCASE;
as necessary for 3.6.17+ and acceptable to 3.5.4

and these, as necessary for both:

SELECT * FROM
 (SELECT  Name COLLATE NOCASE
  FROM AddressTable
  WHERE  Name COLLATE NOCASE LIKE '%_'
  ORDER BY Name COLLATE NOCASE);

-- OR --

SELECT * FROM
 (SELECT   Name COLLATE NOCASE AS Nuts
   FROM  AddressTable
   WHERE   Nuts LIKE '%_'
   ORDER BY Nuts);

So it appears that the later versions are more rigorous than 3.5.4, having a 
consistent requirement for the collation override in any clause where 
comparison is made on a field with a missing, application dependent 
collation sequence, rather than an inferred override from a previous 
declaration.

The trap then is that 'lazy' overrides allowed in 3.5.4 will trigger a "No 
such collation sequence" in later versions. I think I prefer the 'lazy' 
override for both WHERE and ORDER BY but perhaps there is good reason to 
require more explicit COLLATION declarations.

Tom

"Hub Dog"  wrote in message 
news:4de0b9581001220719u72a3857eq88b674a14d265...@mail.gmail.com...
>I can confirm that the crash problem has been fixed by
> http://www.sqlite.org/src/info/1258875e07 checked-in .
> Now executing following sql will report no such collation sequence: 
> RMNOCASE
> instead of crash.



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

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

2010-01-22 Thread Hub Dog

I can confirm that the crash problem has been fixed by
http://www.sqlite.org/src/info/1258875e07 checked-in .
Now executing following sql will report no such collation sequence: RMNOCASE
instead of crash.

SELECT
  Surname || ', ' || Given COLLATE NOCASE AS Person ,
  Adr.Name COLLATE NOCASE AS AddressName ,
  Adr.Street1 ,
  Adr.Street2 ,
  Adr.City ,
  Adr.State
FROM
  NameTable AS Nam ,
  AddressLinkTable AS Lnk ,
  AddressTable AS Adr
WHERE
  Nam.OwnerID = Lnk.OwnerID AND Lnk.AddressID = Adr.AddressID AND
Adr.AddressType = 0 AND Adr.Name LIKE '%_'
/* Error message: no such collation sequence: RMNOCASE */

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

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

2010-01-22 Thread Shane Harrelson

Can you verify that changes for ticket
http://www.sqlite.org/src/info/1258875e07 checked-in yesterday resolve your
issue?

Thanks.
-Shane

On Fri, Jan 22, 2010 at 9:51 AM, ve3meo  wrote:

> I just discovered that attachments can be sent through this newsgroup so I
> have attached a small database with which you should be able to reproduce
> the problem. The one table in it has a field collated RMNOCASE. The
> following query produces these results in three different versions of
> sqlite:
>
> 3.5.4 works perfectly
> 3.6.17 gracefully reports an error - missing RMNOCASE collation
> 3.6.21 crash
>
> SELECT
>  Name COLLATE NOCASE
> FROM
>  AddressTable
> WHERE
>  Name LIKE '%_';
>
> 3.5.4 carried the COLLATE NOCASE override at the beginning of the SELECT
> through to the comparison in the WHEN. The later ones do not, and they
> 'progress' from reporting an error to a crash.
>
> This regression renders queries, on databases having collations unavailable
> to the sqlite in use, that were developed on older versions of sqlite
> problematic when run from newer versions.
>
> Regards,
> Tom
>
> "D. Richard Hipp"  wrote in
> message news:41371dfd-279f-429d-9186-476efb63e...@hwaci.com...
> >I am unable to reproduce this problem.  Using the script below, with
> > RMNOCASE changed to just NOCASE, everything works fine on the SQLite
> > command-line shell on the website on Linux.  I also tried various
> > other versions of SQLite with the same result.
> >
> >
> > On Jan 21, 2010, at 8:00 AM, Hub Dog wrote:
> >
> >> I hava a table. The table schema is
> >>
> >> CREATE TABLE AddressTable
> >> (
> >>  AddressID INTEGER PRIMARY KEY ,
> >>  AddressType INTEGER ,
> >>  Name TEXT COLLATE RMNOCASE ,
> >>  Street1 TEXT ,
> >>  Street2 TEXT ,
> >>  City TEXT ,
> >>  State TEXT ,
> >>  Zip TEXT ,
> >>  Country TEXT ,
> >>  Phone1 TEXT ,
> >>  Phone2 TEXT ,
> >>  Fax TEXT ,
> >>  Email TEXT ,
> >>  URL TEXT ,
> >>  Latitude INTEGER ,
> >>  Longitude INTEGER ,
> >>  Note BLOB
> >> ) ;.
> >>
> >> if I execute following sql to query data , the sqlite 3.6.22 command
> >> line
> >> downloaded from www.sqlite.org will crash.
> >>
> >> SELECT
> >>  Adr.Name COLLATE NOCASE AS AddressName
> >> FROM
> >>  AddressTable AS Adr
> >> WHERE
> >>  Adr.Name LIKE '%_'.
> >>
> >> if I change the Adr.Name to AddressName  , the sql execute result is
> >> ok.
> >>
> >> SELECT
> >>  Adr.Name COLLATE NOCASE AS AddressName
> >> FROM
> >>  AddressTable AS Adr
> >> WHERE
> >>  AddressName LIKE '%_' ;
> >>
> >> it seems the crash was related with the collate RMNOCASE of
> >> AddressTable
> >> table's field Name.
> >> in default sqlite command line, there is no rmnocase collation. so I
> >> mapped
> >> it to the default  nocase collation.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> > D. Richard Hipp
> > d...@hwaci.com
> >
> >
> >
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

2010-01-22 Thread ve3meo

I just discovered that attachments can be sent through this newsgroup so I 
have attached a small database with which you should be able to reproduce 
the problem. The one table in it has a field collated RMNOCASE. The 
following query produces these results in three different versions of 
sqlite:

3.5.4 works perfectly
3.6.17 gracefully reports an error - missing RMNOCASE collation
3.6.21 crash

SELECT
  Name COLLATE NOCASE
FROM
  AddressTable
WHERE
  Name LIKE '%_';

3.5.4 carried the COLLATE NOCASE override at the beginning of the SELECT 
through to the comparison in the WHEN. The later ones do not, and they 
'progress' from reporting an error to a crash.

This regression renders queries, on databases having collations unavailable 
to the sqlite in use, that were developed on older versions of sqlite 
problematic when run from newer versions.

Regards,
Tom

"D. Richard Hipp"  wrote in 
message news:41371dfd-279f-429d-9186-476efb63e...@hwaci.com...
>I am unable to reproduce this problem.  Using the script below, with
> RMNOCASE changed to just NOCASE, everything works fine on the SQLite
> command-line shell on the website on Linux.  I also tried various
> other versions of SQLite with the same result.
>
>
> On Jan 21, 2010, at 8:00 AM, Hub Dog wrote:
>
>> I hava a table. The table schema is
>>
>> CREATE TABLE AddressTable
>> (
>>  AddressID INTEGER PRIMARY KEY ,
>>  AddressType INTEGER ,
>>  Name TEXT COLLATE RMNOCASE ,
>>  Street1 TEXT ,
>>  Street2 TEXT ,
>>  City TEXT ,
>>  State TEXT ,
>>  Zip TEXT ,
>>  Country TEXT ,
>>  Phone1 TEXT ,
>>  Phone2 TEXT ,
>>  Fax TEXT ,
>>  Email TEXT ,
>>  URL TEXT ,
>>  Latitude INTEGER ,
>>  Longitude INTEGER ,
>>  Note BLOB
>> ) ;.
>>
>> if I execute following sql to query data , the sqlite 3.6.22 command
>> line
>> downloaded from www.sqlite.org will crash.
>>
>> SELECT
>>  Adr.Name COLLATE NOCASE AS AddressName
>> FROM
>>  AddressTable AS Adr
>> WHERE
>>  Adr.Name LIKE '%_'.
>>
>> if I change the Adr.Name to AddressName  , the sql execute result is
>> ok.
>>
>> SELECT
>>  Adr.Name COLLATE NOCASE AS AddressName
>> FROM
>>  AddressTable AS Adr
>> WHERE
>>  AddressName LIKE '%_' ;
>>
>> it seems the crash was related with the collate RMNOCASE of
>> AddressTable
>> table's field Name.
>> in default sqlite command line, there is no rmnocase collation. so I
>> mapped
>> it to the default  nocase collation.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 


begin 666 Address.db3

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

2010-01-21 Thread Tom Holden

The point is not how the table was created but rather that the absence of 
the RMNOCASE collation causes the query to crash the latest versions of 
sqlite while earlier versions gracefully report an error. Moreover, having 
saved a VIEW from this query resulted in these managers of later releases of 
sqlite (e.g. 3.6.21/22) reporting the access violation on opening the 
database. Go back far enough, to, say 3.5.4, and the query runs with no 
problem. I think that may have been where the VIEW was created.

So what is a working query and VIEW in 3.5.4, became syntactically an error 
by 3.6.17 and a crash by 3.6.21.

Tom

- Original Message - 
From: "Pavel Ivanov" <paiva...@gmail.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Thursday, January 21, 2010 9:36 AM
Subject: Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline


> I am unable to reproduce this problem. Using the script below, with
> RMNOCASE changed to just NOCASE

Probably that's exactly the point of crash in the OP's test case. He
created table when RMNOCASE collation existed but then tries to
execute query when that collation is not registered and unknown.



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

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

2010-01-21 Thread ve3meo

I originally experienced the problem using a couple of Windows sqlite 
managers. It seems that ones using the latest few versions of sqlite have 
the problem, variously reported as:
"Access violation at address x in module . Read of address 
0005", where x is dependent on the application. Of the three that I 
tested that had this crash, two reported it thusly, one threw up an 
unhandled Win32 exception and croaked - they are all, I believe using 3.6.21 
or 22.

Running the sqlite.exe command line version 3.6.22 throws up the unhandled 
Win32 exception.
Running the 3.6.17 release gracefully reports "SQL error near line 1: no 
such collation sequence: RMNOCASE".

The errors and crashes are from the following query:
SELECT
  Name COLLATE NOCASE
FROM
  AddressTable
WHERE
  Name LIKE '%_';

This revised query works on all versions listed above:
SELECT
  Name COLLATE NOCASE AS NewName
FROM
  AddressTable
WHERE
  NewName LIKE '%_' ;

as does:
SELECT
  Name
FROM
  AddressTable
WHERE
  Name COLLATE NOCASE LIKE '%_';

I can forward you a sample database but I do not have access to the RMNOCASE 
collation.

Tom

"D. Richard Hipp"  wrote in 
message news:41371dfd-279f-429d-9186-476efb63e...@hwaci.com...
>I am unable to reproduce this problem.  Using the script below, with
> RMNOCASE changed to just NOCASE, everything works fine on the SQLite
> command-line shell on the website on Linux.  I also tried various
> other versions of SQLite with the same result.
>
>
> On Jan 21, 2010, at 8:00 AM, Hub Dog wrote:
>
>> I hava a table. The table schema is
>>
>> CREATE TABLE AddressTable
>> (
>>  AddressID INTEGER PRIMARY KEY ,
>>  AddressType INTEGER ,
>>  Name TEXT COLLATE RMNOCASE ,
>>  Street1 TEXT ,
>>  Street2 TEXT ,
>>  City TEXT ,
>>  State TEXT ,
>>  Zip TEXT ,
>>  Country TEXT ,
>>  Phone1 TEXT ,
>>  Phone2 TEXT ,
>>  Fax TEXT ,
>>  Email TEXT ,
>>  URL TEXT ,
>>  Latitude INTEGER ,
>>  Longitude INTEGER ,
>>  Note BLOB
>> ) ;.
>>
>> if I execute following sql to query data , the sqlite 3.6.22 command
>> line
>> downloaded from www.sqlite.org will crash.
>>
>> SELECT
>>  Adr.Name COLLATE NOCASE AS AddressName
>> FROM
>>  AddressTable AS Adr
>> WHERE
>>  Adr.Name LIKE '%_'.
>>
>> if I change the Adr.Name to AddressName  , the sql execute result is
>> ok.
>>
>> SELECT
>>  Adr.Name COLLATE NOCASE AS AddressName
>> FROM
>>  AddressTable AS Adr
>> WHERE
>>  AddressName LIKE '%_' ;
>>
>> it seems the crash was related with the collate RMNOCASE of
>> AddressTable
>> table's field Name.
>> in default sqlite command line, there is no rmnocase collation. so I
>> mapped
>> it to the default  nocase collation.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] SQL Crash with sqlite 3.6.22 commandline

2010-01-21 Thread Pavel Ivanov

> I am unable to reproduce this problem.  Using the script below, with
> RMNOCASE changed to just NOCASE

Probably that's exactly the point of crash in the OP's test case. He
created table when RMNOCASE collation existed but then tries to
execute query when that collation is not registered and unknown.


Pavel

On Thu, Jan 21, 2010 at 9:30 AM, D. Richard Hipp  wrote:
> I am unable to reproduce this problem.  Using the script below, with
> RMNOCASE changed to just NOCASE, everything works fine on the SQLite
> command-line shell on the website on Linux.  I also tried various
> other versions of SQLite with the same result.
>
>
> On Jan 21, 2010, at 8:00 AM, Hub Dog wrote:
>
>> I hava a table. The table schema is
>>
>> CREATE TABLE AddressTable
>> (
>>  AddressID INTEGER PRIMARY KEY ,
>>  AddressType INTEGER ,
>>  Name TEXT COLLATE RMNOCASE ,
>>  Street1 TEXT ,
>>  Street2 TEXT ,
>>  City TEXT ,
>>  State TEXT ,
>>  Zip TEXT ,
>>  Country TEXT ,
>>  Phone1 TEXT ,
>>  Phone2 TEXT ,
>>  Fax TEXT ,
>>  Email TEXT ,
>>  URL TEXT ,
>>  Latitude INTEGER ,
>>  Longitude INTEGER ,
>>  Note BLOB
>> ) ;.
>>
>> if I execute following sql to query data , the sqlite 3.6.22 command
>> line
>> downloaded from www.sqlite.org will crash.
>>
>> SELECT
>>  Adr.Name COLLATE NOCASE AS AddressName
>> FROM
>>  AddressTable AS Adr
>> WHERE
>>  Adr.Name LIKE '%_'.
>>
>> if I change the Adr.Name to AddressName  , the sql execute result is
>> ok.
>>
>> SELECT
>>  Adr.Name COLLATE NOCASE AS AddressName
>> FROM
>>  AddressTable AS Adr
>> WHERE
>>  AddressName LIKE '%_' ;
>>
>> it seems the crash was related with the collate RMNOCASE of
>> AddressTable
>> table's field Name.
>> in default sqlite command line, there is no rmnocase collation. so I
>> mapped
>> it to the default  nocase collation.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] SQL Crash with sqlite 3.6.22 commandline

2010-01-21 Thread D. Richard Hipp

I am unable to reproduce this problem.  Using the script below, with  
RMNOCASE changed to just NOCASE, everything works fine on the SQLite  
command-line shell on the website on Linux.  I also tried various  
other versions of SQLite with the same result.


On Jan 21, 2010, at 8:00 AM, Hub Dog wrote:

> I hava a table. The table schema is
>
> CREATE TABLE AddressTable
> (
>  AddressID INTEGER PRIMARY KEY ,
>  AddressType INTEGER ,
>  Name TEXT COLLATE RMNOCASE ,
>  Street1 TEXT ,
>  Street2 TEXT ,
>  City TEXT ,
>  State TEXT ,
>  Zip TEXT ,
>  Country TEXT ,
>  Phone1 TEXT ,
>  Phone2 TEXT ,
>  Fax TEXT ,
>  Email TEXT ,
>  URL TEXT ,
>  Latitude INTEGER ,
>  Longitude INTEGER ,
>  Note BLOB
> ) ;.
>
> if I execute following sql to query data , the sqlite 3.6.22 command  
> line
> downloaded from www.sqlite.org will crash.
>
> SELECT
>  Adr.Name COLLATE NOCASE AS AddressName
> FROM
>  AddressTable AS Adr
> WHERE
>  Adr.Name LIKE '%_'.
>
> if I change the Adr.Name to AddressName  , the sql execute result is  
> ok.
>
> SELECT
>  Adr.Name COLLATE NOCASE AS AddressName
> FROM
>  AddressTable AS Adr
> WHERE
>  AddressName LIKE '%_' ;
>
> it seems the crash was related with the collate RMNOCASE of  
> AddressTable
> table's field Name.
> in default sqlite command line, there is no rmnocase collation. so I  
> mapped
> it to the default  nocase collation.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

[sqlite] SQL Crash with sqlite 3.6.22 commandline

2010-01-21 Thread Hub Dog

I hava a table. The table schema is

CREATE TABLE AddressTable
(
  AddressID INTEGER PRIMARY KEY ,
  AddressType INTEGER ,
  Name TEXT COLLATE RMNOCASE ,
  Street1 TEXT ,
  Street2 TEXT ,
  City TEXT ,
  State TEXT ,
  Zip TEXT ,
  Country TEXT ,
  Phone1 TEXT ,
  Phone2 TEXT ,
  Fax TEXT ,
  Email TEXT ,
  URL TEXT ,
  Latitude INTEGER ,
  Longitude INTEGER ,
  Note BLOB
) ;.

if I execute following sql to query data , the sqlite 3.6.22 command line
downloaded from www.sqlite.org will crash.

SELECT
  Adr.Name COLLATE NOCASE AS AddressName
FROM
  AddressTable AS Adr
WHERE
  Adr.Name LIKE '%_'.

if I change the Adr.Name to AddressName  , the sql execute result is ok.

SELECT
  Adr.Name COLLATE NOCASE AS AddressName
FROM
  AddressTable AS Adr
WHERE
  AddressName LIKE '%_' ;

it seems the crash was related with the collate RMNOCASE of AddressTable
table's field Name.
in default sqlite command line, there is no rmnocase collation. so I mapped
it to the default  nocase collation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

[sqlite] SQL Crash with sqlite 3.6.22 commandline

9 matches

Site Navigation

Mail list logo

Footer information