Re: [sqlite] operation unexpectedly applied to both main and temp?

[Mark Brand]

On 29/03/17 02:38, Simon Slavin wrote:

On 29 Mar 2017, at 1:34am, Domingo Alvarez Duarte  wrote:

It seems sqlite look first if there is a temp.table before main.table and 
without qualification temp.table is used.

You got it.  It’s not obvious that this is what SQLite would do.  But now you 
know it you understand what is happening.


Thanks for helping to clear this up.  I should have tested the content 
of the table before doing the update and especially before posting. Oh well.


 I didn't expect temp to win over main like that. Especially because I 
recently read the documentation page for "ATTACH DATABASE".


Tables in an attached database can be referred to using the syntax 
/schema-name.table-name/. If the name of the table is unique across 
all attached databases and the main and temp databases, then the 
/schema-name/ prefix is not required. If two or more tables in 
different databases have the same name and the /schema-name/ prefix is 
not used on a table reference, then the table chosen is the one in the 
database that was *least recently attached*.


So I guess for our purposes temp is "less recently" attached than main, 
which wasn't an obvious fact to me.


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

Re: [sqlite] BUG?: operation unexpectedly applied to both main and temp

[Simon Slavin]

On 29 Mar 2017, at 1:34am, Domingo Alvarez Duarte  wrote:
> 
> It seems sqlite look first if there is a temp.table before main.table and 
> without qualification temp.table is used.

You got it.  It’s not obvious that this is what SQLite would do.  But now you 
know it you understand what is happening.

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

Re: [sqlite] BUG?: operation unexpectedly applied to both main and temp

[Simon Slavin]

On 29 Mar 2017, at 1:33am, Mark Brand  wrote:

>> The point isn't about which table one expects to receive the update, it's 
>> that *both* tables get updated.

No.  Just the one table is updated: the temp.t table.  Both rows of data are in 
that table.  Try repeating your experiment but replace this line

insert into t select 'main', 'original';

with this:

insert into main.t select 'main', 'original';

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

Re: [sqlite] BUG?: operation unexpectedly applied to both main and temp

[Domingo Alvarez Duarte]

Hello !

I repeated your simple case and could see that only the temp.t tabale is 
populated/updates.


It seems sqlite look first if there is a temp.table before main.table 
and without qualification temp.table is used.


Cheers !


On 28/03/17 21:12, Mark Brand wrote:



On 29/03/17 01:35, Simon Slavin wrote:

On 28 Mar 2017, at 11:02pm, Mark Brand  wrote:


create temp table t (db, val);
insert into t select 'temp', 'original';

create table t (db, val);
insert into t select 'main', 'original';

Here’s your problem:

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> create temp table t (db, val);
sqlite> insert into t select 'temp', 'original';
sqlite> .schema
CREATE TABLE t (db, val);
sqlite> create table t (db, val);
sqlite> insert into t select 'main', 'original';
sqlite> .schema
CREATE TABLE t (db, val);
CREATE TABLE t (db, val);
sqlite> SELECT * FROM t;
temp|original
main|original
sqlite> SELECT * FROM temp.t;
temp|original
main|original
sqlite> DROP TABLE temp.t;
sqlite> SELECT * FROM t;
sqlite>

While temp.t exists it gets in the way of main.t.  When you refer to 
"t" you’re talking about temp.t, not main.t.




The point isn't about which table one expects to receive the update, 
it's that *both* tables get updated. In fact, now I realize that the 
effect can be demonstrated with a simpler demo than my original:


create temp table t (db, val);
insert into t select 'temp', 'original';

create table main.t (db, val);
insert into t select 'main', 'original';

update t set val = 'touched';

select * from temp.t;
select * from main.t;

/*  output
temp|default table
main|default table
*/

___
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] BUG?: operation unexpectedly applied to both main and temp

[Mark Brand]

On 29/03/17 02:12, Mark Brand wrote:



On 29/03/17 01:35, Simon Slavin wrote:

On 28 Mar 2017, at 11:02pm, Mark Brand  wrote:


create temp table t (db, val);
insert into t select 'temp', 'original';

create table t (db, val);
insert into t select 'main', 'original';

