Re: [sqlite] [EXTERNAL] sqlite as 'streaming mode' query engine?

[Hick Gunter]

If your stream mode virtual table stalls in it's xNext method until the next 
row from the stream is available (or the stream is closed, in which case it 
needs to set an EOF flag for the xEof method to return), then each row will be 
aggregated as it becomes available. The result will not be available until 
after the stream is closed.

Receive row a
Aggregate row a
Receive row b
Aggregate row b
...
Stream ends
xEof returns true
result row(s) returned

If your stream is not repeatable, be sure to make it the primary table in any 
join, i.e. SELECT ... FROM  CROSS JOIN ...

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nelson, Erik - 2
Gesendet: Donnerstag, 25. Jänner 2018 20:03
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] sqlite as 'streaming mode' query engine?

Sqlite aggregation functions receive one call for each row in the underlying 
results set.  Has anyone on the list done a virtual table that's a based on a 
stream?

Something conceptually along the lines of

Insert row a
Insert row b
begin aggregation on rows a, b
insert row c
insert row d
update aggregation with rows c, d
...
finish aggregation when the stream of rows ends


--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
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] UTF8 and NUL

[J Decker]

NUL is a valid utf8 character
but FF is never valid.  (would be like a 36 bit length specification)
and practically anthing more than F8 is invalid utf8 character.
Other than BOM
https://en.wikipedia.org/wiki/Byte_order_mark#UTF-8
EF BB BF 239 187 191

// EF - 80 | 3b - 80 | 3f
( 0xfeff  )


Many Windows  programs
(including Windows Notepad )
add the bytes 0xEF, 0xBB, 0xBF at the start of any document saved as UTF-8.
Th

(Not that BOM is even required, because, it's already ordered bytes)
--
But anYway FF could be used as a string terminator instead of 00.  It is
never legal in any utf-8 sequence.
(F8,F9,FA,FB,FC,FD,FE,FF)
F8 would be a 5 byte encoding, but that is more code points than unicode
has allocated.  It could be potentially useful to permit a little extra
space in sequences , so I would avoid F8(F9,FA,FB) and stick to FC-FF for
possible control characters.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atoi64 bug(s)

[Cezary H. Noweta]

Hello,

On 2018-01-26 00:54, petern wrote:

I am interested to see your solution where NUMERIC CAST has sensible
interpretation between MAX_INT and MAX_REAL.


The patch focuses on problems with TEXT=>NUM conversions. The sole 
exception is INT=>FLOAT, when a value could lose an information.



IMO, simple INT saturation is not convenient for easy overflow detection in
SQL.   So there is work to be done where the upcasted number is large but
not quite large enough for REAL saturation. Nearby upcasted INTs must sort
sensibly.


Indeed -- INT saturation is for more predictability of INTEGER CAST's 
results, when converted from TEXTs.



Also, what happens to overflowing hex constants and from BLOB casts?


HEX is not touched by CASTs and AFFINITIes (from TEXT).


It is important to curate such patches in case the priority for execution
speed/size cannot be reconciled with accuracy and generality.  If your
improvements make v3.23 slower or larger than v3.22, they may be rejected.


My patch is not to be released as a replacement of SQLite. Speed tests 
showed that it is faster (even 10-30%) then SQLite, but only due to a 
fact that SQLite until 3.21 did not use an exponentiation by squares -- 
this is improved in SQLite 3.22. (I have not made the patch for 3.22 
yet.) The patch is to illustrate the problem and as a reference.



Nevertheless, I think users who prioritize dependability, accuracy, and
generality over slightly degraded executable speed/size will be very
interested to have your long form improvements.


I'm using few compilers and platforms. There are many surprising 
constructs which can be 30% faster on old VS2005 (the last which 
produced EXE for Win16 -- aka 98/ME) then on VS2015. Fountain of bugs is 
a main problem in Watcom. MSVC has a buggy preprocessor (I have not 
tried VS2017 yet) which is grinding all macro from the beginning to the 
end, every round until there is nothing to expand. GCC had a minor 
problem with ASAN (mentioned on this list), and with a comparison of 
doubles<=>int64s (mentioned/fixed recently by DRH), and so on, and so 
on. If the patch is to illustrate the problem (if any), I do not want to 
obfuscate it and I'm trying to use most universal constructs.


Besides that IMHO the problem (with trailing spaces and loss of 
precision) is too complicated to be effectively (and universally) 
resolved by someone other (me for sure) then the team.


On the other side, the problem with saturation is completely resolvable 
by adding 5 characters to ``Atoi64()''.


-- best regards

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

Re: [sqlite] Atoi64 bug(s)

[petern]

Cezary.  Your short form fix for the spurious NUMERIC CAST due to trailing
space was definitely received in your original posting.

I am interested to see your solution where NUMERIC CAST has sensible
interpretation between MAX_INT and MAX_REAL.
IMO, simple INT saturation is not convenient for easy overflow detection in
SQL.   So there is work to be done where the upcasted number is large but
not quite large enough for REAL saturation. Nearby upcasted INTs must sort
sensibly.

Also, what happens to overflowing hex constants and from BLOB casts?

It is important to curate such patches in case the priority for execution
speed/size cannot be reconciled with accuracy and generality.  If your
improvements make v3.23 slower or larger than v3.22, they may be rejected.
Nevertheless, I think users who prioritize dependability, accuracy, and
generality over slightly degraded executable speed/size will be very
interested to have your long form improvements.

Best regards.
Peter

On Thu, Jan 25, 2018 at 3:15 PM, Cezary H. Noweta 
wrote:

> Hello,
>
> On 2018-01-25 22:58, petern wrote:
>
>> Thank you for expanding on your detailed observations.
>> If you can, please post the long patch at your customary patch site
>> http://sqlite.chncc.eu/
>>
> I was convinced that I had publicized my patch already. For the people who
> are interested in the patch, please give me a few hours to cut my new
> not-so-completely implemented functionalities from my draft version.
>
> -- best regards
>
> Cezary H. Noweta
> ___
> 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] Atoi64 bug(s)

