Re: [sqlite] Lots of enhancements coming to version 3.24.0 - please test

[Seiji Amalai]

Hey

On Wed, 9 May 2018, 13:12 Seiji Amalai,  wrote:

> Nonono lol
>
> On Tue, 8 May 2018, 15:42 Richard Hipp,  wrote:
>
>> There are a lot of important changes in the SQLite code base since the
>> previous release.  See
>> https://www.sqlite.org/draft/releaselog/current.html for the latest
>> summary.
>>
>> All of our legacy tests pass and many new test cases have been added.
>> Nevertheless, your testing of SQLite in real-world applications is
>> greatly appreciated.  Please consider downloading the latest
>> pre-release snapshot (https://www.sqlite.org/download.html) and giving
>> it a whirl.
>>
>> Report any problems or concerns to this mailing list, or directly to me.
>>
>> Thanks.
>> --
>> 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

Re: [sqlite] Lots of enhancements coming to version 3.24.0 - please test

[Seiji Amalai]

Nonono lol

On Tue, 8 May 2018, 15:42 Richard Hipp,  wrote:

> There are a lot of important changes in the SQLite code base since the
> previous release.  See
> https://www.sqlite.org/draft/releaselog/current.html for the latest
> summary.
>
> All of our legacy tests pass and many new test cases have been added.
> Nevertheless, your testing of SQLite in real-world applications is
> greatly appreciated.  Please consider downloading the latest
> pre-release snapshot (https://www.sqlite.org/download.html) and giving
> it a whirl.
>
> Report any problems or concerns to this mailing list, or directly to me.
>
> Thanks.
> --
> 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

Re: [sqlite] Lots of enhancements coming to version 3.24.0 - please test

[Dominique Pellé]

Richard Hipp  wrote:

> There are a lot of important changes in the SQLite code base since the
> previous release.  See
> https://www.sqlite.org/draft/releaselog/current.html for the latest
> summary.

Some corrections in the draft doc:

In https://www.sqlite.org/draft/releaselog/current.html

"[...] EXPLAIN QUERY PLAN output *an* reformats it [...]"
-> typo: an -> and


In https://www.sqlite.org/draft/c3ref/keyword_check.html

"Put all *indentifier* names [...]"
-> typo: indentifier -> identifier


In https://www.sqlite.org/draft/eqp.html

"EXPLAIN QUERY PLAN is most useful on a SELECT statement, but may also *be*
appear with other statements that [...]"
-> The word "be" should be removed.

"In the *In the* following example [...]"
-> Repeated "In the"

"[...] is *acctually* evaluated [...]
-> typo: acctually -> actually

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

Re: [sqlite] Lots of enhancements coming to version 3.24.0 - please test

[David Raymond]

Nifty.

I tried out the explain query plan on the sample queries in the recursive doc 
(http://www.sqlite.org/lang_with.html), the output is below as a nice example. 
The one comment I have is that the co-routine/sub-query numbers are 
non-deterministic, so any of your tests that check the output of explain query 
plan will have to take into account that those can change on every run of it.


--Mandelbrot Set 3.23.1
selectid|order|from|detail
7|0|0|SCAN TABLE xaxis
5|0|0|COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)
10|0|0|SCAN TABLE yaxis
8|0|0|COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)
4|0|0|SCAN SUBQUERY 5
4|1|1|SCAN SUBQUERY 8
11|0|0|SCAN TABLE m
3|0|0|COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)
2|0|0|SCAN SUBQUERY 3
2|0|0|USE TEMP B-TREE FOR GROUP BY
1|0|0|SCAN SUBQUERY 2
1|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|SCAN SUBQUERY 1


--Mandelbrot Set 3.24.0
QUERY PLAN
|--CO-ROUTINE 0x5BC208
|  |--CO-ROUTINE 0x5BC088
|  |  |--CO-ROUTINE 0x5BC488
|  |  |  |--SETUP
|  |  |  |  |--MATERIALIZE 0x5BC388
|  |  |  |  |  |--SETUP
|  |  |  |  |  |  `--SCAN CONSTANT ROW
|  |  |  |  |  `--RECURSIVE STEP
|  |  |  |  | `--SCAN TABLE xaxis
|  |  |  |  |--MATERIALIZE 0x5BC288
|  |  |  |  |  |--SETUP
|  |  |  |  |  |  `--SCAN CONSTANT ROW
|  |  |  |  |  `--RECURSIVE STEP
|  |  |  |  | `--SCAN TABLE yaxis
|  |  |  |  |--SCAN SUBQUERY 0x5BC388
|  |  |  |  `--SCAN SUBQUERY 0x5BC288
|  |  |  `--RECURSIVE STEP
|  |  | `--SCAN TABLE m
|  |  |--SCAN SUBQUERY 0x5BC488
|  |  `--USE TEMP B-TREE FOR GROUP BY
|  |--SCAN SUBQUERY 0x5BC088
|  `--USE TEMP B-TREE FOR GROUP BY
`--SCAN SUBQUERY 0x5BC208
group_concat(rtrim(t),x'0a')
#
   ..#*..
 ..++.
