Track issue for expression test cases generated by LLM

[YangKeao]

I'm trying to generate test cases for expression through LLM. In this issue, I'll track all issues and error cases found by this method. The description will included all sub-issues, and the failed test cases will be added to the comment.

Some of them may be duplicated, I didn't search the github before creating the issue (sorry). Feel free to close them if you find it's duplicated.

• REGEXP_LIKE shouldn't accept arguments with mixed collation #59411
• The argument range of REGEXP_LIKE is not compatible with MySQL #59412
• Cast from utf8mb4 to latin1 is not compatible with MySQL #59413
• ROUND has different result with MySQL for even positive precision #59414
• ~BINARY will have different result with MySQL #59415
• REGEXP_INSTR is not compatible with MySQL for some cases #59416
• Shouldn't allow zero month in datetime string #59417
• Implicit casting rule is not compatible with MySQL for BIN(current_date) #59418
• GET_FORMAT returns empty string instead of NULL for unknown format #59419
• GET_FORMAT should be case insensitive for formats #59420
• EXTRACT is not compatible with MySQL for truncated value #59421
• The DATE_FORMAT is not compatible with MySQL for 0 date #59423
• DATE_FORMAT is not compatible with MySQL for empty foramt #59424
• REGEXP_REPLACE is not compatible with MySQL for empty string #59425
• REGEXP_REPLACE doesn't support regex 1(?=2) #59426
• SEC_TO_TIME has different precision compared with MySQL #59428
• ADDDATE is not compatible with MySQL when encoutering the zero time #59429
• CONVERT_TZ is not compatible with MySQL for edging value #59430
• MINUTE cannot return correct minutes for truncated value #59431
• TIME returns zero time instead of NULL for invalid time #59432
• STR_TO_DATE fail to parse 'Tuesday, 23 May 2023' for '%W, %d %M %Y' #59433
• UNIX_TIMESTAMP doesn't support all formats which are supported by MySQL #59434
• ADDTIME returns datetime instead of time #59435
• PERIOD_DIFF failed to validate incorrect argument #59436
• TIMESTAMP should return NULL for zero time #59437
• TIMESTAMP shouldn't shift timezone #59438
• The return type of TIMESTAMPADD is not correct. #59439
• TO_SECONDS is not compatible with MySQL for invalid truncated values. #59440
• BIN should return NULL for empty string #59441
• The result of BIN is not compatible with MySQL when the argument is truncated #59442
• FORMAT has incorrect result #59443
• WEEK should return week even when the mode is NULL #59444
• TIME_FORMAT should return NULL for empty format #59445
• OCT should return NULL for empty string #59446
• LPAD and RPAD shouldn't return NULL for empty padstr #59447
• The implicit casting rule of FIELD is not compatible with MySQL #59448
• The implicit casting for QUOTE function is not compatible with MySQL #59449
• The implicit casting rule for TO_BASE64 is not compatible with MySQL #59450
• SUBTIME should return TIME even if the argument looks like DATETIME #59451
• SUBTIME returns incorrect result for edging cases #59452
• FIND_IN_SET is not compatible with MySQL #59453
• The implicit casting rule for WEIGHT_STRING is not correct #59454
• FORMAT_BYTES is not compatible with MySQL #59455
• is_ipv4_mapped, IS_IPV4, IS_IPV4_COMPAT, IS_IPV6 should return NULL for NULL argument #59456
• IS_UUID and UUID_TO_BIN shouldn't trim the argument #59457
• IS_IPV4_COMPAT gives wrong result for all zero value #59458
• NAME_CONST accepts different kind of arguments compared with MySQL #59459
• json_replace should accept number value as json #59460
• INET6_NTOA should return null for invalid address #59461
• JSON_LENGTH, JSON_DEPTH, JSON_INSERT, JSON_QUOTE shouldn't implicitly cast argument to json #59462
• JSON_SEARCH returns incorrect result for some special value #59463
• JSON_QUOTE(1/0) should return NULL #59464
• JSON_ARRAY_APPEND returns incorrect value for array arguments #59465
• The result of UNCOMPRESSED_LENGTH is different with MySQL #59617
• The COERCIBILITY of variables are different from MySQL #59618
• The JSON_SET returns wrong result for empty object #59619
• TiDB returns error instead of warning for some truncated error #59620
• interval is truncated to wrong value #59621
• TIMEDIFF might give wrong result when casting values #59622
• JSON_SCHEMA_VALID returns wrong result for strange schema #59623
• Should return warning instead of error for truncated argument in sec_to_time. #59624