[Cezary H. Noweta]

Hello,

On 2018-01-25 22:58, petern wrote:

Thank you for expanding on your detailed observations.
If you can, please post the long patch at your customary patch site
http://sqlite.chncc.eu/
I was convinced that I had publicized my patch already. For the people 
who are interested in the patch, please give me a few hours to cut my 
new not-so-completely implemented functionalities from my draft version.


-- best regards

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

Re: [sqlite] Atoi64 bug(s)

[Cezary H. Noweta]

Hello,

On 2018-01-25 22:08, Stephen Chrzanowski wrote:

According to https://www.sqlite.org/datatype3.html

*2. Storage Classes and DatatypesEach value stored in an SQLite database
(or manipulated by the database engine) has one of the following storage
classes:NULL. The value is a NULL value.INTEGER. The value is a
signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the
magnitude of the value.REAL. The value is a floating point value,
stored as an 8-byte IEEE floating point number.TEXT. The value is a
text string, stored using the database encoding (UTF-8, UTF-16BE or
UTF-16LE).BLOB. The value is a blob of data, stored exactly as it was
input.*

*Each column in an SQLite 3 database is assigned one of the following type
affinities:*

- *TEXT*
- *NUMERIC*
- *INTEGER*
- *REAL*
- *BLOB*

*...*

*A column with NUMERIC affinity may contain values using all five storage
classes. When text data is inserted into a NUMERIC column, the storage
class of the text is converted to INTEGER or REAL (in order of preference)
if such conversion is lossless and reversible.*

*...*


3 paragraphs.


My understanding of this is that since you're using strings as integer
literals, its going to use integer calculations, and not go back to using
"REAL" calculations as you'll be losing information.  According to
http://en.cppreference.com/w/cpp/language/types if the engine was able to
predetermine that your string (Which looks like an integer, not a REAL)
it'd have more than a just over (1.7 * 10^308) but you're going to lose a
lot of accuracy as the IEEE specs state that there's 15 digit accuracy, and
you're asking for 23.  You can't just stuff that many numbers in a 64-bit
number and maintain accuracy, plus, you're looking at data loss since
you're going from a 23 digit number to a maximum of 15.  So according to
documentation, this is working as intended.  IMO, the query you gave should
FAULT as its an overrun and what you're asking it to do is impossible at a
64-bit integer level.


