Description:
after changing SUM(t3.c9) to -SUM(-t3.c9),the rows become less
mysql> SELECT DISTINCT SUM(t3.c9) AS col_1_3, LAST_VALUE(t3.c1) OVER () AS col_1_4, t2.c9 AS c9 FROM t2 AS t2 LEFT JOIN t3 AS t3 ON (t3.c3 = t2.c1) WHERE (NOT REGEXP_LIKE(t3.c14, '[a-z]+@[a-z]+\.[a-z]{2,3}')) GROUP BY ROUND(t2.c1), t2.c9, t3.c5, t3.c1 HAVING (COUNT(DISTINCT t2.c3) <= 4) ;
+---------+---------+--------------------------------------------------------------------------------------------------+
| col_1_3 | col_1_4 | c9 |
+---------+---------+--------------------------------------------------------------------------------------------------+
| 61 | 7629 | 0xCD86D69A1FD0B1D8B3CAAEDAB8E3BEA4CD8868EB95BDDD89E7AC9DE6BAA2C39CC28B431D7AE1B999E584A1DF82DFB7 |
| 33 | 7629 | 0xCD86D69A1FD0B1D8B3CAAEDAB8E3BEA4CD8868EB95BDDD89E7AC9DE6BAA2C39CC28B431D7AE1B999E584A1DF82DFB7 |
| 99 | 7629 | 0xE5AA91EDBEBFCCA2D7B848C5A371D5B1C49DCAB2EC8E81DBB005C083E6B59703E18E81 |
+---------+---------+--------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> SELECT DISTINCT -SUM(-t3.c9) AS col_1_3, LAST_VALUE(t3.c1) OVER () AS col_1_4, t2.c9 AS c9 FROM t2 AS t2 LEFT JOIN t3 AS t3 ON (t3.c3 = t2.c1) WHERE (NOT REGEXP_LIKE(t3.c14, '[a-z]+@[a-z]+\.[a-z]{2,3}')) GROUP BY ROUND(t2.c1), t2.c9, t3.c5, t3.c1 HAVING (COUNT(DISTINCT t2.c3) <= 4) ;
+---------+---------+--------------------------------------------------------------------------------------------------+
| col_1_3 | col_1_4 | c9 |
+---------+---------+--------------------------------------------------------------------------------------------------+
| 61 | 7629 | 0xCD86D69A1FD0B1D8B3CAAEDAB8E3BEA4CD8868EB95BDDD89E7AC9DE6BAA2C39CC28B431D7AE1B999E584A1DF82DFB7 |
| 99 | 7629 | 0xE5AA91EDBEBFCCA2D7B848C5A371D5B1C49DCAB2EC8E81DBB005C083E6B59703E18E81 |
+---------+---------+--------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;
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)
);
CREATE TABLE t3 (
c1 INT NOT NULL AUTO_INCREMENT,
c2 INT NOT NULL,
c3 INT NOT NULL,
c4 YEAR NOT NULL,
c5 TIME NULL,
c6 TINYINT NULL,
c7 SMALLINT NULL,
c8 MEDIUMINT NULL,
c9 BIGINT NULL,
c10 LONGTEXT NULL,
c11 GEOMETRY NULL,
c12 TINYTEXT NULL,
c13 TINYBLOB NULL,
c14 SET('x','y','z') NULL,
c15 TINYINT(1) NULL,
PRIMARY KEY (c1)
);
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}]');
INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (679, 9871, 123.32, 'sample_1wsyw5snwdBZm2Q71', '2025-07-04', 'sample_Jpd2CMXS04Sx3vYqmpKUaPXxJwfLlPyh2CcP8DodxVfvtVaECENbyOEF4Kbkro9fMokpWCgKj84FLrZZzgCRcF6UNVkRjAqpsIhj3UOT27lLxgwIPrUUrZvGfWmN6ClTql', 'sample_nW1t84dFEPzQsKsRiVVDz7IQo3vU751fycMfWF8gGTKvyZVp9iPoh5nt2m64jg8U2cnDtKgd8qpWHTrKTMFob1owsRIR8CLZ6AOaC8Qt7UyTiB9rwmw0JsW4t01hLllmZuA6PTXwwoTEWflLuNUAETRnZiXlQSSi5JWco3f10x3LQDj11wHFcsGepwff7lBIpW3FNrYpXZLG1rNOolKE0PLsnmtMwbuQaCBZnfQvrIAsr6RfpbwjoISK9bJefuq7DcOCzdQ36X1qrCrbdut8w5OEhPjxlbkV6d5MfWIseq6a60OfMopz8FjzaZZu9Yfcs6rFipRnHMAOkMQnC1HiQlkahF660GiPXRoFRWfMZP1ayQHzvpfhMe8jsBLJ9dSC7caOc98WkfV73OVwYg7cxi3CFx5DcsMQWjm52NRCb3WRREILJTGALzswA8UwMrlIEFo4h5ZX5zWsFF1rRRgjFBkISKaIqWkd9h2aJqFbf74QqxxMpIIYDpC0LaBpIuVBbN4AaSZYZ1d1v27MYUp7SjbwbMcVWk', X'E197A5E78A8E58D582E08AA3E9BC8822EC9FBB3E5271E28694D3A1E3BF83EA9983202BD39CE58F8F0A66', X'CD86D69A1FD0B1D8B3CAAEDAB8E3BEA4CD8868EB95BDDD89E7AC9DE6BAA2C39CC28B431D7AE1B999E584A1DF82DFB7', 'value2', 'a,b,c,d', b'10000001', '2025-02-15 04:27:12', 74.80, 32.99, '[{"k1": 0, "k2": "item_0", "k3": 7.414315396542949}, {"k1": 1, "k2": "item_1", "k3": 19.06943328813213}, {"k1": 2, "k2": "item_2", "k3": 93.63953854908058}, {"k1": 3, "k2": "item_3", "k3": 27.802181306838524}, {"k1": 4, "k2": "item_4", "k3": 46.3327283670097}, {"k1": 5, "k2": "item_5", "k3": 37.22464316038244}]');
INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (7720, 7523, 475.03, 'sample_L8M4j7WrLx4Xgj0BkE', '2025-11-07', 'sample_88W18Y4BL1zDpLdznHhElizFFD72xn6FTOTu7FWY1TGH9ccKhQ7KJsBFYm6VgIbvdzrWuUskHngFhuYSWooKqXAqW5SKc8AkA710W3qmBpbthfZpRYRoF0FgdKLUDJlpzPyPU1qJ6hvKpbd3AJ91CwWQjS534aH3WiE5eJVnEAVtmkdqr1L9Lv0SAnbIODdONkNO73qYUkNPRUvtlVIb2VIgngX0XO9cd9O2JMisAxBBUvKihTmR0gStbp8KjCX3aO8AtCjaYQM8mnQG1EYNDdFJhaEvQBR1wWScC1gdcLY1jcUn98Wpn8eqHmi02MCC9JyvnzICrUvzAyCrqG2hG2AC5Rcs3ZnkfW3zhu8lHLThshgCXiNDifg9bC4RX7Nn3bwAQva4AMrd1keFEr', 'sample_Ut8wsuWeAHS5p09ZHO1lNIp7xKXKU5UedMfrbIVsf8qKOSmNAoWUpCwjXTZxhzoXzKy6onj7ni03WdcmJyQ7V3iwJoO1RFA1nsi0gkLXsWx83dsBboizD8GDdqEynoAzd1YHEGPWBY1RRKAxxVzBs1FoizGSxyHsboKljT7PUfm7uEPNMnS1N8PA0vmzV5EIcjF3PBQTHC4wZi7Ztnyiuz12Eo58CwUstTMn7zDTLw8UZQEDhXAlxOnKnEY3LaGEqvoYnzj1vbKpzpo7d9UCjN46S0NLv0joitrKBkhciIt0ZWvjWJtSOEy00t5hgAacrDTYe4qZmx1Nnmf47R7ORyVHIV3kYlUbxupAyE3l9rkVbilHY7oYzqO1yndVZTCP1mZeL6Woka0iTLqBTCyjNKLIyWcri2TWmnidZcNkDhOwfvmdskY23KR12CLAf7SloYDIwdZ9OA4GIA10bOmYrOSuvd21dxLStljS2V5mwkErz8OItB4FPRChXWYQPDIHjbJun8nnbEsTG51aRyS91bAbBdMvQEd6vr6EhGQ1uZ5e3tr0seUHPWlSoEmGXqPO5J3ldBLL5iqDb1vpcuRSlvBbDZTKChfyFeuGHkea9PolKN481tNKPLi26EkAglzbkKPNHLoEpMvqjllry389BRuz6rd1aq2rW', X'37DBBEC3A953D48B6607EC8395E691A3C9AFC79A70', X'E5AA91EDBEBFCCA2D7B848C5A371D5B1C49DCAB2EC8E81DBB005C083E6B59703E18E81', 'value2', 'd,a,b,c', b'11001010', '2025-10-01 09:09:23', 37.13, 74.76, '{"k1": {"k2": 368, "k3": "user_59", "k4": {"k5": 18, "k6": "user28@example.com", "k7": {"k8": "city_52", "k9": "Sample Country"}}}, "k10": "2025-12-03T14:34:08.822739"}');
INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (4201, 139, 983.73, 'sample_LFWAE', '2025-04-28', 'sample_HgO1xcnIDKDGPBxAFcVtHhFyAtU8o14XoSmWJMp5xRMdK64CGU7pZw3L2NVg36pWjSrRHtXijabsAe2dAqDkvJbyKOsJwXKurDsbjRj2qNzJ9LEgpJzu2qjkTkoBX0Ejr3mVLeJbpakJbP7IIWHB49OBfNV458yUdQPreQdDQcH5VeMX9QSj4p2tzmawpCG1VVUfYSN2A4y5J9HlQzvRe3cy0uB6mvPjVxQC2ZJlbJaCXTD46yfRyarRJ3Oxr2mP0qmSmmGzDVBSQSNbVg6GlMMEMKrqjDOw4m36QLRNpAeA7lmdnoA4iOYGM5FNXiDZhVhrOqzGya7kzhsAb88V6cndWPiDB5FRNCfUDvdMatEspIanFwrOL6nHxcB5RAJ2bEhLAfNPtGtbcE2zKjuZKR1TuxVv', 'sample_kpyoWe4re7fisU9DeyA9qqUBTECHdqENoJH0kG8Hwzudp2lD19RPMRlA3DLgoUT4K6B5czF9Itf1pfsdLIrCzk26hTjCnmTRFHkkDhNE86x1aWpjk3x34S6GKVByovB9mfa6yooyw0gNAgQ2KZ5ummouMoTKdyMcrbFMooWI7Ua0ccvdOzW9rSoTkHoApTW3DemS4aR7fAoS5gzGWPPwW0XaJLhfRr90znJafq8MvSTdxQAXIZwOOIfv1WuHRzptmjNidXsy1RS0sqNiehDYAjrpZjPRewnAbPDXZCrOODBlty6cLmzdJK8Hn2vDCtFdQsLeTihRGG6Rozs6vjDNqhNz14NF23hMxEfp4IZTeWO6iXxp8iZMfPo6AB3Sr0D4ikc3B0OuAoL4FKS6NUstKDFZEdQP9ybU4uFOWEbViNjwYR0zBpb8dmODObc89MW6MEgEtoZdHChdvlxspN3d2irDwJIOMYVqVPIpsUkMvt2PTZJagfWU40syfBA6rIxoc53OVGPzDISg9GNLZs1TJTShyOXn6zx6', X'110BDEA5E5818BECAAB6C5BBD7B203C98C697FEAA182EF9CAFD0BC', X'CE8717E48C84D486E39BBBD1BDD590D682C990D697EA89B72EEE98B8EDA591CBBA', 'value3', 'b', b'11101100', '2025-07-15 10:22:35', 27.45, 18.31, '{"k1": 448, "k2": "mixed_8", "k3": ["tag_73"], "k4": {"k5": "A", "k6": [81, 61, 60, 23], "k7": {"k8": "user_69", "k9": "2025-12-03T14:34:08.822739"}}, "k10": true}');
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (4231, 3143, 679, 2009, '2023-01-01 15:48:29', 16, 17, 71, 33, 'sample_UTwKlXiZzwO5QG6ULpawcoVKUNENxTbYWcmH8d73s8aczLpas8R3NJ4K9lCzYyA74yZLFCPASl', ST_GeomFromText('POINT(34.830679 -44.881986)'), 'sample_bHQCLgNXQK7pYcOFaK0yjSbIyM4VrUf2MiF7PmNDqBFpHiIZXnKQd2YM3FjkwvpT63x8T5eNF5dA7', X'CA8476E5B9B834', 'x,y', NULL);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (7852, 139, 679, 2007, '2023-01-01 11:25:55', 54, 65, 48, 61, 'sample_v4lOnlWYKQrD6ew2FBGi3ibbJzcqCM9hDd2t7PSFSbjKGBobQo6zhvPd2f3xrB5cM5vu6p1dKkh2CBdda0GVsBz0SabPGQuMy7Ivd4PxyaQpPTPz7XdNz0z0Qnmo7KVmnMt77PzkrNRmu1kI4QNdbkkc1mA7zfYcGimsRAlqfXpmxOn86upYJDgMqWsYxiep7nzW7lfclLIlaJuyAGcjMDNplpXEyKM1zqASpEWuMgN0cmAprJYMIPa1zWUWtMTSqTcdc3j0ayHT7az5R8KhLknd8YxmSnPk0EDIfw6cznYJk0WhixQru5kN2NvlS3ij9Jyhn0Uja1RG0B7RW8DXD9JLC8fII0m0g9cxZoQjM6lbuQdlW4y99ehV2SP2Sy6AGNCqk8DN3vpjgJ9ZqbLzHw1bluFOiDVK3lO2YWoS9EVigNFRjPuk8oXyOYK91AHWqeiB3g7bbdjD8THHbOTMqyO75fBnybHPohVfc1UbUnPi49nQdJCOfXVb8TyhDO9T6bKmEhFAd4eja3qMRY47OOMMKQzD56Yf9guaeKklGQctBQ3BoDaRSHuX4KScF35rUdnSNbNntheyRanS5GW9iE1zXGPl1a9qk5PKhAJLPee6YuMt86BQqKNwJEueRzdlA86Sd7S4DUayOyV8IpMzavmgrii0RXLHXoRR4zgn3DHdWDqacMHlVabQjfLBtwwBbCp4YSfe84PjPR7slqhvpw9hSZnXN8D8Diws0PN303cxSmRxeXl0hSjbP0zuZFZLftFwY697zIGMmeYcZpb1npkj9FfPcEL4CjLjybaTDKS0Siw2YHIhn80fRaTRHkZWwAeBr5KQbmQgB74iPLaWA1MZl3mLFanRT5IXwSwUAdgLqJ3n7PM9gWSUYch4IoIoiokJ9tJ4z7IAqgw864mQZr9cgmjpIHCJhrkfb81gcwSBxLuC6amaFnVt4k8K60TSCZcqoDj26QHcnMhsqhDVMd2qWOp8TJwO9nryLhx8Di9r1hcX2D47Z7wpyRxsRYMvmNV4VN2nUpC9y8GVNutvacbMXEmoIxpOlGfuNcysVpAvqEwkjXn18WTV1Hw45rguoXa4BVQVsEWLzTmJjL9yenNPOOHP94BlraK6cpVwksWuH0wk', ST_GeomFromText('POINT(73.739477 155.07955)'), 'sample_siNHY2L0knKgjWHFX06VOxkw0kIPZoBF2', X'E2B9B8D183E1B1AA1DE5889BCDBCCBA3E5BEBDC2AB07EEB78AC282CAB15ECEA4CE816DC696ED8A9B', 'y,x,z', NULL);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (7629, 2644, 7720, 2003, '2023-01-01 17:33:49', 62, 94, 44, 99, 'sample_3ftJK6oL604rWP2Xur8pQOcxAR4tqMxp8mnR2q0ySwmntwwtUKAhAL6OGzE7ixAdKZn9MOAjNpogO1ANZI4vBAYmEDt7AMUS1BntVfcO12FEYQere8MQruD4EXTxb6OHGrALnI2ByNdSQBNA1XtG5vkeVlI8cu8FM6qN1AsY2yHfIXnvCnHJ4Ec0D0TcmeXtkh7pMdSS75LGIUMkL7PjXIon1ZupGaCoRb976GbHy7YLUtYNrZSamAenneuHRzfRVaSt9sWPhjQl6gdAyoVwr5ZZuiWURhb3H1GjumiOYukSIQGEwhqfAGb27HjcvEJucZV3QrsSWXWGMA2AV5VEnYJ54OjTIsqCURzuc57RCAsdvbPN6M4AYJfI58TY2eXePkQvu5qRYkyP8hNOK176ZwJ8C1eBCOgm4HHUDzFf8lYWa8rBvElAk6g61OvUIssW8Jq3GjlZ5V37eLrP9hH6EbEeirKJmsqJScewExiiaHQNfAmNtNDoHhwBg7nqcjkWAwteuovrb0W2TUVFgHTHAEIEEq7IcsL3LtKj88Bvdg3rNg2N00HHajz4MJGfkKKQ6U9ZkMSX4yhhUNhIkCrDvG7rS9O0ATT6GiCA5Ajyh9e2jErVY1hdVMBO9mqo2TYP8Op1CY4c1i0VOMGosz1Daeg6vPZogjklyoFkY7MJYn6rHHVWlYjp8LLX5qj0G07STmTO5zg1Wz4i2BFAKKIw9LzQcFwOXJ6ds4ZjRZtaYzoprHikqToOWAmjeMkPTbtjbvVJgrgon4842hLBTxoz9CHhasTnspCKc3LmRuMgsvPFiFNEzKTWYGQqn9lKbPky1xUuEpoxGkTmAPt2pnxAMrLC5npPmMaLiPU76uXiGdNMOnJCcIsn5BELCAqbKALylrtKjCwK4TFyecfIFzBE3DJcjsLo6MISbqACaPNL7NBuD7bqQEPDcIe791LeDL12EgZSB4wBDs8g5qH2RBgF4txgZMvJST9K25K2RMCrRqgnIycKXvN6AyBUB58QvRXO5TMx8zJoyuNv7ipiYqRAqDTAMB9C3IP9fDBLEeYDK1H4jfTbCCC8hWSuZWjeJhqS0hMFjQYiYHsPzcOpUKSOmWg4iOS2NKq9shlsJXUB5VzhJVGnhgDe9OEHwDfZn15DBYfz8DXReYVKyiH5dbJub9S0cEyoU9rTm1eeKFBq061XfRIMy3ZbtDI1rvbNqhVLFaU8pGjcDsz1Ty7RI9kkiS53oskMSuxbMQKEqAfKtL8XyPImriyGidgPFQc07Hpm3E93drqFayhONioHj4fcxe2JEjgKuu6FVQAuKmnrAWzR5d8EgpurIrLB2frzn2kTwImm78equOdlyo2C9DbDsRRbqQPjqtDmRjISrLbMYIpVyJ8SHgRhLtbKL9sZaidglNL49zMmhS0280tTGQiRC6HZfMZbdgOszz9Y8NwMGjaibOVsE9PQKZfXk2p6aESpcNGTw7MWHmKukrurvVC0sAE5eLdEzztyRpjTPkYLI7Wky14y5eJm36GZT80qWmpJUWUbfGZrKbthjYQOTlemSmApgIVGfGU7rfldXSij2rez3tLJIsog4JL6XekIFvu3frTKdhwHnXebI9h79nP8xMMfqCrXjX61mHWXAZ4d1OD5wYk9pGhqZvl4D0InN7OsTXJqDRWQyU8oDVAfC7QEkGMnDDm2fa32k9C7SlTLY2kLYfFCPTgs2TL86KSeM8yfNwPZteoTyjUQQFXCetjY8L8QwPFD4zLtRQ5XPrLPyhU1Oj', ST_GeomFromText('POINT(-61.085338 -135.365706)'), 'sample_XhBEVenL2MqVIjUYNaXE9gh2ceFNtnuF7eXufUetX38U1PnthAAnZYKsW3ZdMlesnNEJvDzVfvhgKygCKAwBdBOecCSS12QM5XdKzubsisGHfaYkeiLO42EjL74s7uuNdD7oxBcytM63svySlaiMnm6GcFwKRhbAkqLYIKdGoJBymjXn', X'E58A887E1B70D29AE88CA4307A', 'y,z,x', NULL);
SELECT DISTINCT SUM(t3.c9) AS col_1_3, LAST_VALUE(t3.c1) OVER () AS col_1_4, t2.c9 AS c9 FROM t2 AS t2 LEFT JOIN t3 AS t3 ON (t3.c3 = t2.c1) WHERE (NOT REGEXP_LIKE(t3.c14, '[a-z]+@[a-z]+\.[a-z]{2,3}')) GROUP BY ROUND(t2.c1), t2.c9, t3.c5, t3.c1 HAVING (COUNT(DISTINCT t2.c3) <= 4) ;
SELECT DISTINCT -SUM(-t3.c9) AS col_1_3, LAST_VALUE(t3.c1) OVER () AS col_1_4, t2.c9 AS c9 FROM t2 AS t2 LEFT JOIN t3 AS t3 ON (t3.c3 = t2.c1) WHERE (NOT REGEXP_LIKE(t3.c14, '[a-z]+@[a-z]+\.[a-z]{2,3}')) GROUP BY ROUND(t2.c1), t2.c9, t3.c5, t3.c1 HAVING (COUNT(DISTINCT t2.c3) <= 4) ;
Description: after changing SUM(t3.c9) to -SUM(-t3.c9),the rows become less mysql> SELECT DISTINCT SUM(t3.c9) AS col_1_3, LAST_VALUE(t3.c1) OVER () AS col_1_4, t2.c9 AS c9 FROM t2 AS t2 LEFT JOIN t3 AS t3 ON (t3.c3 = t2.c1) WHERE (NOT REGEXP_LIKE(t3.c14, '[a-z]+@[a-z]+\.[a-z]{2,3}')) GROUP BY ROUND(t2.c1), t2.c9, t3.c5, t3.c1 HAVING (COUNT(DISTINCT t2.c3) <= 4) ; +---------+---------+--------------------------------------------------------------------------------------------------+ | col_1_3 | col_1_4 | c9 | +---------+---------+--------------------------------------------------------------------------------------------------+ | 61 | 7629 | 0xCD86D69A1FD0B1D8B3CAAEDAB8E3BEA4CD8868EB95BDDD89E7AC9DE6BAA2C39CC28B431D7AE1B999E584A1DF82DFB7 | | 33 | 7629 | 0xCD86D69A1FD0B1D8B3CAAEDAB8E3BEA4CD8868EB95BDDD89E7AC9DE6BAA2C39CC28B431D7AE1B999E584A1DF82DFB7 | | 99 | 7629 | 0xE5AA91EDBEBFCCA2D7B848C5A371D5B1C49DCAB2EC8E81DBB005C083E6B59703E18E81 | +---------+---------+--------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> SELECT DISTINCT -SUM(-t3.c9) AS col_1_3, LAST_VALUE(t3.c1) OVER () AS col_1_4, t2.c9 AS c9 FROM t2 AS t2 LEFT JOIN t3 AS t3 ON (t3.c3 = t2.c1) WHERE (NOT REGEXP_LIKE(t3.c14, '[a-z]+@[a-z]+\.[a-z]{2,3}')) GROUP BY ROUND(t2.c1), t2.c9, t3.c5, t3.c1 HAVING (COUNT(DISTINCT t2.c3) <= 4) ; +---------+---------+--------------------------------------------------------------------------------------------------+ | col_1_3 | col_1_4 | c9 | +---------+---------+--------------------------------------------------------------------------------------------------+ | 61 | 7629 | 0xCD86D69A1FD0B1D8B3CAAEDAB8E3BEA4CD8868EB95BDDD89E7AC9DE6BAA2C39CC28B431D7AE1B999E584A1DF82DFB7 | | 99 | 7629 | 0xE5AA91EDBEBFCCA2D7B848C5A371D5B1C49DCAB2EC8E81DBB005C083E6B59703E18E81 | +---------+---------+--------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE IF NOT EXISTS test; USE test; 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) ); CREATE TABLE t3 ( c1 INT NOT NULL AUTO_INCREMENT, c2 INT NOT NULL, c3 INT NOT NULL, c4 YEAR NOT NULL, c5 TIME NULL, c6 TINYINT NULL, c7 SMALLINT NULL, c8 MEDIUMINT NULL, c9 BIGINT NULL, c10 LONGTEXT NULL, c11 GEOMETRY NULL, c12 TINYTEXT NULL, c13 TINYBLOB NULL, c14 SET('x','y','z') NULL, c15 TINYINT(1) NULL, PRIMARY KEY (c1) ); 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}]'); INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (679, 9871, 123.32, 'sample_1wsyw5snwdBZm2Q71', '2025-07-04', 'sample_Jpd2CMXS04Sx3vYqmpKUaPXxJwfLlPyh2CcP8DodxVfvtVaECENbyOEF4Kbkro9fMokpWCgKj84FLrZZzgCRcF6UNVkRjAqpsIhj3UOT27lLxgwIPrUUrZvGfWmN6ClTql', 'sample_nW1t84dFEPzQsKsRiVVDz7IQo3vU751fycMfWF8gGTKvyZVp9iPoh5nt2m64jg8U2cnDtKgd8qpWHTrKTMFob1owsRIR8CLZ6AOaC8Qt7UyTiB9rwmw0JsW4t01hLllmZuA6PTXwwoTEWflLuNUAETRnZiXlQSSi5JWco3f10x3LQDj11wHFcsGepwff7lBIpW3FNrYpXZLG1rNOolKE0PLsnmtMwbuQaCBZnfQvrIAsr6RfpbwjoISK9bJefuq7DcOCzdQ36X1qrCrbdut8w5OEhPjxlbkV6d5MfWIseq6a60OfMopz8FjzaZZu9Yfcs6rFipRnHMAOkMQnC1HiQlkahF660GiPXRoFRWfMZP1ayQHzvpfhMe8jsBLJ9dSC7caOc98WkfV73OVwYg7cxi3CFx5DcsMQWjm52NRCb3WRREILJTGALzswA8UwMrlIEFo4h5ZX5zWsFF1rRRgjFBkISKaIqWkd9h2aJqFbf74QqxxMpIIYDpC0LaBpIuVBbN4AaSZYZ1d1v27MYUp7SjbwbMcVWk', X'E197A5E78A8E58D582E08AA3E9BC8822EC9FBB3E5271E28694D3A1E3BF83EA9983202BD39CE58F8F0A66', X'CD86D69A1FD0B1D8B3CAAEDAB8E3BEA4CD8868EB95BDDD89E7AC9DE6BAA2C39CC28B431D7AE1B999E584A1DF82DFB7', 'value2', 'a,b,c,d', b'10000001', '2025-02-15 04:27:12', 74.80, 32.99, '[{"k1": 0, "k2": "item_0", "k3": 7.414315396542949}, {"k1": 1, "k2": "item_1", "k3": 19.06943328813213}, {"k1": 2, "k2": "item_2", "k3": 93.63953854908058}, {"k1": 3, "k2": "item_3", "k3": 27.802181306838524}, {"k1": 4, "k2": "item_4", "k3": 46.3327283670097}, {"k1": 5, "k2": "item_5", "k3": 37.22464316038244}]'); INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (7720, 7523, 475.03, 'sample_L8M4j7WrLx4Xgj0BkE', '2025-11-07', 'sample_88W18Y4BL1zDpLdznHhElizFFD72xn6FTOTu7FWY1TGH9ccKhQ7KJsBFYm6VgIbvdzrWuUskHngFhuYSWooKqXAqW5SKc8AkA710W3qmBpbthfZpRYRoF0FgdKLUDJlpzPyPU1qJ6hvKpbd3AJ91CwWQjS534aH3WiE5eJVnEAVtmkdqr1L9Lv0SAnbIODdONkNO73qYUkNPRUvtlVIb2VIgngX0XO9cd9O2JMisAxBBUvKihTmR0gStbp8KjCX3aO8AtCjaYQM8mnQG1EYNDdFJhaEvQBR1wWScC1gdcLY1jcUn98Wpn8eqHmi02MCC9JyvnzICrUvzAyCrqG2hG2AC5Rcs3ZnkfW3zhu8lHLThshgCXiNDifg9bC4RX7Nn3bwAQva4AMrd1keFEr', 'sample_Ut8wsuWeAHS5p09ZHO1lNIp7xKXKU5UedMfrbIVsf8qKOSmNAoWUpCwjXTZxhzoXzKy6onj7ni03WdcmJyQ7V3iwJoO1RFA1nsi0gkLXsWx83dsBboizD8GDdqEynoAzd1YHEGPWBY1RRKAxxVzBs1FoizGSxyHsboKljT7PUfm7uEPNMnS1N8PA0vmzV5EIcjF3PBQTHC4wZi7Ztnyiuz12Eo58CwUstTMn7zDTLw8UZQEDhXAlxOnKnEY3LaGEqvoYnzj1vbKpzpo7d9UCjN46S0NLv0joitrKBkhciIt0ZWvjWJtSOEy00t5hgAacrDTYe4qZmx1Nnmf47R7ORyVHIV3kYlUbxupAyE3l9rkVbilHY7oYzqO1yndVZTCP1mZeL6Woka0iTLqBTCyjNKLIyWcri2TWmnidZcNkDhOwfvmdskY23KR12CLAf7SloYDIwdZ9OA4GIA10bOmYrOSuvd21dxLStljS2V5mwkErz8OItB4FPRChXWYQPDIHjbJun8nnbEsTG51aRyS91bAbBdMvQEd6vr6EhGQ1uZ5e3tr0seUHPWlSoEmGXqPO5J3ldBLL5iqDb1vpcuRSlvBbDZTKChfyFeuGHkea9PolKN481tNKPLi26EkAglzbkKPNHLoEpMvqjllry389BRuz6rd1aq2rW', X'37DBBEC3A953D48B6607EC8395E691A3C9AFC79A70', X'E5AA91EDBEBFCCA2D7B848C5A371D5B1C49DCAB2EC8E81DBB005C083E6B59703E18E81', 'value2', 'd,a,b,c', b'11001010', '2025-10-01 09:09:23', 37.13, 74.76, '{"k1": {"k2": 368, "k3": "user_59", "k4": {"k5": 18, "k6": "user28@example.com", "k7": {"k8": "city_52", "k9": "Sample Country"}}}, "k10": "2025-12-03T14:34:08.822739"}'); INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (4201, 139, 983.73, 'sample_LFWAE', '2025-04-28', 'sample_HgO1xcnIDKDGPBxAFcVtHhFyAtU8o14XoSmWJMp5xRMdK64CGU7pZw3L2NVg36pWjSrRHtXijabsAe2dAqDkvJbyKOsJwXKurDsbjRj2qNzJ9LEgpJzu2qjkTkoBX0Ejr3mVLeJbpakJbP7IIWHB49OBfNV458yUdQPreQdDQcH5VeMX9QSj4p2tzmawpCG1VVUfYSN2A4y5J9HlQzvRe3cy0uB6mvPjVxQC2ZJlbJaCXTD46yfRyarRJ3Oxr2mP0qmSmmGzDVBSQSNbVg6GlMMEMKrqjDOw4m36QLRNpAeA7lmdnoA4iOYGM5FNXiDZhVhrOqzGya7kzhsAb88V6cndWPiDB5FRNCfUDvdMatEspIanFwrOL6nHxcB5RAJ2bEhLAfNPtGtbcE2zKjuZKR1TuxVv', 'sample_kpyoWe4re7fisU9DeyA9qqUBTECHdqENoJH0kG8Hwzudp2lD19RPMRlA3DLgoUT4K6B5czF9Itf1pfsdLIrCzk26hTjCnmTRFHkkDhNE86x1aWpjk3x34S6GKVByovB9mfa6yooyw0gNAgQ2KZ5ummouMoTKdyMcrbFMooWI7Ua0ccvdOzW9rSoTkHoApTW3DemS4aR7fAoS5gzGWPPwW0XaJLhfRr90znJafq8MvSTdxQAXIZwOOIfv1WuHRzptmjNidXsy1RS0sqNiehDYAjrpZjPRewnAbPDXZCrOODBlty6cLmzdJK8Hn2vDCtFdQsLeTihRGG6Rozs6vjDNqhNz14NF23hMxEfp4IZTeWO6iXxp8iZMfPo6AB3Sr0D4ikc3B0OuAoL4FKS6NUstKDFZEdQP9ybU4uFOWEbViNjwYR0zBpb8dmODObc89MW6MEgEtoZdHChdvlxspN3d2irDwJIOMYVqVPIpsUkMvt2PTZJagfWU40syfBA6rIxoc53OVGPzDISg9GNLZs1TJTShyOXn6zx6', X'110BDEA5E5818BECAAB6C5BBD7B203C98C697FEAA182EF9CAFD0BC', X'CE8717E48C84D486E39BBBD1BDD590D682C990D697EA89B72EEE98B8EDA591CBBA', 'value3', 'b', b'11101100', '2025-07-15 10:22:35', 27.45, 18.31, '{"k1": 448, "k2": "mixed_8", "k3": ["tag_73"], "k4": {"k5": "A", "k6": [81, 61, 60, 23], "k7": {"k8": "user_69", "k9": "2025-12-03T14:34:08.822739"}}, "k10": true}'); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (4231, 3143, 679, 2009, '2023-01-01 15:48:29', 16, 17, 71, 33, 'sample_UTwKlXiZzwO5QG6ULpawcoVKUNENxTbYWcmH8d73s8aczLpas8R3NJ4K9lCzYyA74yZLFCPASl', ST_GeomFromText('POINT(34.830679 -44.881986)'), 'sample_bHQCLgNXQK7pYcOFaK0yjSbIyM4VrUf2MiF7PmNDqBFpHiIZXnKQd2YM3FjkwvpT63x8T5eNF5dA7', X'CA8476E5B9B834', 'x,y', NULL); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (7852, 139, 679, 2007, '2023-01-01 11:25:55', 54, 65, 48, 61, 'sample_v4lOnlWYKQrD6ew2FBGi3ibbJzcqCM9hDd2t7PSFSbjKGBobQo6zhvPd2f3xrB5cM5vu6p1dKkh2CBdda0GVsBz0SabPGQuMy7Ivd4PxyaQpPTPz7XdNz0z0Qnmo7KVmnMt77PzkrNRmu1kI4QNdbkkc1mA7zfYcGimsRAlqfXpmxOn86upYJDgMqWsYxiep7nzW7lfclLIlaJuyAGcjMDNplpXEyKM1zqASpEWuMgN0cmAprJYMIPa1zWUWtMTSqTcdc3j0ayHT7az5R8KhLknd8YxmSnPk0EDIfw6cznYJk0WhixQru5kN2NvlS3ij9Jyhn0Uja1RG0B7RW8DXD9JLC8fII0m0g9cxZoQjM6lbuQdlW4y99ehV2SP2Sy6AGNCqk8DN3vpjgJ9ZqbLzHw1bluFOiDVK3lO2YWoS9EVigNFRjPuk8oXyOYK91AHWqeiB3g7bbdjD8THHbOTMqyO75fBnybHPohVfc1UbUnPi49nQdJCOfXVb8TyhDO9T6bKmEhFAd4eja3qMRY47OOMMKQzD56Yf9guaeKklGQctBQ3BoDaRSHuX4KScF35rUdnSNbNntheyRanS5GW9iE1zXGPl1a9qk5PKhAJLPee6YuMt86BQqKNwJEueRzdlA86Sd7S4DUayOyV8IpMzavmgrii0RXLHXoRR4zgn3DHdWDqacMHlVabQjfLBtwwBbCp4YSfe84PjPR7slqhvpw9hSZnXN8D8Diws0PN303cxSmRxeXl0hSjbP0zuZFZLftFwY697zIGMmeYcZpb1npkj9FfPcEL4CjLjybaTDKS0Siw2YHIhn80fRaTRHkZWwAeBr5KQbmQgB74iPLaWA1MZl3mLFanRT5IXwSwUAdgLqJ3n7PM9gWSUYch4IoIoiokJ9tJ4z7IAqgw864mQZr9cgmjpIHCJhrkfb81gcwSBxLuC6amaFnVt4k8K60TSCZcqoDj26QHcnMhsqhDVMd2qWOp8TJwO9nryLhx8Di9r1hcX2D47Z7wpyRxsRYMvmNV4VN2nUpC9y8GVNutvacbMXEmoIxpOlGfuNcysVpAvqEwkjXn18WTV1Hw45rguoXa4BVQVsEWLzTmJjL9yenNPOOHP94BlraK6cpVwksWuH0wk', ST_GeomFromText('POINT(73.739477 155.07955)'), 'sample_siNHY2L0knKgjWHFX06VOxkw0kIPZoBF2', X'E2B9B8D183E1B1AA1DE5889BCDBCCBA3E5BEBDC2AB07EEB78AC282CAB15ECEA4CE816DC696ED8A9B', 'y,x,z', NULL); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (7629, 2644, 7720, 2003, '2023-01-01 17:33:49', 62, 94, 44, 99, 'sample_3ftJK6oL604rWP2Xur8pQOcxAR4tqMxp8mnR2q0ySwmntwwtUKAhAL6OGzE7ixAdKZn9MOAjNpogO1ANZI4vBAYmEDt7AMUS1BntVfcO12FEYQere8MQruD4EXTxb6OHGrALnI2ByNdSQBNA1XtG5vkeVlI8cu8FM6qN1AsY2yHfIXnvCnHJ4Ec0D0TcmeXtkh7pMdSS75LGIUMkL7PjXIon1ZupGaCoRb976GbHy7YLUtYNrZSamAenneuHRzfRVaSt9sWPhjQl6gdAyoVwr5ZZuiWURhb3H1GjumiOYukSIQGEwhqfAGb27HjcvEJucZV3QrsSWXWGMA2AV5VEnYJ54OjTIsqCURzuc57RCAsdvbPN6M4AYJfI58TY2eXePkQvu5qRYkyP8hNOK176ZwJ8C1eBCOgm4HHUDzFf8lYWa8rBvElAk6g61OvUIssW8Jq3GjlZ5V37eLrP9hH6EbEeirKJmsqJScewExiiaHQNfAmNtNDoHhwBg7nqcjkWAwteuovrb0W2TUVFgHTHAEIEEq7IcsL3LtKj88Bvdg3rNg2N00HHajz4MJGfkKKQ6U9ZkMSX4yhhUNhIkCrDvG7rS9O0ATT6GiCA5Ajyh9e2jErVY1hdVMBO9mqo2TYP8Op1CY4c1i0VOMGosz1Daeg6vPZogjklyoFkY7MJYn6rHHVWlYjp8LLX5qj0G07STmTO5zg1Wz4i2BFAKKIw9LzQcFwOXJ6ds4ZjRZtaYzoprHikqToOWAmjeMkPTbtjbvVJgrgon4842hLBTxoz9CHhasTnspCKc3LmRuMgsvPFiFNEzKTWYGQqn9lKbPky1xUuEpoxGkTmAPt2pnxAMrLC5npPmMaLiPU76uXiGdNMOnJCcIsn5BELCAqbKALylrtKjCwK4TFyecfIFzBE3DJcjsLo6MISbqACaPNL7NBuD7bqQEPDcIe791LeDL12EgZSB4wBDs8g5qH2RBgF4txgZMvJST9K25K2RMCrRqgnIycKXvN6AyBUB58QvRXO5TMx8zJoyuNv7ipiYqRAqDTAMB9C3IP9fDBLEeYDK1H4jfTbCCC8hWSuZWjeJhqS0hMFjQYiYHsPzcOpUKSOmWg4iOS2NKq9shlsJXUB5VzhJVGnhgDe9OEHwDfZn15DBYfz8DXReYVKyiH5dbJub9S0cEyoU9rTm1eeKFBq061XfRIMy3ZbtDI1rvbNqhVLFaU8pGjcDsz1Ty7RI9kkiS53oskMSuxbMQKEqAfKtL8XyPImriyGidgPFQc07Hpm3E93drqFayhONioHj4fcxe2JEjgKuu6FVQAuKmnrAWzR5d8EgpurIrLB2frzn2kTwImm78equOdlyo2C9DbDsRRbqQPjqtDmRjISrLbMYIpVyJ8SHgRhLtbKL9sZaidglNL49zMmhS0280tTGQiRC6HZfMZbdgOszz9Y8NwMGjaibOVsE9PQKZfXk2p6aESpcNGTw7MWHmKukrurvVC0sAE5eLdEzztyRpjTPkYLI7Wky14y5eJm36GZT80qWmpJUWUbfGZrKbthjYQOTlemSmApgIVGfGU7rfldXSij2rez3tLJIsog4JL6XekIFvu3frTKdhwHnXebI9h79nP8xMMfqCrXjX61mHWXAZ4d1OD5wYk9pGhqZvl4D0InN7OsTXJqDRWQyU8oDVAfC7QEkGMnDDm2fa32k9C7SlTLY2kLYfFCPTgs2TL86KSeM8yfNwPZteoTyjUQQFXCetjY8L8QwPFD4zLtRQ5XPrLPyhU1Oj', ST_GeomFromText('POINT(-61.085338 -135.365706)'), 'sample_XhBEVenL2MqVIjUYNaXE9gh2ceFNtnuF7eXufUetX38U1PnthAAnZYKsW3ZdMlesnNEJvDzVfvhgKygCKAwBdBOecCSS12QM5XdKzubsisGHfaYkeiLO42EjL74s7uuNdD7oxBcytM63svySlaiMnm6GcFwKRhbAkqLYIKdGoJBymjXn', X'E58A887E1B70D29AE88CA4307A', 'y,z,x', NULL); SELECT DISTINCT SUM(t3.c9) AS col_1_3, LAST_VALUE(t3.c1) OVER () AS col_1_4, t2.c9 AS c9 FROM t2 AS t2 LEFT JOIN t3 AS t3 ON (t3.c3 = t2.c1) WHERE (NOT REGEXP_LIKE(t3.c14, '[a-z]+@[a-z]+\.[a-z]{2,3}')) GROUP BY ROUND(t2.c1), t2.c9, t3.c5, t3.c1 HAVING (COUNT(DISTINCT t2.c3) <= 4) ; SELECT DISTINCT -SUM(-t3.c9) AS col_1_3, LAST_VALUE(t3.c1) OVER () AS col_1_4, t2.c9 AS c9 FROM t2 AS t2 LEFT JOIN t3 AS t3 ON (t3.c3 = t2.c1) WHERE (NOT REGEXP_LIKE(t3.c14, '[a-z]+@[a-z]+\.[a-z]{2,3}')) GROUP BY ROUND(t2.c1), t2.c9, t3.c5, t3.c1 HAVING (COUNT(DISTINCT t2.c3) <= 4) ;