[sqlite] How does _exec() do a transaction ?

[Simon Slavin]

Suppose I have no transaction open, and use _exec() with a multi-command string 
that has no transaction commands in.

Does SQLite perform the whole _exec() in one transaction or each command in a 
separate transaction ?

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

Re: [sqlite] SQLite is a LoC Preferred Format for datasets

[Scott Robison]

On Wed, May 30, 2018 at 12:15 PM, dmp  wrote:
> DROP TABLE IF EXISTS mySinkDBTable;
> CREATE TABLE mySinkDBTable (
> key_id1 INTEGER UNSIGNED NOT NULL,
> key_id2 INTEGER UNSIGNED NOT NULL,
> text VARCHAR
> );
>
> --
> -- Dumping data for table mySinkDBTable
> --
>
> INSERT INTO mySinkDBTable (key_id1, key_id2, text) VALUES('1', '8', '51');
> Corrected:
> INSERT INTO mySinkDBTable (key_id1, key_id2, text) VALUES(1, 8, '51');
>
> Since the user is allowed to store the metadata for the table
> types, example above, it is difficult for tools too determine
> the proper processing for the data. I understand the flexibility,
> and perhaps typeof() would solve most of my issues, but it would
> be nice to have metadata field type stored as INTEGER, REAL,
> NONE, TEXT, or BLOB.

What version of SQLite are you using for this? I just did the
following and do not see the string quoted values you are describing:

sqlite> CREATE TABLE mySinkDBTable (
   ...>   key_id1 INTEGER UNSIGNED NOT NULL,
   ...>   key_id2 INTEGER UNSIGNED NOT NULL,
   ...>   text VARCHAR
   ...> );
sqlite> insert into mySinkDBTable (key_id1, key_id2, text) VALUES('1','8','51');
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mySinkDBTable (
  key_id1 INTEGER UNSIGNED NOT NULL,
  key_id2 INTEGER UNSIGNED NOT NULL,
  text VARCHAR
);
INSERT INTO mySinkDBTable VALUES(1,8,'51');
COMMIT;

Given the comment in your data dump, I'm thinking your example came
from MySQL, not SQLite. Even if you try to insert quoted strings into
SQLite with the given column definitions, SQLite converts them to the
given type affinity before storing them, and uses that type affinity
when dumping the database.

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

Re: [sqlite] SQLite is a LoC Preferred Format for datasets

[dmp]

Suppose outside the subject of this thread, but in the document.


Sustainability factors

Self-documentation:
"The database format incorporates technical and structural metadata
 needed to interpret and manipulate the data itself. For example,
 a database file will include the CREATE TABLE declarations that
 define tables and columns. To the extent that meaningful names are
 used for tables and columns, the nature and context of the data
 may be recorded. However, there is no explicit structure within
 the file for storing fuller descriptive and contextual metadata.
 Nor is there a capability to embed in the file a metadata object
 conforming to a schema outside the SQLite specification."


This constantly bites me. This morning I had to generate a fix
to correct context of exported SQL statements for a dump of data.

The numeric values were being quoted as strings so therefore when
imported back in, they would have been treated as strings instead of
numbers.

DROP TABLE IF EXISTS mySinkDBTable;
CREATE TABLE mySinkDBTable (
key_id1 INTEGER UNSIGNED NOT NULL,
key_id2 INTEGER UNSIGNED NOT NULL,
text VARCHAR
);

--
-- Dumping data for table mySinkDBTable
--

INSERT INTO mySinkDBTable (key_id1, key_id2, text) VALUES('1', '8', '51');
Corrected:
INSERT INTO mySinkDBTable (key_id1, key_id2, text) VALUES(1, 8, '51');

Since the user is allowed to store the metadata for the table
types, example above, it is difficult for tools too determine
the proper processing for the data. I understand the flexibility,
and perhaps typeof() would solve most of my issues, but it would
be nice to have metadata field type stored as INTEGER, REAL,
NONE, TEXT, or BLOB.