For me, it looks like an attorney's speech in a court. From 3 paragraphs 
going more or less around a problem you draw conclusions which are 
contradictory to the explicit documentation's statement 
(https://www.sqlite.org/lang_expr.html#castexpr):


INTEGER: ``When casting a TEXT value to INTEGER, the longest possible 
prefix of the value that can be interpreted as an integer number is 
extracted from the TEXT value and the remainder ignored. [...] If there 
is no prefix that can be interpreted as an integer number, the result of 
the conversion is 0.''



To test, I ran this:

C:\Users\schrzanowski.YKF>sqlite3
SQLite version 3.20.0 2017-08-01 13:24:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT CAST('12345678901234567890123' AS INTEGER);
4807115922877859019


If this is good, then what prefix of ``12345678901234567890123'' could 
be interpreted as ``4807115922877859019''?



sqlite> SELECT CAST('12345678901234567890123.0' AS INTEGER);
4807115922877859019


If this is good, then what prefix of ``12345678901234567890123'' could 
be interpreted as ``4807115922877859019''?



I'm not a developer of SQLite3 by any stretch of the imagination, so I
can't say for certain whether your patch is going to be accepted or not.
In my view, I've never had to deal with numbers that large, and I suspect
this is going to end up being looked at as an edge case.
Ok, to summarize, in your opinion, (a) ``CAST('...' AS INTEGER) => (not 
so) quite random number [NSQRN follows]'' and (b) losing of an info 
(900...001 => 900...000, etc.) is good (or, at least better then (a) ... 
=> {SMALLEST,LARGEST}_INT64; (b) not losing of an info), because:


(a) It is impossible to express some number '112676...878676878' by a 
64bit variable, then


(a.1) CAST('toobignum' AS INTEGER) => [NSQRN]) is ok and conforming to 
the doc (``working as intended''). CAST('toobignumstring' AS INTEGER) => 
LARGEST_INT64) is not ok and not conforming to the doc (``not working as 
intended''), if so, then report that CAST of string numbers 
<'9223372036854775809';'18446744073709551615'> AS INTEGER ``is not 
working as intended'', or


(a.2) both ways are ``working as intended'', if so, then I proposed a 
patch which requires 5 characters and is making a SQLite behave in a 
more (IMHO) reasonable way;


(b) I'm sorry - I haven't got your point here. Appending a trailing 
space would lose 10 bits because 'toobignumstring' lose accuracy when 
converting to FLOAT?? There is other thing then (a). I did not postulate 
to convert '' into 
exact INT64 value. I agree with you that it is impossible. Simply, I do 
not agree that appending a space to a number which _can_ be fully 
represented causes that the number needlessly loses an accuracy, and 
have proposed the (second part of the) patch which requi

Re: [sqlite] Atoi64 bug(s)

[petern]

Thank you for expanding on your detailed observations.
If you can, please post the long patch at your customary patch site
http://sqlite.chncc.eu/
Also, in the link text, please make note of the exact SQLite version the
patch is for.

The lesson I draw is that unconditionally correct queries must check the
input type for all id range and offset computations.
So unfortunately, if the query depends on id input, it must be wrapped by
inefficient CASE typeof() protection statements.
On the bright side, INT overflow is at least detectable:

.mode line
WITH id AS (SELECT (0x7FFF)id)  SELECT
id,typeof(id),id+1,typeof(id+1) FROM id;
-- id = 9223372036854775807
-- typeof(id) = integer
-- id+1 = 9.22337203685478e+18
-- typeof(id+1) = real

Peter

On Thu, Jan 25, 2018 at 12:36 PM, Cezary H. Noweta 
wrote:

> Hello,
>
> On 2018-01-25 19:54, petern wrote:
>
>> CREATE TABLE IF NOT EXISTS a (a INTEGER);
>>
>
> INTEGER == NUMERIC in case of column declarations.
>
> -- Note however, the constant table expression works fine...
>>
>> SELECT CAST(column1 AS INTEGER) FROM (VALUES
>> ('901'),('901 '));
>> -- "CAST(column1 AS INTEGER)"
>> -- 901
>> -- 901
>>
>
> This is due a fact that ``CAST AS INTEGER'' blindly calls ``Atoi64()'' and
> returns its result.
>
> INTEGER/FLOAT handling/recognition is a bit more complicated in other
> places, what causes that '901 ' will become 9e18.
>
> For the same reason ``CAST ('901X' AS INTEGER)'' gives INT
> 901, while ``SELECT CAST ('901X' AS
> NUMERIC);'' gives FLOAT 9e18.
>
> Due to a bit disordered treatment of values, my own patch involves many
> changes. The ``concise'' patch, which I proposed in my original post,
> eliminates: (1) (mod 2^64) bug, and (2) an erratic treatment of INTs and
> FLOATs in some (not all) places. It changes only one line and adds one, and
> does not change affinity/type system at all. (As opposed to my ``long''
> version patch).
>
> 1. IMHO (mod 2^64) bug is serious. Speculative example: my app has a
> dangerous number ``1234'' and is checking input text against it; then
> ``295147905179352827090'' -- OK, go on -- says my app; then suddenly
> ``CAST('295147905179352827090' AS INTEGER)'' ==> BOOM: 1234.
>
> 2. STRING to be INT must be reacting to ``attention'' command immediately
> (even small, chaste space at the end will bother); to be FLOAT, it can
> carousel from dusk till next dusk all the time.
>
> There was no noticeable side effects (besides a performance) in old 32bit
> INT days, however now, 10 bits of each INT can be going to a vacuum.
>
> -- best regards
>
> Cezary H. Noweta
> ___
> 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] Atoi64 bug(s)

[Stephen Chrzanowski]

According to https://www.sqlite.org/datatype3.html










*2. Storage Classes and DatatypesEach value stored in an SQLite database
(or manipulated by the database engine) has one of the following storage
classes:NULL. The value is a NULL value.INTEGER. The value is a
signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the
magnitude of the value.REAL. The value is a floating point value,
stored as an 8-byte IEEE floating point number.TEXT. The value is a
text string, stored using the database encoding (UTF-8, UTF-16BE or
UTF-16LE).BLOB. The value is a blob of data, stored exactly as it was
input.*

*Each column in an SQLite 3 database is assigned one of the following type
affinities:*

   - *TEXT*
   - *NUMERIC*
   - *INTEGER*
   - *REAL*
   - *BLOB*

*...*

*A column with NUMERIC affinity may contain values using all five storage
classes. When text data is inserted into a NUMERIC column, the storage
class of the text is converted to INTEGER or REAL (in order of preference)
if such conversion is lossless and reversible.*

*...*

My understanding of this is that since you're using strings as integer
literals, its going to use integer calculations, and not go back to using
"REAL" calculations as you'll be losing information.  According to
http://en.cppreference.com/w/cpp/language/types if the engine was able to
predetermine that your string (Which looks like an integer, not a REAL)
it'd have more than a just over (1.7 * 10^308) but you're going to lose a
lot of accuracy as the IEEE specs state that there's 15 digit accuracy, and
you're asking for 23.  You can't just stuff that many numbers in a 64-bit
number and maintain accuracy, plus, you're looking at data loss since
you're going from a 23 digit number to a maximum of 15.  So according to
documentation, this is working as intended.  IMO, the query you gave should
FAULT as its an overrun and what you're asking it to do is impossible at a
64-bit integer level.

To test, I ran this:

C:\Users\schrzanowski.YKF>sqlite3
SQLite version 3.20.0 2017-08-01 13:24:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT CAST('12345678901234567890123' AS INTEGER);
4807115922877859019
sqlite> SELECT CAST('12345678901234567890123.0' AS INTEGER);
4807115922877859019
sqlite> SELECT CAST('12345678901234567890123.0' AS REAL);
1.23456789012346e+22
sqlite> SELECT CAST('12345678901234567890123' AS REAL);
1.23456789012346e+22


I'm not a developer of SQLite3 by any stretch of the imagination, so I
can't say for certain whether your patch is going to be accepted or not.
In my view, I've never had to deal with numbers that large, and I suspect
this is going to end up being looked at as an edge case.

On Thu, Jan 25, 2018 at 3:36 PM, Cezary H. Noweta 
wrote:

