Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

bug: Unable to DELETE rows with where clause #678

Closed
2 of 3 tasks
davidshiz opened this issue Oct 10, 2022 · 2 comments
Closed
2 of 3 tasks

bug: Unable to DELETE rows with where clause #678

davidshiz opened this issue Oct 10, 2022 · 2 comments
Assignees
@konghaiya
Labels
A-bug Something isn't working prio: high High priority
Milestone
StoneDB_5.7_v1.0.1

Comments

@davidshiz
Copy link
Collaborator

Have you read the Contributing Guidelines on issues?

Please confirm if bug report does NOT exists already ?

  • I confirm there is no existing issue for this

Describe the problem

mysql> delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
Query OK, 0 rows affected (0.00 sec)

Expected behavior

mysql> delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
Query OK, 5 rows affected (0.01 sec)

How To Reproduce

CREATE TABLE t1 (a int not null,b int not null)ENGINE=TIANMU;
CREATE TABLE t2 (a int not null, b int not null, primary key (a,b))ENGINE=TIANMU;
CREATE TABLE t3 (a int not null, b int not null, primary key (a,b))ENGINE=TIANMU;
insert into t1 values (1,1),(2,1),(1,3);
insert into t2 values (1,1),(2,2),(3,3);
insert into t3 values (1,1),(2,1),(1,3);
delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;

Environment

ubuntu@ubuntu:~$ cat /etc/os-release
NAME="Ubuntu"
VERSION="20.04.4 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.4 LTS"
VERSION_ID="20.04"

ubuntu@ubuntu:~$ /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld Ver 5.7.36_v1.0.1_beta_2-StoneDB for Linux on x86_64 (build-)
build information as follow:
Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
Branch name: stonedb-5.7-dev
Last commit ID: 208f79d
Last commit time: Date: Sat Sep 17 16:19:42 2022 +0800
Build time: Date: Fri Sep 23 14:14:27 CST 2022

Are you interested in submitting a PR to solve the problem?

  • Yes, I will!
@davidshiz davidshiz added the A-bug Something isn't working label Oct 10, 2022
@davidshiz davidshiz added this to the stonedb_5.7_v1.0.1 milestone Oct 10, 2022
@davidshiz
Copy link
Collaborator Author

this issue may be similar to #675

@konghaiya
Copy link
Collaborator

konghaiya commented Oct 10, 2022 •
edited
Loading

Like #675, this problem is caused by the primary key

mergify bot pushed a commit that referenced this issue Oct 12, 2022
@adofsauron @mergify
fix delete.result of
b7e1694 
    remote

        CREATE TABLE t1 (a int not null,b int not null)ENGINE=TIANMU;
        CREATE TABLE t2 (a int not null, b int not null, primary key (a,b))ENGINE=TIANMU;
        CREATE TABLE t3 (a int not null, b int not null, primary key (a,b))ENGINE=TIANMU;
        insert into t1 values (1,1),(2,1),(1,3);
        insert into t2 values (1,1),(2,2),(3,3);
        insert into t3 values (1,1),(2,1),(1,3);
        select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
        delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
        # This should be empty
        select * from t3;
        drop table t1,t2,t3;

        delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
        select * from t3;
        ab

        delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
        select * from t3;
        ab
        11
        21
        13

    because of #678
