Re: [sqlite] Sqlite support for UCLINUX

On 15 Aug 2013, at 5:42am, Ajazur Rahaman wrote: > After getting executables When we try to run it on our Board,We are getting > Error message that "DATABASE IS LOCKED". This suggests that your compilation of SQLite has no problems and that your problem comes when you

[sqlite] Sqlite support for UCLINUX

Dear sir, Do we have sqlite support for UCLINUX Kernel Version 2.6.38 ? If it is ,from which version it is supported. We are trying to compile " sqlite-3.6.12 autoconf " code to get executables so as to run it on our Board which has no support for MMU(Memory Management Unit). Below is the

Re: [sqlite] name resolutionn in GROUP BY

On Wed, 14 Aug 2013 14:57:19 -0500 "Marc L. Allen" wrote: > I'd actually like a compromise. Allow GROUP BY to accept a derived > name if no base name exists. I realize that's against spec, but > there's no ambiguity (as it otherwise errors out), It would also

Re: [sqlite] name resolutionn in GROUP BY

On Thu, Aug 15, 2013 at 1:12 AM, Peter Aronson wrote: > While I can certainly see the value of going with what PostgreSQL and SQL > Server do on the ORDER BY issue, I have to say that I suspect that Oracle's > behavior here seem more in line Principle of Least Astonishment.

Re: [sqlite] name resolutionn in GROUP BY

While I can certainly see the value of going with what PostgreSQL and SQL Server do on the ORDER BY issue, I have to say that I suspect that Oracle's behavior here seem more in line Principle of Least Astonishment. First, because ORDER BY generally works on the resultant relation, and second,

Re: [sqlite] Database locking without locking database file

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 14/08/13 06:06, Ralf Ramsauer wrote: > Neither cifs. I worked on a CIFS server (visionfs)[1]. They are a convoluted complicated mess. During the OLE2 era, Microsoft's apps abused locking as a means of inter-process communication. It got very

Re: [sqlite] Difference in pragma table_info between tables and views

On Wed, 14 Aug 2013 10:03:53 +0200, Thomas Krueger wrote: >Hi All, > >I noticed a difference in the output that pragma table_info gives for >tables and views. It seems, that not null conditions aren't properly >returned for views: > >create table atab ( id int not null

Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 5:02 PM, Peter Aronson wrote: > If I understand Dominique's post, Oracle works like SQLite 3.7.15 as > well. Things only got confusing when we moved from discussing GROUP BY to > discussing ORDER BY for some reason. > There are two separate (though

Re: [sqlite] name resolutionn in GROUP BY

If I understand Dominique's post, Oracle works like SQLite 3.7.15 as well.  Things only got confusing when we moved from discussing GROUP BY to discussing ORDER BY for some reason. From: Richard Hipp >To: General Discussion of SQLite Database >Sent:

Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn wrote: > > The behaviour of Sqlite of w.r.t. name resolving in "group by" caluses > seems to have changed in the latest version > Two new tickets have been entered: http://www.sqlite.org/src/info/1c69be2daf

Re: [sqlite] name resolution in GROUP BY

On 14 Aug 2013, at 7:13pm, Peter Aronson wrote: > I dug out my copy of THE GUIDE TO THE SQL STANDARD, 4th Edition, by Date and > Darwen, and it states (in a footnote on page 151) that name specified for a > scalar-expression in a SELECT clause can not be used in a WHERE,

Re: [sqlite] name resolutionn in GROUP BY

I'd actually like a compromise. Allow GROUP BY to accept a derived name if no base name exists. I realize that's against spec, but there's no ambiguity (as it otherwise errors out), and does make it much nicer when the derived column is a hairy expression that I end up needing to replicate

Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 3:08 PM, Igor Tandetnik wrote: > > Most DBMS allow sorting (and grouping) by arbitrary expressions, which > means that the standard is not directly applicable. One has to extrapolate. > PostgreSQL, MS-SQL, and SQLite 3.7.15 work one way. Oracle and

Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 3:08 PM, Igor Tandetnik wrote: > Note that "ORDER BY lower(m)" is not valid SQL-92. The standard only > allows sorting by columns that appear in the SELECT clause, referenced by > name or by ordinal. It doesn't allow sorting by arbitrary expressions,