[YangKeao]

The following SQLs don't have same result between TiDB and MySQL v8.4.4.

SELECT REGEXP_LIKE(BINARY 'ABC', 'abc', 'i');
SELECT REGEXP_LIKE(CAST('2023' AS BINARY), '^202');
SELECT REGEXP_LIKE('🇨🇦', '\x{1F1E8}\x{1F1E6}');
SELECT REGEXP_LIKE(0x48656C6C6F, '^Hello$');
SELECT REGEXP_LIKE('π', '\x{03C0}');
SELECT CAST('中文' AS CHAR CHARACTER SET latin1);
SELECT ROUND(123.4, 5);
SELECT ~BINARY '999';
SELECT BINARY 'Abc' REGEXP '^abc';
SELECT REGEXP_INSTR('a\nb', 'a.b', 1, 1, 0, 'n');
SELECT DATE('2024-00-15');
SELECT BIN(current_date);
SELECT GET_FORMAT(DATETIME, 123);
SELECT GET_FORMAT(TIME, 'usa');
SELECT GET_FORMAT(DATE, 0);
SELECT GET_FORMAT(DATE, 'FOO');
SELECT EXTRACT(DAY_HOUR FROM 0x0D0E0A);
SELECT DATE_FORMAT(0, '%Y%m%d');
SELECT DATE_FORMAT('2023-10-30', '');
SELECT REGEXP_REPLACE('', '^$', 'empty');
SELECT REGEXP_REPLACE('123', '1(?=2)', 'X', 1, 0);
SELECT sec_to_time(b'1111');
SELECT sec_to_time(0x1E0);
SELECT ADDDATE('0000-01-01', INTERVAL -1 DAY);
SELECT CONVERT_TZ('0000-01-01 00:00:00', '+00:00', '+01:00');
SELECT CONVERT_TZ('9999-12-31 23:59:59', 'UTC', 'Asia/Kolkata');
SELECT MINUTE('12:30:00+05:00');
SELECT TIME('');
SELECT TIME('NOT_A_TIME');
SELECT STR_TO_DATE('Tuesday, 23 May 2023', '%W, %d %M %Y');
SELECT UNIX_TIMESTAMP('');
SELECT UNIX_TIMESTAMP('2024');
SELECT UNIX_TIMESTAMP('20240228T123456');
SELECT UNIX_TIMESTAMP('28 February 2024');
SELECT UNIX_TIMESTAMP('2024-02-28abc');
SELECT ADDTIME('2023-01-01', '12:34:56');
SELECT ADDTIME('2023-01-01', '237:00:00');
SELECT PERIOD_DIFF('\n202405\n', '\t202406');
SELECT TIMESTAMP('0000-00-00 00:00:00');
SELECT TIMESTAMP('2023-10-10T12:00:00Z');
SELECT TIMESTAMPADD(DAY, -1, '2023-01-01');
SELECT TIMESTAMPADD(WEEK, 2, '2023-12-31');
SELECT TIMESTAMPADD(MONTH, 13, '2022-05-15');
SELECT TIMESTAMPADD(QUARTER, 4, '2023-01-01');
SELECT TIMESTAMPADD(DAY, 0, '2023-10-23');
SELECT TIMESTAMPADD(DAY, 1, 20231023);
SELECT TIMESTAMPADD(MONTH, 1, '2020-02-29');
SELECT TIMESTAMPADD(MONTH, 12, '2023-10-31');
SELECT TO_SECONDS('2024-03-05XYZ');
SELECT BIN(18446744073709551616);
SELECT BIN('');
SELECT FORMAT(1000/3, 5);
SELECT WEEK('2023-01-01', NULL);
SELECT TIME_FORMAT('12:34:56', '');
SELECT OCT('');
SELECT LPAD('abc', 5, '');
SELECT LPAD('abc', 5, '');
SELECT FIELD('2023-10-01', DATE '2023-10-01');
SELECT QUOTE(0xDEADBEEF);
SELECT TO_BASE64(1E+30);
SELECT SUBTIME('2024-03-01', '24:00:00');
SELECT SUBTIME('2024-02-29', '1 12:00:00');
SELECT SUBTIME('9999-12-31 23:59:59.999999', '9999-12-31 23:59:59.999999');
SELECT SUBTIME('2024-02-29', '1 00:00:00');
SELECT FIND_IN_SET(' ', '  , , ,');
SELECT WEIGHT_STRING(123);
SELECT WEIGHT_STRING(TRUE);
SELECT WEIGHT_STRING('a' COLLATE utf8mb4_bin);
SELECT RPAD('abc', 10, '');
SELECT FORMAT_BYTES(0);
SELECT FORMAT_BYTES(1023.999999999);
SELECT FORMAT_BYTES(b'11111111');
SELECT FORMAT_BYTES('');
SELECT FORMAT_BYTES(CHAR(52));
SELECT FORMAT_BYTES(JSON_EXTRACT('{"x": 512}','$.x'));
SELECT is_ipv4_mapped(NULL);
SELECT IS_IPV4(NULL);
SELECT IS_IPV4(IF(1, NULL, '127.0.0.1'));
SELECT IS_UUID(' 6ccd780c-baba-1026-8567-4cc3505b2a62 ');
SELECT IS_IPV4_COMPAT(NULL);
SELECT IS_IPV4_COMPAT(X'00000000000000000000000000000000');
SELECT IS_IPV6(NULL + INTERVAL 1 DAY);
SELECT NAME_CONST('bool1', TRUE);
SELECT NAME_CONST('bool2', FALSE);
SELECT NAME_CONST('pi', PI());
SELECT UUID_TO_BIN(' 6ccd780c-baba-1026-9564-5b8c656024db ');
SELECT json_replace(123, '$', 456);
SELECT INET6_NTOA(1234);
SELECT INET6_NTOA('abcdefghijklmnop');
SELECT JSON_LENGTH(123);
SELECT JSON_LENGTH(123.45);
SELECT JSON_LENGTH(CAST('{"i": 0}' AS BINARY));
SELECT JSON_DEPTH(10);
SELECT JSON_DEPTH(TRUE);
SELECT JSON_INSERT(123, '$.a', 'test');
SELECT JSON_SEARCH('{"h": "i"}', 'all', 'i', '\\', NULL);
SELECT JSON_QUOTE(X'7B');
SELECT JSON_QUOTE(BINARY '\0\x1F');
SELECT JSON_QUOTE(1/0);
SELECT JSON_QUOTE(0xDEADBEEF);
SELECT JSON_ARRAY_APPEND('[1]', '$', JSON_ARRAY(2, 3));
SELECT JSON_SET('{}', '$[last]', 'end')
SELECT MINUTE('12:30:00+05:00')
SELECT FOUND_ROWS() - 3;
SELECT SUBDATE(_utf8mb4'2024-01-01', INTERVAL X'01' DAY);
SELECT TIMEDIFF(CAST('2024-05-01' AS DATE), '2024-05-01 12:30:45');
SELECT TIMEDIFF(CAST('2024-05-01' AS DATE), '2024-05-01 12:30:45');
SELECT TIMEDIFF(123456.789, 987654.321);
SELECT TIMEDIFF('12:34', '56:78');
SELECT TIMEDIFF('1.5:0:0', '3:0:0');
SELECT sec_to_time(3020399.999999)

Too many issues. I will continue to group them tomorrow 🤦 . Have a good sleep tonight.

[2010YOUY01]

This is cool. I'm curious how did you guide LLM to generate different queries?
like

for func_doc in all_functions_doc:
    LLM_call(func_doc + "please generate queries with edge cases for this function")

[YangKeao]

This is cool. I'm curious how did you guide LLM to generate different queries? like

for func_doc in all_functions_doc:
    LLM_call(func_doc + "please generate queries with edge cases for this function")

@2010YOUY01 You can see the prompt here