Re: [sqlite] sqlite3 feature or regression

Wed, 08 Mar 2017 02:01:49 -0800 


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

On Mon, 6 Mar 2017 18:34:40 -0500
Richard Hipp <d...@sqlite.org> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to