> Hello,
>
> On 2018-01-25 19:54, petern wrote:
>
>> CREATE TABLE IF NOT EXISTS a (a INTEGER);
>>
>
> INTEGER == NUMERIC in case of column declarations.
>
> -- Note however, the constant table expression works fine...
>>
>> SELECT CAST(column1 AS INTEGER) FROM (VALUES
>> ('901'),('901 '));
>> -- "CAST(column1 AS INTEGER)"
>> -- 901
>> -- 901
>>
>
> This is due a fact that ``CAST AS INTEGER'' blindly calls ``Atoi64()'' and
> returns its result.
>
> INTEGER/FLOAT handling/recognition is a bit more complicated in other
> places, what causes that '901 ' will become 9e18.
>
> For the same reason ``CAST ('901X' AS INTEGER)'' gives INT
> 901, while ``SELECT CAST ('901X' AS
> NUMERIC);'' gives FLOAT 9e18.
>
> Due to a bit disordered treatment of values, my own patch involves many
> changes. The ``concise'' patch, which I proposed in my original post,
> eliminates: (1) (mod 2^64) bug, and (2) an erratic treatment of INTs and
> FLOATs in some (not all) places. It changes only one line and adds one, and
> does not change affinity/type system at all. (As opposed to my ``long''
> version patch).
>
> 1. IMHO (mod 2^64) bug is serious. Speculative example: my app has a
> dangerous number ``1234'' and is checking input text against it; then
> ``295147905179352827090'' -- OK, go on -- says my app; then suddenly
> ``CAST('295147905179352827090' AS INTEGER)'' ==> BOOM: 1234.
>
> 2. STRING to be INT must be reacting to ``attention'' command immediately
> (even small, chaste space at the end will bother); to be FLOAT, it can
> carousel from dusk till next dusk all the time.
>
> There was no noticeable side effects (besides a performance) in old 32bit
> INT days, however now, 10 bits of each INT can be going to a vacuum.
>
>
> -- best regards
>
> Cezary H. Noweta
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
__

Re: [sqlite] Atoi64 bug(s)

[Cezary H. Noweta]

Hello,

On 2018-01-25 19:54, petern wrote:

CREATE TABLE IF NOT EXISTS a (a INTEGER);


INTEGER == NUMERIC in case of column declarations.


-- Note however, the constant table expression works fine...

SELECT CAST(column1 AS INTEGER) FROM (VALUES
('901'),('901 '));
-- "CAST(column1 AS INTEGER)"
-- 901
-- 901


This is due a fact that ``CAST AS INTEGER'' blindly calls ``Atoi64()'' 
and returns its result.


INTEGER/FLOAT handling/recognition is a bit more complicated in other 
places, what causes that '901 ' will become 9e18.


For the same reason ``CAST ('901X' AS INTEGER)'' gives 
INT 901, while ``SELECT CAST ('901X' AS 
NUMERIC);'' gives FLOAT 9e18.


Due to a bit disordered treatment of values, my own patch involves many 
changes. The ``concise'' patch, which I proposed in my original post, 
eliminates: (1) (mod 2^64) bug, and (2) an erratic treatment of INTs and 
FLOATs in some (not all) places. It changes only one line and adds one, 
and does not change affinity/type system at all. (As opposed to my 
``long'' version patch).


1. IMHO (mod 2^64) bug is serious. Speculative example: my app has a 
dangerous number ``1234'' and is checking input text against it; then 
``295147905179352827090'' -- OK, go on -- says my app; then suddenly 
``CAST('295147905179352827090' AS INTEGER)'' ==> BOOM: 1234.


2. STRING to be INT must be reacting to ``attention'' command 
immediately (even small, chaste space at the end will bother); to be 
FLOAT, it can carousel from dusk till next dusk all the time.


There was no noticeable side effects (besides a performance) in old 
32bit INT days, however now, 10 bits of each INT can be going to a vacuum.


-- best regards

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

[sqlite] sqlite as 'streaming mode' query engine?

[Nelson, Erik - 2]

Sqlite aggregation functions receive one call for each row in the underlying 
results set.  Has anyone on the list done a virtual table that's a based on a 
stream?

Something conceptually along the lines of

Insert row a
Insert row b
begin aggregation on rows a, b
insert row c
insert row d
update aggregation with rows c, d
...
finish aggregation when the stream of rows ends


--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atoi64 bug(s)

[petern]

Confirmed.
sqlite> .version
SQLite 3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
zlib version 1.2.8
gcc-4.8.4

--FYI some background:
--

--min 64b signed int:
SELECT CAST(0x8000 AS INTEGER);
-- "CAST(0x8000 AS INTEGER)"
-- -9223372036854775808