@RingsC RingsC added the prio: high High priority label Oct 18, 2022
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 18, 2022
@konghaiya
Fix delete from Multiple-Table, Failed to delete the data (stoneatom#663
52bdaa3 
)(stoneatom#678)

Cause:
At present, the primary key of the tianmu engine does not fully support delete and update statements.
Temporary solution:
Modify the execution plan of the SQL layer so that the delete and update statements do not follow the primary key logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 18, 2022
@konghaiya
fix(tianmu)Fix delete from Multiple-Table, Failed to delete the data (s…
85ac8d4 
…toneatom#663)(stoneatom#678)

Cause:
At present, the primary key of the tianmu engine does not fully support delete and update statements.
Temporary solution:
Modify the execution plan of the SQL layer so that the delete and update statements do not follow the primary key logic
@konghaiya konghaiya mentioned this issue Oct 18, 2022
Merged
15 tasks
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 18, 2022
@konghaiya
fix(tianmu)Fix delete from Multiple-Table, Failed to delete the data (s…
5d3fbb9 
…toneatom#663)(stoneatom#678)

Cause:
At present, the primary key of the tianmu engine does not fully support delete and update statements.
Temporary solution:
Modify the execution plan of the SQL layer so that the delete and update statements do not follow the primary key logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 18, 2022
@konghaiya
fix(tianmu)Fix delete from Multiple-Table, Failed to delete the data (s…
65a88e4 
…toneatom#663)(stoneatom#678)

Cause:
At present, the primary key of the tianmu engine does not fully support delete and update statements.
Temporary solution:
Modify the execution plan of the SQL layer so that the delete and update statements do not follow the primary key logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 18, 2022
@konghaiya
fix(tianmu)Fix delete from Multiple-Table, Failed to delete the data (s…
72e8635 
…toneatom#663)(stoneatom#678)

Cause:
At present, the primary key of the tianmu engine does not fully support delete and update statements.
Temporary solution:
Modify the execution plan of the SQL layer so that the delete and update statements do not follow the primary key logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 18, 2022
@konghaiya
fix(tianmu)Fix delete from Multiple-Table, Failed to delete the data (s…
1faf41c 
…toneatom#663)(stoneatom#678)

Cause:
At present, the primary key of the tianmu engine does not fully support delete and update statements.
Temporary solution:
Modify the execution plan of the SQL layer so that the delete and update statements do not follow the primary key logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 18, 2022
@konghaiya
fix(tianmu)Fix delete from Multiple-Table, Failed to delete the data (s…
1891460 
…toneatom#663)(stoneatom#678)

Cause:
At present, the primary key of the tianmu engine does not fully support delete and update statements.
Temporary solution:
Modify the execution plan of the SQL layer so that the delete and update statements do not follow the primary key logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 18, 2022
@konghaiya
fix(tianmu)Fix delete from Multiple-Table, Failed to delete the data (s…
86ed23f 
…toneatom#663)(stoneatom#678)

Cause:
At present, the primary key of the tianmu engine does not fully support delete and update statements.
Temporary solution:
Modify the execution plan of the SQL layer so that the delete and update statements do not follow the primary key logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 19, 2022
@konghaiya
fix(tianmu)Fix delete from Multiple-Table, Failed to delete the data (s…
47a4dbd 
…toneatom#663)(stoneatom#678)

Cause:
At present, the primary key of the tianmu engine does not fully support delete and update statements.
Temporary solution:
Modify the execution plan of the SQL layer so that the delete and update statements do not follow the primary key logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 19, 2022
@konghaiya
fix(tianmu)Fix delete from Multiple-Table, Failed to delete the data (s…
862cf27 
…toneatom#663)(stoneatom#678)

Cause:
At present, the primary key of the tianmu engine does not fully support delete and update statements.
Temporary solution:
Modify the execution plan of the SQL layer so that the delete and update statements do not follow the primary key logic
mergify bot pushed a commit that referenced this issue Oct 20, 2022
@konghaiya @mergify
fix(tianmu)Fix delete from Multiple-Table, Failed to delete the data (#…
ea99be9 
…663)(#678)

Cause:
At present, the primary key of the tianmu engine does not fully support delete and update statements.
Temporary solution:
Modify the execution plan of the SQL layer so that the delete and update statements do not follow the primary key logic
@hustjieke hustjieke mentioned this issue Mar 19, 2023
Closed
@hustjieke hustjieke mentioned this issue May 24, 2023
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@konghaiya konghaiya

Labels
A-bug Something isn't working prio: high High priority
Projects
None yet
Milestone
StoneDB_5.7_v1.0.1
Development

No branches or pull requests
3 participants
@RingsC @davidshiz @konghaiya