danap.

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

Re: [sqlite] Congratulations on 18 years

[jungle Boogie]

On 30 May 2018 at 03:27, Christian Schmitz
 wrote:
> Hello,
>
> Congratulations to the SQLite team.
>
> As far as I see, the first checkin was 2000-05-29, which was over 18 years 
> ago.

Way to go! What a truly awesome project this has been!

>
> Sincerely
> Christian
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

[Vladimir Vissoultchev]

> By the way, this feature is documented for ORDER BY, but I don't see it for 
> GROUP BY.

It's not standard for GROUP BY e.g. SQL Server does not support it (ORDER BY 
col indexes are fine there too)

At least sqlite does not support the abomination GROUP BY 1 DESC the way MySQL 
does.

cheers,


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Mark Brand
Sent: Wednesday, May 30, 2018 5:22 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks for the clarification.


> You have constant integers, output column identifiers and "any other 
> expression" as terms for GROUP BY.

Just to make sure I'm not missing something subtle: I understand the "constant 
integer" is what gets interpreted as a result column number. What is an "output 
column identifier" then? Isn't it already covered by the broader category "any 
other expression"?

It's still a pretty astonishing language feature(!?) that an integer 
numeric-literal, which in every other column-like context represents its 
integer value, gets interpreted after GROUP BY or ORDER BY as a result column 
number. Fortunately, SQLite isn't to blame for designing this.

By the way, this feature is documented for ORDER BY, but I don't see it for 
GROUP BY.

Mark

On 30/05/18 13:28, Hick Gunter wrote:
> You have constant integers, output column identifiers and "any other 
> expression" as terms for GROUP BY. If the expression evalutes to a constant 
> value, you will have only one output row.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von 
> Mark Brand
> Gesendet: Mittwoch, 30. Mai 2018 12:11
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"
>
> Thanks. I had forgotten that GROUP BY considers a literal integer in this 
> context to be a column number, a feature I don't use.
>
> These, on the other hand, work as I would have expected:
>
> sqlite> select 0 group by cast (0 as int);
> 0
> sqlite> select 0 group by (select 0);
> 0
>
> Mark
>
>
> On 30/05/18 12:00, Hick Gunter wrote:
>> Yes. If the expression is a constant integer K, then it is considered an 
>> alias for the K-th column of the result set. Columns are ordered from left 
>> to right starting with 1.
>>
>> There is no 0-th column, so GROUP BY 0 is "out of range", just the same as 
>> "SELECT 0 GROUP BY 31" would be.
>>
>> -Ursprüngliche Nachricht-
>> Von: sqlite-users
>> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von 
>> Mark Brand
>> Gesendet: Mittwoch, 30. Mai 2018 11:32
>> An: SQLite mailing list 
>> Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"
>>
>> Hi,
>>
>> Is there a good reason for this error:
>>
>> sqlite> SELECT  0 GROUP BY 0;
>> Error: 1st GROUP BY term out of range - should be between 1 and 1
>> sqlite> SELECT 0 GROUP BY 1;
>> 0
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___
>>Gunter Hick | Software Engineer | Scientific Games International 
>> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>> 0430013 | (O) +43 1 80100 - 0
>>
>> May be privileged. May be confidential. Please delete if not the addressee.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>   Gunter Hick | Software Engineer | Scientific Games International 
> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 
> 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

Re: [sqlite] How does _exec() do a transaction ?

[R Smith]

On 2018/05/30 3:33 PM, Simon Slavin wrote:

On 30 May 2018, at 2:30pm, Simon Slavin  wrote:


Does SQLite perform the whole _exec() in one transaction or each command in a 
separate transaction ?

Subsidiary question:

Does SQLite parse the entire string of commands for a syntax error first, 
triggering an error if anything is wrong before executing the first command ?  
Or does it execute the first command, then parse the next part of the string 
for a command ?


