Re: [sqlite] Sqlite support for UCLINUX

[Simon Slavin]

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 call the SQLite API to make it do something.

On many embedded devices, the default path leads to a folder which is 
read-only.  So if you try to create a new SQLite database without specifying 
the path, it tries to create a new file in a read-only folder and, of course, 
fails.

Do  you get this error when you try to open a SQLite database ?  If so, are you 
providing a full path for the database file ?

If your problem is not with sqlite3_open() can you please tell us which call 
returns that error ?

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

[sqlite] Sqlite support for UCLINUX

[Ajazur Rahaman]

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 cross compiling procedure we are following to compile
sqlite-3.6.12

-->./configure CC= /m68k-uclinux/bin/m68k-uclinux-gcc
  --build=i686-pc-linux-gnu --host=m68k-uclinux
--prefix=/home/Aijas/Sqlite3 --disable-libtool-lock
--> make
--> sudo make install

After getting executables When we try to run it on our Board,We are getting
Error message that "DATABASE IS LOCKED".

I have already posted this issue on sqlite-mailing list.But still there is
no response.
http://www.mail-archive.com/sqlite-users@sqlite.org/msg78514.html

Can you please help us in resolving this issue.

Thanks & Regards,
Aijas Mohammed
+91-9642933393


-- 
Thanks & Regards,
Mohammed Aijas
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolutionn in GROUP BY

[James K. Lowden]

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 mean the query's meaning could change if the underlying
DDL changed.  If the column were later added (say, to a view) that
happened to have the same name as that of the alias in the query, the
DML would silently start using the "base" name instead of the derived
one.  

That's not normally the case in SQL.  Normally, adding a column to a
table/view cannot change the query's meaning.  Either the query is
unaffected, or the new column introduces ambiguity (in the presence of
a join) that causes it to return an error.  

The only safe and proper thing is for GROUP BY to refer to the column
names as known to the database, not to aliases mentioned in the query. 

> and does make it much nicer when the derived column is a hairy
> expression that I end up needing to replicate in the GROUP BY clause.

We do need a better language, yes.  

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

Re: [sqlite] name resolutionn in GROUP BY

[Stephan Beal]

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.


Another factor to consider here is "expected portability": it is much more
often to see apps which support sqlite/mysql/postgres combination than an
sqlite/oracle combination. Even if Oracle may have the least surprising
behaviour, it's likely that most sqlite users who have used other DBs, have
used one of the OSS DBs, and would likely be more surprised by Oracle's
behaviour.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolutionn in GROUP BY

[Peter Aronson]

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, the same 
expression (say lower(m)) in GROUP BY and ORDER BY can have different values in 
the same SELECT statement with that logic.  But I admit it isn't a big deal as 
long as it is properly documented.  Really, using a column alias that is the 
same as an actual base column isn't a particularly clever thing to do in the 
first place, even if legal.
 
Peter

From: Richard Hipp 
>To: Peter Aronson ; General Discussion of SQLite Database 
> 
>Sent: Wednesday, August 14, 2013 2:05 PM
>Subject: 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 related) issues:
>
> http://www.sqlite.org/src/info/1c69be2daf
> http://www.sqlite.org/src/info/f617ea3125
>
>
>Oracle is the outlier on the second of the two.
>
>
>-- 
>D. Richard Hipp
>d...@sqlite.org 
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locking without locking database file

[Roger Binns]

-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 complicated, with
Excel especially bad/adventurous in this respect.

CIFS locks are also completely different in virtually every way from Unix
ones.  You cannot map between them sensibly.  Both VisionFS and Samba
ended up having a "database" of the lock information and essentially
ignored what Unix said.

What this means is you cannot use CIFS from a Unix box and get correct
semantics because the Unix API does not expose anything close to cifs reality.

Windows machines can in theory do the right thing.  It has been a while,
but back in those days there would be calls over the wire that I couldn't
find any API to request.  For well over a decade the protocol has roughly
consisted of NT kernel data structures being sent over the wire.  The
server side gets adjusted to detect and work around bugs in the various
client versions.

Because of the asynchronous nature of networked file systems, race
conditions, and the possibilities of running out of disk space, most apps
have done updates by writing to a new file and only when everything is
successful overwriting the original.

