[MCOL-5606] resultset changed with subselects without changing data Created: 2023-11-09  Updated: 2024-01-30

Status: Open
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 23.02.3, 23.02.4, 23.10.0
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Richard Stracke Assignee: Leonid Fedorov
Resolution: Unresolved Votes: 1
Labels: None


 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)


Generated at Thu Feb 08 02:59:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.