UNION ALL bug in Multi-threading

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

UNION ALL bug in Multi-threading

sanhua.zh
I find a bug that exists in `UNION ALL`.


tldr: `UNION ALL` will not merge the data in different schemas in the moment that one of schema is committed but the another not. BUT, `UNION` will.


Here are the reproduce steps:


Preparation:
1. Prepare a database named "OLD"
1.1 Create a table for "OLD": `CREATE TABLE old(i INTEGER)`
1.2 Insert values into "OLD" to make it large enough: `INSERT INTO old VALUES(?1)`
2. Prepare a database named "NEW"
2.1 Create a table for "NEW": `CREATE TABLE new(i INTEGER)`


Migration:
For thread 1:
1. Attach "OLD" to "NEW" as "oldSchema": `ATTACH OLD AS oldSchema`
2. Migrate data from "OLD" to "NEW" in same transaction. Note that they should be executed with same handle using ATTACH mentioned in 1.
2.1 `BEGIN IMMEDIATE`
2.1 Select one of the row from "OLD": `SELECT i FROM oldSchema.old`
2.2 Insert the row into "NEW": `INSERT INTO main.new VALUES(?1)`
2.3 Delete the row from "OLD": `DELETE FROM oldSchema.old WHERE i == ?1`
2.4 `COMMIT`


For thread 2-N:
1. Create a view that union two tables: `CREATE TEMP VIEW v AS SELECT i FROM oldSchema.old UNION ALL SELECT i FROM main.new`
2. Select one of the value from view: `SELECT i FROM temp.v ORDER BY i LIMIT 1 OFFSET ?1`.
Here is the strange result:
As an example, if the values of 0-999 is inserted into "OLD", then value N should be selected as expected at offset N.
But in these kind of steps, it will not.


It can be a little bit hard to reproduce due to the multi-threading. BUT if it sleeps for a while when committing, it will be much easier to reproduce:
// vdbeCommit method of vdbeaux.c
for(i=0; rc==SQLITE_OK  idb-nDb; i++){
 Btree *pBt = db-aDb[i].pBt;
 sqlite3_sleep(10); // additional sleep here
 if( pBt ){
  rc = sqlite3BtreeCommitPhaseOne(pBt, 0);
 }
}


It seems that the bug happens when one of the schema is committed but the another one is not.
On the other handle, if `UNION ALL` is changed to `UNION` while creating view, the bug will not happen too.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users