Updating a (possibly shared) file in-place which is what SQLite does is
fraught with peril, which is why it turns out to be the least exercised
part of protocols and where bugs/quirks lie.

[1] http://www.rogerbinns.com/visionfs.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlIMB3wACgkQmOOfHg372QTyGgCgxB/7XxUibw+p0T/aI0cNecE4
ZegAoMDoh7RKb6hZmXsWTbreWhXpSYWP
=0UbL
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Kees Nuyt]

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 primary key, withnulls text,
>withoutnulls text NOT NULL );
>create view aview as select * from atab;
>pragma table_info(atab);
>pragma table_info(aview);
>
>Please notice that table_info(aview) returns always 0 in the not_null
>column, whereas the same column is properly reported for pragma
>table_info(atab).
>
>Is this known and intentional? Is there plans to correct that behavior? I
>have to deal with any database schema, wanted to use table_info as a means
>to extract the column definitions of views.

NOT NULL is a check constraint, which is only effective for INSERT or
UPDATE operations. A view is a SELECT query and it is not inserted into
or updated ever, except when an INSTEAD OF trigger is created, in which
case any modification triggers the check constraints of the underlying
table[s].
So, to me, not returning values for the notnull properties appears to be
proper behaviour (but I understand your wish).
For a generic tool, it probably is not overly difficult to parse the
column list and source tables of a simple view, and refer back to the
appropriate table_info() results to derive the NOT NULL constraint.

>I understand that reporting the pk-ness of a column is likely to be
>intentionally not correct for views.

Indeed.

>Thomas

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] name resolutionn in GROUP BY

[Richard Hipp]

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 related) issues:

 http://www.sqlite.org/src/info/1c69be2daf
 http://www.sqlite.org/src/info/f617ea3125

Oracle is the outlier on the second of the two.

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

Re: [sqlite] name resolutionn in GROUP BY

[Peter Aronson]

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: Wednesday, August 14, 2013 12:40 PM
>Subject: 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 SQLite
>3.7.17 work the other.  I think I'm going to revert SQLite to working as
>does PostgreSQL.
>
>-- 
>D. Richard Hipp
>d...@sqlite.org
>___
>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] name resolutionn in GROUP BY

[Richard Hipp]

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
http://www.sqlite.org/src/info/f617ea3125

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

Re: [sqlite] name resolution in GROUP BY

[Simon Slavin]

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, GROUP BY or 
> HAVING clause as it is a column in the derived table, not the base table.

That was my interpretation of the standard.  Names specified by 'AS' clauses 
cannot be used elsewhere in the SELECT that defines them.

Therefore the earlier behaviour is correct, GROUP BY must be referring to the 
names of columns in the table.

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

Re: [sqlite] name resolutionn in GROUP BY

[Marc L. Allen]

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 in the GROUP BY clause.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, August 14, 2013 3:40 PM
To: General Discussion of SQLite Database
Subject: 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 SQLite
3.7.17 work the other.  I think I'm going to revert SQLite to working as does 
PostgreSQL.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolutionn in GROUP BY

[Richard Hipp]

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 SQLite
3.7.17 work the other.  I think I'm going to revert SQLite to working as
does PostgreSQL.

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

Re: [sqlite] name resolutionn in GROUP BY

[Richard Hipp]

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, nor
> even by columns that appear in the underlying tables but are not selected.
>
> Most DBMS allow sorting (and grouping) by arbitrary expressions, which
> means that the standard is not directly applicable. One has to extrapolate.
>

Over on the postgresql mailing list, Tom Lane has this to say:

Our interpretation is that a bare column name ("ORDER BY foo") is resolved
> first as an output-column label, or failing that as an input-column name.
> However, as soon as you embed a name in an expression, it will be treated
> *only* as an input column name.
>
> The SQL standard is not a lot of help here.  In SQL92, the only allowed
> forms of ORDER BY arguments were an output column name or an output column
> number.  SQL99 and later dropped that definition (acknowledging that they
> were being incompatible) and substituted some fairly impenetrable verbiage
> that seems to boil down to allowing input column names that can be within
> expressions.  At least that's how we've chosen to read it.  Our current
> behavior is a compromise that tries to support both editions of the spec.
>

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

Re: [sqlite] name resolution in GROUP BY