...+   +
   ..##+*##+.
  .+.##+.
  .+###+.+
  ..++..#.*#+.
 ...+###++###.
  +*.
 #...
  +*.
 ...+###++###.
  ..++..#.*#+.
  .+###+.+
  .+.##+.
   ..##+*##+.
...+   +
 ..++.
   ..#*..
#
+.


--Sudoku 3.23.1
selectid|order|from|detail
2|0|0|SCAN SUBQUERY 3
7|0|0|SCAN TABLE digits
5|0|0|COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)
4|0|0|SCAN TABLE x
4|1|1|SCAN SUBQUERY 5 AS z
4|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 8
11|0|0|SCAN TABLE digits
9|0|0|COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)
8|0|0|SCAN SUBQUERY 9 AS lp
1|0|0|COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)
0|0|0|SCAN SUBQUERY 1


--Sudoku 3.24.0
QUERY PLAN
|--CO-ROUTINE 0x340B08
|  |--SETUP
|  |  |--CO-ROUTINE 0x340E08
|  |  |  `--SCAN CONSTANT ROW
|  |  `--SCAN SUBQUERY 0x340E08
|  `--RECURSIVE STEP
| |--MATERIALIZE 0x340E88
| |  |--SETUP
| |  |  `--SCAN CONSTANT ROW
| |  `--RECURSIVE STEP
| | `--SCAN TABLE digits
| |--SCAN TABLE x
| |--SCAN SUBQUERY 0x340E88 AS z
| `--CORRELATED SCALAR SUBQUERY
||--CO-ROUTINE 0x340F88
||  |--SETUP
||  |  `--SCAN CONSTANT ROW
||  `--RECURSIVE STEP
|| `--SCAN TABLE digits
|`--SCAN SUBQUERY 0x340F88 AS lp
`--SCAN SUBQUERY 0x340B08


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Tuesday, May 08, 2018 10:43 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Lots of enhancements coming to version 3.24.0 - please test

There are a lot of important changes in the SQLite code base since the
previous release.  See
https://www.sqlite.org/draft/releaselog/current.html for the latest
summary.