Here’s your problem:

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> create temp table t (db, val);
sqlite> insert into t select 'temp', 'original';
sqlite> .schema
CREATE TABLE t (db, val);
sqlite> create table t (db, val);
sqlite> insert into t select 'main', 'original';
sqlite> .schema
CREATE TABLE t (db, val);
CREATE TABLE t (db, val);
sqlite> SELECT * FROM t;
temp|original
main|original
sqlite> SELECT * FROM temp.t;
temp|original
main|original
sqlite> DROP TABLE temp.t;
sqlite> SELECT * FROM t;
sqlite>

While temp.t exists it gets in the way of main.t.  When you refer to 
"t" you’re talking about temp.t, not main.t.




The point isn't about which table one expects to receive the update, 
it's that *both* tables get updated. In fact, now I realize that the 
effect can be demonstrated with a simpler demo than my original:


create temp table t (db, val);
insert into t select 'temp', 'original';

create table main.t (db, val);
insert into t select 'main', 'original';

update t set val = 'touched';

select * from temp.t;
select * from main.t;

/*  output



Sorry, the output I meant to paste is:

temp|touched
main|touched


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

Re: [sqlite] BUG?: operation unexpectedly applied to both main and temp

[Mark Brand]

On 29/03/17 01:35, Simon Slavin wrote:

On 28 Mar 2017, at 11:02pm, Mark Brand  wrote:


create temp table t (db, val);
insert into t select 'temp', 'original';

create table t (db, val);
insert into t select 'main', 'original';

Here’s your problem:

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> create temp table t (db, val);
sqlite> insert into t select 'temp', 'original';
sqlite> .schema
CREATE TABLE t (db, val);
sqlite> create table t (db, val);
sqlite> insert into t select 'main', 'original';
sqlite> .schema
CREATE TABLE t (db, val);
CREATE TABLE t (db, val);
sqlite> SELECT * FROM t;
temp|original
main|original
sqlite> SELECT * FROM temp.t;
temp|original
main|original
sqlite> DROP TABLE temp.t;
sqlite> SELECT * FROM t;
sqlite>

While temp.t exists it gets in the way of main.t.  When you refer to "t" you’re 
talking about temp.t, not main.t.



The point isn't about which table one expects to receive the update, 
it's that *both* tables get updated. In fact, now I realize that the 
effect can be demonstrated with a simpler demo than my original:


create temp table t (db, val);
insert into t select 'temp', 'original';

create table main.t (db, val);
insert into t select 'main', 'original';

update t set val = 'touched';

select * from temp.t;
select * from main.t;

/*  output
temp|default table
main|default table
*/

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

Re: [sqlite] BUG?: operation unexpectedly applied to both main and temp

[Simon Slavin]

On 28 Mar 2017, at 11:02pm, Mark Brand  wrote:

> create temp table t (db, val);
> insert into t select 'temp', 'original';
> 
> create table t (db, val);
> insert into t select 'main', 'original';

Here’s your problem:

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> create temp table t (db, val);
sqlite> insert into t select 'temp', 'original';
sqlite> .schema
CREATE TABLE t (db, val);
sqlite> create table t (db, val);
sqlite> insert into t select 'main', 'original';
sqlite> .schema
CREATE TABLE t (db, val);
CREATE TABLE t (db, val);
sqlite> SELECT * FROM t;
temp|original
main|original
sqlite> SELECT * FROM temp.t;
temp|original
main|original
sqlite> DROP TABLE temp.t;
sqlite> SELECT * FROM t;
sqlite> 

While temp.t exists it gets in the way of main.t.  When you refer to "t" you’re 
talking about temp.t, not main.t.

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

[sqlite] BUG?: operation unexpectedly applied to both main and temp

[Mark Brand]

HI,

Something seems to go wrong in this example where an operation 
unexpectedly gets applied both main and temp.


The order of table creation seems to be one crucial factor. I ran into 
this while trying to get my head around the use of temporary triggers, 
which seems to be the other necessary factor.


create temp table t (db, val);
insert into t select 'temp', 'original';

create table t (db, val);
insert into t select 'main', 'original';

create view v as select NULL val WHERE 1;

create temp trigger trg instead of update on v  /* main.v make no 
difference */

begin
update t set val = new.val;
end;

update v set val = 'touched';

select * from temp.t;
select * from main.t;

select sqlite_version();

OUTPUT:

temp|touched
main|touched
3.17.0

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

Re: [sqlite] Use of sqlite3_bind_int() vs sqlite3_bind_int64()

[Paul]

Thank you very much for replying so quickly!
 
>   On 3/28/17, Paul  wrote:
> > According to datatypes page https://sqlite.org/datatype3.html SQLite choses
> > width of the integer automatically. Does it mean that if I let's say want to
> > bind a number 5 in the query that inserts/updates rows it will be stored in
> > database as 1-byte integer regardless of the use of sqlite3_bind_int()
> > or sqlite3_bind_int64()?
> 
> Correct.
> 
> > If so, is it safe to *always*
> > use sqlite3_bind_int64()/sqlite3_column_int64() and forget about pain int
> > versions?
> 
> Correct.
> 
> Also, always use sqlite3_malloc64() and sqlite3_realloc64() and
> sqlite3_column_int64(), etc.
> -- 
> D. Richard Hipp
> d...@sqlite.org
 
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Use of sqlite3_bind_int() vs sqlite3_bind_int64()

[Richard Hipp]

On 3/28/17, Paul  wrote:
> According to datatypes page https://sqlite.org/datatype3.html SQLite choses
> width of the integer automatically. Does it mean that if I let's say want to
> bind a number 5 in the query that inserts/updates rows it will be stored in
> database as 1-byte integer regardless of the use of sqlite3_bind_int()
> or sqlite3_bind_int64()?

Correct.

> If so, is it safe to *always*
> use sqlite3_bind_int64()/sqlite3_column_int64() and forget about pain int
> versions?

Correct.

Also, always use sqlite3_malloc64() and sqlite3_realloc64() and
sqlite3_column_int64(), etc.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Use of sqlite3_bind_int() vs sqlite3_bind_int64()

[Paul]

According to datatypes page https://sqlite.org/datatype3.html SQLite choses 
width of the integer automatically. Does it mean that if I let's say want to 
bind a number 5 in the query that inserts/updates rows it will be stored in 
database as 1-byte integer regardless of the use of sqlite3_bind_int() or 
sqlite3_bind_int64()? 
If so, is it safe to *always* use sqlite3_bind_int64()/sqlite3_column_int64() 
and forget about pain int versions? 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] All versions compatible "very big" estimatedCost (Virtual tables)

[Hick Gunter]

You can always use CROSS JOIN to force a specific join order as in:

SELECT ... FROM mytable m CROSS_JOIN split s ON (s.input = m.string_field) JOIN 
anothertable a ON (a.field = s.output) ...

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Max Vlasov
Gesendet: Dienstag, 28. März 2017 13:37
An: SQLite mailing list 
Betreff: Re: [sqlite] All versions compatible "very big" estimatedCost (Virtual 
tables)

On Tue, Mar 28, 2017 at 12:51 PM, Dominique Devienne 
wrote:

> On Tue, Mar 28, 2017 at 11:00 AM, Hick Gunter  wrote:
>
> >
> > The "estimated cost" is described as "how many disk IO operations
> > are expected". Version higher than 3.8.2 allow setting an "estimatedRows"
> > (default: 25) and versions above 3.9.0 allow setting a flag that
> indicates
> > that 0 or 1 rows will be returned (i.e. the constraints form a
> > "unique index").
> >
>
> Thanks for the RTFM. That helps. Looks like I have about 3 years worth
> of catch-up to do on sqlite3_index_info.
>
>

Thanks, Dominique, Hick

I looked at the additional fields of sqlite3_index_info and probably they're of 
no use for me, at least they don't provide some enforcing of my particular 
index. As for my problem queries, they usually involve "order by" or "group 
by", that's when the planner (despite the obvious bias from me about the 
particular index) decides to provide only one of my two required fields.

Also interesting that actually what I called input/output approach, sqlite 
implemented calling it table-valued functions (probably starting 3.9.0) ( 
https://sqlite.org/vtab.html#tabfunc2). To make my tables compatible with this 
syntax, I just had to append HIDDEN to the "input" fileds of the table 
definition. But seems like changing to this definition doesn't affect the 
planner.
___
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
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] BLOB sizes beyond 2GB?

[Paul Sanderson]

I was a bit/very dull, schoolboy error :( re 32-bit - long week and it's
only Tuesday :)

