sqlite3 feature or regression

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

sqlite3 feature or regression

Vermes Mátyás-2
Hi,
I resend my earlier mail, now with an URL instead of attachement:

http://comfirm.hu/pub/sqlite3-regression.rb

The linked ruby script demonstrates a feature of the newer sqlite3 libraries, which may be a regression.
--
Vermes Mátyás  <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Richard Hipp-3
For the benefit of those of us who do not do Ruby, perhaps you could
explain in words what you think it is that SQLite is doing
incorrectly?

On 3/6/17, Vermes Mátyás <[hidden email]> wrote:

> Hi,
> I resend my earlier mail, now with an URL instead of attachement:
>
> http://comfirm.hu/pub/sqlite3-regression.rb
>
> The linked ruby script demonstrates a feature of the newer sqlite3
> libraries, which may be a regression.
> --
> Vermes Mátyás  <[hidden email]>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Vermes Mátyás
This post has NOT been accepted by the mailing list yet.
    
 puts "Updates"
 cnt=-1
 db.execute("select szamla,megnevezes from proba") do |row|  #(select)
     print cnt+=1," ",row[0]," ",row[1],"\n"
     db.execute( "update proba set megnevezes=? where szamla=?", row[1]+"*", row[0] ) #(update)
     if( cnt<20 )
         sleep(1)
     else
         break  #no endless loop
     end
 end

This is a loop. The loop iterates on the rows of a select statement (which does not include an order by clause). In the loop each row is (would be) updated: megnevezes+="*". But instead of every row, select gives again and again the rows that  have already updated. This makes an endless loop.

It is much better to  see  the program to run, then read about it.  So I propose to run the linked script.


       
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Clemens Ladisch
In reply to this post by Vermes Mátyás-2
Vermes Mátyás wrote:
> http://comfirm.hu/pub/sqlite3-regression.rb

>     db.execute("select szamla,megnevezes from proba") do |row|
>         ...
>         db.execute( "update proba set megnevezes=? where szamla=?", row[1]+"*", row[0] )

The equivalent Python code would be:

  for row in db.execute("select ... from proba"):
    db.execute("update proba ...")

> The linked ruby script demonstrates a feature of the newer sqlite3 libraries, which may be a regression.

I do not know what you expect to happen, or what actually happens, but
changing a table and reading it through a query at the same time has an
unspecified result.  In particular, the database might either fetch the
next result on demand from the actual table or have some result rows
already precomputed, so it is undefined whether continuing the SELECT
sees the old values or the new values or both or neither.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Dominique Devienne
On Tue, Mar 7, 2017 at 9:36 AM, Clemens Ladisch <[hidden email]> wrote:

> Vermes Mátyás wrote:
> > http://comfirm.hu/pub/sqlite3-regression.rb
>
> >     db.execute("select szamla,megnevezes from proba") do |row|
> >         ...
> >         db.execute( "update proba set megnevezes=? where szamla=?",
> row[1]+"*", row[0] )
>
> The equivalent Python code would be:
>
>   for row in db.execute("select ... from proba"):
>     db.execute("update proba ...")
>
> > The linked ruby script demonstrates a feature of the newer sqlite3
> libraries, which may be a regression.
>
> I do not know what you expect to happen, or what actually happens, but
> changing a table and reading it through a query at the same time has an
> unspecified result.  In particular, the database might either fetch the
> next result on demand from the actual table or have some result rows
> already precomputed, so it is undefined whether continuing the SELECT
> sees the old values or the new values or both or neither.
>

Right, unless you use WAL mode for MVCC, and do the updates on a separate
connection and transaction.
That way you iterate on the old rows at the time (snapshot, SCN, etc...)
the select transaction starts,
and the updates (on that other connection, in a different transaction) do
not affect that select.

At least that's the conceptual model I have in my head for SQLite in WAL
mode.
If that's incorrect, please let me know. Thanks, --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Vermes Mátyás-2
In reply to this post by Richard Hipp-3
On Mon, 6 Mar 2017 18:34:40 -0500
Richard Hipp <[hidden email]> wrote:

> For the benefit of those of us who do not do Ruby, perhaps you could
> explain in words what you think it is that SQLite is doing
> incorrectly?


I am not a Ruby programmer either nor a real SQLite user. I am interested in writing  SQL interfaces to <a href="http://github.com/mrev11/ccc3">CCC</a> to various databases.  Ruby was chosen only because it can be run everywhere.   Just run the script: A select of ten rows turns into an endless loop.