[Marc L. Allen]

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] 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.
 
Peter

From: Marc L. Allen 
>To: General Discussion of SQLite Database 
>Sent: Wednesday, August 14, 2013 11:28 AM
>Subject: 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 
>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
>Sent: Wednesday, August 14, 2013 2:26 PM
>To: General Discussion of SQLite Database
>Subject: 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 queries are these:
>
>SELECT '1', substr(m,2) AS m
>  FROM t1
>ORDER BY m;
>
>SELECT '2', substr(m,2) AS m
>  FROM t1
>ORDER BY lower(m);
>
>Notice that the "m" in the ORDER BY clause might refer to column t1.m or it 
>might refer to the result set column labeled "AS m".  The question is which 
>one.  PostgreSQL answers t1.m for the first case and "AS m" for the second.  
>SQLite used to do that, but now it answers "t1.m" in both cases, which seems 
>to be a better fit to the SQL standard that Peter reports.
>
>Marc, if you can also try the query below on SQL Server, that would be 
>most
>helpful:
>
>SELECT '3', substr(m,2) AS m
>  FROM t1
>ORDER BY m COLLATE Latin1_General_CS_AS;
>
>
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>This email and any attachments are only for use by the intended recipient(s) 
>and may contain legally privileged, confidential, proprietary or otherwise 
>private information. Any unauthorized use, reproduction, dissemination, 
>distribution or other disclosure of the contents of this e-mail or its 
>attachments is strictly prohibited. If you have received this email in error, 
>please notify the sender immediately and delete the original.
>___
>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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolutionn in GROUP BY

[Igor Tandetnik]

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.


Most DBMS allow sorting (and grouping) by arbitrary expressions, which 
means that the standard is not directly applicable. One has to extrapolate.


Igor Tandetnik

On 8/14/2013 2:41 PM, Marc L. Allen wrote:

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 why that changed the 
result.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Wednesday, August 14, 2013 2:27 PM
To: sqlite-users@sqlite.org
Subject: 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 lower(name);



So the question is, should the "name" symbol in the GROUP BY clause
refer to the original column name in the TEST table, or should it
refer to the result column called "name".  SQLite version 3.7.15 picks
the TEST table column.  Version 3.7.17 picks the result column.

Anybody know which is correct?


For what it's worth, my reading of SQL-92 suggests that 3.7.15 is correct.

1) If no  is specified, then let T be the result of
  the preceding ; otherwise, let T be the result of
  the preceding .
2) Each  in the  shall unambigu-
  ously reference a column of T.

ORDER BY clause is different - column names there resolve in the context of the 
whole preceding SELECT statement, not just its FROM part:

   3) Let T be the table specified by the .
   4) If ORDER BY is specified, then each  in the
   shall identify a column of T.

--
Igor Tandetnik

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.




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

Re: [sqlite] name resolutionn in GROUP BY

[Dominique Devienne]

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 agree with the
>> previous behavior. --DD
>>
>> Dominique, can you please try the following SQL on Oracle and let me
>> know what you get:
>>
>
> Sure, no problem:
>
> SQL> CREATE TABLE t1(m VARCHAR(4));
>
> Table created.
>
> SQL> INSERT INTO t1 VALUES('az');
>
> 1 row created.
>
> SQL> INSERT INTO t1 VALUES('by');
>
> 1 row created.
>
> SQL> INSERT INTO t1 VALUES('cx');
>
> 1 row created.
>
> SQL> SELECT '1', substr(m,2) AS m
>   2FROM t1
>   3   ORDER BY m;
>
> ' M
> - 
> 1 x
> 1 y
> 1 z
>
> SQL> SELECT '2', substr(m,2) AS m
>   2FROM t1
>   3   ORDER BY lower(m);
>
> ' M
> - 
> 2 x
> 2 y
> 2 z
>

Oracle 11gR2 didn't like the COLLATE query:

SQL> SELECT '3', substr(m,2) AS m
  2FROM t1
  3   ORDER BY m COLLATE Latin1_General_CS_AS;
 ORDER BY m COLLATE Latin1_General_CS_AS
*
ERROR at line 3:
ORA-00933: SQL command not properly ended

Not sure the below will be useful, I just quickly googled what may be the
equivalent:

SQL> SELECT '3', substr(m,2) AS m
  2FROM t1
  3   ORDER BY NLSSORT(m, 'NLS_SORT=german');

' M
- 
3 x
3 y
3 z

SQL> ALTER SESSION SET NLS_SORT=german;

Session altered.

SQL> SELECT '3', substr(m,2) AS m
  2FROM t1
  3   ORDER BY m;

' M
- 
3 x
3 y
3 z
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolution in GROUP BY

[Peter Aronson]

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.
 
Peter

From: Marc L. Allen 
>To: General Discussion of SQLite Database  
>Sent: Wednesday, August 14, 2013 11:28 AM
>Subject: 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 [mailto:sqlite-users-boun...@sqlite.org] 
>On Behalf Of Richard Hipp
>Sent: Wednesday, August 14, 2013 2:26 PM
>To: General Discussion of SQLite Database
>Subject: 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 queries are these:
>
>SELECT '1', substr(m,2) AS m
>  FROM t1
>ORDER BY m;
>
>SELECT '2', substr(m,2) AS m
>  FROM t1
>ORDER BY lower(m);
>
>Notice that the "m" in the ORDER BY clause might refer to column t1.m or it 
>might refer to the result set column labeled "AS m".  The question is which 
>one.  PostgreSQL answers t1.m for the first case and "AS m" for the second.  
>SQLite used to do that, but now it answers "t1.m" in both cases, which seems 
>to be a better fit to the SQL standard that Peter reports.
>
>Marc, if you can also try the query below on SQL Server, that would be most
>helpful:
>
>SELECT '3', substr(m,2) AS m
>  FROM t1
>ORDER BY m COLLATE Latin1_General_CS_AS;
>
>
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>This email and any attachments are only for use by the intended recipient(s) 
>and may contain legally privileged, confidential, proprietary or otherwise 
>private information. Any unauthorized use, reproduction, dissemination, 
>distribution or other disclosure of the contents of this e-mail or its 
>attachments is strictly prohibited. If you have received this email in error, 
>please notify the sender immediately and delete the original.
>___
>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] Database locking without locking database file

[Ralf Ramsauer]

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 us have had horrible experiences
> (regardless of sqlite) with locking on network drives, either corruption or
> 100x slower performance when file locking is used. Try it if you must, but
> don't say you weren't warned.
Okay, I'll try it out and stress test it. I never had such problems with
NFS before.
I'll want to be able to reconstruct it...
> Neither cifs.
> ha. Ha. HAHAHAHA!!!
>
> That made my day :).
Cheers ;-)

Of course i prefer NFS but in some cases one doesn't get out of Cifs.

Regards,

-- 
Ralf Ramsauer

PGP: 0x8F10049B

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

Re: [sqlite] name resolutionn in GROUP BY

[Dominique Devienne]

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 SQL on Oracle and let me
> know what you get:
>

Sure, no problem:

SQL> CREATE TABLE t1(m VARCHAR(4));

Table created.

SQL> INSERT INTO t1 VALUES('az');

1 row created.

SQL> INSERT INTO t1 VALUES('by');

1 row created.

SQL> INSERT INTO t1 VALUES('cx');

1 row created.

SQL> SELECT '1', substr(m,2) AS m
  2FROM t1
  3   ORDER BY m;

' M
- 
1 x
1 y
1 z

SQL> SELECT '2', substr(m,2) AS m
  2FROM t1
  3   ORDER BY lower(m);

' M
- 
2 x
2 y
2 z
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolutionn in GROUP BY

[Marc L. Allen]

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 why that changed the 
result.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Wednesday, August 14, 2013 2:27 PM
To: sqlite-users@sqlite.org
Subject: 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 lower(name);
>>
>
> So the question is, should the "name" symbol in the GROUP BY clause 
> refer to the original column name in the TEST table, or should it 
> refer to the result column called "name".  SQLite version 3.7.15 picks 
> the TEST table column.  Version 3.7.17 picks the result column.
>
> Anybody know which is correct?

For what it's worth, my reading of SQL-92 suggests that 3.7.15 is correct.

1) If no  is specified, then let T be the result of
 the preceding ; otherwise, let T be the result of
 the preceding .
2) Each  in the  shall unambigu-
 ously reference a column of T.