Re: storing the length in the blob itself this would affect parsing the
serial types where, as now, you can determine the record length by looking
at the serial types and 'skip' through them to load a specific column. If
the length is stored in the record itself then reading past a blob means
that the first part of the blob itself needs to be read. Whether this would
have any significant impact on speed for parsing serial types in general I
don't know.





Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 28 March 2017 at 12:33, Dominique Devienne  wrote:

> On Tue, Mar 28, 2017 at 12:52 PM, Paul Sanderson <
> sandersonforens...@gmail.com> wrote:
>
> > I am sure Richard will correct me if I am wrong. But...
> >
> > The format for a record is
> >
> > 1. payload length varint
> > 2. rowid varint (optional)
> > 3. serial type array varint
> > 4. serial types
> > followed by the data for the serial types
> >
> > The issue are as I see them:
> >
> > The payload length varint above, this is the sum of 3 + 4 above plus all
> of
> > the following data forming the record. So as things stand you can't store
> > any record where the sum of the bytes in the serial types array and the
> > actual data that follows is greater than MAXVARINT because the total
> length
> > must be stored in 1. (MAXVARINT is actually max positive varint - see
> > below).
> >
>
> Good point. But still, MAXVARINT is 64-bit (see below) not 32-bit.
>
> The record format makes extensive use of the variable-length integer or
> > varint representation of 64-bit signed integers defined above.
> >
>
>
> > If you want to use one of the reserved serial types to store a blob of
> 6GB
> > then the serial type itself must be capable of storing the size of the
> > blob. Currently, a blob has *any* serial type of >= 12 and even, so the
> > maximum size for a blob is (MAXVARINT-12)/2 i.e. *any* even serial type
> >=
> > 12 and a text serial type is any odd serial type >= 13. All of the
> > remaining utilised serial types (i.e. those <= 9) refer to fixed length
> > data (ints and a 64 bit real).
> >
>
> I understand that. That's why I put the length in the "old style" blob
> value itself.
> But again, the varint encodes a 64-bit signed integer, and the "new style"
> blob could
> be assumed if the blob length exceed 2GiB (or 4 GiB), not even resorting to
> the
> two reserved serial types.
>
>
> > The remaining 2 serial types (remember these are just two bits from a
> > 64-bit serial type, each serial type is not a separate varint in its own
> > right) could be used to signify something like a 128-bit integer or some
> > other fixed-length data type, but, 1 bit by definition cannot store an
> > arbitrary length value.
> >
>
> I understand that (see above). But using the level of indirection of
> storing
> in the record only the meta-data of the blob, e.g. its full length, its
> in-record
> length (in case using 10, or 11 serial type, which cannot encode the length
> like the traditional text and blob serial types), and the ordered list of
> blob
> pages to read the blob from, seems completely possible.
>
>
> > I guess that the change Richard mentions (to up to 4GB) would be by
> > treating the varints as unsigned integers, rather than signed as they
> > currently are. This could be done (as far as I can see) for all varints
> > other than the rowid without affecting existing DBs.
> >
>
> That would be an implementation limitation though, not a file format
> limitation.
>
> Again, I'm probably naive here, but I still don't clearly see the file
> format limitation,
> and that's what I'm trying to understand. I completely accept this would be
> a lot of
> work and that the incentive for Richard to do it is rather low, to
> extremely low, although
> of course that does bum me out, I have to admit :), but really
> understanding the
> limitation I'm not seeing now is what I'm after here. Thanks, --DD
>
> PS: The alternate scheme of assuming new-style blob for length > 4 GiB,
> which is more backward-compatible, could be further refined via a pragma to
> put it lower, make the DB incompatible with older SQLite versions, but no
> more
> than the many other opt-in features old versions don't support.
> ___
> 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] All versions compatible "very big" estimatedCost (Virtual tables)

[Max Vlasov]

On Tue, Mar 28, 2017 at 12:51 PM, Dominique Devienne 
wrote:

> On Tue, Mar 28, 2017 at 11:00 AM, Hick Gunter  wrote:
>
> >
> > The "estimated cost" is described as "how many disk IO operations are
> > expected". Version higher than 3.8.2 allow setting an "estimatedRows"
> > (default: 25) and versions above 3.9.0 allow setting a flag that
> indicates
> > that 0 or 1 rows will be returned (i.e. the constraints form a "unique
> > index").
> >
>
> Thanks for the RTFM. That helps. Looks like I have about 3 years worth of
> catch-up to do on sqlite3_index_info.
>
>

Thanks, Dominique, Hick

I looked at the additional fields of sqlite3_index_info and probably
they're of no use for me, at least they don't provide some enforcing of my
particular index. As for my problem queries, they usually involve "order
by" or "group by", that's when the planner (despite the obvious bias from
me about the particular index) decides to provide only one of my two
required fields.

Also interesting that actually what I called input/output approach, sqlite
implemented calling it table-valued functions (probably starting 3.9.0) (
https://sqlite.org/vtab.html#tabfunc2). To make my tables compatible with
this syntax, I just had to append HIDDEN to the "input" fileds of the table
definition. But seems like changing to this definition doesn't affect the
planner.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB sizes beyond 2GB?

[Dominique Devienne]

On Tue, Mar 28, 2017 at 12:52 PM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> I am sure Richard will correct me if I am wrong. But...
>
> The format for a record is
>
> 1. payload length varint
> 2. rowid varint (optional)
> 3. serial type array varint
> 4. serial types
> followed by the data for the serial types
>
> The issue are as I see them:
>
> The payload length varint above, this is the sum of 3 + 4 above plus all of
> the following data forming the record. So as things stand you can't store
> any record where the sum of the bytes in the serial types array and the
> actual data that follows is greater than MAXVARINT because the total length
> must be stored in 1. (MAXVARINT is actually max positive varint - see
> below).
>

Good point. But still, MAXVARINT is 64-bit (see below) not 32-bit.

The record format makes extensive use of the variable-length integer or
> varint representation of 64-bit signed integers defined above.
>


> If you want to use one of the reserved serial types to store a blob of 6GB
> then the serial type itself must be capable of storing the size of the
> blob. Currently, a blob has *any* serial type of >= 12 and even, so the
> maximum size for a blob is (MAXVARINT-12)/2 i.e. *any* even serial type >=
> 12 and a text serial type is any odd serial type >= 13. All of the
> remaining utilised serial types (i.e. those <= 9) refer to fixed length
> data (ints and a 64 bit real).
>

I understand that. That's why I put the length in the "old style" blob
value itself.
But again, the varint encodes a 64-bit signed integer, and the "new style"
blob could
be assumed if the blob length exceed 2GiB (or 4 GiB), not even resorting to
the
two reserved serial types.


> The remaining 2 serial types (remember these are just two bits from a
> 64-bit serial type, each serial type is not a separate varint in its own
> right) could be used to signify something like a 128-bit integer or some
> other fixed-length data type, but, 1 bit by definition cannot store an
> arbitrary length value.
>

I understand that (see above). But using the level of indirection of storing
in the record only the meta-data of the blob, e.g. its full length, its
in-record
length (in case using 10, or 11 serial type, which cannot encode the length
like the traditional text and blob serial types), and the ordered list of
blob
pages to read the blob from, seems completely possible.


> I guess that the change Richard mentions (to up to 4GB) would be by
> treating the varints as unsigned integers, rather than signed as they
> currently are. This could be done (as far as I can see) for all varints
> other than the rowid without affecting existing DBs.
>

That would be an implementation limitation though, not a file format
limitation.

Again, I'm probably naive here, but I still don't clearly see the file
format limitation,
and that's what I'm trying to understand. I completely accept this would be
a lot of
work and that the incentive for Richard to do it is rather low, to
extremely low, although
of course that does bum me out, I have to admit :), but really
understanding the
limitation I'm not seeing now is what I'm after here. Thanks, --DD

PS: The alternate scheme of assuming new-style blob for length > 4 GiB,
which is more backward-compatible, could be further refined via a pragma to
put it lower, make the DB incompatible with older SQLite versions, but no
more
than the many other opt-in features old versions don't support.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB sizes beyond 2GB?

[Paul Sanderson]

I am sure Richard will correct me if I am wrong. But...

The format for a record is

1. payload length varint
2. rowid varint (optional)
3. serial type array varint
4. serial types
followed by the data for the serial types

The issue are as I see them:

The payload length varint above, this is the sum of 3 + 4 above plus all of
the following data forming the record. So as things stand you can't store
any record where the sum of the bytes in the serial types array and the
actual data that follows is greater than MAXVARINT because the total length
must be stored in 1. (MAXVARINT is actually max positive varint - see
below).

If you want to use one of the reserved serial types to store a blob of 6GB
then the serial type itself must be capable of storing the size of the
blob. Currently, a blob has *any* serial type of >= 12 and even, so the
maximum size for a blob is (MAXVARINT-12)/2 i.e. *any* even serial type >=
12 and a text serial type is any odd serial type >= 13. All of the
remaining utilised serial types (i.e. those <= 9) refer to fixed length
data (ints and a 64 bit real).

The remaining 2 serial types (remember these are just two bits from a
64-bit serial type, each serial type is not a separate varint in its own
right) could be used to signify something like a 128-bit integer or some
other fixed-length data type, but, 1 bit by definition cannot store an
arbitrary length value.

I guess that the change Richard mentions (to up to 4GB) would be by
treating the varints as unsigned integers, rather than signed as they
currently are. This could be done (as far as I can see) for all varints
other than the rowid without affecting existing DBs.











Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 28 March 2017 at 11:08, Dominique Devienne  wrote:

> On Tue, Mar 28, 2017 at 11:08 AM, Richard Hipp  wrote:
>
> > On 3/27/17, Andrew Cunningham  wrote:
> > > Is it likely the maximum BLOB size will be increased in a not too
> distant
> > > future version of SQLite?
> >
> > The maximum blob size could, in theory, be increased to 4GiB.  But the
> > current file format will not accommodate anything larger than that.
>
>
> Any chance you'd elaborate on which the format is blocking here?
> I have no doubt you're right, but I'd really appreciate a better
> understanding of that limitation.
>
> As a naive developer, I can see the Record Format [1] uses a varint, which
> can go up to 64-bit integers.
> And also that there are Serial Types 10,11, which are "Not used. Reserved
> for expansion".
>
> Which combined with The B-tree Page Format [2], which has only 4 page
> types,
> while a whole bytes is available for blob pages, a new blob-specific page
> type would seem possible.
>
> Given the above, I can (wrongly) imagine use Record Type 10 for "new-style
> blobs",
> which store a varint length for the "blob index" that follows, where that
> blob index is a ordered list
> of page-specific page numbers (as varints or not) where that blob is
> stored.
>
> In such a scheme, updating a single byte of a blob requires changing 1 blob
> page,
> and the page(s) storing the "blob index"; and blobs can also be expanded or
> contracted
> transitionally w/o having to rewrite the whole blob.
>
> I'm just trying to understand how/where that mental model is wrong. Thanks,
> --DD
> ___
> 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] BLOB sizes beyond 2GB?

[Dominique Devienne]

On Tue, Mar 28, 2017 at 11:08 AM, Richard Hipp  wrote:

> On 3/27/17, Andrew Cunningham  wrote:
> > Is it likely the maximum BLOB size will be increased in a not too distant
> > future version of SQLite?
>
> The maximum blob size could, in theory, be increased to 4GiB.  But the
> current file format will not accommodate anything larger than that.


Any chance you'd elaborate on which the format is blocking here?
I have no doubt you're right, but I'd really appreciate a better
understanding of that limitation.

As a naive developer, I can see the Record Format [1] uses a varint, which
can go up to 64-bit integers.
And also that there are Serial Types 10,11, which are "Not used. Reserved
for expansion".

Which combined with The B-tree Page Format [2], which has only 4 page types,
while a whole bytes is available for blob pages, a new blob-specific page
type would seem possible.

Given the above, I can (wrongly) imagine use Record Type 10 for "new-style
blobs",
which store a varint length for the "blob index" that follows, where that
blob index is a ordered list
of page-specific page numbers (as varints or not) where that blob is stored.

In such a scheme, updating a single byte of a blob requires changing 1 blob
page,
and the page(s) storing the "blob index"; and blobs can also be expanded or
contracted
transitionally w/o having to rewrite the whole blob.

I'm just trying to understand how/where that mental model is wrong. Thanks,
--DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] All versions compatible "very big" estimatedCost (Virtual tables)

[Dominique Devienne]

On Tue, Mar 28, 2017 at 11:00 AM, Hick Gunter  wrote:

> >FWIW, I've often wondered about the cost estimates of real tables versus
> virtual tables, especially since many vtables implementations don't involve
> real IO but pure in-memory computations. There's >very little advice or
> documentation on this important subject, and Max's email reveals that
> empirical testing leading to ad-hoc heuristics are vulnerable to breaking
> when SQLite itself evolves. More >guidance and perhaps even some "normative"
> >documentation is needed IMHO. Thanks, --DD __
> _
>
> The "estimated cost" is described as "how many disk IO operations are
> expected". Version higher than 3.8.2 allow setting an "estimatedRows"
> (default: 25) and versions above 3.9.0 allow setting a flag that indicates
> that 0 or 1 rows will be returned (i.e. the constraints form a "unique
> index").
>

Thanks for the RTFM. That helps. Looks like I have about 3 years worth of
catch-up to do on sqlite3_index_info.

Sorry for hijacking Max's thread, which is why his costs seems to be
ignored. --DD

  /* Fields below are only available in SQLite 3.8.2 and later */
  sqlite3_int64 estimatedRows;/* Estimated number of rows returned */
  /* Fields below are only available in SQLite 3.9.0 and later */
  int idxFlags;  /* Mask of SQLITE_INDEX_SCAN_* flags */
  /* Fields below are only available in SQLite 3.10.0 and later */
  sqlite3_uint64 colUsed;/* Input: Mask of columns used by statement */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB sizes beyond 2GB?

[Richard Hipp]

On 3/27/17, Andrew Cunningham  wrote:
> HI,
> Is it likely the maximum BLOB size will be increased in a not too distant
> future version of SQLite?
>

The maximum blob size could, in theory, be increased to 4GiB.  But the
current file format will not accommodate anything larger than that.

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

Re: [sqlite] All versions compatible "very big" estimatedCost (Virtual tables)

[Hick Gunter]

>FWIW, I've often wondered about the cost estimates of real tables versus 
>virtual tables, especially since many vtables implementations don't involve 
>real IO but pure in-memory computations. There's >very little advice or 
>documentation on this important subject, and Max's email reveals that 
>empirical testing leading to ad-hoc heuristics are vulnerable to breaking when 
>SQLite itself evolves. More >guidance and perhaps even some "normative"
>documentation is needed IMHO. Thanks, --DD 
>___

The "estimated cost" is described as "how many disk IO operations are 
expected". Version higher than 3.8.2 allow setting an "estimatedRows" (default: 
25) and versions above 3.9.0 allow setting a flag that indicates that 0 or 1 
rows will be returned (i.e. the constraints form a "unique index").


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] BLOB sizes beyond 2GB?

[Andrew Cunningham]

HI,
Is it likely the maximum BLOB size will be increased in a not too distant
future version of SQLite?

In a world of machines where 1TB memory is not unusual the current  upper
limit of ~2GB is proving to be restrictive for my use.

One might suggest that storing binary data of that size using SQLite is
abusing SQLite and an alternative, such as HDF , should be used.  To a
certain extent that is true, but having to manage multiple files with some
data in SQLite and other data in HDF files brings along another set of
complications. And SQLite reads/writes BLOBs at disk access speeds so
SQLite does not suffer from efficiency issues. Plus SQLite supports partial
BLOB read/write.

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

Re: [sqlite] All versions compatible "very big" estimatedCost (Virtual tables)

[Dominique Devienne]

On Tue, Mar 28, 2017 at 10:26 AM, Max Vlasov  wrote:

> I sometimes use virtual tables to implement some kind of one-to-many
> output. One of examples mentioned previously was the comma list virtual
> table when a field containing comma-separated values might be used to
> output rows of values from this list. Other example - performing
> regexp-like query against a text and outputting the result columns. But
> this involves assuming that some columns of the virtual table are required
> "input" parameters while the other is "output".  For example, for comma
> list, the required parameter is the list, the output - extracted values.
> The consequence of this trick is that this virtual table is not fully
> functional one, so you can't query select * from it, it's useful only by
> providing where or "join .. on" clause containing the required "input"
> parameters.
>
> I usually encouraged sqlite to provide all required input parameters by
> reporting a "cheap" value of estimatedCost when I recognized my "input"
> parameters in xBestIndex call and providing an "expensive" values for any
> other cases. This usually worked for simple and complex cases until
> 3.8.0.0. It seems the version introduced Next-Generation Query Planner and
> I noticed that in some complex cases my cheap-expensive recommendations
> were ignored, so even when I noticed in the debugger that cheap value was
> provided for the right index and expensive for all other cases, the
> following xFilter provided not all values required probably deciding it
> knew better :). Before this I used cheap value 1 and expensive value 1.
> Testing (3.17.0) revealed that in some cases multiplying expensive by x100
> helped, but other cases required increasing this value even more.
>
> So, what is the maximum reasonable value of estimatedCost that will not
> turn sqlite into possible overflow errors while telling at the same time
> that I consider some variant very, very expensive? Or maybe changing cheap
> from 1 to 0 will do the trick?
>

