[MDEV-3489] LP:601164 - Wrong result for DELETE over a table with a virtual column Created: 2010-07-02  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug601164.xml    

 Description   

DELETE over a table containing a virtual column with ORDER BY an index and with a LIMIT clause may return wrong results
if the WHERE clause contains a condition for the virtual column:

MariaDB [test]> create table t1 (a int, b int, v int as (a+1), index idx(b));
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into t1(a, b) values (4, 40), (3, 30), (5, 50), (7, 70), (8, 80), (2, 20), (1, 10);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0

MariaDB [test]> select * from t1 order by b;
--------------

a b v

--------------

1 10 2
2 20 3
3 30 4
4 40 5
5 50 6
7 70 8
8 80 9

--------------
7 rows in set (0.00 sec)

MariaDB [test]> delete from t1 where v > 6 order by b limit 1;
Query OK, 0 rows affected (6.19 sec)

MariaDB [test]> select * from t1;
--------------

a b v

--------------

4 40 5
3 30 4
5 50 6
7 70 8
8 80 9
2 20 3
1 10 2

--------------
7 rows in set (0.00 sec)

 Comments   
Comment by Sergei Petrunia [ 2010-07-02 ]

Re: Wrong result for DELETE over a table with a virtual column
This bug can be observed in current MariaDB 5.2

Comment by Igor Babaev [ 2010-07-03 ]

Re: Wrong result for DELETE over a table with a virtual column
There is a similar problem for some UPDATEs.
For the same table as in the reported test case we have:

MariaDB [test]> update t1 set a=v order by b limit 1;
Query OK, 1 row affected (34.82 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [test]> select * from t1 order by b;
--------------

a b v

--------------

NULL 10 NULL
2 20 3
3 30 4
4 40 5
5 50 6
7 70 8
8 80 9

--------------
7 rows in set (0.00 sec)

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 601164

Generated at Thu Feb 08 06:49:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.