ORDER BY clause is different - column names there resolve in the context of the 
whole preceding SELECT statement, not just its FROM part:

  3) Let T be the table specified by the .
  4) If ORDER BY is specified, then each  in the
  shall identify a column of T.

--
Igor Tandetnik

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolution in GROUP BY

[Marc L. Allen]

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
 ORDER BY m;

SELECT '2', right(m,1) AS m
  FROM #t1
 ORDER BY LOWER(m)

SELECT '3', right(m,1) AS m
  FROM #t1
 ORDER BY m COLLATE Latin1_General_CS_AS;

Untitled1  m
-  -
1  x
1  y
1  z


Untitled1  m
-  -
2  z
2  y
2  x


Untitled1  m
-  -
3  z
3  y
3  x




-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, August 14, 2013 2:26 PM
To: General Discussion of SQLite Database
Subject: 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 queries are these:

SELECT '1', substr(m,2) AS m
  FROM t1
 ORDER BY m;

SELECT '2', substr(m,2) AS m
  FROM t1
 ORDER BY lower(m);

Notice that the "m" in the ORDER BY clause might refer to column t1.m or it 
might refer to the result set column labeled "AS m".  The question is which 
one.  PostgreSQL answers t1.m for the first case and "AS m" for the second.  
SQLite used to do that, but now it answers "t1.m" in both cases, which seems to 
be a better fit to the SQL standard that Peter reports.

Marc, if you can also try the query below on SQL Server, that would be most
helpful:

SELECT '3', substr(m,2) AS m
  FROM t1
 ORDER BY m COLLATE Latin1_General_CS_AS;


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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolution in GROUP BY

[Marc L. Allen]

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 [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, August 14, 2013 2:26 PM
To: General Discussion of SQLite Database
Subject: 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 queries are these:

SELECT '1', substr(m,2) AS m
  FROM t1
 ORDER BY m;

SELECT '2', substr(m,2) AS m
  FROM t1
 ORDER BY lower(m);

Notice that the "m" in the ORDER BY clause might refer to column t1.m or it 
might refer to the result set column labeled "AS m".  The question is which 
one.  PostgreSQL answers t1.m for the first case and "AS m" for the second.  
SQLite used to do that, but now it answers "t1.m" in both cases, which seems to 
be a better fit to the SQL standard that Peter reports.

Marc, if you can also try the query below on SQL Server, that would be most
helpful:

SELECT '3', substr(m,2) AS m
  FROM t1
 ORDER BY m COLLATE Latin1_General_CS_AS;


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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolutionn in GROUP BY

[Igor Tandetnik]

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 lower(name);



So the question is, should the "name" symbol in the GROUP BY clause refer
to the original column name in the TEST table, or should it refer to the
result column called "name".  SQLite version 3.7.15 picks the TEST table
column.  Version 3.7.17 picks the result column.

Anybody know which is correct?


For what it's worth, my reading of SQL-92 suggests that 3.7.15 is correct.

1) If no  is specified, then let T be the result of
the preceding ; otherwise, let T be the result of
the preceding .
2) Each  in the  shall unambigu-
ously reference a column of T.

ORDER BY clause is different - column names there resolve in the context 
of the whole preceding SELECT statement, not just its FROM part:


 3) Let T be the table specified by the .
 4) If ORDER BY is specified, then each  in the
 shall identify a column of T.

--
Igor Tandetnik

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

Re: [sqlite] name resolution in GROUP BY

[Richard Hipp]

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 queries are these:

SELECT '1', substr(m,2) AS m
  FROM t1
 ORDER BY m;

SELECT '2', substr(m,2) AS m
  FROM t1
 ORDER BY lower(m);

Notice that the "m" in the ORDER BY clause might refer to column t1.m or it
might refer to the result set column labeled "AS m".  The question is which
one.  PostgreSQL answers t1.m for the first case and "AS m" for the
second.  SQLite used to do that, but now it answers "t1.m" in both cases,
which seems to be a better fit to the SQL standard that Peter reports.

Marc, if you can also try the query below on SQL Server, that would be most
helpful:

SELECT '3', substr(m,2) AS m
  FROM t1
 ORDER BY m COLLATE Latin1_General_CS_AS;


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

