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: The insert value is null,select * from t2 where f3 is null;The query result is empty #281

Closed
1 task
shangyanwen opened this issue Jul 21, 2022 · 3 comments
Assignees
Labels
A-bug Something isn't working

Comments

@shangyanwen
Copy link
Contributor

Describe the problem

create table t3 (f3 int unique)engine=stonedb;
insert into t3 values(1),(NULL);
select * from t3 where f3 is null;
##The error results are as follows
Empty set (0.00 sec)

Expected behavior

##The following is:innodb test result
select * from t3 where f3 is null;
+------+
| f3   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

How To Reproduce

create table t3 (f3 int unique)engine=stonedb;
insert into t3 values(1),(NULL);
select * from t3 where f3 is null;

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
@shangyanwen
Copy link
Contributor Author

2022-08-09 stonedb5.7(tianmu engine),The validation bug did not reappear

@xiaoguangye
Copy link
Contributor

xiaoguangye commented Aug 9, 2022

NULL value missing updates:

1: Like MySQL and SQL standard, we DO support NULL values in UNIQUE constraint.
However there are some related points need to be highlighted.
1.1 Tianmu engine DO NOT support UNIQUE constrtaint at the moment.
1.2 According to @xiaoguangye 's discovery, this issue was addressed before

commit 3bdb611c539362e8691ee712f303b80d594712b2 (HEAD)
Author: jun-lihaijun <103155258+jun-lihaijun@users.noreply.github.com>
Date:   Mon Jul 18 11:00:34 2022 +0800
    Fix(docs): fix compile-using-ubuntu2004.md (#224) (#225)
    * Update compile-using-ubuntu2004.md

and there are compile issues under ubuntu 20.04 release before this commit.
2: This particular issue is addressed and confirmed by our testing team.
3: As to why this issue was fixed before the issue was raised, we know that testing team is using an older version of release.
3.1 We urge the people who raise issues to bear in mind that tickets should contain info about build No. or commit ID to help developers better reproduce issues.

For these reasons, we have decided not to pursue any further in the commit which fixed this issue.

@xiaoguangye
Copy link
Contributor

Fixed

mergify bot added a commit that referenced this issue Aug 10, 2022
) (#379)

* fix(core): Double prepare on lex unit for cts with union(#226)

fix(core): MTR + Double prepare on lex unit for ctas with union(#226)

* fix(mtr): Add test cases for NULL value select on UNIQUE constraint(#281)

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>
xiaoguangye pushed a commit to xiaoguangye/stonedb that referenced this issue Aug 11, 2022
Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix(mtr):mtr result stonedb change to tianmu(stoneatom#359) (stoneatom#368)

* fix(mtr):mysqlpump*.test sys_stoendb to sys_tianmu

fix(mtr):fix main result stonedb to tianmu

fix(mtr):mv innodb_fts.ngram_1 to test/innodb_fts

fix(mtr):fix mtr funcs_1 stonedb to tianmu

fix(mtr):fix innodb mtr result stonedb change to tianmu

fix(mtr):fix perfschema mtr result stonedb change to tianmu

* fix(mtr):fix mtr stoendb change to tianmu

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix(tianmu): fix group_concat_max_len warning (stoneatom#338) (stoneatom#363)

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

docs(stonedb): update docs and fix bugs (stoneatom#373)

* docs(Quick Deployment and Compiling Methods): update docs

Make the document more user-friendly for new users

* fix(docs): Let the official website pictures can be adaptive

* Update compile-using-ubuntu2004.md

In the install make step, add the go to directory:cd make-3.82

* Update compile-using-redhat7.md

In the install make step, add the go to directory:cd make-3.82

* Update compile-using-centos7.md

In the install make step, add the go to directory:cd make-3.82

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>
Co-authored-by: haitaoguan <105625912+haitaoguan@users.noreply.github.com>

feat(doc):doc add innodb backup to stonedb demonstrate(stoneatom#353) (stoneatom#355)

* feat(doc):doc add innodb backup to stonedb demonstrate

* fix(doc):fix mydumper and mysqldump doc

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

feat(innodb): To support compiling innodb as C++ 17(stoneatom#375) (stoneatom#376)

To support innodb is compiled as C++ 17. For now, tianmu is compiling as C++17,
and C++17 has became mainstream of C++ standard. Therefore, now, we star to support
to compile Innodb as C++17 in stonedb.

This is backport from MySQL, which commit id is: 1a8a111d8f855a31d0aeffc8f02309b2b82dd410 and bug# is #32907274

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix stonedb for optimize exists subquery and deep memory copy stoneatom#369 (stoneatom#330, stoneatom#329, stoneatom#340, stoneatom#332) (stoneatom#369)

* fix stonedb for optimize exists subquery and deep memcopy copy

* Update parameterized_filter.cpp

fix ParameterizedFilter deep copy mind memory leak

* fix subquery when this is in sql

* fix code readability and defensive

* use shallow memory when paral hash join by class DimensionGroupMultiMaterialized

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

feat(stonedb): To support gcc 9 and adds `WITH_ROCKSDB` and `WITH_MARISA` to specify where (stoneatom#377)

rocksdb and marisa installed, respectively.

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix(mtr): Add test cases for NULL value select on UNIQUE constraint(stoneatom#281) (stoneatom#379)

* fix(core): Double prepare on lex unit for cts with union(stoneatom#226)

fix(core): MTR + Double prepare on lex unit for ctas with union(stoneatom#226)

* fix(mtr): Add test cases for NULL value select on UNIQUE constraint(stoneatom#281)

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix bug stoneatom#342 (stoneatom#346)

Co-authored-by: dfx <duanfuxiang>
Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

Doc(stonedb): Added extensions deccriptions in topics related to backup and recovery for O&M Guide(stoneatom#360) (stoneatom#361)

* updated some code snippets in Install RocksDB step

Signed-off-by: xuejiao-joy <821682573@qq.com>

* some description here

Signed-off-by: xuejiao-joy <821682573@qq.com>

Co-authored-by: Yuting <34496563+Nliver@users.noreply.github.com>
Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix(mtr): Add more ctas queries for the same bug-fix from code-analysis perspectives(stoneatom#226)
xiaoguangye added a commit to xiaoguangye/stonedb that referenced this issue Aug 11, 2022
fix(core): MTR + Double prepare on lex unit for ctas with union(stoneatom#226)

rich doc to fix  env dependence "not found" (stoneatom#316)

fix(mtr): make the mtr passed (stoneatom#315) (stoneatom#317)

[summary]
How to run:
./mysql-test-run.pl --suite=stonedb --nowarnings --force --nocheck-testcases

1. test cases below are failed, we ignored them first:
issue410
issue415
issue433
issue446
issue515
load.test
2. Sys variables next are not set correctly, they should be added to `/mysql-test/include/default_mysqld.cnf`.
stonedb_insert_delayed = 0
stonedb_ini_allowmysqlquerypath=1

3.check-testcases: A new MTR option to enforce strict cleanup:
https://dev.mysql.com/blog-archive/fail-check-testcases-a-new-mtr-option-to-enforce-strict-cleanup

In `mtr` comment:
```
Some options that control enabling a feature for normal test runs,
can be turned off by prepending 'no' to the option, e.g. --notimer.
This applies to reorder, timer, check-testcases and warnings.
```
Currently we turn off it first to make mtr run success.

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

feat(stonedb): update the copyright date time (stoneatom#321) (stoneatom#324)

fix(core): fix crash when executing sql. (stoneatom#327) (stoneatom#328)

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

feat(index): Unified class member variable name with google style (stoneatom#311) (stoneatom#312)

[summary]
Class member name will be like:

class TableInfo {
  ...
 private:
  std::string table_name_;  // OK - underscore at end.
  static Pool<TableInfo>* pool_;  // OK.
};

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix(mtr):move character_set to test.character_set, fix main mtr_test and innodb mtr_test(stoneatom#277) (stoneatom#335)

* fix(mtr):fix innodb mtr and mv some test to test/innodb

* fix(mtr):move character_set to test/main/

fix(mtr):mv main mtr character_set test to test/main/cahracter_set

fix(mtr):mv myisam error mtr-test to test/main/myisam

fix(mtr):add optimizer_switch to include/default_mysqld.cnf

* fix(mtr):fix main mtr optimizer_switch amd mv some test to test/main

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix(mtr): Add more ctas queries for the same bug-fix from code-analysis perspectives(stoneatom#226)

fix bugs of stoneatom#309 and stoneatom#310 (stoneatom#313)

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix(core): Double prepare on lex unit for cts with union(stoneatom#226) (stoneatom#365)

fix(core): MTR + Double prepare on lex unit for ctas with union(stoneatom#226)

fix(mtr):mtr result stonedb change to tianmu(stoneatom#359) (stoneatom#368)

* fix(mtr):mysqlpump*.test sys_stoendb to sys_tianmu

fix(mtr):fix main result stonedb to tianmu

fix(mtr):mv innodb_fts.ngram_1 to test/innodb_fts

fix(mtr):fix mtr funcs_1 stonedb to tianmu

fix(mtr):fix innodb mtr result stonedb change to tianmu

fix(mtr):fix perfschema mtr result stonedb change to tianmu

* fix(mtr):fix mtr stoendb change to tianmu

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix(tianmu): fix group_concat_max_len warning (stoneatom#338) (stoneatom#363)

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

docs(stonedb): update docs and fix bugs (stoneatom#373)

* docs(Quick Deployment and Compiling Methods): update docs

Make the document more user-friendly for new users

* fix(docs): Let the official website pictures can be adaptive

* Update compile-using-ubuntu2004.md

In the install make step, add the go to directory:cd make-3.82

* Update compile-using-redhat7.md

In the install make step, add the go to directory:cd make-3.82

* Update compile-using-centos7.md

In the install make step, add the go to directory:cd make-3.82

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>
Co-authored-by: haitaoguan <105625912+haitaoguan@users.noreply.github.com>

feat(doc):doc add innodb backup to stonedb demonstrate(stoneatom#353) (stoneatom#355)

* feat(doc):doc add innodb backup to stonedb demonstrate

* fix(doc):fix mydumper and mysqldump doc

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

feat(innodb): To support compiling innodb as C++ 17(stoneatom#375) (stoneatom#376)

To support innodb is compiled as C++ 17. For now, tianmu is compiling as C++17,
and C++17 has became mainstream of C++ standard. Therefore, now, we star to support
to compile Innodb as C++17 in stonedb.

This is backport from MySQL, which commit id is: 1a8a111d8f855a31d0aeffc8f02309b2b82dd410 and bug# is #32907274

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix stonedb for optimize exists subquery and deep memory copy stoneatom#369 (stoneatom#330, stoneatom#329, stoneatom#340, stoneatom#332) (stoneatom#369)

* fix stonedb for optimize exists subquery and deep memcopy copy

* Update parameterized_filter.cpp

fix ParameterizedFilter deep copy mind memory leak

* fix subquery when this is in sql

* fix code readability and defensive

* use shallow memory when paral hash join by class DimensionGroupMultiMaterialized

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

feat(stonedb): To support gcc 9 and adds `WITH_ROCKSDB` and `WITH_MARISA` to specify where (stoneatom#377)

rocksdb and marisa installed, respectively.

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix(mtr): Add test cases for NULL value select on UNIQUE constraint(stoneatom#281) (stoneatom#379)

* fix(core): Double prepare on lex unit for cts with union(stoneatom#226)

fix(core): MTR + Double prepare on lex unit for ctas with union(stoneatom#226)

* fix(mtr): Add test cases for NULL value select on UNIQUE constraint(stoneatom#281)

Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix bug stoneatom#342 (stoneatom#346)

Co-authored-by: dfx <duanfuxiang>
Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

Doc(stonedb): Added extensions deccriptions in topics related to backup and recovery for O&M Guide(stoneatom#360) (stoneatom#361)

* updated some code snippets in Install RocksDB step

Signed-off-by: xuejiao-joy <821682573@qq.com>

* some description here

Signed-off-by: xuejiao-joy <821682573@qq.com>

Co-authored-by: Yuting <34496563+Nliver@users.noreply.github.com>
Co-authored-by: mergify[bot] <37929162+mergify[bot]@users.noreply.github.com>

fix(mtr): Add more ctas queries for the same bug-fix from code-analysis perspectives(stoneatom#226)
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
Development

No branches or pull requests

2 participants