[MCOL-5606] resultset changed with subselects without changing data - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 23.02.3, 23.02.4, 23.10.0, 23.02.8
Fix Version/s: 23.10.2
Component/s: PrimProc
Labels:
- triage

Sprint:
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)

Attachments

Activity

Ascending order - Click to sort in descending order

Richard Stracke created issue - 2023-11-09 13:20

Richard Stracke made changes - 2024-01-24 12:08

Field	Original Value	New Value
Affects Version/s		23.10.0 [ 29422 ]

Leonid Fedorov made changes - 2024-01-26 16:08

Assignee

Leonid Fedorov [ JIRAUSER48443 ]

Richard Stracke made changes - 2024-02-21 13:32

Affects Version/s

23.02.8 [ 29635 ]

Leonid Fedorov made changes - 2024-02-27 12:08

Fix Version/s

23.10 [ 28540 ]

Leonid Fedorov made changes - 2024-02-27 12:08

Assignee

Leonid Fedorov [ JIRAUSER48443 ]

Roman [ drrtuy ]

Julien Fritsch made changes - 2024-05-24 16:23

Status

Open [ 1 ]

Needs Feedback [ 10501 ]

Valerii Kravchuk made changes - 2024-05-24 16:31

Status

Needs Feedback [ 10501 ]

Open [ 1 ]

Julien Fritsch made changes - 2024-05-30 07:46

Labels

triage

Julien Fritsch made changes - 2024-05-30 07:46

Priority

Major [ 3 ]

Critical [ 2 ]

Julien Fritsch made changes - 2024-06-06 16:42

Status

Open [ 1 ]

Confirmed [ 10101 ]

Leonid Fedorov made changes - 2024-07-01 09:21

Assignee

Roman [ drrtuy ]

Alexey Antipovsky [ JIRAUSER47594 ]

Julien Fritsch made changes - 2024-07-01 12:51

Sprint

2024-2 [ 764 ]

Jira Automation (IT) made changes - 2024-07-04 12:35

Zendesk Related Tickets		201716 120100 201784 170061
Zendesk active tickets		201716 201784

Jira Automation (IT) made changes - 2024-09-05 19:29

Zendesk active tickets

201716 201784

CS0000 201716

Jira Automation (IT) made changes - 2024-09-05 19:32

Zendesk active tickets

201716 CS0000

201716 CS0000 201784

Alexey Antipovsky made changes - 2024-10-04 12:18

Component/s		PrimProc [ 13700 ]
Fix Version/s		N/A [ 24311 ]
Fix Version/s	23.10 [ 28540 ]
Resolution		Fixed [ 1 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

Alexey Antipovsky made changes - 2024-10-04 12:23

Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Alexey Antipovsky made changes - 2024-10-04 12:23

Resolution		Cannot Reproduce [ 5 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Allen Herrera added a comment - 2024-10-09 15:32

Today i confirmed

This is not fixed in 23.02.x and wont be fixed

[root@ip-172-31-36-104 rocky]# mariadb -e "show status like '%Columnstore%';"

+-------------------------+---------------+

| Variable_name           | Value         |

+-------------------------+---------------+

| Columnstore_commit_hash | 7b64072-dirty |

| Columnstore_version     | 23.02.11      |

+-------------------------+---------------+

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) |

+-----------+

|    116384 |

+-----------+

1 row in set (0.463 sec)

+-----------+

| count(id) |

+-----------+

|     33616 |

+-----------+

1 row in set (0.636 sec)

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) |

+-----------+

|    247456 |

+-----------+

1 row in set (0.442 sec)

+-----------+

| count(id) |

+-----------+

|    150000 |

+-----------+

but is fixed in 23.10.2+

+-------------------------+---------+

| Variable_name           | Value   |

+-------------------------+---------+

| Columnstore_commit_hash | source  |

| Columnstore_version     | 23.10.2 |

+-------------------------+---------+

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) |

+-----------+

|    250033 |

+-----------+

1 row in set (0.399 sec)

+-----------+

| count(id) |

+-----------+

|    150000 |

+-----------+

1 row in set (0.637 sec)

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) |

+-----------+

|    250033 |

+-----------+

1 row in set (0.492 sec)

+-----------+

| count(id) |

+-----------+

|    150000 |

+-----------+

1 row in set (0.620 sec)

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) |

+-----------+

|    250033 |

+-----------+

1 row in set (0.443 sec)

+-----------+

| count(id) |

+-----------+

|    150000 |

+-----------+

1 row in set (0.620 sec)

Allen Herrera added a comment - 2024-10-09 15:32 Today i confirmed This is not fixed in 23.02.x and wont be fixed [root @ip - 172 - 31 - 36 - 104 rocky]# mariadb -e "show status like '%Columnstore%';" +-------------------------+---------------+ | Variable_name | Value | +-------------------------+---------------+ | Columnstore_commit_hash | 7b64072-dirty | | Columnstore_version | 23.02 . 11 | +-------------------------+---------------+ 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) | +-----------+ | 116384 | +-----------+ 1 row in set ( 0.463 sec) +-----------+ | count(id) | +-----------+ | 33616 | +-----------+ 1 row in set ( 0.636 sec) 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) | +-----------+ | 247456 | +-----------+ 1 row in set ( 0.442 sec) +-----------+ | count(id) | +-----------+ | 150000 | +-----------+ but is fixed in 23.10.2+ +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Columnstore_commit_hash | source | | Columnstore_version | 23.10 . 2 | +-------------------------+---------+ 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) | +-----------+ | 250033 | +-----------+ 1 row in set ( 0.399 sec) +-----------+ | count(id) | +-----------+ | 150000 | +-----------+ 1 row in set ( 0.637 sec) 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) | +-----------+ | 250033 | +-----------+ 1 row in set ( 0.492 sec) +-----------+ | count(id) | +-----------+ | 150000 | +-----------+ 1 row in set ( 0.620 sec) 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) | +-----------+ | 250033 | +-----------+ 1 row in set ( 0.443 sec) +-----------+ | count(id) | +-----------+ | 150000 | +-----------+ 1 row in set ( 0.620 sec)

Julien Fritsch made changes - 2024-10-09 15:37

Resolution	Cannot Reproduce [ 5 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Julien Fritsch made changes - 2024-10-09 15:38

Fix Version/s		23.10.2 [ 29807 ]
Fix Version/s	N/A [ 24311 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

People

Assignee:: Alexey Antipovsky

Reporter:: Richard Stracke

Votes:: 1 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2023-11-09 13:20

Updated:: 2024-12-27 04:15

Resolved:: 2024-10-09 15:38

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB ColumnStore

Details

Description

Attachments

Activity

People

Dates

Git Integration