Re: [sqlite] name resolution in GROUP BY

Peter, I'm sorry.. you're correct. I missed that. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Aronson Sent: Wednesday, August 14, 2013 2:53 PM To: General Discussion of SQLite Database Subject: Re: [sqlite]

Re: [sqlite] name resolutionn in GROUP BY

Note that "ORDER BY lower(m)" is not valid SQL-92. The standard only allows sorting by columns that appear in the SELECT clause, referenced by name or by ordinal. It doesn't allow sorting by arbitrary expressions, nor even by columns that appear in the underlying tables but are not selected.

Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 8:42 PM, Dominique Devienne wrote: > On Wed, Aug 14, 2013 at 8:03 PM, Richard Hipp wrote: > >> On Wed, Aug 14, 2013 at 1:59 PM, Dominique Devienne > >wrote: >> > Not authoritative of course, but Oracle seems to

Re: [sqlite] name resolution in GROUP BY

Except the quote I provided said nothing about ORDER BY, just WHERE, GROUP BY or HAVING clauses.  So I'm not sure what all tests with ORDER BY are demonstrating, since the original question was about GROUP BY, which is a different thing, since ORDER BY operates strictly on the derived table.  

Re: [sqlite] Database locking without locking database file

On 14.08.2013 18:36, Stephan Beal wrote: > On Wed, Aug 14, 2013 at 3:06 PM, Ralf Ramsauer < > ralf+sql...@ramses-pyramidenbau.de> wrote: > >> Why is it proposed not to use NFS? Why is it so risky? I can hardly >> believe that NFS locking is that broken... >> > Few of us can believe it, but many of

Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 8:03 PM, Richard Hipp wrote: > On Wed, Aug 14, 2013 at 1:59 PM, Dominique Devienne >wrote: > > Not authoritative of course, but Oracle seems to agree with the > previous behavior. --DD > > Dominique, can you please try the following

Re: [sqlite] name resolutionn in GROUP BY

This appears to be how MS SQL handles it... looking at the definitions below, MS SQL uses the base value in GROUP BY and the derived value in ORDER BY. That said, 'lower(m)' referenced the base m, not the derived m in the ORDER BY. I'm afraid I don't understand enough about COLLATE to get

Re: [sqlite] name resolution in GROUP BY

Ok... looks like MSSQL 2008 R2 picks a different value of 'm' for cases 1 and 2. I'm not sure why 3 isn't the same as 1, though. CREATE TABLE #t1(m VARCHAR(4)); INSERT INTO #t1 VALUES('az'); INSERT INTO #t1 VALUES('by'); INSERT INTO #t1 VALUES('cx'); SELECT '1', right(m,1) AS m FROM #t1

Re: [sqlite] name resolution in GROUP BY

I understand. My previous email had the values of your original request. This email was in response to Peter who found a reference that you could not use derived names in a ORDER BY clause. -Original Message- From: sqlite-users-boun...@sqlite.org

Re: [sqlite] name resolutionn in GROUP BY

On 8/14/2013 12:59 PM, Richard Hipp wrote: On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn wrote: create table test(name); insert into test values (NULL); insert into test values ('abc'); select count(), NULLIF(name,'abc') AS name from test group by

Re: [sqlite] name resolution in GROUP BY

On Wed, Aug 14, 2013 at 2:18 PM, Marc L. Allen wrote: > Heh... I forgot.. both selects below are identical, as 'lower(m1)' is > incorrect. MS SQL does not permit further operations on the derived value. > I think you also missed the name ambiguity issue. The

Re: [sqlite] name resolution in GROUP BY

Heh... I forgot.. both selects below are identical, as 'lower(m1)' is incorrect. MS SQL does not permit further operations on the derived value. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marc L. Allen Sent: Wednesday,

Re: [sqlite] name resolution in GROUP BY