Re: [sqlite] name resolution in GROUP BY

[Marc L. Allen]

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, August 14, 2013 2:17 PM
To: Peter Aronson; General Discussion of SQLite Database
Subject: 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');
INSERT INTO #t1 VALUES('cx');

SELECT '1', right(m,1) AS m1
  FROM #t1
 ORDER BY m1;

SELECT '2', right(m,1) AS m1
  FROM #t1
 ORDER BY m1

Works, returning:

Untitled1  m1
-  --
1  x
1  y
1  z


Untitled1  m1
-  --
2  x
2  y
2  z



-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:13 PM
To: General Discussion of SQLite Database
Subject: 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 base table.
 
Peter

From: Richard Hipp 
>To: General Discussion of SQLite Database 
>Sent: Wednesday, August 14, 2013 9:59 AM
>Subject: 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, should the "name" symbol in the GROUP BY clause 
>refer to the original column name in the TEST table, or should it refer 
>to the result column called "name".  SQLite version 3.7.15 picks the 
>TEST table column.  Version 3.7.17 picks the result column.
>
>Anybody know which is correct?
>
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolution in GROUP BY

[Marc L. Allen]

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');
INSERT INTO #t1 VALUES('cx');

SELECT '1', right(m,1) AS m1
  FROM #t1
 ORDER BY m1;

SELECT '2', right(m,1) AS m1
  FROM #t1
 ORDER BY m1

Works, returning:

Untitled1  m1
-  --
1  x
1  y
1  z


Untitled1  m1
-  --
2  x
2  y
2  z



-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:13 PM
To: General Discussion of SQLite Database
Subject: 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 base table.
 
Peter

From: Richard Hipp 
>To: General Discussion of SQLite Database 
>Sent: Wednesday, August 14, 2013 9:59 AM
>Subject: 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, should the "name" symbol in the GROUP BY clause 
>refer to the original column name in the TEST table, or should it refer 
>to the result column called "name".  SQLite version 3.7.15 picks the 
>TEST table column.  Version 3.7.17 picks the result column.
>
>Anybody know which is correct?
>
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolution in GROUP BY

[Peter Aronson]

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 base table.
 
Peter

From: Richard Hipp 
>To: General Discussion of SQLite Database  
>Sent: Wednesday, August 14, 2013 9:59 AM
>Subject: 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, should the "name" symbol in the GROUP BY clause refer
>to the original column name in the TEST table, or should it refer to the
>result column called "name".  SQLite version 3.7.15 picks the TEST table
>column.  Version 3.7.17 picks the result column.
>
>Anybody know which is correct?
>
>-- 
>D. Richard Hipp
>d...@sqlite.org
>___
>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] name resolutionn in GROUP BY

[Marc L. Allen]

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  y
1  z


Untitled1  m
-  -
2  z
2  y
2  x

(I used the following code)

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
 ORDER BY m;

SELECT '2', right(m,1) AS m
  FROM #t1
 ORDER BY lower(m);

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dominique Devienne
Sent: Wednesday, August 14, 2013 2:00 PM
To: General Discussion of SQLite Database
Subject: 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(),
> >NULLIF(name,'abc') AS name
> > from test
> > group by lower(name);
> >
>
> So the question is, should the "name" symbol in the GROUP BY clause 
> refer to the original column name in the TEST table, or should it 
> refer to the result column called "name".  SQLite version 3.7.15 picks 
> the TEST table column.  Version 3.7.17 picks the result column.
>
> Anybody know which is correct?
>

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ...
SQL> create table test (name varchar2(64));
Table created.
SQL> insert into test values (NULL);
1 row created.
SQL> insert into test values ('abc');
1 row created.
SQL> select count(*), max(nullif(name, 'abc')) as name from test group 
SQL> by
lower(name);
  COUNT(*) NAME
-- 
 1
 1
 SQL> select count(*), nullif(lower(name), 'abc') as name from test group by 
lower(name);
  COUNT(*) NAME
-- 
 1
 1

Not authoritative of course, but Oracle seems to agree with the previous 
behavior. --DD ___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolutionn in GROUP BY

[Richard Hipp]

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));
INSERT INTO t1 VALUES('az');
INSERT INTO t1 VALUES('by');
INSERT INTO t1 VALUES('cx');

