Description:
after changing AVG(mjs8.c1) to -AVG(-mjs8.c1) ,col_3's value's Floating-point precision lose.additionally,with the union affecting this bug apppear.
mysql> WITH cte_380 AS (SELECT JSON_REPLACE(FIRST_VALUE(utj7.c16) OVER (), '$.key', utj7.c11) AS col_2, LENGTH(utj7.c7) AS col_4 FROM t2 AS utj7) (SELECT DISTINCT AVG(mjs8.c1) AS col_3, BIT_OR(mjs8.c15) AS col_4 FROM t1 AS kcm89 RIGHT JOIN t2 AS mjs8 ON kcm89.c1 IN (SELECT t2.c3 AS c3 FROM t2 AS t2 WHERE t2.c3 BETWEEN 1 AND 100)) UNION SELECT xza85.col_2 AS col_3, xza85.col_4 AS col_4 FROM cte_380 AS xza85 WHERE xza85.col_4 IS NULL;
+----------------+-------+
| col_3 | col_4 |
+----------------+-------+
| 4952.666666666 | 63 |
+----------------+-------+
1 row in set (0.00 sec)
mysql> WITH cte_380 AS (SELECT JSON_REPLACE(FIRST_VALUE(utj7.c16) OVER (), '$.key', utj7.c11) AS col_2, LENGTH(utj7.c7) AS col_4 FROM t2 AS utj7) (SELECT DISTINCT -AVG(-mjs8.c1) AS col_3, BIT_OR(mjs8.c15) AS col_4 FROM t1 AS kcm89 RIGHT JOIN t2 AS mjs8 ON kcm89.c1 IN (SELECT t2.c3 AS c3 FROM t2 AS t2 WHERE t2.c3 BETWEEN 1 AND 100)) UNION SELECT xza85.col_2 AS col_3, xza85.col_4 AS col_4 FROM cte_380 AS xza85 WHERE xza85.col_4 IS NULL;
+-----------+-------+
| col_3 | col_4 |
+-----------+-------+
| 4952.6667 | 63 |
+-----------+-------+
1 row in set (0.00 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,
c16 JSON NULL,
PRIMARY KEY (c1)
);
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (9505, 'sample_j6tRCMl2ntyXKOJEbYA1cM0rvcwpdKAcFn0YWKrCKnu3WgFFCrLj3UNBc3r0OF7q8uip4bOEuLRYII19C2S5qEaJjRJTU8TKZLWWoGUsHSVRxz1dRztOnTDtYBY4DRndJEl80NQgnZRcyNjwqJWoje0BloyBO6pONgdOaYlL4LVYA4YBB97fuaiNZHuqN3cbb0aI6BdVxhvNwuVXqLZEI4NTUQBbg2QSoJ9IL49VpgrAVaahv9DPz', 'sample_FslXMXkrstdX6KR63rp7SuzwgkzaVAWVG7pPEULjq3STRGzDQ82473KqhecREn4XoFg63sUdHz6awadu3vYDH2tEXOy0zP4Ybr4epzn8AniupuoryIsLYB2BvUcqxi2V3X1DxNA8bf4Co0emlCYz', 61, '2025-12-02', 'sample_h');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (7523, 'sample_UcD5Y2tr7VUjqptMEy4SF7jspb6jB1Xk1laRrPr2fQmKRH4ceeBAJ5ZiaHxlkwPWHxeWArUcyZQ0m4VpWXsE9HBnCaBBqVsbBxedWJpoI5uj3gFJmx3nCttKqa2xjTWpqMPYsKWA33o8HJeFNMbVdWdMy3INZrcQHi', 'sample_xPqhIxkYsJC2cVR4IvZZbDymOJo95JssjjyURXaPmrQMN5zSO5SWE1Ll5Qsb72IOCg9GUFrplaCLWI5YRh1n44CPb1LnGbrVFJjuPrrMyXRiEkeXYe9Sm0ZZSiGTfKNWK8jtmIQjamVJHnfW4TuE1DA', 49, '2025-02-01', 'sample_x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (3143, 'sample_COuw3sqV16xthjVaunYhBidChBZTOAx5R4eLFM7CnQ1e2H7aFOHZVXa0KP29TYGwdRPQ4pqubPHmMEMhhnGXtaEygfeG4DitS0XA', 'sample_5PVBQigDUIpT1E8e8ZgDdHbvI6fJkc55dpG2kpnjBcm8pCMfJrJun0ucpHhgUZBO97o4QjRSLGzlp2TdcTBTS39hoNuYdBWUpFlTgaGa949RkAKpd9dDpJ5iEAOBJZPk2Y6We4E6yd7WHGzraFvzwjSnLKHFCU1NnhHrbR2dByeScJWOopDr0daPmsZa4YUlw4UJLCO', 32, '2025-11-25', 'sample_QW8');
INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (8704, 2839, 146.86, 'sample_0n6nNhYhRlc7Lc4vgMqBwXUG8aZIr31iRCJm9s', '2025-10-23', 'sample_JvKtXVz44cZQD2Kxur8DKBk59l2bwR93QwtioGuxhGSAiy2D7Z3kWNSrRT6eP6bU4aPRQuKWSlQcebu3pqLy3DxMLtdyDy4mUVyC2GF894OccU1NlAQBQIRtzjufKhF4uuLLtFuPSeiN1ApaB1BcHgXiBndWMb2CDJdJp9HQ2gqNoLxQNYMyRTvu6OPixNi0fAEktI9Dpm37YCoSf8N1U3U66XSFn604CQWuUHj9UN1mbdHsZIlsMTOTSbiJi8GniNYDwOcqiI6c25OAQGQkBjEXXMJyNlIweZ8HoJw0VP0nrLBBd3iCpPHGyVcFQFF1iJTIbIwrdcrAvVrm2ao7L6VSZ5wVgjjTyeVtGfzQCaCqMKwOlEbg2OtirwxxsVBslolQS4W9e0yj7G1Mjn2GKiULE42chXA8UHdaKSXJshyzqx40rLRRsMjxditaLFDxcNxrv3ou8ZWk1CdJ0vhCWQLJjRlK7DyE5oQTHXfP36sNcktShqTcyMBus6N0Kae35a9YlkKifRy0uJLjOfo9ItXv2ZhFpGEkSKrUDynzQ3C1n1aMLqATdSevrT4vo7HrYQciBEVMN9fYjjepuARo4TStCNy17Uj9s8jQH6Fa1igBogmE5snqwq5A75I8NSOkoTn2LxcdxQ2qdG9cJJj7szX68MtIFaIUqWqhCX1KHD0BMqmwPjkk2mvybXeaKpGOxZ0bdojyYJn2Qs5YYN7RgWfbUA7P652vbjBCfMrx1PExSGDQZ0wwEZxdvAeJxCuWNNY8ubPPXPdbHvA1cAURRl0bgZAy0Lnw4YnIz7NfQ07H40H9YFHotAxc5CpimCHCastvlMhoZUImkTYSfuueYrqk9FOF4BfG', 'sample_9SIHfozd8PNto9En1JkJHOaIdYxGcNVDx7dxABNxERrS3ZsUC6PTSxA2Z7ppRJ77XCLtGHtEdiMZ4dZNjslAbMAcz3LzCIUHJEw8Gf', X'1A72C28EE79B9D3AC7ABDF8134E9B5B528C783DFA91C5C7E7DE6A282EEB5B8E4A183EBABAEE0B9A313E1AD84', X'E285A36157C9952D', 'value1', 'b,d,c', b'11111010', '2025-10-16 23:45:33', 49.37, 59.04, '{"k1": 410, "k2": "sample_1", "k3": 571.4004337388058, "k4": true}');
INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (2021, 2644, 165.04, 'sample_xFL84dLOKfUGSH2JFy3uDk9U6lg', '2024-12-27', 'sample_ABDa9X2hfaoTYjpvg0ELYnM6qqc4vQk9lsX2DIRDjp0VrDAiONSF0fT3gV4N1BMAhgW8NJNZQT1bSxy3iS3wkO9QmIXZoU3VjmLZMHA3oGZkIQhGdqcWI6R5l2QsMRZzwWetQsfm1aUWDuPTwrk4KSYjt4zx2csAL8wRTW33SrAqRGoPCX2o169AuX7xOa5Dn8yOSGvavzRk9DdflVLDNy6GBo2OT36t5N9KvC7S6beU0v8Py3iUy6UGS9dJl97ezzMAvXP6YFlPmK9SZhjrqAUwxdOCojYXiFBtDA2JaIsXTM3yF5sKlxgLgLYaQgoKH2qqnwDEuNQbeeiUG47Pi0nRD1fXyUQQQQe9QWrXEZFWeHqFiS0tsZq92D799TCyaBbJZlkYKDsLOUGBJG1PjRHkAVdPdfSvEsN3Bb7hSGK3P3PxXMbBzAd5wUNkml4wWINVNbQaUT1A5UBzHQkSOZlpF3vesWUCE9FJtOIG5x3NKGOIyyKUGVq63fMNRVGaiGmHSjsg4bazPqLbPHZ8gcZRaSVgqtD', 'sample_YD9VWWzpGTkYcPy5mdt14fYB61Aqef1beDsKQia5DXR4g0UflmGbGqnGDZxgIWeeBMeU6iQtpwT41X1QzTFZVoCAcy6KWVYWNGX5KKBENp6QnwrY2EC2IbEk0XgJkVdSto6AbmtCzx8oB1Uvy1hXjWFRFxs8KX7mSjd1PVIBAno4X0l5zyE2wowBJmABBw0NxRHvqbKRIcOj03lmVeiysbkeeiFnWFknqlXFHA5IeIp4kg38YnLz0AgXta8uwuNbt0SyOn3lYW6ohvhZ6UvHGRaKQnMlDclB1zvVV4L4Ir9GJc0t6MbygtnRUowbE342oTQaZX6YUZgMYWXyEITpZ3CL37rl4NfH1BF336ISmWZtS3iJoBv9aSqaapTICrMB4vWGULEXVMTwpgUHbk71LN6odXLoE3S19EgquFqEwjewH35lz9GJQ3UZL1fi07UgTBPW7e9kyFyirdlkE0BbOnwd0UIY6sSc4LV82OdxRSF6wYaNlu8TwwD65BmgN3XZb31tIIubcb4FN05RvSP5OHdB3f5SqdSGN9fMSMVIRhxX0oFKIQO60rpQ8Hv7iLR4WFQFmeQoaWgxlEHPvfWIqRV4Ip549kMqzURBn9YKxTDJhTDRTF6Vz0cWl63XfzfnFHZhFtPx8LVErs6v8muwLT0tpsnnvODAguYlxPbCuwXf7uDUnzmuPJHH6uGBgj0nrJWxdWc9thRJeq5azIXRr8ee3vx5mLtTZyFsIdg33qCSf7l0aI6JaP9emHV4oiqKjerm0mpfBVU87rt9bAhcCLWLanW1G2SpmrFv9rarh7eT7xIF6tNTyemCwrJnkkO38zyuxji9cdxxLIRzdI09AfjPopgJfQ1mgmcOQa5YSAWPj1gM6kbr5FbtSC1S9pw9cfwFX2nl8gnwu4oeyp1TCNTKq7VIPPLbJAXyxxh2S2xkUtgrtrdxrb8ucZVCdo5G98uK2SwTC9fGJfjhgG8xo00A5qMORhc56oLiNoud2X9Oeuk5uxdhPR6wpUCe55H8ZMHvmf5oWg2ZJWHNSyw5yzDyxDGtlJcxL6XlV1hvJ804ueSimb90mhiUrcY7miH4nYPadVByiI8qe0dB2UddJRwDd089mw9xPnN9G6yB8SCH2Ei7XbHIkfAUdiyTdY0zxyYqnGPL7lRLHWDEOHc1Wrq3x3uzMQuHscwW7tDiKqTknHYkjjmwof3MxsEXNdumis0QnNX2adJ9M92Idmg2M4w7DMKSato5P8NrJkL9RYglTaZIWvGVA0iqOYB90NEfEFLovBFJXFE54mUq5WV3Gw5gwtSvnM6JUeSgcVFmrpV2EAVmgmvK3gWPqvt7HP3iCUFUBl8ipfnKge6E78QWpkbngpDg7nXQhr0uwWriR0Sn5344U6Lmq2YSiNxH7bhYwFMTXFJxVAjFEpPzBN7Ifdqr26K453a40IEwU0ZIlMMDKQUhHwKoY6J679tiNyCdu89i6oWUI9GO0IHdeLHwwUffRx5qkQXtBs3LNxOXSRojYqhC2XmcO7V5KwfAGLzEbLXXU2Xajsml3Aw5VQkNAkuHm4chYHIube61Y2IzzrpyuW7onRHqJKe4TRSJmquDmyi467v5QAB23ibS554BYcGw0J0N2tK52LKxvCq3XiZqKByJ4JKNLY0KIPPdfWO1Yw4jlmjrWZPVKAzlivWdlx738ujWPXOUtv5lu8mOwRVhJn1hJhCDxhHih9bSSc1B3TA7L', X'E6BEB0E49AB90F69EFA2B4D9A6DC8C2973', X'7BC3AC4900E69A81DCAAD49ECEB93EC0BDC09C', 'value3', 'c', b'11001110', '2025-07-21 10:00:06', 45.95, 14.43, '{"k1": 198, "k2": "mixed_27", "k3": ["tag_31"], "k4": {"k5": "C", "k6": [17, 82, 10, 63, 1], "k7": {"k8": "user_36", "k9": "2025-12-03T14:34:08.822739"}}, "k10": false}');
INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (4133, 2644, 932.17, 'sample_1YyRulrh1NNFUPfsgt5mBeISUbIjKQIujjATqpjkv0', '2024-12-21', 'sample_swzB7LpubsT9Htz0BS0fJEFSXJTv1IlLOE70lk4NIw7HPSzVSaYCaGXBeWaGS4tXWA25eE9oRgZRZ4JK0c9uBwX8zgpgxk4SbyPs3cGT2MEdllNpIzbQYpDAFQtvBkCPEXff1FVAoVPrDJREen9RknQUb8qvMjPa7wHvn80qpPtgBlOncchR4TojPuytLET5cqLWODPEcFM3LFV97DYZpRzAdppBXp301jHlQ5I9X1jmv28FgzJKC5R0fq32caFGlwbRNSiVpTRdYPb0lg9HVOwR5kH08JzbB3lY6RJqwnubhq0A1Ayl0AmkwxBah5Idp2ayWhFaNVgSfCc5xn8ltTee8Pu5jqFkULHjsBPXSMMu3e5tzL9zGHlsmRXATWnpuEE0YvUfS1wMQKKkuICBeDkSUmzorPq9HEXEaabaEHaGOazGaZOxyVatD0k09MpfcdIZojsOjLsUqnYiNgjFjOTDvsjbMFc6r1mO189b0GKBkJfobkUcOgRQozEoRJcP2uooiUbwQ8QRFu5Crs80MSLKTgKJhrHsP4bijSbbdaWGEd0cfKnQV48dTPz4srMRvBHOE22gqWlRW3QKjxhaROEIZOIaVwagfD8UBOYqHnrVuzzBsF2moZVhDKFcJ3S2K4LNBgbK829QMSwFff8Dl6TaF11jiu4CUGIylBfVSDjPYY0ZEP42djNFJ3Kvumnb2BMUPWi1d3WUjKJ8eWuqBhoT9cwsKMHnvdt8WerxNCo1MkiiXMXl31ZGSMopnOnNEmx8c3NeiRJ9Rdd4eRYiewgsRAiNnsFkqRzRkmVNqxXEp6nouubw8D5gltRVtlwt7gM5MQWitQDF8oDyncR2c8mJA0nMMraobUi78nB7O6zgDs0MxCwsMFe2kbqBOAG68tkv', 'sample_XQek1BJzheIBVkFqbRz9BAumGJpDhKPjMQcdbM4QeqCnmLoHd8YM7amYx8kZjMPEsA8iWybNtuRUEnWO26jk73e7GsuyzocUypuz7Jq89BlU0iSKkTkYcaZYLuzmQ1aWaWXmbAgSLEsP7HG4g1H1X5DrEQ93vi3Ge6mrP24D7AVB9CaTcKpqbjSnEB2SVVFqsOi5olCw57wCpT273S0eYicRH6XQn1kXbtifwCZ1TJLrqa92jSYb6hKptTY7lGe9Vbs4I24NIp2OVGfmsSXnhIqnG9lYCzDIUTrp5XoUaUYkiQE9eVQ4NoxYSBuM3wKfW2Haw74xSbeebPtga3DTByOsffNMjzbhDJQaJa5wo0iweXW9hMgJUZ5N6HX3gcMpHZcNJjrV1Ikve7p2PCwIxrUW8UrKwZcHQm2AFS4ko59TgObZM8nvLcy97Qa0pkBtj5oDdGsjNEafe3pAzKuVH363M20HVM99bWWcwjAFzRuZAkKzz4PJQTQI8ytDiUFPLXuK28KUZUbYkZui8bDUONFdEiutESrwinm1l09sLZgXlUBRLs8T5HnUyQQNtKEft0C7UEzcmTpOAEhnv6ZOQVBEVURHiA4DGR1gZPObFVMFuxfgFVQa5F6eBDcBem75zuFpavGiyIGpugVgIhzjmjk5TiLxsbmPLuZMcRi8lNh8gxmIulw6rHiLIMpkA6iW50IFjueb8QTiaAHRnHy5MkU9PssePyziYwrzJqkGSfDHCJq9UCCh3cUgUA2dArQIfUgKfX6ehk0y7CJqp4j9jSu67Ca4tG5KMLNJEACr61SOEPeXvg5KZHUBDddUxFwarfjiGRD5kVSJiUtoez6wp1QGluWPmKpLfGznk1iXz5FupjNob3TmNedsbx0aHgfMEkrlIXfx4E1bBWa0J770aNv3MPmn10lnvPF9cKUat7qG5BqnBQW2Msa02NE0Q9JjySu6VHwE0Ce6dYY7HNKrjq8xQdbzplkH2lLOtLhqecwJQOBAgk3IwTgFoWrM2nIuRPMPR8lw6zIdnHMOStOiI5MrbhEx7kI55JNTl18wquVPfSj0UZ8ABGFwM6gyCZsP20yU9rzdnhOsALoM0qhGMidwCzWIw85NQkzvB1ha7Qqw3J2nJ1maho5b9NxJdcYCTaS9BePTHLmm2Vr5nInY086up9nMTstrWN4dWWtkzDvQYbX5PqiLNKpsw9CV1KiXhXzoRnzHOrT4DcvwBBBcytFVb4TKyG0QVGHk6SLgMTSXr4g8NiQ9RQu3ZYc9TunbpQYMppVotqsqmsUsl9gcEyjRhaQgLTc1OjWgzT9lTBRa5ZqNf6JyMXb7NTJ7HXtc2wFlrHKNuI3Rz9ywbMBnfDvJqwtr0lC8Eb8SIK5dfVmrEWgJFdPWmBhSIvfwE', X'E98AB5C3807440E2A0BDEAA7986C00', X'E3899FDD8FE6978FEA8FBFD4A2211DCDB048', 'value2', 'd', b'10010111', '2025-08-18 04:47:28', 92.32, 9.75, '[{"k1": 0, "k2": "item_0", "k3": 64.68005827747231}, {"k1": 1, "k2": "item_1", "k3": 41.09269929837975}, {"k1": 2, "k2": "item_2", "k3": 58.90622459358933}, {"k1": 3, "k2": "item_3", "k3": 48.6643547208647}, {"k1": 4, "k2": "item_4", "k3": 62.39057661988535}, {"k1": 5, "k2": "item_5", "k3": 41.62982455523014}, {"k1": 6, "k2": "item_6", "k3": 10.966086344198425}]');
WITH cte_380 AS (SELECT JSON_REPLACE(FIRST_VALUE(utj7.c16) OVER (), '$.key', utj7.c11) AS col_2, LENGTH(utj7.c7) AS col_4 FROM t2 AS utj7) (SELECT DISTINCT AVG(mjs8.c1) AS col_3, BIT_OR(mjs8.c15) AS col_4 FROM t1 AS kcm89 RIGHT JOIN t2 AS mjs8 ON kcm89.c1 IN (SELECT t2.c3 AS c3 FROM t2 AS t2 WHERE t2.c3 BETWEEN 1 AND 100)) UNION SELECT xza85.col_2 AS col_3, xza85.col_4 AS col_4 FROM cte_380 AS xza85 WHERE xza85.col_4 IS NULL;
WITH cte_380 AS (SELECT JSON_REPLACE(FIRST_VALUE(utj7.c16) OVER (), '$.key', utj7.c11) AS col_2, LENGTH(utj7.c7) AS col_4 FROM t2 AS utj7) (SELECT DISTINCT -AVG(-mjs8.c1) AS col_3, BIT_OR(mjs8.c15) AS col_4 FROM t1 AS kcm89 RIGHT JOIN t2 AS mjs8 ON kcm89.c1 IN (SELECT t2.c3 AS c3 FROM t2 AS t2 WHERE t2.c3 BETWEEN 1 AND 100)) UNION SELECT xza85.col_2 AS col_3, xza85.col_4 AS col_4 FROM cte_380 AS xza85 WHERE xza85.col_4 IS NULL;
Description: after changing AVG(mjs8.c1) to -AVG(-mjs8.c1) ,col_3's value's Floating-point precision lose.additionally,with the union affecting this bug apppear. mysql> WITH cte_380 AS (SELECT JSON_REPLACE(FIRST_VALUE(utj7.c16) OVER (), '$.key', utj7.c11) AS col_2, LENGTH(utj7.c7) AS col_4 FROM t2 AS utj7) (SELECT DISTINCT AVG(mjs8.c1) AS col_3, BIT_OR(mjs8.c15) AS col_4 FROM t1 AS kcm89 RIGHT JOIN t2 AS mjs8 ON kcm89.c1 IN (SELECT t2.c3 AS c3 FROM t2 AS t2 WHERE t2.c3 BETWEEN 1 AND 100)) UNION SELECT xza85.col_2 AS col_3, xza85.col_4 AS col_4 FROM cte_380 AS xza85 WHERE xza85.col_4 IS NULL; +----------------+-------+ | col_3 | col_4 | +----------------+-------+ | 4952.666666666 | 63 | +----------------+-------+ 1 row in set (0.00 sec) mysql> WITH cte_380 AS (SELECT JSON_REPLACE(FIRST_VALUE(utj7.c16) OVER (), '$.key', utj7.c11) AS col_2, LENGTH(utj7.c7) AS col_4 FROM t2 AS utj7) (SELECT DISTINCT -AVG(-mjs8.c1) AS col_3, BIT_OR(mjs8.c15) AS col_4 FROM t1 AS kcm89 RIGHT JOIN t2 AS mjs8 ON kcm89.c1 IN (SELECT t2.c3 AS c3 FROM t2 AS t2 WHERE t2.c3 BETWEEN 1 AND 100)) UNION SELECT xza85.col_2 AS col_3, xza85.col_4 AS col_4 FROM cte_380 AS xza85 WHERE xza85.col_4 IS NULL; +-----------+-------+ | col_3 | col_4 | +-----------+-------+ | 4952.6667 | 63 | +-----------+-------+ 1 row in set (0.00 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, c16 JSON NULL, PRIMARY KEY (c1) ); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (9505, 'sample_j6tRCMl2ntyXKOJEbYA1cM0rvcwpdKAcFn0YWKrCKnu3WgFFCrLj3UNBc3r0OF7q8uip4bOEuLRYII19C2S5qEaJjRJTU8TKZLWWoGUsHSVRxz1dRztOnTDtYBY4DRndJEl80NQgnZRcyNjwqJWoje0BloyBO6pONgdOaYlL4LVYA4YBB97fuaiNZHuqN3cbb0aI6BdVxhvNwuVXqLZEI4NTUQBbg2QSoJ9IL49VpgrAVaahv9DPz', 'sample_FslXMXkrstdX6KR63rp7SuzwgkzaVAWVG7pPEULjq3STRGzDQ82473KqhecREn4XoFg63sUdHz6awadu3vYDH2tEXOy0zP4Ybr4epzn8AniupuoryIsLYB2BvUcqxi2V3X1DxNA8bf4Co0emlCYz', 61, '2025-12-02', 'sample_h'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (7523, 'sample_UcD5Y2tr7VUjqptMEy4SF7jspb6jB1Xk1laRrPr2fQmKRH4ceeBAJ5ZiaHxlkwPWHxeWArUcyZQ0m4VpWXsE9HBnCaBBqVsbBxedWJpoI5uj3gFJmx3nCttKqa2xjTWpqMPYsKWA33o8HJeFNMbVdWdMy3INZrcQHi', 'sample_xPqhIxkYsJC2cVR4IvZZbDymOJo95JssjjyURXaPmrQMN5zSO5SWE1Ll5Qsb72IOCg9GUFrplaCLWI5YRh1n44CPb1LnGbrVFJjuPrrMyXRiEkeXYe9Sm0ZZSiGTfKNWK8jtmIQjamVJHnfW4TuE1DA', 49, '2025-02-01', 'sample_x'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (3143, 'sample_COuw3sqV16xthjVaunYhBidChBZTOAx5R4eLFM7CnQ1e2H7aFOHZVXa0KP29TYGwdRPQ4pqubPHmMEMhhnGXtaEygfeG4DitS0XA', 'sample_5PVBQigDUIpT1E8e8ZgDdHbvI6fJkc55dpG2kpnjBcm8pCMfJrJun0ucpHhgUZBO97o4QjRSLGzlp2TdcTBTS39hoNuYdBWUpFlTgaGa949RkAKpd9dDpJ5iEAOBJZPk2Y6We4E6yd7WHGzraFvzwjSnLKHFCU1NnhHrbR2dByeScJWOopDr0daPmsZa4YUlw4UJLCO', 32, '2025-11-25', 'sample_QW8'); INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (8704, 2839, 146.86, 'sample_0n6nNhYhRlc7Lc4vgMqBwXUG8aZIr31iRCJm9s', '2025-10-23', 'sample_JvKtXVz44cZQD2Kxur8DKBk59l2bwR93QwtioGuxhGSAiy2D7Z3kWNSrRT6eP6bU4aPRQuKWSlQcebu3pqLy3DxMLtdyDy4mUVyC2GF894OccU1NlAQBQIRtzjufKhF4uuLLtFuPSeiN1ApaB1BcHgXiBndWMb2CDJdJp9HQ2gqNoLxQNYMyRTvu6OPixNi0fAEktI9Dpm37YCoSf8N1U3U66XSFn604CQWuUHj9UN1mbdHsZIlsMTOTSbiJi8GniNYDwOcqiI6c25OAQGQkBjEXXMJyNlIweZ8HoJw0VP0nrLBBd3iCpPHGyVcFQFF1iJTIbIwrdcrAvVrm2ao7L6VSZ5wVgjjTyeVtGfzQCaCqMKwOlEbg2OtirwxxsVBslolQS4W9e0yj7G1Mjn2GKiULE42chXA8UHdaKSXJshyzqx40rLRRsMjxditaLFDxcNxrv3ou8ZWk1CdJ0vhCWQLJjRlK7DyE5oQTHXfP36sNcktShqTcyMBus6N0Kae35a9YlkKifRy0uJLjOfo9ItXv2ZhFpGEkSKrUDynzQ3C1n1aMLqATdSevrT4vo7HrYQciBEVMN9fYjjepuARo4TStCNy17Uj9s8jQH6Fa1igBogmE5snqwq5A75I8NSOkoTn2LxcdxQ2qdG9cJJj7szX68MtIFaIUqWqhCX1KHD0BMqmwPjkk2mvybXeaKpGOxZ0bdojyYJn2Qs5YYN7RgWfbUA7P652vbjBCfMrx1PExSGDQZ0wwEZxdvAeJxCuWNNY8ubPPXPdbHvA1cAURRl0bgZAy0Lnw4YnIz7NfQ07H40H9YFHotAxc5CpimCHCastvlMhoZUImkTYSfuueYrqk9FOF4BfG', 'sample_9SIHfozd8PNto9En1JkJHOaIdYxGcNVDx7dxABNxERrS3ZsUC6PTSxA2Z7ppRJ77XCLtGHtEdiMZ4dZNjslAbMAcz3LzCIUHJEw8Gf', X'1A72C28EE79B9D3AC7ABDF8134E9B5B528C783DFA91C5C7E7DE6A282EEB5B8E4A183EBABAEE0B9A313E1AD84', X'E285A36157C9952D', 'value1', 'b,d,c', b'11111010', '2025-10-16 23:45:33', 49.37, 59.04, '{"k1": 410, "k2": "sample_1", "k3": 571.4004337388058, "k4": true}'); INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (2021, 2644, 165.04, 'sample_xFL84dLOKfUGSH2JFy3uDk9U6lg', '2024-12-27', 'sample_ABDa9X2hfaoTYjpvg0ELYnM6qqc4vQk9lsX2DIRDjp0VrDAiONSF0fT3gV4N1BMAhgW8NJNZQT1bSxy3iS3wkO9QmIXZoU3VjmLZMHA3oGZkIQhGdqcWI6R5l2QsMRZzwWetQsfm1aUWDuPTwrk4KSYjt4zx2csAL8wRTW33SrAqRGoPCX2o169AuX7xOa5Dn8yOSGvavzRk9DdflVLDNy6GBo2OT36t5N9KvC7S6beU0v8Py3iUy6UGS9dJl97ezzMAvXP6YFlPmK9SZhjrqAUwxdOCojYXiFBtDA2JaIsXTM3yF5sKlxgLgLYaQgoKH2qqnwDEuNQbeeiUG47Pi0nRD1fXyUQQQQe9QWrXEZFWeHqFiS0tsZq92D799TCyaBbJZlkYKDsLOUGBJG1PjRHkAVdPdfSvEsN3Bb7hSGK3P3PxXMbBzAd5wUNkml4wWINVNbQaUT1A5UBzHQkSOZlpF3vesWUCE9FJtOIG5x3NKGOIyyKUGVq63fMNRVGaiGmHSjsg4bazPqLbPHZ8gcZRaSVgqtD', 'sample_YD9VWWzpGTkYcPy5mdt14fYB61Aqef1beDsKQia5DXR4g0UflmGbGqnGDZxgIWeeBMeU6iQtpwT41X1QzTFZVoCAcy6KWVYWNGX5KKBENp6QnwrY2EC2IbEk0XgJkVdSto6AbmtCzx8oB1Uvy1hXjWFRFxs8KX7mSjd1PVIBAno4X0l5zyE2wowBJmABBw0NxRHvqbKRIcOj03lmVeiysbkeeiFnWFknqlXFHA5IeIp4kg38YnLz0AgXta8uwuNbt0SyOn3lYW6ohvhZ6UvHGRaKQnMlDclB1zvVV4L4Ir9GJc0t6MbygtnRUowbE342oTQaZX6YUZgMYWXyEITpZ3CL37rl4NfH1BF336ISmWZtS3iJoBv9aSqaapTICrMB4vWGULEXVMTwpgUHbk71LN6odXLoE3S19EgquFqEwjewH35lz9GJQ3UZL1fi07UgTBPW7e9kyFyirdlkE0BbOnwd0UIY6sSc4LV82OdxRSF6wYaNlu8TwwD65BmgN3XZb31tIIubcb4FN05RvSP5OHdB3f5SqdSGN9fMSMVIRhxX0oFKIQO60rpQ8Hv7iLR4WFQFmeQoaWgxlEHPvfWIqRV4Ip549kMqzURBn9YKxTDJhTDRTF6Vz0cWl63XfzfnFHZhFtPx8LVErs6v8muwLT0tpsnnvODAguYlxPbCuwXf7uDUnzmuPJHH6uGBgj0nrJWxdWc9thRJeq5azIXRr8ee3vx5mLtTZyFsIdg33qCSf7l0aI6JaP9emHV4oiqKjerm0mpfBVU87rt9bAhcCLWLanW1G2SpmrFv9rarh7eT7xIF6tNTyemCwrJnkkO38zyuxji9cdxxLIRzdI09AfjPopgJfQ1mgmcOQa5YSAWPj1gM6kbr5FbtSC1S9pw9cfwFX2nl8gnwu4oeyp1TCNTKq7VIPPLbJAXyxxh2S2xkUtgrtrdxrb8ucZVCdo5G98uK2SwTC9fGJfjhgG8xo00A5qMORhc56oLiNoud2X9Oeuk5uxdhPR6wpUCe55H8ZMHvmf5oWg2ZJWHNSyw5yzDyxDGtlJcxL6XlV1hvJ804ueSimb90mhiUrcY7miH4nYPadVByiI8qe0dB2UddJRwDd089mw9xPnN9G6yB8SCH2Ei7XbHIkfAUdiyTdY0zxyYqnGPL7lRLHWDEOHc1Wrq3x3uzMQuHscwW7tDiKqTknHYkjjmwof3MxsEXNdumis0QnNX2adJ9M92Idmg2M4w7DMKSato5P8NrJkL9RYglTaZIWvGVA0iqOYB90NEfEFLovBFJXFE54mUq5WV3Gw5gwtSvnM6JUeSgcVFmrpV2EAVmgmvK3gWPqvt7HP3iCUFUBl8ipfnKge6E78QWpkbngpDg7nXQhr0uwWriR0Sn5344U6Lmq2YSiNxH7bhYwFMTXFJxVAjFEpPzBN7Ifdqr26K453a40IEwU0ZIlMMDKQUhHwKoY6J679tiNyCdu89i6oWUI9GO0IHdeLHwwUffRx5qkQXtBs3LNxOXSRojYqhC2XmcO7V5KwfAGLzEbLXXU2Xajsml3Aw5VQkNAkuHm4chYHIube61Y2IzzrpyuW7onRHqJKe4TRSJmquDmyi467v5QAB23ibS554BYcGw0J0N2tK52LKxvCq3XiZqKByJ4JKNLY0KIPPdfWO1Yw4jlmjrWZPVKAzlivWdlx738ujWPXOUtv5lu8mOwRVhJn1hJhCDxhHih9bSSc1B3TA7L', X'E6BEB0E49AB90F69EFA2B4D9A6DC8C2973', X'7BC3AC4900E69A81DCAAD49ECEB93EC0BDC09C', 'value3', 'c', b'11001110', '2025-07-21 10:00:06', 45.95, 14.43, '{"k1": 198, "k2": "mixed_27", "k3": ["tag_31"], "k4": {"k5": "C", "k6": [17, 82, 10, 63, 1], "k7": {"k8": "user_36", "k9": "2025-12-03T14:34:08.822739"}}, "k10": false}'); INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (4133, 2644, 932.17, 'sample_1YyRulrh1NNFUPfsgt5mBeISUbIjKQIujjATqpjkv0', '2024-12-21', 'sample_swzB7LpubsT9Htz0BS0fJEFSXJTv1IlLOE70lk4NIw7HPSzVSaYCaGXBeWaGS4tXWA25eE9oRgZRZ4JK0c9uBwX8zgpgxk4SbyPs3cGT2MEdllNpIzbQYpDAFQtvBkCPEXff1FVAoVPrDJREen9RknQUb8qvMjPa7wHvn80qpPtgBlOncchR4TojPuytLET5cqLWODPEcFM3LFV97DYZpRzAdppBXp301jHlQ5I9X1jmv28FgzJKC5R0fq32caFGlwbRNSiVpTRdYPb0lg9HVOwR5kH08JzbB3lY6RJqwnubhq0A1Ayl0AmkwxBah5Idp2ayWhFaNVgSfCc5xn8ltTee8Pu5jqFkULHjsBPXSMMu3e5tzL9zGHlsmRXATWnpuEE0YvUfS1wMQKKkuICBeDkSUmzorPq9HEXEaabaEHaGOazGaZOxyVatD0k09MpfcdIZojsOjLsUqnYiNgjFjOTDvsjbMFc6r1mO189b0GKBkJfobkUcOgRQozEoRJcP2uooiUbwQ8QRFu5Crs80MSLKTgKJhrHsP4bijSbbdaWGEd0cfKnQV48dTPz4srMRvBHOE22gqWlRW3QKjxhaROEIZOIaVwagfD8UBOYqHnrVuzzBsF2moZVhDKFcJ3S2K4LNBgbK829QMSwFff8Dl6TaF11jiu4CUGIylBfVSDjPYY0ZEP42djNFJ3Kvumnb2BMUPWi1d3WUjKJ8eWuqBhoT9cwsKMHnvdt8WerxNCo1MkiiXMXl31ZGSMopnOnNEmx8c3NeiRJ9Rdd4eRYiewgsRAiNnsFkqRzRkmVNqxXEp6nouubw8D5gltRVtlwt7gM5MQWitQDF8oDyncR2c8mJA0nMMraobUi78nB7O6zgDs0MxCwsMFe2kbqBOAG68tkv', 'sample_XQek1BJzheIBVkFqbRz9BAumGJpDhKPjMQcdbM4QeqCnmLoHd8YM7amYx8kZjMPEsA8iWybNtuRUEnWO26jk73e7GsuyzocUypuz7Jq89BlU0iSKkTkYcaZYLuzmQ1aWaWXmbAgSLEsP7HG4g1H1X5DrEQ93vi3Ge6mrP24D7AVB9CaTcKpqbjSnEB2SVVFqsOi5olCw57wCpT273S0eYicRH6XQn1kXbtifwCZ1TJLrqa92jSYb6hKptTY7lGe9Vbs4I24NIp2OVGfmsSXnhIqnG9lYCzDIUTrp5XoUaUYkiQE9eVQ4NoxYSBuM3wKfW2Haw74xSbeebPtga3DTByOsffNMjzbhDJQaJa5wo0iweXW9hMgJUZ5N6HX3gcMpHZcNJjrV1Ikve7p2PCwIxrUW8UrKwZcHQm2AFS4ko59TgObZM8nvLcy97Qa0pkBtj5oDdGsjNEafe3pAzKuVH363M20HVM99bWWcwjAFzRuZAkKzz4PJQTQI8ytDiUFPLXuK28KUZUbYkZui8bDUONFdEiutESrwinm1l09sLZgXlUBRLs8T5HnUyQQNtKEft0C7UEzcmTpOAEhnv6ZOQVBEVURHiA4DGR1gZPObFVMFuxfgFVQa5F6eBDcBem75zuFpavGiyIGpugVgIhzjmjk5TiLxsbmPLuZMcRi8lNh8gxmIulw6rHiLIMpkA6iW50IFjueb8QTiaAHRnHy5MkU9PssePyziYwrzJqkGSfDHCJq9UCCh3cUgUA2dArQIfUgKfX6ehk0y7CJqp4j9jSu67Ca4tG5KMLNJEACr61SOEPeXvg5KZHUBDddUxFwarfjiGRD5kVSJiUtoez6wp1QGluWPmKpLfGznk1iXz5FupjNob3TmNedsbx0aHgfMEkrlIXfx4E1bBWa0J770aNv3MPmn10lnvPF9cKUat7qG5BqnBQW2Msa02NE0Q9JjySu6VHwE0Ce6dYY7HNKrjq8xQdbzplkH2lLOtLhqecwJQOBAgk3IwTgFoWrM2nIuRPMPR8lw6zIdnHMOStOiI5MrbhEx7kI55JNTl18wquVPfSj0UZ8ABGFwM6gyCZsP20yU9rzdnhOsALoM0qhGMidwCzWIw85NQkzvB1ha7Qqw3J2nJ1maho5b9NxJdcYCTaS9BePTHLmm2Vr5nInY086up9nMTstrWN4dWWtkzDvQYbX5PqiLNKpsw9CV1KiXhXzoRnzHOrT4DcvwBBBcytFVb4TKyG0QVGHk6SLgMTSXr4g8NiQ9RQu3ZYc9TunbpQYMppVotqsqmsUsl9gcEyjRhaQgLTc1OjWgzT9lTBRa5ZqNf6JyMXb7NTJ7HXtc2wFlrHKNuI3Rz9ywbMBnfDvJqwtr0lC8Eb8SIK5dfVmrEWgJFdPWmBhSIvfwE', X'E98AB5C3807440E2A0BDEAA7986C00', X'E3899FDD8FE6978FEA8FBFD4A2211DCDB048', 'value2', 'd', b'10010111', '2025-08-18 04:47:28', 92.32, 9.75, '[{"k1": 0, "k2": "item_0", "k3": 64.68005827747231}, {"k1": 1, "k2": "item_1", "k3": 41.09269929837975}, {"k1": 2, "k2": "item_2", "k3": 58.90622459358933}, {"k1": 3, "k2": "item_3", "k3": 48.6643547208647}, {"k1": 4, "k2": "item_4", "k3": 62.39057661988535}, {"k1": 5, "k2": "item_5", "k3": 41.62982455523014}, {"k1": 6, "k2": "item_6", "k3": 10.966086344198425}]'); WITH cte_380 AS (SELECT JSON_REPLACE(FIRST_VALUE(utj7.c16) OVER (), '$.key', utj7.c11) AS col_2, LENGTH(utj7.c7) AS col_4 FROM t2 AS utj7) (SELECT DISTINCT AVG(mjs8.c1) AS col_3, BIT_OR(mjs8.c15) AS col_4 FROM t1 AS kcm89 RIGHT JOIN t2 AS mjs8 ON kcm89.c1 IN (SELECT t2.c3 AS c3 FROM t2 AS t2 WHERE t2.c3 BETWEEN 1 AND 100)) UNION SELECT xza85.col_2 AS col_3, xza85.col_4 AS col_4 FROM cte_380 AS xza85 WHERE xza85.col_4 IS NULL; WITH cte_380 AS (SELECT JSON_REPLACE(FIRST_VALUE(utj7.c16) OVER (), '$.key', utj7.c11) AS col_2, LENGTH(utj7.c7) AS col_4 FROM t2 AS utj7) (SELECT DISTINCT -AVG(-mjs8.c1) AS col_3, BIT_OR(mjs8.c15) AS col_4 FROM t1 AS kcm89 RIGHT JOIN t2 AS mjs8 ON kcm89.c1 IN (SELECT t2.c3 AS c3 FROM t2 AS t2 WHERE t2.c3 BETWEEN 1 AND 100)) UNION SELECT xza85.col_2 AS col_3, xza85.col_4 AS col_4 FROM cte_380 AS xza85 WHERE xza85.col_4 IS NULL;