SQLite Forum

bug:update from virtual table (geopoly),and no return received

bug:update from virtual table (geopoly),and no return received

(1.1) By eternal (yh2216) on 2021-04-14 11:07:56 edited from 1.0 [link] [source]

sql: update times  set block_id = 6  from pathway where times.block_id = 3;
    rowid       INT,
    vehicle_id  TEXT,
    begin_time  INT,
    end_time    INT,
    area        REAL,
    rect        TEXT,
    block_id    INT,
    layer_index INT
(vehicle_id   TEXT,
begin_time   BIGINT,
end_time     BIGINT,
area         DOUBLE,
rect         TEXT,
block_id     BIGINT,
layer_index  TINYINT);

when I exec the sql,the program will be running for ever;
It may be some error with update from when operate with geopoly or other virtual table!

(2) By Larry Brasfield (larrybr) on 2021-04-14 12:52:05 in reply to 1.1 [link] [source]

I ran your SQL on a couple different versions of the SQLite shell, including a recent trunk tip.1 It completed in the blink of an eye. This leads me to think that if the problem you report represents a library bug, it must have been fixed. Can you please state what "select sqlite_version();" returns?

  1. I ran the CREATE statements before the update statement.

(3) By eternal (yh2216) on 2021-04-14 15:04:17 in reply to 2 [link] [source]

It’s the latest version 3.35.4,and when to exec the sql “ update times set block_id = 6 from pathway where times.block_id = 3;” the table has record which block_id = 3! And when no record meet the condition,it complete very fast!

(4) By Larry Brasfield (larrybr) on 2021-04-14 16:50:45 in reply to 3 [link] [source]

Could you also provide some data or a link to a DB containing data which will replicate your problem? My guessing effort has exhausted my willingness, without any sign so far of excessive execution time.

I am also curious as to what you expect your query to do. (This is independent of the bug issue. Queries should eventually terminate even if strange.)

(9) By eternal (yh2216) on 2021-04-15 01:52:05 in reply to 4 [link] [source]

could you tell me,what can i do to send the sample data to you? and please run the sql "update times set block_id = 33 from pathway where times.block_id = pathway.block_id";

the DDL of table 'times' and talbe 'path' is shown as above!

(10) By Larry Brasfield (larrybr) on 2021-04-15 02:04:13 in reply to 9 [link] [source]

As I mentioned, I ran (all of) your SQL without seeing a problem. I trust the above "33" is a typo relative to your original "3".

How big is the database? Your query is bound to take some time on large datasets. (I'm pretty sure it did not take "for ever", otherwise we would not have seen your original post yet. ;-) A link to it on a file-sharing site would be welcome.

I did look at the query plan for the 3 statements you provided. The query looked like it should terminate, but it is doing a join which appears to be O(N * log N). If I get the data, I will see if it forms some data-dependent loop, but I do not see how that could happen, yet.

(5) By Keith Medcalf (kmedcalf) on 2021-04-14 17:43:30 in reply to 1.1 [link] [source]

The update query is ill-formed.

There are no conditions joining tables times and pathway expressed in the query.

It devolves to simply:

update times set block_id = 6 where block_id == 3;

with a useless reference to table pathway. While this should not be harmful (other than doing a table scan to cross join times and pathway for each candidate where times.block_id == 3) it should not cause a problem. However, depending on how many rows are in the tables, the cross join operation could take a very long time.

(6) By Larry Brasfield (larrybr) on 2021-04-14 18:35:06 in reply to 5 [link] [source]

I can confirm that a join is being done, uselessly. I am not prepared to suggest this represents an optimization opportunity.

(7.1) By eternal (yh2216) on 2021-04-15 01:47:00 edited from 7.0 in reply to 6 [link] [source]


(8) By eternal (yh2216) on 2021-04-15 01:43:49 in reply to 5 [link] [source]

sql:update times set block_id = 33 from pathway where times.block_id = pathway.block_id;

when reference to a geopoly talbe(pathway),and there ara some records in table pathway that meet the where clause,the sql will be running forever!

(11) By Keith Medcalf (kmedcalf) on 2021-04-15 02:17:12 in reply to 8 [link] [source]

And how does this compare to a table containing the same data that is not a geopoly/virtual table?

(12) By eternal (yh2216) on 2021-04-15 06:06:05 in reply to 11 [source]

completed very soon!