I'm obviously not a Dev, so this is what usage & testing on my part 
suggests when I tried to figure out the same long ago:

- There is no implicit transaction.
- Every Item is parsed separately and in-turn.

It's much like the EXEC command follows this pseudo code:

while (inputStr != "") {
  fetch next string section up to next ";" or end of input, whichever 
comes first;

  process this section as a new SQL command;
  remove this section from inputStr;
};

Also, in case you are wondering, it seems there is not much penalty 
either way - using your own list and preparing and executing each query 
seems much the same speed-wise as feeding the entire list of SQL queries 
to the EXEC command.
This obviously changes significantly the minute you start using 
multi-iteratable queries with bindings.  (Let's imagine multi-iterable 
is a real word for a sec.)  :)




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


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

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Keith Medcalf]

In this case it makes no real difference.  The select on the connection will 
start a "read" transaction and the update on that connection will upgrade the 
transaction to a "write" transaction.  The transaction will complete when both 
the select and the update(s) are complete and the select finalized.

You might want to start a transaction with "BEGIN IMMEDIATE" before the select 
since that will tell SQLite3 that you intend to "write" (update) on the 
connection rather than praying that the later lock upgrade is successful, and 
do a COMMIT at the end of the whole select/update procedure to commit the 
changes to the database.

The only caveat, of course, with only using one connection is that changes made 
by the "update" are visible to the "select" so it is possible that you "update" 
what is being selected in the middle of the select ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Torsten Curdt
>Sent: Wednesday, 30 May, 2018 02:34
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] [EXTERNAL] Re: database locked on select
>
>> Do the select and updates run inside a explicit transaction or they
>> run in individual implicit transactions?
>>
>
>implicit - does that make a big difference in this case?
>
>
>If you really want a single query you could write something like:
>>
>> WITH data(id, c1, c2 /*, ... */) AS (VALUES
>> (123, 'abc', 'xyz' /*, ... */),
>> (456, 'xxx', 'yyy' /*, ... */),
>> (789, 'xyz', 'xyz' /*, ... */)
>> /*  ...  */
>> ) UPDATE tab
>> SET (c1, c2 /*, ... */) = (SELECT c1, c2 /*, ... */ WHERE
>data.id =
>> tab.id)
>> WHERE id IN (SELECT id FROM data);
>>
>>
>But for that again means all the data (or the single query) needs to
>be
>built up in memory.
>
>cheers,
>Torsten
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] How does _exec() do a transaction ?

[Simon Slavin]

On 30 May 2018, at 3:07pm, Abroży Nieprzełoży 
 wrote:

> sqlite3_exec doesn't open transaction by itself.
> 
> Each statement is prepared and executed separately.

Thank you for your fast answers.

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

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

[Mark Brand]

Thanks for the clarification.



You have constant integers, output column identifiers and "any other 
expression" as terms for GROUP BY.


Just to make sure I'm not missing something subtle: I understand the 
"constant integer" is what gets interpreted as a result column number.  
What is an "output column identifier" then? Isn't it already covered by 
the broader category "any other expression"?


It's still a pretty astonishing language feature(!?) that an integer 
numeric-literal, which in every other column-like context represents its 
integer value, gets interpreted after GROUP BY or ORDER BY as a result 
column number. Fortunately, SQLite isn't to blame for designing this.


By the way, this feature is documented for ORDER BY, but I don't see it 
for GROUP BY.


Mark

On 30/05/18 13:28, Hick Gunter wrote:

You have constant integers, output column identifiers and "any other 
expression" as terms for GROUP BY. If the expression evalutes to a constant value, 
you will have only one output row.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Mark Brand
Gesendet: Mittwoch, 30. Mai 2018 12:11
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks. I had forgotten that GROUP BY considers a literal integer in this 
context to be a column number, a feature I don't use.

These, on the other hand, work as I would have expected:

sqlite> select 0 group by cast (0 as int);
0
sqlite> select 0 group by (select 0);
0

Mark


On 30/05/18 12:00, Hick Gunter wrote:

Yes. If the expression is a constant integer K, then it is considered an alias 
for the K-th column of the result set. Columns are ordered from left to right 
starting with 1.

There is no 0-th column, so GROUP BY 0 is "out of range", just the same as "SELECT 0 
GROUP BY 31" would be.

-Ursprüngliche Nachricht-
Von: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
Mark Brand
Gesendet: Mittwoch, 30. Mai 2018 11:32
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"

Hi,

Is there a good reason for this error:

sqlite> SELECT  0 GROUP BY 0;
Error: 1st GROUP BY term out of range - should be between 1 and 1
sqlite> SELECT 0 GROUP BY 1;
0

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


___
   Gunter Hick | Software Engineer | Scientific Games International
GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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


___
  Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] How does _exec() do a transaction ?

[Abroży Nieprzełoży]

sqlite3_exec doesn't open transaction by itself.

Each statement is prepared and executed separately.


2018-05-30 15:33 GMT+02:00, Simon Slavin :
> On 30 May 2018, at 2:30pm, Simon Slavin  wrote:
>
>> Does SQLite perform the whole _exec() in one transaction or each command
>> in a separate transaction ?
>
> Subsidiary question:
>
> Does SQLite parse the entire string of commands for a syntax error first,
> triggering an error if anything is wrong before executing the first command
> ?  Or does it execute the first command, then parse the next part of the
> string for a command ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does _exec() do a transaction ?

[Simon Slavin]

On 30 May 2018, at 2:30pm, Simon Slavin  wrote:

> Does SQLite perform the whole _exec() in one transaction or each command in a 
> separate transaction ?

Subsidiary question:

Does SQLite parse the entire string of commands for a syntax error first, 
triggering an error if anything is wrong before executing the first command ?  
Or does it execute the first command, then parse the next part of the string 
for a command ?

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

[sqlite] How does _exec() do a transaction ?

[Simon Slavin]

Suppose I have no transaction open, and use _exec() with a multi-command string 
that has no transaction commands in.

Does SQLite perform the whole _exec() in one transaction or each command in a 
separate transaction ?

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

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

[Hick Gunter]

You have constant integers, output column identifiers and "any other 
expression" as terms for GROUP BY. If the expression evalutes to a constant 
value, you will have only one output row.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Mark Brand
Gesendet: Mittwoch, 30. Mai 2018 12:11
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks. I had forgotten that GROUP BY considers a literal integer in this 
context to be a column number, a feature I don't use.

These, on the other hand, work as I would have expected:

sqlite> select 0 group by cast (0 as int);
0
sqlite> select 0 group by (select 0);
0

Mark


On 30/05/18 12:00, Hick Gunter wrote:
> Yes. If the expression is a constant integer K, then it is considered an 
> alias for the K-th column of the result set. Columns are ordered from left to 
> right starting with 1.
>
> There is no 0-th column, so GROUP BY 0 is "out of range", just the same as 
> "SELECT 0 GROUP BY 31" would be.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Mark Brand
> Gesendet: Mittwoch, 30. Mai 2018 11:32
> An: SQLite mailing list 
> Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"
>
> Hi,
>
> Is there a good reason for this error:
>
> sqlite> SELECT  0 GROUP BY 0;
> Error: 1st GROUP BY term out of range - should be between 1 and 1
> sqlite> SELECT 0 GROUP BY 1;
> 0
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>   Gunter Hick | Software Engineer | Scientific Games International
> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
> 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Congratulations on 18 years

[Christian Schmitz]

Hello,

Congratulations to the SQLite team.

As far as I see, the first checkin was 2000-05-29, which was over 18 years ago.

Sincerely
Christian

-- 
Read our blog about news on our plugins:

http://www.mbsplugins.de/


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

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