SELECT '1', substr(m,2) AS m
  FROM t1
 ORDER BY m;

SELECT '2', substr(m,2) AS m
  FROM t1
 ORDER BY lower(m);


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

Re: [sqlite] name resolutionn in GROUP BY

[Dominique Devienne]

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(),
> >NULLIF(name,'abc') AS name
> > from test
> > group by lower(name);
> >
>
> So the question is, should the "name" symbol in the GROUP BY clause refer
> to the original column name in the TEST table, or should it refer to the
> result column called "name".  SQLite version 3.7.15 picks the TEST table
> column.  Version 3.7.17 picks the result column.
>
> Anybody know which is correct?
>

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ...
SQL> create table test (name varchar2(64));
Table created.
SQL> insert into test values (NULL);
1 row created.
SQL> insert into test values ('abc');
1 row created.
SQL> select count(*), max(nullif(name, 'abc')) as name from test group by
lower(name);
  COUNT(*) NAME
-- 
 1
 1
 SQL> select count(*), nullif(lower(name), 'abc') as name from test group
by lower(name);
  COUNT(*) NAME
-- 
 1
 1

Not authoritative of course, but Oracle seems to agree with the previous
behavior. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolutionn in GROUP BY

[Richard Hipp]

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, should the "name" symbol in the GROUP BY clause refer
to the original column name in the TEST table, or should it refer to the
result column called "name".  SQLite version 3.7.15 picks the TEST table
column.  Version 3.7.17 picks the result column.

Anybody know which is correct?

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

Re: [sqlite] Database locking without locking database file

[Simon Slavin]

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 of us can believe it, but many of us have had horrible experiences
> (regardless of sqlite) with locking on network drives, either corruption or
> 100x slower performance when file locking is used. Try it if you must, but
> don't say you weren't warned.

I can add from personal experience that various implementations of NFS over the 
years have been terrible.  From sync() and fsync() calls which did nothing at 
all, to unlock calls which unlock all files when you're asking only for one 
file, there has been some truly terrible source code.  Section 2.1 in 
'howtocorrupt' is dead right.

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

Re: [sqlite] Database locking without locking database file

[Stephan Beal]

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) with locking on network drives, either corruption or
100x slower performance when file locking is used. Try it if you must, but
don't say you weren't warned.

Neither cifs.
>

ha. Ha. HAHAHAHA!!!

That made my day :).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] name resolutionn in GROUP BY

[Rob Golsteijn]

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 test group by lower(name); -- OK 
select min(name) as name from test group by lower(name); -- Error: misuse of 
aggregate: min()    in version 3.7.17, OK in 3.7.15

In version version 3.7.15 the last query executed without problems, in 3.7.17 
it reports an error (3.7.16 not tested).
In the last query Sqlite 3.7.15 used "test.name" for "name" in the group by 
clause, Sqlite 3.7.15 seems to refer to the result column named "name".

This difference in name resolution is also illustrated by the following example:

.null 
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);


In version 5.7.15 the output is:
1|
1|
(i.e. "group by" made 2 groups)

In version 5.7.17 the output is:
2|
(i.e. "group by" made 1 group)

I couldn't find a specifcation of which name should be used in the group by 
clause.
I think it does not make sense to use the result column alias in the "group by" 
clause since it is the result of a calculation based on a grouping, but then 
again also used to produce the groups. Hence using the name of the result row 
alias looks like a circular definition.

My question is whether the change is a bug or an intended change?


From the release history's text it looks like it could have been introduced by 
the changes in Ticket 2500cdb9be05

 
Regards,
Rob Golsteijn

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

Re: [sqlite] Database locking without locking database file

[Ralf Ramsauer]

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 the database
>> file or by touching dotfiles for locking?
>>
> You cannot write into the database file to create a lock because that
> presents a race condition if two processes are trying to lock at the same
> time.
Ah ok, point taken. I thought as much.
>
> If you open using the "unix-dotfile" VFS, it will use dotfile locking.
Great! As I see, Sqlite supports unix-dotfile locking [1], I haven't
seen this article before.
Perhaps this solves my problem
> However, you give up a lot of concurrency with this (since dot-files are
> always exclusive locks and never reader/writer locks) and it is slower and
> it does not work with WAL mode (which requires multiple reader/writer locks
> per file) and if your process dies unexpectedly it will leave stale locks
> that have to be removed by hand.
I can accept that :-)