I seem to recall having read that as well. I believe, however, that MySQL does allow it, but I think it defaults to base table when available. Also, a modified form of the test case: DROP TABLE #t1 CREATE TABLE #t1(m VARCHAR(4)); INSERT INTO #t1 VALUES('az'); INSERT INTO #t1 VALUES('by');

Re: [sqlite] name resolution in GROUP BY

I dug out my copy of THE GUIDE TO THE SQL STANDARD, 4th Edition, by Date and Darwen, and it states (in a footnote on page 151) that name specified for a scalar-expression in a SELECT clause can not be used in a WHERE, GROUP BY or HAVING clause as it is a column in the derived table, not the

Re: [sqlite] name resolutionn in GROUP BY

As does MS SQL 2008 R2 DROP TABLE #Test CREATE TABLE #Test ( Val int ) INSERT INTO [#Test] ([Val]) VALUES (-2), (2) SELECT Val FROM #Test GROUP BY Val SELECT ABS(Val) AS Val FROM #Test GROUP BY Val Val --- -2 2 Val --- 2 2 Your requested test case: Untitled1 m - - 1 x 1

Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 1:59 PM, Dominique Devienne wrote: > > Not authoritative of course, but Oracle seems to agree with the previous > behavior. --DD > Dominique, can you please try the following SQL on Oracle and let me know what you get: CREATE TABLE t1(m VARCHAR(4));

Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 6:59 PM, Richard Hipp wrote: > On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn > wrote: > > > create table test(name); > > insert into test values (NULL); > > insert into test values ('abc'); > > > > select count(), > >

Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn wrote: > create table test(name); > insert into test values (NULL); > insert into test values ('abc'); > > select count(), >NULLIF(name,'abc') AS name > from test > group by lower(name); > So the question is,

Re: [sqlite] Database locking without locking database file

On 14 Aug 2013, at 5:36pm, Stephan Beal wrote: > On Wed, Aug 14, 2013 at 3:06 PM, Ralf Ramsauer < > ralf+sql...@ramses-pyramidenbau.de> wrote: > >> Why is it proposed not to use NFS? Why is it so risky? I can hardly >> believe that NFS locking is that broken... > > Few

Re: [sqlite] Database locking without locking database file

On Wed, Aug 14, 2013 at 3:06 PM, Ralf Ramsauer < ralf+sql...@ramses-pyramidenbau.de> wrote: > Why is it proposed not to use NFS? Why is it so risky? I can hardly > believe that NFS locking is that broken... > Few of us can believe it, but many of us have had horrible experiences (regardless of

[sqlite] name resolutionn in GROUP BY

Hi List,   The behaviour of Sqlite of w.r.t. name resolving in "group by" caluses seems to have changed in the latest version. This might lead to errors in previously working SQL code, or worse, undetected changes in behaviour. Example create table test(name); select min(name) from

Re: [sqlite] Database locking without locking database file

Hi, thanks a lot for your answer. On 14.08.2013 13:52, Richard Hipp wrote >> Now my Question: >> According to [4], it seems that NFS also has problems with sharing >> locks. So why does Sqlite make use of file locks instead of writing >> those locks (including a timestamp for expiration) inside

Re: [sqlite] Database locking without locking database file

On Tue, Aug 13, 2013 at 11:35 AM, Ralf Ramsauer wrote: > Now my Question: > According to [4], it seems that NFS also has problems with sharing > locks. So why does Sqlite make use of file locks instead of writing > those locks (including a timestamp for expiration)

[sqlite] Database locking without locking database file

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, the FAQ of qemu describes Sqlite to be threadsafe [1]. The "threadsafeness" can e.g. be chosen at compiletime [2]. If several Sqlite instances try to access the same database file on a system, the database file gets locked via simple file locks

[sqlite] Difference in pragma table_info between tables and views

Hi All, I noticed a difference in the output that pragma table_info gives for tables and views. It seems, that not null conditions aren't properly returned for views: create table atab ( id int not null primary key, withnulls text, withoutnulls text NOT NULL ); create view aview as select * from