[Mark Brand]

Thanks. I had forgotten that GROUP BY considers a literal integer in 
this context to be a column number, a feature I don't use.


These, on the other hand, work as I would have expected:

sqlite> select 0 group by cast (0 as int);
0
sqlite> select 0 group by (select 0);
0

Mark


On 30/05/18 12:00, Hick Gunter wrote:

Yes. If the expression is a constant integer K, then it is considered an alias 
for the K-th column of the result set. Columns are ordered from left to right 
starting with 1.

There is no 0-th column, so GROUP BY 0 is "out of range", just the same as "SELECT 0 
GROUP BY 31" would be.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Mark Brand
Gesendet: Mittwoch, 30. Mai 2018 11:32
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"

Hi,

Is there a good reason for this error:

sqlite> SELECT  0 GROUP BY 0;
Error: 1st GROUP BY term out of range - should be between 1 and 1
sqlite> SELECT 0 GROUP BY 1;
0

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


___
  Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

[Hick Gunter]

Yes. If the expression is a constant integer K, then it is considered an alias 
for the K-th column of the result set. Columns are ordered from left to right 
starting with 1.

There is no 0-th column, so GROUP BY 0 is "out of range", just the same as 
"SELECT 0 GROUP BY 31" would be.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Mark Brand
Gesendet: Mittwoch, 30. Mai 2018 11:32
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"

Hi,

Is there a good reason for this error:

sqlite> SELECT  0 GROUP BY 0;
Error: 1st GROUP BY term out of range - should be between 1 and 1
sqlite> SELECT 0 GROUP BY 1;
0

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Paul Sanderson]

If you are doing each update in a separate transaction it will be much
slower than wrapping them in a single transaction.

See the faq here, it refers to inserts but updates will be the same.

http://sqlite.org/faq.html#q19


Cheers
Paul


On Wed, 30 May 2018 at 09:34, Torsten Curdt  wrote:

> > Do the select and updates run inside a explicit transaction or they
> > run in individual implicit transactions?
> >
>
> implicit - does that make a big difference in this case?
>
>
> If you really want a single query you could write something like:
> >
> > WITH data(id, c1, c2 /*, ... */) AS (VALUES
> > (123, 'abc', 'xyz' /*, ... */),
> > (456, 'xxx', 'yyy' /*, ... */),
> > (789, 'xyz', 'xyz' /*, ... */)
> > /*  ...  */
> > ) UPDATE tab
> > SET (c1, c2 /*, ... */) = (SELECT c1, c2 /*, ... */ WHERE data.id =
> > tab.id)
> > WHERE id IN (SELECT id FROM data);
> >
> >
> But for that again means all the data (or the single query) needs to be
> built up in memory.
>
> cheers,
> Torsten
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Paul
www.sandersonforensics.com
SQLite Forensics Book 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] unexpected error with "GROUP BY 0"

[Mark Brand]

Hi,

Is there a good reason for this error:

sqlite> SELECT  0 GROUP BY 0;
Error: 1st GROUP BY term out of range - should be between 1 and 1
sqlite> SELECT 0 GROUP BY 1;
0

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

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Torsten Curdt]

> Do the select and updates run inside a explicit transaction or they
> run in individual implicit transactions?
>

implicit - does that make a big difference in this case?


If you really want a single query you could write something like:
>
> WITH data(id, c1, c2 /*, ... */) AS (VALUES
> (123, 'abc', 'xyz' /*, ... */),
> (456, 'xxx', 'yyy' /*, ... */),
> (789, 'xyz', 'xyz' /*, ... */)
> /*  ...  */
> ) UPDATE tab
> SET (c1, c2 /*, ... */) = (SELECT c1, c2 /*, ... */ WHERE data.id =
> tab.id)
> WHERE id IN (SELECT id FROM data);
>
>
But for that again means all the data (or the single query) needs to be
built up in memory.

cheers,
Torsten
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users