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.trigger contains a SELECT with , trigger fields in the select list under DISTINCT,No result is found #586

Closed
1 task
shangyanwen opened this issue Sep 27, 2022 · 0 comments
Assignees
Labels
A-bug Something isn't working prio: high High priority

Comments

@shangyanwen
Copy link
Contributor

Describe the problem

SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1 (
  id int NOT NULL DEFAULT '0',
  a  varchar(10) NOT NULL,
  b  varchar(10),
  c  varchar(10),
  d  timestamp NOT NULL,
  PRIMARY KEY (id, a)
)engine=tianmu;

CREATE TABLE t2 (
  fubar_id         int  NOT NULL DEFAULT '0',
  last_change_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY  (fubar_id)
)engine=tianmu;

DELIMITER |
CREATE TRIGGER fubar_change
  AFTER UPDATE ON t1
    FOR EACH ROW
      BEGIN
        INSERT INTO t2 (fubar_id, last_change_time)
          SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time
            FROM t1 WHERE (id = NEW.id) AND (OLD.c != NEW.c)
        ON DUPLICATE KEY UPDATE
          last_change_time =
            IF((fubar_id = NEW.id)AND(OLD.c != NEW.c),NOW(),last_change_time);
      END|

DELIMITER ;

INSERT INTO t1 (id,a, b,c,d) VALUES
 (1,'a','b','c',now()),(2,'a','b','c',now());

UPDATE t1 SET c='Bang!' WHERE id=1;

##Incorrect test results
SELECT fubar_id FROM t2;
Empty set (0.00 sec)

DROP TABLE t1,t2;
SET sql_mode = default;

Expected behavior

#Correct test results(note:Innodb test verification, test results are as follows)
SELECT fubar_id FROM t2;
fubar_id
1

How To Reproduce

Follow the test steps above

Environment

./mysqld  Ver 5.7.36_v1.0.1_beta-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: Sat Sep 17 17:59:04 CST 2022

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 Sep 27, 2022
@shangyanwen shangyanwen added this to the stonedb_5.7_v1.0.1 milestone Sep 27, 2022
@shangyanwen shangyanwen changed the title bug: bug: trigger contains a SELECT with , trigger fields in the select list under DISTINCT,No result is found Sep 27, 2022
@shangyanwen shangyanwen changed the title bug: trigger contains a SELECT with , trigger fields in the select list under DISTINCT,No result is found bug: incorrect result set.trigger contains a SELECT with , trigger fields in the select list under DISTINCT,No result is found Sep 29, 2022
@RingsC RingsC added the prio: high High priority label Oct 18, 2022
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 prio: high High priority
Projects
None yet
Development

No branches or pull requests

4 participants