Consider my post as a bug report. I do not need any workaround, and do know how to use WAL or duplicate database connections.

--
Vermes Mátyás  <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

James K. Lowden
In reply to this post by Clemens Ladisch
On Tue, 7 Mar 2017 09:36:34 +0100
Clemens Ladisch <[hidden email]> wrote:

> I do not know what you expect to happen, or what actually happens, but
> changing a table and reading it through a query at the same time has
> an unspecified result.  

It is also unnecessarily complex and slow.  

To the OP, Vermes: if you are updating row-by-row, then you are
combining some information in your application with other information
in the database.  Instead of combining that information using
application logic, use the DBMS.  Insert the application's information
into the database, and use a single UPDATE statement to apply it.  

SQL lets you work with sets.  Instead of thinking about each
"megnevezes", think about the set of "megnevezes" that share a
particular "szamla".  You'll write less code that way, and get done
sooner.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Vermes Mátyás-2
> It is also unnecessarily complex and slow.  

The script demonstrates a regression (a bug). It is written in Ruby so that everybody can run it, and see its _results_. It is absolutely not interesting that it is slow or complex.
--
Vermes Mátyás  <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

R Smith
In reply to this post by Vermes Mátyás-2

On 2017/03/07 5:00 PM, Vermes Mátyás wrote:

> On Mon, 6 Mar 2017 18:34:40 -0500
> Richard Hipp <[hidden email]> wrote:
>
> I am not a Ruby programmer either nor a real SQLite user. I am
> interested in writing SQL interfaces to <a
> href="http://github.com/mrev11/ccc3">CCC</a> to various databases.
> Ruby was chosen only because it can be run everywhere. Just run the
> script: A select of ten rows turns into an endless loop.
> Consider my post as a bug report. I do not need any workaround, and do know how to use WAL or duplicate database connections.
>

Well, therein lies the rub. We can't find what the bug is. There
certainly is no bug, no endless execution and no wrong answer when we do
the same script in pure SQL (See details below) nor if we do it in C
using the C API (on which the system below runs and I have tested in C,
just to be sure). So this means either you have a Ruby connector that is
bugged, or you have a real case of SQLite regression, but until you can
show the problem in SQL script or C code using the C API, there is no
bug we can reproduce.

To put this another way: The devs can't fix Ruby code, they can only fix
faulty SQL execution and faulty C API's.

Example SQL code:
While I might be missing something important (which you are welcome to
highlight), this SQL script demonstrates all the steps you have showed
as far as execution goes. I include the same script for both 3.16 and
3.17 (which is stated to be a problem edition on Windows in your bug
report) running from the pre-compiled binaries (DLLs) for Windows on a
Windows 10 machine. I don't have a 3.7.9 handy, but I also checked 3.8.7
and it worked too.
All this runs in a Transaction that gets rolled back at the end, but
committing it worked too.


   -- Processing SQL in: E:\Documents\RubyRegressionTest.sql
   -- SQLite version 3.16.1  [ Release: 2017-01-03 ]  on SQLitespeed
version 2.0.2.4.

   -- Script Items: 8          Parameter Count: 0
   -- 2017-03-08 11:28:55.217  |  [Info]       Script Initialized,
Started executing...
   --
================================================================================================

PRAGMA compile_options;

   -- compile_options
   -- ------------------------
   -- COMPILER=gcc-5.2.0
   -- ENABLE_COLUMN_METADATA
   -- ENABLE_FTS3
   -- ENABLE_FTS5
   -- ENABLE_JSON1
   -- ENABLE_RTREE
   -- SYSTEM_MALLOC
   -- THREADSAFE=1


CREATE TABLE proba(
   szamla varchar(24) collate rtrim not null
  ,devnem char(3) collate rtrim
  ,megnevezes varchar(30) collate rtrim
  ,osszeg numeric(17,2)
  ,tulmenflag boolean
  ,konyvkelt date
  ,megjegyzes blob
);

CREATE UNIQUE INDEX proba_primarykey on proba (szamla,devnem);

CREATE INDEX proba_nev on proba (megnevezes,szamla);

INSERT INTO proba (szamla,devnem,megnevezes) VALUES
   ('000000000000000000000000','HUF','BLACK'),
   ('111111111111111111111111','HUF','WHITE'),
   ('222222222222222222222222','HUF','WHITE'),
   ('333333333333333333333333','HUF','BLACK'),
   ('444444444444444444444444','HUF','WHITE'),
   ('555555555555555555555555','HUF','WHITE'),
   ('666666666666666666666666','HUF','BLACK'),
   ('777777777777777777777777','HUF','WHITE'),
   ('888888888888888888888888','HUF','WHITE'),
   ('999999999999999999999999','HUF','BLACK');

