unexpected value change caused by changing count(x) to count(x)+3 #255
Description
after changing COUNT(DISTINCT kej98.c11) to COUNT(DISTINCT kej98.c11) + 3,col_4's value unexpectedly change from value1 to NULL
mysql> WITH cte_552 AS (SELECT kej98.c15 AS col_1, COUNT(DISTINCT kej98.c11) AS col_2, kej98.c4 AS col_3 FROM t2 AS kej98 GROUP BY kej98.c4, kej98.c15) SELECT eym89.c6 AS col_1, eym89.c4 AS col_2, tvi14.c15 AS col_3, tvi14.c10 AS col_4, eym89.c3 AS c3 FROM t1 AS eym89 CROSS JOIN t2 AS tvi14 ON 1 = 1 UNION SELECT boa86.col_3 AS col_1, boa86.col_1 AS col_2, boa86.col_2 AS col_3, boa86.col_2 AS col_4, boa86.col_3 AS col_5 FROM cte_552 AS boa86;
+--------------------------+-------+-------+--------+--------------------------+
| col_1 | col_2 | col_3 | col_4 | c3 |
+--------------------------+-------+-------+--------+--------------------------+
| sample_J | 36 | 53.96 | value1 | sample_v |
| sample_wfxp8dzD7XrFnPLzr | 53.96 | 1 | value1 | sample_wfxp8dzD7XrFnPLzr |
+--------------------------+-------+-------+--------+--------------------------+
2 rows in set (0.00 sec)
mysql> WITH cte_552 AS (SELECT kej98.c15 AS col_1, COUNT(DISTINCT kej98.c11) + 3 AS col_2, kej98.c4 AS col_3 FROM t2 AS
kej98 GROUP BY kej98.c4, kej98.c15) SELECT eym89.c6 AS col_1, eym89.c4 AS col_2, tvi14.c15 AS col_3, tvi14.c10 AS col_4,
eym89.c3 AS c3 FROM t1 AS eym89 CROSS JOIN t2 AS tvi14 ON 1 = 1 UNION SELECT boa86.col_3 AS col_1, boa86.col_1 AS col_2
, boa86.col_2 AS col_3, boa86.col_2 AS col_4, boa86.col_3 AS col_5 FROM cte_552 AS boa86;
+--------------------------+-------+-------+--------+--------------------------+
| col_1 | col_2 | col_3 | col_4 | c3 |
+--------------------------+-------+-------+--------+--------------------------+
| sample_J | 36 | 53.96 | value1 | sample_v |
| sample_wfxp8dzD7XrFnPLzr | 53.96 | 4 | NULL | sample_wfxp8dzD7XrFnPLzr |
+--------------------------+-------+-------+--------+--------------------------+
2 rows in set (0.01 sec)
How to repeat
DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
c2 VARCHAR(255) NOT NULL,
c3 VARCHAR(255) NULL,
c4 INT NULL,
c5 DATE NOT NULL,
c6 VARCHAR(10) NOT NULL,
PRIMARY KEY (c1)
);
CREATE TABLE t2 (
c1 INT NOT NULL AUTO_INCREMENT,
c2 INT NOT NULL,
c3 DECIMAL(10,2) NOT NULL,
c4 VARCHAR(50) NOT NULL,
c5 DATE NOT NULL,
c6 MEDIUMTEXT NULL,
c7 LONGTEXT NULL,
c8 MEDIUMBLOB NULL,
c9 LONGBLOB NULL,
c10 ENUM('value1','value2','value3') NULL,
c11 SET('a','b','c','d') NULL,
c12 BIT(8) NULL,
c13 DATETIME NULL,
c14 FLOAT(8,2) NULL,
c15 DOUBLE(12,4) NULL,
PRIMARY KEY (c1)
);
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (3584, 'sample_AQJP3FCb7PX46hF2d3VoKsPIHtIgzieVgOK1Ue7iSbNProjjh', 'sample_v', 36, '2025-09-07', 'sample_J');
INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (994, 3423, 181.05, 'sample_wfxp8dzD7XrFnPLzr', '2024-12-16', 'sample_pVtuVRHgHyCTAdMjTX5uDmgDFTTcRtYK4MA3VzWGhxyRAthAan095ltHnWC2wZqFzmNIz2BhrrY2h0fxo8UzijtqT3Qzyc7ydCZ760AbwzyWxKRhxPNIiXBNmglw9ylwoKoF0Q5QMAuWKKHLT07TyALEjZEq9SXDPPHZkG637npBmt2TKOxONpcXFVVrsW3jgaSAVhmwZMYON8YAyhzkhs0fsDZAohNdcrXeb43HI5de5EWnnY2lj8xyjRyfvWyVnBrhfeRiVnwUHuk0bcwoN3ueyVTRnXwQSF56ZbxUsw4hUmzUQ4GGRNJqlHOzrdMFLZHnsp5SamGZ0WPFJC1Vx1RuAnB5RdThXSIL8o2Fgebw5VIlETIWBvsuSQqrlbUxl0DbKa4xwcIiP23T8cVxdUgU4xKNvx6nab1kO47gdMcGoSGyXyvr23yKaVpjtw6XNe0uwE6zNt4nqZ1IMHxo', 'sample_oIxWhZ5PNoCnBUDNNG1t4uGQ80Jm3dOMyFRe0xsG8bqvWYFDuMqCy715ukwGqx4YD0tNg95vNkY49I1JPAbh5BBrcbTCYpkH1R37hDwaEo0Lzl91rc0tCeXUzOy258wzk7DX3Z11LNDgWU4awzLAAqwgg6l6QUVNdWbDUN3MQYXgivI8FcfivR8MVM40OIkt9fO8BNRyM2wTSSdov5M69ahQHUactc9lQyhXQfKFssG6OK2qKe2LIxK7FT1nHxPTNh7d41fB2bHcrjQ51tWpKJcLUFVVansOZC4fsvYKAamSAEMg3CsdHRNxw9dNCn5504tI75YCiAZT9DJEv7eTX7R07OJUaKE95P4VCs4XzftyMlVR4sPXkCsh7k7MYYBRd5d9iJdQLLOKXV3FUX5qjMRwF0oltoDkNGhVzUwlRuOptO8ROwzJLwCo5Zc7ce9l3bVoWQ3LsFymuRfVPQNPDkec3GZtAddEZBmIvzojWCpi0vhTzdYojxEdQwMHomRvrrYI7XWkrLrOOYwgMnPfHTeyO9oBFhEksiqIvbJHAEP2xCD2J0NvGcap1lZc2ueOUe6pJbZ95HmiFyRwwxMOxfLY4VCD5vgXNZTzeP0q8G0caUPPmLnaPcrw0vcnvR1gJnPdl8YyggslnNMOFmhPU2TK8EO5Xn6QVpvb6zPaM3Bw6sK3u10cw3i3jPuAYGSdMFNIdrU6L0zMUiI1LO4T7us2MUaXM', X'4DD6A6D687C6A9C08CDA93E9B48429DAA8ECBF93E891B67DDEBCDEBCE79C8A20', X'CF8CCBA2C880ED9DA9CF8644ECA5BD53E6B0AAED898E10C398D0AD', 'value1', 'a,c,b,d', b'01101010', '2025-04-25 18:28:30', 90.91, 53.96);
WITH cte_552 AS (SELECT kej98.c15 AS col_1, COUNT(DISTINCT kej98.c11) AS col_2, kej98.c4 AS col_3 FROM t2 AS kej98 GROUP BY kej98.c4, kej98.c15) SELECT eym89.c6 AS col_1, eym89.c4 AS col_2, tvi14.c15 AS col_3, tvi14.c10 AS col_4, eym89.c3 AS c3 FROM t1 AS eym89 CROSS JOIN t2 AS tvi14 ON 1 = 1 UNION SELECT boa86.col_3 AS col_1, boa86.col_1 AS col_2, boa86.col_2 AS col_3, boa86.col_2 AS col_4, boa86.col_3 AS col_5 FROM cte_552 AS boa86;
WITH cte_552 AS (SELECT kej98.c15 AS col_1, COUNT(DISTINCT kej98.c11) + 3 AS col_2, kej98.c4 AS col_3 FROM t2 AS kej98 GROUP BY kej98.c4, kej98.c15) SELECT eym89.c6 AS col_1, eym89.c4 AS col_2, tvi14.c15 AS col_3, tvi14.c10 AS col_4, eym89.c3 AS c3 FROM t1 AS eym89 CROSS JOIN t2 AS tvi14 ON 1 = 1 UNION SELECT boa86.col_3 AS col_1, boa86.col_1 AS col_2, boa86.col_2 AS col_3, boa86.col_2 AS col_4, boa86.col_3 AS col_5 FROM cte_552 AS boa86;
Version
mysql> select polardb_version();
+---------+--------------+---------------------+
| TYPE | VERSION | RELEASE_DATE |
+---------+--------------+---------------------+
| Product | PolarDB V2.0 | Distributed Edition |
| CN | 2.4.0.5.4.19 | SNAPSHOT |
| DN | 2.4.0.8.4.19 | 20240430 |
| GMS | 2.4.0.8.4.19 | 20240430 |
+---------+--------------+---------------------+
4 rows in set (0.01 sec)
mysql> select @@version;
+-------------------------+
| @@version |
+-------------------------+
| 8.0.32-X-Cluster-8.4.19 |
+-------------------------+
1 row in set (0.01 sec)