Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
23.02.3, 23.02.4, 23.10.0, 23.02.8
-
2024-2
Description
to reproduce
CREATE TABLE IF NOT EXISTS `t1` ( `id` VARCHAR(50) NOT NULL) ENGINE=Columnstore DEFAULT CHARSET=utf8; |
CREATE TABLE IF NOT EXISTS `t2` ( `id` VARCHAR(50) NOT NULL) ENGINE=Columnstore DEFAULT CHARSET=utf8; |
|
|
|
set max_recursive_iterations = 10000000; |
|
|
INSERT INTO `t1` (
|
with recursive series as (
|
select 1 as id,LEFT(MD5(RAND()), 36) as r1 union all |
select id +1 as id, LEFT(MD5(RAND()), 36) as r1 from series |
where id < 100000) |
select r1 from series);
|
|
|
|
|
|
|
insert into `t2` select * from `t1`;
|
|
|
INSERT INTO `t2` (
|
with recursive series as (
|
select 1 as id,LEFT(MD5(RAND()), 36) as r1 union all |
select id +1 as id, LEFT(MD5(RAND()), 36) as r1 from series |
where id < 50000) |
select r1 from series);
|
|
|
|
|
|
|
insert into `t1` select * from `t2`;
|
|
|
INSERT INTO `t1` (
|
with recursive series as (
|
select 1 as id,LEFT(MD5(RAND()), 36) as r1 union all |
select id +1 as id, LEFT(MD5(RAND()), 36) as r1 from series |
where id < 5000) |
select r1 from series);
|
repeat multipe time one or both statements
select count(id) from t1 where id in (select id from t2);
|
select count(id) from t2 where id in (select id from t1);
|
Results differ sometimes:
MariaDB [d1]> select count(id) from t2 where id in (select id from t1);
|
+-----------+
|
| count(id) |
|
+-----------+
|
| 150000 | |
+-----------+
|
1 row in set (0.299 sec) |
|
|
MariaDB [d1]> select count(id) from t2 where id in (select id from t1);
|
+-----------+
|
| count(id) |
|
+-----------+
|
| 33616 | |
+-----------+
|
1 row in set (0.290 sec) |
|
Today i confirmed
This is not fixed in 23.02.x and wont be fixed
+-------------------------+---------------+| Variable_name | Value |+-------------------------+---------------+| Columnstore_commit_hash | 7b64072-dirty |+-------------------------+---------------+MariaDB [test2]> select count(id) from t1 where id in (select id from t2);select count(id) from t2 where id in (select id from t1);+-----------+| count(id) |+-----------++-----------++-----------+| count(id) |+-----------++-----------+MariaDB [test2]> select count(id) from t1 where id in (select id from t2);select count(id) from t2 where id in (select id from t1);+-----------+| count(id) |+-----------++-----------++-----------+| count(id) |+-----------++-----------+but is fixed in 23.10.2+
+-------------------------+---------+| Variable_name | Value |+-------------------------+---------+| Columnstore_commit_hash | source |+-------------------------+---------+MariaDB [test]> select count(id) from t1 where id in (select id from t2);select count(id) from t2 where id in (select id from t1);+-----------+| count(id) |+-----------++-----------++-----------+| count(id) |+-----------++-----------+MariaDB [test]> select count(id) from t1 where id in (select id from t2);select count(id) from t2 where id in (select id from t1);+-----------+| count(id) |+-----------++-----------++-----------+| count(id) |+-----------++-----------+MariaDB [test]> select count(id) from t1 where id in (select id from t2);select count(id) from t2 where id in (select id from t1);+-----------+| count(id) |+-----------++-----------++-----------+| count(id) |+-----------++-----------+