/*  What is in the table?  */
SELECT szamla, megnevezes FROM proba;


   --                            | megnevez-
   -- szamla                     | es
   -- -------------------------- | ---------
   -- 000000000000000000000000   | BLACK
   -- 333333333333333333333333   | BLACK
   -- 666666666666666666666666   | BLACK
   -- 999999999999999999999999   | BLACK
   -- 111111111111111111111111   | WHITE
   -- 222222222222222222222222   | WHITE
   -- 444444444444444444444444   | WHITE
   -- 555555555555555555555555   | WHITE
   -- 777777777777777777777777   | WHITE
   -- 888888888888888888888888   | WHITE

/*  Do Updates  */
WITH UL AS (
      SELECT szamla AS UpdKey, megnevezes AS UpdVal FROM proba
)
UPDATE proba SET megnevezes=(SELECT UpdVal||'*' FROM UL WHERE
UpdKey=szamla);


/*  What is in the table now?  */
SELECT szamla, megnevezes from proba;


   --                            | megnevez-
   -- szamla                     | es
   -- -------------------------- | ---------
   -- 000000000000000000000000   | BLACK*
   -- 333333333333333333333333   | BLACK*
   -- 666666666666666666666666   | BLACK*
   -- 999999999999999999999999   | BLACK*
   -- 111111111111111111111111   | WHITE*
   -- 222222222222222222222222   | WHITE*
   -- 444444444444444444444444   | WHITE*
   -- 555555555555555555555555   | WHITE*
   -- 777777777777777777777777   | WHITE*
   -- 888888888888888888888888   | WHITE*

DROP TABLE proba;

   --   Script Stats: Total Script Execution Time:     0d 00h 00m and
00.032s
   --                 Total Script Query Time:         -- --- --- ---
--.----
   --                 Total Database Rows Changed:     20
   --                 Total Virtual-Machine Steps:     933
   --                 Last executed Item Index:        8
   --                 Last Script Error:
   --
------------------------------------------------------------------------------------------------

   -- 2017-03-08 11:28:55.233  |  [Success]    Script Success.
   -- 2017-03-08 11:28:55.233  |  [Success]    Transaction Rolled back.
   -- -------  DB-Engine Logs (Contains logged information from all DB
connections during run)  ------
   -- [2017-03-08 11:28:55.179] APPLICATION : Script
E:\Documents\RubyRegressionTest.sql started at 11:28:55.179 on 08 March.
   --
================================================================================================



-- AND with the newest downloadable pre-compiled SQLite DLL:

   -- Processing SQL in: E:\Documents\RubyRegressionTest.sql
   -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed
version 2.0.2.4.

   -- Script Items: 8          Parameter Count: 0
   -- 2017-03-08 11:36:33.933  |  [Info]       Script Initialized,
Started executing...
   --
================================================================================================

PRAGMA compile_options;

   -- compile_options
   -- ------------------------
   -- COMPILER=gcc-5.2.0
   -- ENABLE_COLUMN_METADATA
   -- ENABLE_FTS3
   -- ENABLE_FTS5
   -- ENABLE_JSON1
   -- ENABLE_RTREE
   -- SYSTEM_MALLOC
   -- THREADSAFE=1


CREATE TABLE proba(
   szamla varchar(24) collate rtrim not null
  ,devnem char(3) collate rtrim
  ,megnevezes varchar(30) collate rtrim
  ,osszeg numeric(17,2)
  ,tulmenflag boolean
  ,konyvkelt date
  ,megjegyzes blob
);

CREATE UNIQUE INDEX proba_primarykey on proba (szamla,devnem);

CREATE INDEX proba_nev on proba (megnevezes,szamla);

INSERT INTO proba (szamla,devnem,megnevezes) VALUES
   ('000000000000000000000000','HUF','BLACK'),
   ('111111111111111111111111','HUF','WHITE'),
   ('222222222222222222222222','HUF','WHITE'),
   ('333333333333333333333333','HUF','BLACK'),
   ('444444444444444444444444','HUF','WHITE'),
   ('555555555555555555555555','HUF','WHITE'),
   ('666666666666666666666666','HUF','BLACK'),
   ('777777777777777777777777','HUF','WHITE'),
   ('888888888888888888888888','HUF','WHITE'),
   ('999999999999999999999999','HUF','BLACK');

