bug: The result that is null is not queried

[shangyanwen]

Describe the problem

CREATE TABLE `A` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  `datetime_key` datetime NOT NULL,
  `int_nokey` int(11) NOT NULL,
  `time_key` time NOT NULL,
  `time_nokey` time NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`)
)engine=stonedb;

CREATE TABLE `B` (
  `date_nokey` date NOT NULL,
  `date_key` date NOT NULL,
  `time_key` time NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `varchar_key` (`varchar_key`)
)engine=stonedb;

INSERT IGNORE INTO `B` VALUES ('2003-07-28','2003-07-28','15:13:38','0000-00-00 00:00:00','f'),('0000-00-00','0000-00-00','00:05:48','2004-07-02 14:34:13','x');

CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  `date_nokey` date NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `date_key` (`date_key`)
)engine=stonedb;

INSERT IGNORE INTO `BB` VALUES (10,8,'0000-00-00','i','0000-00-00'),(11,0,'2005-08-18','','2005-08-18');

SELECT table3 .`date_key` field1
  FROM
    B table1 LEFT JOIN B table3 JOIN
      (BB table6 JOIN A table7 ON table6 .`varchar_nokey`)
       ON table6 .`int_nokey` ON table6 .`date_key`
  WHERE  NOT ( table1 .`varchar_key`  AND table7 .`pk`) GROUP  BY field1;
##The error results are as follows
Empty set, 1 warning (0.03 sec)

Expected behavior

SELECT table3 .`date_key` field1
      FROM
      B table1 LEFT JOIN B table3 JOIN
        (BB table6 JOIN A table7 ON table6 .`varchar_nokey`)
         ON table6 .`int_nokey` ON table6 .`date_key`
    WHERE  NOT ( table1 .`varchar_key`  AND table7 .`pk`) GROUP  BY field1;

##The following is：innodb test result
+--------+
| field1 |
+--------+
| NULL   |
+--------+
1 row in set, 2 warnings (0.01 sec)

How To Reproduce

CREATE TABLE `A` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  `datetime_key` datetime NOT NULL,
  `int_nokey` int(11) NOT NULL,
  `time_key` time NOT NULL,
  `time_nokey` time NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`)
)engine=stonedb;

CREATE TABLE `B` (
  `date_nokey` date NOT NULL,
  `date_key` date NOT NULL,
  `time_key` time NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `varchar_key` (`varchar_key`)
)engine=stonedb;

INSERT IGNORE INTO `B` VALUES ('2003-07-28','2003-07-28','15:13:38','0000-00-00 00:00:00','f'),('0000-00-00','0000-00-00','00:05:48','2004-07-02 14:34:13','x');

CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  `date_nokey` date NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `date_key` (`date_key`)
)engine=stonedb;

INSERT IGNORE INTO `BB` VALUES (10,8,'0000-00-00','i','0000-00-00'),(11,0,'2005-08-18','','2005-08-18');

SELECT table3 .`date_key` field1
  FROM
    B table1 LEFT JOIN B table3 JOIN
      (BB table6 JOIN A table7 ON table6 .`varchar_nokey`)
       ON table6 .`int_nokey` ON table6 .`date_key`
  WHERE  NOT ( table1 .`varchar_key`  AND table7 .`pk`) GROUP  BY field1;

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!

[hustjieke]

Check if related to #232 ? There are many keys in create schema.

[shangyanwen]

Has nothing to do with #232. It's all individually tested, individually created

[shangyanwen]

Bug solved, closed