FWIW, I've often wondered about the cost estimates of real tables versus
virtual tables,
especially since many vtables implementations don't involve real IO but
pure in-memory
computations. There's very little advice or documentation on this important
subject, and
Max's email reveals that empirical testing leading to ad-hoc heuristics are
vulnerable to
breaking when SQLite itself evolves. More guidance and perhaps even some
"normative"
documentation is needed IMHO. Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] All versions compatible "very big" estimatedCost (Virtual tables)

[Max Vlasov]

Hi,

I sometimes use virtual tables to implement some kind of one-to-many
output. One of examples mentioned previously was the comma list virtual
table when a field containing comma-separated values might be used to
output rows of values from this list. Other example - performing
regexp-like query against a text and outputting the result columns. But
this involves assuming that some columns of the virtual table are required
"input" parameters while the other is "output".  For example, for comma
list, the required parameter is the list, the output - extracted values.
The consequence of this trick is that this virtual table is not fully
functional one, so you can't query select * from it, it's useful only by
providing where or "join .. on" clause containing the required "input"
parameters.

I usually encouraged sqlite to provide all required input parameters by
reporting a "cheap" value of estimatedCost when I recognized my "input"
parameters in xBestIndex call and providing an "expensive" values for any
other cases. This usually worked for simple and complex cases until
3.8.0.0. It seems the version introduced Next-Generation Query Planner and
I noticed that in some complex cases my cheap-expensive recommendations
were ignored, so even when I noticed in the debugger that cheap value was
provided for the right index and expensive for all other cases, the
following xFilter provided not all values required probably deciding it
knew better :). Before this I used cheap value 1 and expensive value 1.
Testing (3.17.0) revealed that in some cases multiplying expensive by x100
helped, but other cases required increasing this value even more.

So, what is the maximum reasonable value of estimatedCost that will not
turn sqlite into possible overflow errors while telling at the same time
that I consider some variant very, very expensive? Or maybe changing cheap
from 1 to 0 will do the trick?

Thanks

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

Re: [sqlite] Second beta for SQLite 3.18.0.

[Clemens Ladisch]

Daniel Polski wrote:
> Can using "PRAGMA optimize" in one thread create a situation where
> "BEGIN IMMEDIATE TRANSACTION" in another thread fails?

While the optimization is done, the database is locked, just as with
any other transaction that does writes.  So any other thread has to use
a timeout.

> Is there any risk of "optimization fighting" if several threads are
> using the same database but with different work load?

At the moment, it only runs ANALYZE, which (in theory) never hurts.

The documentation says that in the future, it will "record usage and
performance information from the current session in the database file so
that it will be available to "optimize" pragmas run by future database
connections."  So it will not matter which connection runs the PRAGMA.


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

Re: [sqlite] Second beta for SQLite 3.18.0.

[Daniel Polski]

Den 2017-03-24 kl. 08:09, skrev Daniel Polski:

The "PRAGMA optimize" looks interesting.

- Can using "PRAGMA optimize" in one thread create a situation where 
"BEGIN IMMEDIATE TRANSACTION" in another thread fails? (The threads 
are using different connections)


- Is there any risk of "optimization fighting" if several threads are 
using the same database but with different work load? (i.e. can an 
optimization replace another optimization, or do they just accumulate?)


Unrelated:
Is there any public information about feature/enhancement ideas in 
early stages? The actual development can be seen in the commit 
history, but I mean a more general overview when you discuss & plan 
for future enhancements. Just out of curiosity.


No information regarding the questions above?

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