--max 64b signed int:
SELECT CAST(0x7FFF AS INTEGER);
-- "CAST(0x7FFF AS INTEGER)"
-- 9223372036854775807

-- Note how place counts align with numeric example:

-- 9223372036854775807
-- 901

-- I reproduce problem with spurious CAST due to trailing zero as follows:
---

CREATE TABLE IF NOT EXISTS a (a INTEGER);
INSERT INTO a VALUES ('901'),('901 ');
SELECT * FROM a;
-- a
-- 901
-- 900

-- Note however, the constant table expression works fine...

SELECT CAST(column1 AS INTEGER) FROM (VALUES
('901'),('901 '));
-- "CAST(column1 AS INTEGER)"
-- 901
-- 901

---
Peter














On Thu, Jan 25, 2018 at 9:14 AM, Stephen Chrzanowski 
wrote:

> RE the first question, the largest positive 64-bit number is
> 18446744073709551615, or, 18,446,744,073,709,551,615.  Your number is
> overloading that value.
> 12,345,678,901,234,567,890,123
> vs
> 18,446,744,073,709,551,615
>
> AFAIK, SQLite uses max of 64-bit integer math, not 128.
>
>
> On Thu, Jan 25, 2018 at 10:04 AM, Cezary H. Noweta 
> wrote:
>
> > Hello,
> >
> > About year age I reported some strange behavior:
> >
> > 1.
> >
> > https://www.sqlite.org/lang_expr.html#castexpr:
> >
> > INTEGER: ``When casting a TEXT value to INTEGER, the longest possible
> > prefix of the value that can be interpreted as an integer number is
> > extracted from the TEXT value and the remainder ignored. [...] If there
> is
> > no prefix that can be interpreted as an integer number, the result of the
> > conversion is 0.''
> >
> > sqlite> SELECT CAST('12345678901234567890123' AS INTEGER);
> > 4807115922877859019
> >
> > What prefix of ``12345678901234567890123'' can be interpreted as
> > ``4807115922877859019''?
> >
> > 2.
> >
> > Why trailing spaces are allowed in FLOATs and not in INTEGERs? It would
> > not be a problem, if INTs were 32bit. Now, a value is lost:
> >
> > sqlite> CREATE TABLE a (a NUMERIC);
> > sqlite> INSERT INTO a VALUES ('901'),('
> 901
> > ');
> > sqlite> SELECT * FROM a;
> > 901
> > 900
> >
> > 2a.
> >
> > https://www.sqlite.org/lang_expr.html#castexpr:
> >
> > NUMERIC: ``Casting a TEXT or BLOB value into NUMERIC first does a forced
> > conversion into REAL but then further converts the result into INTEGER if
> > and only if the conversion from REAL to INTEGER is lossless and
> > reversible.''
> >
> > Why a cast to universal AFFINITY (NUMERIC -- designed to represent both
> > INTEGERs as well as FLOATs) could be skewed into FLOAT direction loosing
> a
> > INTEGER's LSBits? INT is not 32bit wide. Hopefully above is not true:
> >
> > sqlite> SELECT CAST('901' AS NUMERIC);
> > 901
> >
> > However due to pt 2.:
> >
> > sqlite> SELECT CAST('901 ' AS NUMERIC);
> > 900
> >
> > The most concise patch (without, for example ``SELECT
> > CAST('901X' AS NUMERIC);'') contains only two lines:
> > ==
> > --- sqlite-src-322/src/util.c   2018-01-23 01:57:26.0
> +0100
> > +++ sqlite-src-322/src/util.c   2018-01-25 14:22:18.428460300
> +0100
> > @@ -625,6 +625,7 @@
> >  zNum += (enc&1);
> >}
> >while( zNum > +  while( zNum >if( zNum >  if( *zNum=='-' ){
> >neg = 1;
> > @@ -638,7 +639,7 @@
> >for(i=0; &zNum[i]='0' && c<='9'; i+=incr){
> >  u = u*10 + c - '0';
> >}
> > -  if( u>LARGEST_INT64 ){
> > +  if( 19 < i || u>LARGEST_INT64 ){
> >  *pNum = neg ? SMALLEST_INT64 : LARGEST_INT64;
> >}else if( neg ){
> >  *pNum = -(i64)u;
> > ==
> >
> > -- best regards
> >
> > Cezary H. Noweta
> > ___
> > 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] Atoi64 bug(s)

[Stephen Chrzanowski]

RE the first question, the largest positive 64-bit number is
18446744073709551615, or, 18,446,744,073,709,551,615.  Your number is
overloading that value.
12,345,678,901,234,567,890,123
vs
18,446,744,073,709,551,615

AFAIK, SQLite uses max of 64-bit integer math, not 128.


On Thu, Jan 25, 2018 at 10:04 AM, Cezary H. Noweta 
wrote:

> Hello,
>
> About year age I reported some strange behavior:
>
> 1.
>
> https://www.sqlite.org/lang_expr.html#castexpr:
>
> INTEGER: ``When casting a TEXT value to INTEGER, the longest possible
> prefix of the value that can be interpreted as an integer number is
> extracted from the TEXT value and the remainder ignored. [...] If there is
> no prefix that can be interpreted as an integer number, the result of the
> conversion is 0.''
>
> sqlite> SELECT CAST('12345678901234567890123' AS INTEGER);
> 4807115922877859019
>
> What prefix of ``12345678901234567890123'' can be interpreted as
> ``4807115922877859019''?
>
> 2.
>
> Why trailing spaces are allowed in FLOATs and not in INTEGERs? It would
> not be a problem, if INTs were 32bit. Now, a value is lost:
>
> sqlite> CREATE TABLE a (a NUMERIC);
> sqlite> INSERT INTO a VALUES ('901'),('901
> ');
> sqlite> SELECT * FROM a;
> 901
> 900
>
> 2a.
>
> https://www.sqlite.org/lang_expr.html#castexpr:
>
> NUMERIC: ``Casting a TEXT or BLOB value into NUMERIC first does a forced
> conversion into REAL but then further converts the result into INTEGER if
> and only if the conversion from REAL to INTEGER is lossless and
> reversible.''
>
> Why a cast to universal AFFINITY (NUMERIC -- designed to represent both
> INTEGERs as well as FLOATs) could be skewed into FLOAT direction loosing a
> INTEGER's LSBits? INT is not 32bit wide. Hopefully above is not true:
>
> sqlite> SELECT CAST('901' AS NUMERIC);
> 901
>
> However due to pt 2.:
>
> sqlite> SELECT CAST('901 ' AS NUMERIC);
> 900
>
> The most concise patch (without, for example ``SELECT
> CAST('901X' AS NUMERIC);'') contains only two lines:
> ==
> --- sqlite-src-322/src/util.c   2018-01-23 01:57:26.0 +0100
> +++ sqlite-src-322/src/util.c   2018-01-25 14:22:18.428460300 +0100
> @@ -625,6 +625,7 @@
>  zNum += (enc&1);
>}
>while( zNum +  while( zNumif( zNum  if( *zNum=='-' ){
>neg = 1;
> @@ -638,7 +639,7 @@
>for(i=0; &zNum[i]='0' && c<='9'; i+=incr){
>  u = u*10 + c - '0';
>}
> -  if( u>LARGEST_INT64 ){
> +  if( 19 < i || u>LARGEST_INT64 ){
>  *pNum = neg ? SMALLEST_INT64 : LARGEST_INT64;
>}else if( neg ){
>  *pNum = -(i64)u;
> ==
>
> -- best regards
>
> Cezary H. Noweta
> ___
> 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] SQLite DELUXE Re: sqlite command line tool NUL support

[Luke Amery]

https://www.sqlite.org/privatebranch.html

This command both creates the new repository and populates it with all the
latest SQLite could
->
This command both creates the new repository and populates it with all the
latest SQLite code

On Wed, Jan 24, 2018 at 10:44 PM Richard Hipp  wrote:

> On 1/24/18, petern  wrote:
> > Have you worked out an automated way for your changes to shadow and
> > auto-merge from the official trunk?
>
> https://www.sqlite.org/privatebranch.html
>
> --
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Atoi64 bug(s)

[Cezary H. Noweta]

Hello,

About year age I reported some strange behavior:

1.

https://www.sqlite.org/lang_expr.html#castexpr:

INTEGER: ``When casting a TEXT value to INTEGER, the longest possible 
prefix of the value that can be interpreted as an integer number is 
extracted from the TEXT value and the remainder ignored. [...] If there 
is no prefix that can be interpreted as an integer number, the result of 
the conversion is 0.''


sqlite> SELECT CAST('12345678901234567890123' AS INTEGER);
4807115922877859019

What prefix of ``12345678901234567890123'' can be interpreted as 
``4807115922877859019''?


2.

Why trailing spaces are allowed in FLOATs and not in INTEGERs? It would 
not be a problem, if INTs were 32bit. Now, a value is lost:


sqlite> CREATE TABLE a (a NUMERIC);
sqlite> INSERT INTO a VALUES 
('901'),('901 ');

sqlite> SELECT * FROM a;
901
900

2a.

https://www.sqlite.org/lang_expr.html#castexpr:

NUMERIC: ``Casting a TEXT or BLOB value into NUMERIC first does a forced 
conversion into REAL but then further converts the result into INTEGER 
if and only if the conversion from REAL to INTEGER is lossless and 
reversible.''


Why a cast to universal AFFINITY (NUMERIC -- designed to represent both 
INTEGERs as well as FLOATs) could be skewed into FLOAT direction loosing 
a INTEGER's LSBits? INT is not 32bit wide. Hopefully above is not true:


sqlite> SELECT CAST('901' AS NUMERIC);
901

However due to pt 2.:

sqlite> SELECT CAST('901 ' AS NUMERIC);
900

The most concise patch (without, for example ``SELECT 
CAST('901X' AS NUMERIC);'') contains only two lines:

==
--- sqlite-src-322/src/util.c   2018-01-23 01:57:26.0 +0100
+++ sqlite-src-322/src/util.c   2018-01-25 14:22:18.428460300 +0100
@@ -625,6 +625,7 @@
 zNum += (enc&1);
   }
   while( zNum='0' && c<='9'; i+=incr){
 u = u*10 + c - '0';
   }
-  if( u>LARGEST_INT64 ){
+  if( 19 < i || u>LARGEST_INT64 ){
 *pNum = neg ? SMALLEST_INT64 : LARGEST_INT64;
   }else if( neg ){
 *pNum = -(i64)u;
==

-- best regards

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

[sqlite] Small sqlite3_analyzer doc link issue

[Dominique Devienne]

FYI: The "additional explanation" link (of target
https://www.sqlite.org/'#defs') in
https://www.sqlite.org/sqlanalyze.html is not working properly. Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make some changes to the source code

[Nick]

Thank you Simon, I totally understand you. 

And still hope for someone to give me some advice about my wal+mmap (Map
file when opening it and do not truncate the file) .

Thanks.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make some changes to the source code

[Simon Slavin]

On 25 Jan 2018, at 5:55am, Nick  wrote:

> The one thing that bothers me the most is that I have no way to check my
> code, as there is a testvfs in sqlite test. So could you please review my
> train of thought about my wal+mmap? (Map file when opening it and do not
> truncate the file)

That is way beyond my area of competence.  I’m not a good C programmer.  But 
there are other people on this list who are.

> By the way, is there a possibly way to submit patch to sqlite? 

Although SQLite is 'open source' it's not your typical open source project.  
Almost all the code in SQLite was written by a small development team.  The 
team tends to accept development ideas from the outside (e.g. this list) and 
write the code themselves rather than to accept code written outside the team.  
This allows them to enforce a consistent programming style and to ensure code 
is not copied from other projects under licence.  In fact it's a problem if you 
show them your code since they then might be accused of copying it.  (Not to 
accuse you personally of copying code, I'm just layout out the general 
principle.)

There’s also a requirement of usefulness vs. code size.  One of SQLite's 
jealously-guarded advantages is that it's tiny.  It has to fit in embedded 
processors, on phones, and in various hand-held devices with very limited 
space.  SQLite doesn’t add size to the project just because it’s easy, or 
because someone wrote the code and it works.  It adds it because it would be 
useful to a lot of users.  I've had an SQL-conformance change neglected because 
even though SQLite would conform more closely to SQL it wouldn't allow users to 
do anything more.

If you find your new facility useful then you can use it yourself, by 
maintaining your own fork.  But a minor speed improvement for a single 
platform, dependent on an OS facility, may not get integrated into the main 
projects.

Sorry if I have disappointed you.

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