DELETE and SELECT evaluate the same WHERE condition differently

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

DELETE and SELECT evaluate the same WHERE condition differently

Jan Slodicka
This post has NOT been accepted by the mailing list yet.
I just observed a strange sqlite behavior that I am unable to explain.

SELECT/DELETE commads listed bellow use identical WHERE condition so that I would expect them to evaluate it identically. However, that's not the case. (Commands look strangely to me but that's how the customer designed them.)

1. This SELECT lists 72778 rows:
SELECT bego_accountrelationshipid, statuscode, bego_territoryid FROM X
WHERE X.bego_accountrelationshipid NOT IN
(SELECT DISTINCT X.bego_accountrelationshipid FROM X
inner JOIN account AS L0 ON (X.bego_accountid = L0.accountid)
inner JOIN X AS L1 ON (L0.accountid = L1.bego_accountid AND (L1.statuscode IN ('1')))
inner JOIN territory AS L2 ON (L1.bego_territoryid = L2.territoryid)
inner JOIN bego_systemuser_territory AS L3 ON (L2.territoryid=L3.territoryid AND (L3.systemuserid='9074d20c-ac84-e211-bd81-005056a51b72'))
WHERE (X.statuscode IN ('1')));

2. This DELETE deletes 65955 rows.
DELETE FROM X
WHERE X.bego_accountrelationshipid NOT IN
(SELECT DISTINCT X.bego_accountrelationshipid FROM X
inner JOIN account AS L0 ON (X.bego_accountid = L0.accountid)
inner JOIN X AS L1 ON (L0.accountid = L1.bego_accountid AND (L1.statuscode IN ('1')))
inner JOIN territory AS L2 ON (L1.bego_territoryid = L2.territoryid)
inner JOIN bego_systemuser_territory AS L3 ON (L2.territoryid=L3.territoryid AND (L3.systemuserid='9074d20c-ac84-e211-bd81-005056a51b72'))
WHERE (X.statuscode IN ('1')));

3. When I run the DELETE again, it deletes 0 rows. (Expected.)
When I run the first SELECT again, it lists 6823 rows. (Expected: 72778-65955=6823)

Using sqlite 3.8.8.1.
Slite logging does not send any infomation.
Integrity check does not reveal any problem.

Note that I formatted the commands for easier reading and substituted ? placeholders by the literals. (In the reality they are sent as command parameters.)

Is anybody able to explain this behavior?
Reply | Threaded
Open this post in threaded view
|

Re: DELETE and SELECT evaluate the same WHERE condition differently

Jan Slodicka
This post has NOT been accepted by the mailing list yet.
Here is additional info that I found out since I submitted the post:

- Internal subquery yields empty result set.
- The table X has 72778 rows.
- The command "DELETE FROM X WHERE bego_accountrelationshipid NOT IN ()" works correctly, i.e. deletes all 72778 rows
Reply | Threaded
Open this post in threaded view
|

Re: DELETE and SELECT evaluate the same WHERE condition differently

Jan Slodicka
This post has NOT been accepted by the mailing list yet.
And here are the query plans:


EXPLAIN QUERY PLAN SELECT bego_accountrelationshipid, statuscode, bego_territoryid FROM X WHERE X.bego_accountrelationshipid NOT IN (SELECT DISTINCT X.bego_accountrelationshipid   FROM X inner JOIN account AS L0 ON (X.bego_accountid = L0.accountid) inner JOIN X AS L1 ON (L0.accountid = L1.bego_accountid AND  ( ( (L1.statuscode IN (?)) ))) inner JOIN territory AS L2 ON (L1.bego_territoryid = L2.territoryid) inner JOIN bego_systemuser_territory AS L3 ON (L2.territoryid = L3.territoryid AND  ( ( (L3.systemuserid = ?) ))) WHERE  ( ( (X.statuscode IN (?)) )))
0 | 0 | 0 | SCAN TABLE X |
0 | 0 | 0 | EXECUTE LIST SUBQUERY 1 |
1 | 0 | 4 | SEARCH TABLE bego_systemuser_territory AS L3 USING COVERING INDEX idx_bego_systemuser_territory (systemuserid=?) |
1 | 1 | 3 | SEARCH TABLE territory AS L2 USING COVERING INDEX sqlite_autoindex_territory_1 (territoryid=?) |
1 | 2 | 2 | SEARCH TABLE X AS L1 USING INDEX idx_bego_accountrelationship_bego_territoryid (bego_territoryid=?) |
1 | 3 | 1 | SEARCH TABLE account AS L0 USING COVERING INDEX sqlite_autoindex_account_1 (accountid=?) |
1 | 4 | 0 | SEARCH TABLE X USING INDEX idx_bego_accountrelationship_bego_accountid (bego_accountid=?) |
1 | 0 | 0 | USE TEMP B-TREE FOR DISTINCT |


EXPLAIN QUERY PLAN DELETE FROM X WHERE X.bego_accountrelationshipid NOT IN (SELECT DISTINCT X.bego_accountrelationshipid   FROM X inner JOIN account AS L0 ON (X.bego_accountid = L0.accountid) inner JOIN X AS L1 ON (L0.accountid = L1.bego_accountid AND  ( ( (L1.statuscode IN (?)) ))) inner JOIN territory AS L2 ON (L1.bego_territoryid = L2.territoryid) inner JOIN bego_systemuser_territory AS L3 ON (L2.territoryid = L3.territoryid AND  ( ( (L3.systemuserid = ?) ))) WHERE  ( ( (X.statuscode IN (?)) )))
0 | 0 | 0 | SCAN TABLE X |
0 | 0 | 0 | EXECUTE LIST SUBQUERY 0 |
0 | 0 | 4 | SEARCH TABLE bego_systemuser_territory AS L3 USING COVERING INDEX idx_bego_systemuser_territory (systemuserid=?) |
0 | 1 | 3 | SEARCH TABLE territory AS L2 USING COVERING INDEX sqlite_autoindex_territory_1 (territoryid=?) |
0 | 2 | 2 | SEARCH TABLE X AS L1 USING INDEX idx_bego_accountrelationship_bego_territoryid (bego_territoryid=?) |
0 | 3 | 1 | SEARCH TABLE account AS L0 USING COVERING INDEX sqlite_autoindex_account_1 (accountid=?) |
0 | 4 | 0 | SEARCH TABLE X USING INDEX idx_bego_accountrelationship_bego_accountid (bego_accountid=?) |
0 | 0 | 0 | USE TEMP B-TREE FOR DISTINCT |