One further question:
Why is it proposed not to use NFS? Why is it so risky? I can hardly
believe that NFS locking is that broken...
Neither cifs.

[1] : http://www.sqlite.org/vfs.html

Thanks in advance

-- 
Ralf Ramsauer

PGP: 0x8F10049B


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

Re: [sqlite] Database locking without locking database file

[Richard Hipp]

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) inside the database
> file or by touching dotfiles for locking?
>

You cannot write into the database file to create a lock because that
presents a race condition if two processes are trying to lock at the same
time.

If you open using the "unix-dotfile" VFS, it will use dotfile locking.
However, you give up a lot of concurrency with this (since dot-files are
always exclusive locks and never reader/writer locks) and it is slower and
it does not work with WAL mode (which requires multiple reader/writer locks
per file) and if your process dies unexpectedly it will leave stale locks
that have to be removed by hand.


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

[sqlite] Database locking without locking database file

[Ralf Ramsauer]

-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 [3].

Those files can also be located on network filesystem like NFS or CIFS.
[4] describes, that there might be some broken NFS implementations,
which have apparently issues with file locking

I'm working on a project where it is necessary that the Sqlite database
is stored on a networking filesystem and several other clients have
access to that file. I first use sshfs-fuse when i realized, that
sshfs-fuse is not able to forward file locks. So I got (of course)
malformed databases.

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 the database
file or by touching dotfiles for locking?

[1] : http://www.sqlite.org/faq.html#q6
[2] : http://www.sqlite.org/compile.html#threadsafe
[3] : http://www.sqlite.org/howtocorrupt.html Chapter 2.1
[4] : http://www.sqlite.org/faq.html#q5

Regards,

- -- 
Ralf Ramsauer

PGP: 0x8F10049B
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.20 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJSClJeAAoJEPovtFKPEASbWbMQAJPhmoCxoWWbDDFxAtNVSVAo
Jd9HmuUsPxGtKUVW+5yUvyHbtUcKaL9dRLq5uzswN3PiWfa0ro0h6FcWXGpwZYwG
+wVopcPHbCNxnbLwCQ05xdgtrJ7eLgvD5zj6/Y4ud+9OTHwLm27hjXorZX9Lt9qG
aq6sEMynuVGv136xWuJTDxPjJdgCtsQSaT2VMeArIA+Mn92CkqhK5YHYZ37RVMRh
l6wmX7U30d6cr7WqkV/wfVR7xjvQruum+4kMCAovpnNQJOTMa7m4Ky6SiclVN4bu
6AxEjhX7vk565PpWLm4UoMUma7TXHvd7FFCCKdagEe28ZF8Q+Bl2I1Lyn0+AD715
THCVgbpXms6IYptBHRQxuCC4/saGOMICdyn2ZBHDj6XmTYhR2d+3Reu5Men42QWI
QHY2AEcmQANN+dRWvUtwfKW738nGGqgDsB97cz8iUI3eWfY/VlKe5ZbPPB2RM/mc
w+yb1YxKFpAWXyVBPUC8HrT+AB2fWzqG+fijsaAeWfNxGt8cQivnBThmyf+JrJOC
A+lQAwHiADY7lyTG3q8VgwmznRCPGLUd2M246h2hvSBfyG+/OLd+iH6Kk5E9Grnl
jK6gRotY7A7T6of+sOwwcFbAtAZ0r8DGflGw0BoIglJjecstp3XdkBgxByXPABQs
ohEq/wiS/PDKaMp2rP95
=MNHI
-END PGP SIGNATURE-

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

[sqlite] Difference in pragma table_info between tables and views

[Thomas Krueger]

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 atab;
pragma table_info(atab);
pragma table_info(aview);

Please notice that table_info(aview) returns always 0 in the not_null
column, whereas the same column is properly reported for pragma
table_info(atab).

Is this known and intentional? Is there plans to correct that behavior? I
have to deal with any database schema, wanted to use table_info as a means
to extract the column definitions of views.
I understand that reporting the pk-ness of a column is likely to be
intentionally not correct for views.

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