All of our legacy tests pass and many new test cases have been added.
Nevertheless, your testing of SQLite in real-world applications is
greatly appreciated.  Please consider downloading the latest
pre-release snapshot (https://www.sqlite.org/download.html) and giving
it a whirl.

Report any problems or concerns to this mailing list, or directly to me.

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

Re: [sqlite] Lots of enhancements coming to version 3.24.0 - please test

[Don V Nielsen]

Two thumbs up, Dan. Thanks for the clarification.

On Tue, May 8, 2018 at 11:11 AM, Dan Kennedy  wrote:

> On 05/08/2018 11:04 PM, Don V Nielsen wrote:
>
>> " UPDATE  avoids writing
>>>
>> database pages that do not actually change. For example, "UPDATE t1 SET
>> x=25 WHERE y=?" becomes a no-op if the value in column x is already 25."
>>
>> I have a ridiculous assertion concerning this, but please here me out.
>>
>> Given rows where y = ? are true
>> When an update is made to those rows
>> Then I expect the row ChangeDate column to be set to the current date/time
>> (dt)
>>
>> Even if x = 25, would I want it changed to show that thew row was touched
>> by the update ran on dt.
>>
>
> Triggers will still fire. Auth callbacks will still be made. And
> sqlite3_total_changes() will still be updated. The change is at quite a low
> level - after doing all the work to compose the new database record, if
> SQLite notices that it is exactly the same as the record being replaced,
> the IO is just omitted.
>
> Does that assuage your concerns?
>
> Dan.
>
>
>
>
>
>   If it is not touched, then I may be left
>> investigating why counts against dt to not equal the anticipated number of
>> changes. For example, as part of my qc procedure, I would do a count to
>> see
>> how many rows should be changed before running the update. The qc count
>> would be base on y=?. When the update executes, it will not reflect the
>> number of updates, no would a post-update frequency count reflect it
>> because the ChangeDate on those rows would not be updated.
>>
>> I realize my pre|post-update now has to be y = ? AND x <> 25. But I wonder
>> what else might be affected by this. It has a complexity to automated qc
>> procedures.
>>
>> Just my thoughts.
>> dvn
>>
>>
>>
>> On Tue, May 8, 2018 at 9:42 AM, Richard Hipp  wrote:
>>
>> There are a lot of important changes in the SQLite code base since the
>>> previous release.  See
>>> https://www.sqlite.org/draft/releaselog/current.html for the latest
>>> summary.
>>>
>>> All of our legacy tests pass and many new test cases have been added.
>>> Nevertheless, your testing of SQLite in real-world applications is
>>> greatly appreciated.  Please consider downloading the latest
>>> pre-release snapshot (https://www.sqlite.org/download.html) and giving
>>> it a whirl.
>>>
>>> Report any problems or concerns to this mailing list, or directly to me.
>>>
>>> Thanks.
>>> --
>>> 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-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] Lots of enhancements coming to version 3.24.0 - please test

[Dan Kennedy]

On 05/08/2018 11:04 PM, Don V Nielsen wrote:

" UPDATE  avoids writing

database pages that do not actually change. For example, "UPDATE t1 SET
x=25 WHERE y=?" becomes a no-op if the value in column x is already 25."

I have a ridiculous assertion concerning this, but please here me out.

Given rows where y = ? are true
When an update is made to those rows
Then I expect the row ChangeDate column to be set to the current date/time
(dt)

Even if x = 25, would I want it changed to show that thew row was touched
by the update ran on dt.


Triggers will still fire. Auth callbacks will still be made. And 
sqlite3_total_changes() will still be updated. The change is at quite a 
low level - after doing all the work to compose the new database record, 
if SQLite notices that it is exactly the same as the record being 
replaced, the IO is just omitted.


Does that assuage your concerns?

Dan.





  If it is not touched, then I may be left
investigating why counts against dt to not equal the anticipated number of
changes. For example, as part of my qc procedure, I would do a count to see
how many rows should be changed before running the update. The qc count
would be base on y=?. When the update executes, it will not reflect the
number of updates, no would a post-update frequency count reflect it
because the ChangeDate on those rows would not be updated.

I realize my pre|post-update now has to be y = ? AND x <> 25. But I wonder
what else might be affected by this. It has a complexity to automated qc
procedures.

Just my thoughts.
dvn



On Tue, May 8, 2018 at 9:42 AM, Richard Hipp  wrote:


There are a lot of important changes in the SQLite code base since the
previous release.  See
https://www.sqlite.org/draft/releaselog/current.html for the latest
summary.

All of our legacy tests pass and many new test cases have been added.
Nevertheless, your testing of SQLite in real-world applications is
greatly appreciated.  Please consider downloading the latest
pre-release snapshot (https://www.sqlite.org/download.html) and giving
it a whirl.

Report any problems or concerns to this mailing list, or directly to me.

Thanks.
--
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lots of enhancements coming to version 3.24.0 - please test

[Don V Nielsen]

> " UPDATE  avoids writing
database pages that do not actually change. For example, "UPDATE t1 SET
x=25 WHERE y=?" becomes a no-op if the value in column x is already 25."

I have a ridiculous assertion concerning this, but please here me out.

Given rows where y = ? are true
When an update is made to those rows
Then I expect the row ChangeDate column to be set to the current date/time
(dt)

Even if x = 25, would I want it changed to show that thew row was touched
by the update ran on dt. If it is not touched, then I may be left
investigating why counts against dt to not equal the anticipated number of
changes. For example, as part of my qc procedure, I would do a count to see
how many rows should be changed before running the update. The qc count
would be base on y=?. When the update executes, it will not reflect the
number of updates, no would a post-update frequency count reflect it
because the ChangeDate on those rows would not be updated.

I realize my pre|post-update now has to be y = ? AND x <> 25. But I wonder
what else might be affected by this. It has a complexity to automated qc
procedures.

Just my thoughts.
dvn



On Tue, May 8, 2018 at 9:42 AM, Richard Hipp  wrote:

> There are a lot of important changes in the SQLite code base since the
> previous release.  See
> https://www.sqlite.org/draft/releaselog/current.html for the latest
> summary.
>
> All of our legacy tests pass and many new test cases have been added.
> Nevertheless, your testing of SQLite in real-world applications is
> greatly appreciated.  Please consider downloading the latest
> pre-release snapshot (https://www.sqlite.org/download.html) and giving
> it a whirl.
>
> Report any problems or concerns to this mailing list, or directly to me.
>
> Thanks.
> --
> 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] Lots of enhancements coming to version 3.24.0 - please test

[Richard Hipp]

There are a lot of important changes in the SQLite code base since the
previous release.  See
https://www.sqlite.org/draft/releaselog/current.html for the latest
summary.

All of our legacy tests pass and many new test cases have been added.
Nevertheless, your testing of SQLite in real-world applications is
greatly appreciated.  Please consider downloading the latest
pre-release snapshot (https://www.sqlite.org/download.html) and giving
it a whirl.

Report any problems or concerns to this mailing list, or directly to me.

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