/*  What is in the table?  */
SELECT szamla, megnevezes FROM proba;


   --                            | megnevez-
   -- szamla                     | es
   -- -------------------------- | ---------
   -- 000000000000000000000000   | BLACK
   -- 333333333333333333333333   | BLACK
   -- 666666666666666666666666   | BLACK
   -- 999999999999999999999999   | BLACK
   -- 111111111111111111111111   | WHITE
   -- 222222222222222222222222   | WHITE
   -- 444444444444444444444444   | WHITE
   -- 555555555555555555555555   | WHITE
   -- 777777777777777777777777   | WHITE
   -- 888888888888888888888888   | WHITE

/*  Do Updates  */
WITH UL AS (
      SELECT szamla AS UpdKey, megnevezes AS UpdVal FROM proba
)
UPDATE proba SET megnevezes=(SELECT UpdVal||'*' FROM UL WHERE
UpdKey=szamla);


/*  What is in the table now?  */
SELECT szamla, megnevezes from proba;


   --                            | megnevez-
   -- szamla                     | es
   -- -------------------------- | ---------
   -- 000000000000000000000000   | BLACK*
   -- 333333333333333333333333   | BLACK*
   -- 666666666666666666666666   | BLACK*
   -- 999999999999999999999999   | BLACK*
   -- 111111111111111111111111   | WHITE*
   -- 222222222222222222222222   | WHITE*
   -- 444444444444444444444444   | WHITE*
   -- 555555555555555555555555   | WHITE*
   -- 777777777777777777777777   | WHITE*
   -- 888888888888888888888888   | WHITE*

DROP TABLE proba;

   --   Script Stats: Total Script Execution Time:     0d 00h 00m and
00.031s
   --                 Total Script Query Time:         -- --- --- ---
--.----
   --                 Total Database Rows Changed:     20
   --                 Total Virtual-Machine Steps:     911
   --                 Last executed Item Index:        8
   --                 Last Script Error:
   --
------------------------------------------------------------------------------------------------

   -- 2017-03-08 11:36:33.948  |  [Success]    Script Success.
   -- 2017-03-08 11:36:33.948  |  [Success]    Transaction Rolled back.
   -- -------  DB-Engine Logs (Contains logged information from all DB
connections during run)  ------
   -- [2017-03-08 11:36:33.894] APPLICATION : Script
E:\Documents\RubyRegressionTest.sql started at 11:36:33.894 on 08 March.
   --
================================================================================================

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Richard Hipp-3
In reply to this post by Vermes Mátyás-2
On 3/7/17, Vermes Mátyás <[hidden email]> wrote:
>> It is also unnecessarily complex and slow.
>
> The script ... is written in Ruby so that
> everybody can run it, and see its _results_.

The point is that I cannot run your Ruby script on my Ubuntu desktop
because I get an error:

/usr/lib/ruby/2.3.0/rubygems/core_ext/kernel_require.rb:55:in
`require': cannot load such file -- sqlite3 (LoadError)

No doubt there is some magic command (that I do not know) that I could
run that would load the necessary components.  But then I would only
be able to run ruby using whatever version of SQLite the package
supplier choose to bundle.  I would not be able to debug it.  I would
not be able to test a proposed fix.

Perhaps I could take some time and figure out the Ruby language, and
how to compile the SQLite Ruby Gem from scratch, then set up a
development environment so that I could debug a Ruby script.  That
might take a few hours, or a few days.  Why are you wanting me to work
so hard at this?

Wouldn't it be a lot easier if you just tell us what you think the
problem is, in words?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Vermes Mátyás-2
In reply to this post by R Smith

Well, as I wrote, I am not a Ruby programmer. I found this phenomena first in a CCC test program. CCC is a translator, which translate a Clipper-like language to C. So there is a state of a CCC program, which can be considered as pure C. There is no Ruby in it  at all. Ruby version was only written so that I can show the result on this mailing list. The CCC and the Ruby versions produce essentially the same results.


The wrong output on my Ubuntu Xenial box, SQLite 3.11.0  (Ruby script):
---------------------------------------------------------------------------------
 ./sqlite3-regression.rb
no such table: proba
What is in the table?
0 000000000000000000000000 BLACK
1 333333333333333333333333 BLACK
2 666666666666666666666666 BLACK
3 999999999999999999999999 BLACK
4 111111111111111111111111 WHITE
5 222222222222222222222222 WHITE
6 444444444444444444444444 WHITE
7 555555555555555555555555 WHITE
8 777777777777777777777777 WHITE
9 888888888888888888888888 WHITE
Updates
0 000000000000000000000000 BLACK
1 333333333333333333333333 BLACK
2 666666666666666666666666 BLACK
3 999999999999999999999999 BLACK
4 000000000000000000000000 BLACK*
5 333333333333333333333333 BLACK*
6 666666666666666666666666 BLACK*
7 999999999999999999999999 BLACK*
8 000000000000000000000000 BLACK**
9 333333333333333333333333 BLACK**
10 666666666666666666666666 BLACK**
11 999999999999999999999999 BLACK**
12 000000000000000000000000 BLACK***
13 333333333333333333333333 BLACK***
14 666666666666666666666666 BLACK***
15 999999999999999999999999 BLACK***
16 000000000000000000000000 BLACK****
17 333333333333333333333333 BLACK****
18 666666666666666666666666 BLACK****
19 999999999999999999999999 BLACK****
20 000000000000000000000000 BLACK*****
What is in the table?
0 333333333333333333333333 BLACK*****
1 666666666666666666666666 BLACK*****
2 999999999999999999999999 BLACK*****
3 000000000000000000000000 BLACK******
4 111111111111111111111111 WHITE
5 222222222222222222222222 WHITE
6 444444444444444444444444 WHITE
7 555555555555555555555555 WHITE
8 777777777777777777777777 WHITE
9 888888888888888888888888 WHITE
Cleanup


The right output on Ubuntu Precise, SQLite 3.7.9  (Ruby script):
---------------------------------------------------------------------------------
 ./sqlite3-regression.rb
What is in the table?
0 000000000000000000000000 BLACK
1 111111111111111111111111 WHITE
2 222222222222222222222222 WHITE
3 333333333333333333333333 BLACK
4 444444444444444444444444 WHITE
5 555555555555555555555555 WHITE
6 666666666666666666666666 BLACK
7 777777777777777777777777 WHITE
8 888888888888888888888888 WHITE
9 999999999999999999999999 BLACK
Updates
0 000000000000000000000000 BLACK
1 111111111111111111111111 WHITE
2 222222222222222222222222 WHITE
3 333333333333333333333333 BLACK
4 444444444444444444444444 WHITE
5 555555555555555555555555 WHITE
6 666666666666666666666666 BLACK
7 777777777777777777777777 WHITE
8 888888888888888888888888 WHITE
9 999999999999999999999999 BLACK
What is in the table?
0 000000000000000000000000 BLACK*
1 111111111111111111111111 WHITE*
2 222222222222222222222222 WHITE*
3 333333333333333333333333 BLACK*
4 444444444444444444444444 WHITE*
5 555555555555555555555555 WHITE*
6 666666666666666666666666 BLACK*
7 777777777777777777777777 WHITE*
8 888888888888888888888888 WHITE*
9 999999999999999999999999 BLACK*
Cleanup
---------------------------------------------------------------------------------

You may think that the error was introduced in the Ruby between Precise and Xenial. But the CCC program which produce the same behavior is exactly the same binary, copied from one machine to the other.

The list of the CCC program:

function main()

local con
local q,szla,nev
local cnt:=0

    set date format "yyyy-mm-dd"
   
    con:=connect()

    cnt:=0
    q:=con:sqlqueryNew("select szamla, megnevezes from konto.proba")
    while( q:next )
        ? ++cnt, szla:=q:getchar(1), nev:=q:getchar(2)
    end
    q:close
    ?

    //q:=con:sqlqueryNew("select szamla, megnevezes from konto.proba order by devnem") //right
    //q:=con:sqlqueryNew("select szamla, megnevezes from konto.proba order by szamla") //right
    //q:=con:sqlqueryNew("select szamla, megnevezes from konto.proba order by tulmenflag") //right
    q:=con:sqlqueryNew("select szamla, megnevezes from konto.proba") //wrong

    cnt:=0
    while( q:next .and. cnt<20 )
        ? ++cnt, szla:=q:getchar(1), nev:=q:getchar(2)
        con:sqlexec("update konto.proba set megnevezes=:2 where szamla=:1",{szla,nev+"!"})
    end
    q:close
    ?

    cnt:=0
    q:=con:sqlqueryNew("select szamla, megnevezes from konto.proba") //wrong
    while( q:next )
        ? ++cnt, szla:=q:getchar(1), nev:=q:getchar(2)
    end
    q:close
    con:sqlcommit


The (wrong) output of the above program with SQLite version 3.11.0 (CCC, the same binary)
---------------------------------------------------------------------------------
         1 000000000000000000000000 BLACK
         2 333333333333333333333333 BLACK
         3 666666666666666666666666 BLACK
         4 999999999999999999999999 BLACK
         5 111111111111111111111111 WHITE
         6 222222222222222222222222 WHITE
         7 444444444444444444444444 WHITE
         8 555555555555555555555555 WHITE
         9 777777777777777777777777 WHITE
        10 888888888888888888888888 WHITE

         1 000000000000000000000000 BLACK
         2 333333333333333333333333 BLACK
         3 666666666666666666666666 BLACK
         4 999999999999999999999999 BLACK
         5 000000000000000000000000 BLACK!
         6 333333333333333333333333 BLACK!
         7 666666666666666666666666 BLACK!
         8 999999999999999999999999 BLACK!
         9 000000000000000000000000 BLACK!!
        10 333333333333333333333333 BLACK!!
        11 666666666666666666666666 BLACK!!
        12 999999999999999999999999 BLACK!!
        13 000000000000000000000000 BLACK!!!
        14 333333333333333333333333 BLACK!!!
        15 666666666666666666666666 BLACK!!!
        16 999999999999999999999999 BLACK!!!
        17 000000000000000000000000 BLACK!!!!
        18 333333333333333333333333 BLACK!!!!
        19 666666666666666666666666 BLACK!!!!
        20 999999999999999999999999 BLACK!!!!

         1 000000000000000000000000 BLACK!!!!!
         2 333333333333333333333333 BLACK!!!!!
         3 666666666666666666666666 BLACK!!!!!
         4 999999999999999999999999 BLACK!!!!!
         5 111111111111111111111111 WHITE
         6 222222222222222222222222 WHITE
         7 444444444444444444444444 WHITE
         8 555555555555555555555555 WHITE
         9 777777777777777777777777 WHITE
        10 888888888888888888888888 WHITE


The (right) output with SQLite version 3.7.9 (CCC, the same binary)
---------------------------------------------------------------------------------
         1 000000000000000000000000 BLACK
         2 111111111111111111111111 WHITE
         3 222222222222222222222222 WHITE
         4 333333333333333333333333 BLACK
         5 444444444444444444444444 WHITE
         6 555555555555555555555555 WHITE
         7 666666666666666666666666 BLACK
         8 777777777777777777777777 WHITE
         9 888888888888888888888888 WHITE
        10 999999999999999999999999 BLACK

         1 000000000000000000000000 BLACK
         2 111111111111111111111111 WHITE
         3 222222222222222222222222 WHITE
         4 333333333333333333333333 BLACK
         5 444444444444444444444444 WHITE
         6 555555555555555555555555 WHITE
         7 666666666666666666666666 BLACK
         8 777777777777777777777777 WHITE
         9 888888888888888888888888 WHITE
        10 999999999999999999999999 BLACK

         1 000000000000000000000000 BLACK!
         2 111111111111111111111111 WHITE!
         3 222222222222222222222222 WHITE!
         4 333333333333333333333333 BLACK!
         5 444444444444444444444444 WHITE!
         6 555555555555555555555555 WHITE!
         7 666666666666666666666666 BLACK!
         8 777777777777777777777777 WHITE!
         9 888888888888888888888888 WHITE!
        10 999999999999999999999999 BLACK!
---------------------------------------------------------------------------------

There is very small probability, that the CCC and Ruby programs contain the same error, unless the bug is in the common part of the two. The common part is only the SQLite library.  While these are not an exact proof certainly a strong argument. So I think that the SQLite library contains a regression at least since 3.8.7.


> Well, therein lies the rub. We can't find what the bug is. There
> certainly is no bug, no endless execution and no wrong answer when we do
> the same script in pure SQL (See details below) nor if we do it in C
> using the C API (on which the system below runs and I have tested in C,
> just to be sure). So this means either you have a Ruby connector that is
> bugged, or you have a real case of SQLite regression, but until you can
> show the problem in SQL script or C code using the C API, there is no
> bug we can reproduce.
>
> To put this another way: The devs can't fix Ruby code, they can only fix
> faulty SQL execution and faulty C API's.

--
Vermes Mátyás  <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Don V Nielsen
In reply to this post by Vermes Mátyás-2
Vermes, I'm late to the party but would still like to comment. The
problem is the ruby code, not sqlite. The following is what you coded in
the Ruby:

     db.execute("select szamla,megnevezes from proba") do |row|

In some shape or fashion, the result set is getting mangled by the
update. When I debug it, the code is getting deeper than I have the
ability to understand, but the execute is being reevaluated. That is
probably getting more complicated due to the open (non-committed
transaction). Again, this is deeper than my ability to understand.

What you need is a static collection and then iterate over the results:

     (db.execute("select szamla,megnevezes from proba")).each {|row|

- or -

     rows = db.execute("select szamla,megnevezes from proba")
     rows.each {|row|

The above will execute the select, returning a collection of the ten
rows, then iterate for each member of the collection. The collection is
not influenced by the update.

dvn


On 03/07/2017 09:00 AM, Vermes Mátyás wrote:

> On Mon, 6 Mar 2017 18:34:40 -0500
> Richard Hipp <[hidden email]> wrote:
>
>> For the benefit of those of us who do not do Ruby, perhaps you could
>> explain in words what you think it is that SQLite is doing
>> incorrectly?
>
> I am not a Ruby programmer either nor a real SQLite user. I am interested in writing  SQL interfaces to <a href="http://github.com/mrev11/ccc3">CCC</a> to various databases.  Ruby was chosen only because it can be run everywhere.   Just run the script: A select of ten rows turns into an endless loop.
>
> Consider my post as a bug report. I do not need any workaround, and do know how to use WAL or duplicate database connections.
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Simon Slavin-3

On 8 Mar 2017, at 2:16pm, don v nielsen <[hidden email]> wrote:

> Vermes, I'm late to the party but would still like to comment. The problem is the ruby code, not sqlite. The following is what you coded in the Ruby:
>
>    db.execute("select szamla,megnevezes from proba") do |row|
>
> In some shape or fashion, the result set is getting mangled by the update.

That makes sense in terms of how SQLite works.  Your UPDATE is modifying the rows from the database while the SELECT is trying to retrieve them.  Because updating the rows causes them to move about the SELECT gets confused about how far it has got.

Both the solutions DVN suggested prevent this by causing Ruby to gather all the results first, then to apply any UPDATEs.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Vermes Mátyás-2
In reply to this post by Richard Hipp-3
> > The script ... is written in Ruby so that
> > everybody can run it, and see its _results_.
>
> The point is that I cannot run your Ruby script on my Ubuntu desktop
> because I get an error:

Perhaps you have not installed Ruby.

    sudo apt-get install ruby ruby-sqlite3

Then

    ./sqlite3-regression.rb

It works for me on a Raspbian, on which Ruby never was installed earlier.
--
Vermes Mátyás  <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Vermes Mátyás-2
In reply to this post by Richard Hipp-3
>  Why are you wanting me to work so hard at this?

Under no circumstances work hard, I rewrote the program for you to C. The results are attached also, so you do not have to compile it,  if you do not want.  You can download it from http://comfirm.hu/pub/sqlite3-regression.tar.gz.
--
Vermes Mátyás  <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Dan Kennedy-2

On 03/09/2017 06:28 AM, Vermes Mátyás wrote:
>>   Why are you wanting me to work so hard at this?
> Under no circumstances work hard, I rewrote the program for you to C. The results are attached also, so you do not have to compile it,  if you do not want.  You can download it from http://comfirm.hu/pub/sqlite3-regression.tar.gz.


You're updating the "megnevezes" field from within a loop on this SELECT:

     select szamla, megnevezes from konto.proba

With 3.11.0, the scan is probably using the index instead of the table
(since it is smaller on disk and therefore faster):

     create index konto.proba_nev on proba (megnevezes,szamla)

So the update is modifying the index while you are iterating through it.
The results of that can be unpredictable. If you change the SELECT to:

     select szamla, megnevezes from konto.proba ORDER BY rowid

the SELECT will use the main table instead of the index and things will
likely work again.

Dan.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Richard Hipp-3
In reply to this post by Vermes Mátyás-2
On 3/8/17, Vermes Mátyás <[hidden email]> wrote:
> I rewrote the program for you to C.

Thank you for the translation.

Below is the equivalent program in 34 lines of TCL.  (Compare to 101
lines of Ruby and 430 lines of C++.  Everybody: If you are unfamiliar
with the TCL programming language, you would do well to learn it, and
start using it.  It will save you a lot of work.)

-------------------------------------- cut here ----------------------------
package require sqlite3
sqlite3 db :memory:
db eval {
  CREATE TABLE proba(
    szamla VARCHAR(24) COLLATE rtrim NOT NULL,
    devnem CHAR(3) COLLATE rtrim,
    megnevezes VARCHAR(30) COLLATE rtrim,
    osszeg NUMERIC(17,2),
    tulmenflag BOOLEAN,
    konyvkelt DATE,
    megjegyzes BLOB
  );
  CREATE UNIQUE INDEX proba_primarykey ON proba(szamla, devnem);
  CREATE INDEX proba_nev ON proba(megnevezes, szamla);
  INSERT INTO proba(szamla,devnem,megnevezes)
VALUES('000000000000000000000000','HUF','BLACK');
  INSERT INTO proba(szamla,devnem,megnevezes)
VALUES('111111111111111111111111','HUF','WHITE');
  INSERT INTO proba(szamla,devnem,megnevezes)
VALUES('222222222222222222222222','HUF','WHITE');
  INSERT INTO proba(szamla,devnem,megnevezes)
VALUES('333333333333333333333333','HUF','BLACK');
  INSERT INTO proba(szamla,devnem,megnevezes)
VALUES('444444444444444444444444','HUF','WHITE');
  INSERT INTO proba(szamla,devnem,megnevezes)
VALUES('555555555555555555555555','HUF','WHITE');
  INSERT INTO proba(szamla,devnem,megnevezes)
VALUES('666666666666666666666666','HUF','BLACK');
  INSERT INTO proba(szamla,devnem,megnevezes)
VALUES('777777777777777777777777','HUF','WHITE');
  INSERT INTO proba(szamla,devnem,megnevezes)
VALUES('888888888888888888888888','HUF','WHITE');
  INSERT INTO proba(szamla,devnem,megnevezes)
VALUES('999999999999999999999999','HUF','BLACK');
}
db eval BEGIN
set cnt 0
db eval {SELECT szamla, megnevezes FROM proba} {
  incr cnt
  puts "$cnt: $szamla $megnevezes"
  db eval {UPDATE proba SET megnevezes=megnevezes||'*' WHERE szamla=$szamla}
}
db eval COMMIT
-------------------------------------- End ------------------------------------

As Dan already observed, the problem results because you are modifying
an index in the middle of a scan of that index, thereby messing up the
scan.  Don't do that.  The safest approach is to run the query to
completion, then go back and start the loop over UPDATEs.

If you add "ORDER BY +rowid" to the query, that forces the query to
run to completion first and then go through the sorter, before you get
any results back, which solves the problem.



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Vermes Mátyás-2
In reply to this post by Dan Kennedy-2

> With 3.11.0, the scan is probably using the index instead of the table

In this case the phenomena would be a new "feature". Unfortunately this would contradict to the axiom, that the result of a query must be independent of the existence of the indices.
--
Vermes Mátyás  <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

Vermes Mátyás-2
In reply to this post by Richard Hipp-3
Thanks. Naturally I had experimented with several versions of the program, and saw that any ordering makes the  new feature/error disappear. You can see it if you read the comment at the bottom of my original script. But my purpose was the opposite: demonstrate the regression. I am not interested in this thing. I do not have applications based on SQLite. Simply I think that this feature/error cannot be left in its current state, because this is in contradiction with the axiom, that the result of a query must not depend from the existence of an index. I wanted to help you with this bug report, but I cannot do more for that.

> As Dan already observed, the problem results because you are modifying
> an index in the middle of a scan of that index, thereby messing up the
> scan.  Don't do that.  The safest approach is to run the query to
> completion, then go back and start the loop over UPDATEs.
>
> If you add "ORDER BY +rowid" to the query, that forces the query to
> run to completion first and then go through the sorter, before you get
> any results back, which solves the problem.

--
Vermes Mátyás  <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 feature or regression

James K. Lowden
In reply to this post by Vermes Mátyás-2
On Tue, 7 Mar 2017 19:04:47 +0100
Vermes Mátyás <[hidden email]> wrote:

> > It is also unnecessarily complex and slow.  
>
> The script demonstrates a regression (a bug). It is written in Ruby
> so that everybody can run it, and see its _results_. It is absolutely
> not interesting that it is slow or complex.

As has been repeated several times in this thread, the act of updating
a SQLite database while a SELECT is being processed interferes with the
SELECT unless WAL mode is used.  

My observations:

1.  Your row-by-row update is not a good design choice.  

2.  And it doesn't work correctly in SQLite, by design.

3.  SQLite fails to keep its ACID promise: SELECT is not atomic, and
not isolated from UPDATE.  

The ACID fault is a recurring theme on this list.  You are not
the first person bitten by it, and won't be the last.  

--jkl

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12
Loading...