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.

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

bug: incorrect result set.Query results more data, see the bug text #282

Closed
1 task done
shangyanwen opened this issue Jul 21, 2022 · 0 comments · Fixed by #599
Closed
1 task done

bug: incorrect result set.Query results more data, see the bug text #282

shangyanwen opened this issue Jul 21, 2022 · 0 comments · Fixed by #599
Assignees
Labels
A-bug Something isn't working

Comments

@shangyanwen
Copy link
Contributor

shangyanwen commented Jul 21, 2022

Describe the problem

CREATE TABLE t1 (sku int PRIMARY KEY, pr int)engine=stonedb;
CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255))engine=stonedb;

INSERT INTO t1 VALUES
  (10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10);

INSERT INTO t2 VALUES 
  (10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'),
  (50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh');

SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
  FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);

##The error results are as follows
+-----+------+------+-----+------+
| sku | sppr | name | sku | pr   |
+-----+------+------+-----+------+
|  20 |   10 | bbb  |  10 |   10 |
|  20 |   10 | bbb  |  20 |   10 |
|  20 |   10 | bbb  |  30 |   20 |
|  20 |   10 | bbb  |  40 |   30 |
|  20 |   10 | bbb  |  50 |   10 |
|  20 |   10 | bbb  |  60 |   10 |
+-----+------+------+-----+------+
6 rows in set (0.02 sec)

Expected behavior

##The following is:innodb test result

+-----+------+------+-----+------+
| sku | sppr | name | sku | pr   |
+-----+------+------+-----+------+
|  20 |   10 | bbb  |  10 |   10 |
|  20 |   10 | bbb  |  20 |   10 |
+-----+------+------+-----+------+
2 rows in set (0.00 sec)

How To Reproduce

CREATE TABLE t1 (sku int PRIMARY KEY, pr int)engine=stonedb;
CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255))engine=stonedb;

INSERT INTO t1 VALUES
  (10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10);

INSERT INTO t2 VALUES 
  (10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'),
  (50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh');

SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
  FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);

Environment

  1. StoneDB for mysql5.7 (release)
  2. Ubuntu 20.04.4

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

  • Yes, I will!
@shangyanwen shangyanwen added the A-bug Something isn't working label Jul 21, 2022
@swoapri swoapri self-assigned this Aug 10, 2022
swoapri added a commit to swoapri/stonedb that referenced this issue Aug 12, 2022
swoapri added a commit to swoapri/stonedb that referenced this issue Aug 12, 2022
	* Execution plan error when AND/OR sql logical operator in condition occurs at the same time
@konghaiya konghaiya self-assigned this Sep 27, 2022
konghaiya added a commit to konghaiya/stonedb that referenced this issue Sep 28, 2022
Cause:
Execution plan error when AND/OR sql logical operator in condition occurs at the same time
Resolvent:
Optimize multiple equal execution plan logic
@shangyanwen shangyanwen changed the title bug: Query results more data, see the bug text bug: incorrect result set.Query results more data, see the bug text Sep 29, 2022
@shangyanwen shangyanwen added this to the stonedb_5.7_v1.0.1 milestone Sep 29, 2022
konghaiya added a commit to konghaiya/stonedb that referenced this issue Sep 29, 2022
Cause:
Execution plan error when AND/OR sql logical operator in condition occurs at the same time
Resolvent:
Optimize multiple equal execution plan logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 17, 2022
Cause:
Execution plan error when AND/OR sql logical operator in condition occurs at the same time
Resolvent:
Optimize multiple equal execution plan logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 17, 2022
Cause:
Execution plan error when AND/OR sql logical operator in condition occurs at the same time
Resolvent:
Optimize multiple equal execution plan logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 17, 2022
Cause:
Execution plan error when AND/OR sql logical operator in condition occurs at the same time
Resolvent:
Optimize multiple equal execution plan logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 17, 2022
Cause:
Execution plan error when AND/OR sql logical operator in condition occurs at the same time
Resolvent:
Optimize multiple equal execution plan logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 17, 2022
Cause:
Execution plan error when AND/OR sql logical operator in condition occurs at the same time
Resolvent:
Optimize multiple equal execution plan logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 17, 2022
Cause:
Execution plan error when AND/OR sql logical operator in condition occurs at the same time
Resolvent:
Optimize multiple equal execution plan logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 17, 2022
Cause:
Execution plan error when AND/OR sql logical operator in condition occurs at the same time
Resolvent:
Optimize multiple equal execution plan logic
konghaiya added a commit to konghaiya/stonedb that referenced this issue Oct 17, 2022
Cause:
Execution plan error when AND/OR sql logical operator in condition occurs at the same time
Resolvent:
Optimize multiple equal execution plan logic
@mergify mergify bot closed this as completed in #599 Oct 17, 2022
mergify bot pushed a commit that referenced this issue Oct 17, 2022
Cause:
Execution plan error when AND/OR sql logical operator in condition occurs at the same time
Resolvent:
Optimize multiple equal execution plan logic
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
A-bug Something isn't working
Projects
None yet
3 participants