Some built-in funcs that are not consistent with MySQL when meet specific parameter types

[XuHuaiyu]

The results of sql list in the .txt file are not totally consistent with MySQL.
sql.txt

CREATE TABLE `table5_utf8_bin_int_autoinc` (
`col_decimal_key` decimal,
`col_tinyint_key` tinyint,
`col_decimal_10_5_key` decimal(10,5),
`col_float_key` float,
`col_int_key` int,
`col_time_key` time,
`col_char_20_utf8_key` char (20)  CHARACTER SET utf8,
`col_bit_20_key` bit(20),
`col_bit_1_key` bit(1),
`col_datetime_6_key` datetime(6),
pk integer auto_increment,
`col_set_utf8_key` set ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z')  CHARACTER SET utf8,
`col_int_unsigned_key` int unsigned,
`col_bool_key` bool,
`col_bigint_key` bigint,
`col_double_key` double,
`col_time_6_key` time(6),
`col_datetime_key` datetime,
`col_char_utf8_key` char (1)  CHARACTER SET utf8,
key (`col_decimal_key` ),
key (`col_tinyint_key` ),
key (`col_decimal_10_5_key` ),
key (`col_float_key` ),
key (`col_int_key` ),
key (`col_time_key` ),
key (`col_char_20_utf8_key` ),
key (`col_bit_20_key` ),
key (`col_bit_1_key` ),
key (`col_datetime_6_key` ),
primary key (pk),
key (`col_set_utf8_key` ),
key (`col_int_unsigned_key` ),
key (`col_bool_key` ),
key (`col_bigint_key` ),
key (`col_double_key` ),
key (`col_time_6_key` ),
key (`col_datetime_key` ),
key (`col_char_utf8_key` )) COLLATE utf8_bin;

INSERT INTO table5_utf8_bin_int_autoinc VALUES  
(0, 7, 0, 9, 0, '2002-05-04 04:45:22.038134', 'ksjijcszxwbjjvvk', 9, b'0', '2002-01-02', NULL, 'a', 564199424, 1, -8385421031187152896, 3, NULL, '20091010084918.041376', 'j'),
(0, 7, 0, 5, 1040384000, '2002-10-09 01:01:01', 'ij', b'00001010111100010011', b'0', '2006-01-21', NULL, 'b', 7, 1, 455145037341130752, 0, '34:52:56.34561', '20080412170352.006463', 'c'),
(0, 0, 0, 0, 611450880, '2002-01-02 01:02:03', 'szxwbjjv', b'011101101110', b'1', '20020908143544.005275', NULL, 'z', 4, 0, 4, 0, '12:51:42.058226', '2004-02-01 04:24:24', 'x'),
(0, 7, 8, 0, 8, '04:05:05.1234', 'wbjjvvkymalukqu', b'001000111110000', b'1', '20000103103821.033692', NULL, 'k', 3674669056, 0, 6463791365183504384, 0, '2007-08-08 04:28:13.058818', '2008-01-28 16:47:00.029273', 'j'),
(0, 66, 0, 0, -1467219968, '05:58:54.016602', 'jvvkym', b'000001010000111', b'0', '2003-01-02', NULL, 'v', 3903193088, 1, 3, 8, '2006-10-27 05:05:38.051899', '2005-12-12 12:12:12', 'v') ;

[XuHuaiyu]

other cases:

SELECT ASCII(`col_bit_1_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT ASCII(`col_set_utf8_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Char_Length(`col_bit_1_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Char_Length(`col_set_utf8_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Character_Length(`col_bit_1_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Character_Length(`col_set_utf8_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Day(`col_bool_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Day(`col_datetime_6_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Day(`col_datetime_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Day(`col_decimal_10_5_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Day(`col_decimal_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Day(`col_double_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Day(`col_float_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Day(`col_int_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Day(`col_tinyint_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Exp(`col_bit_1_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT From_Base64(`col_set_utf8_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT IsNull(`col_bit_1_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT IsNull(`col_set_utf8_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT LTrim(`col_bit_1_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT LTrim(`col_set_utf8_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT LTrim(`col_time_6_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT LTrim(`col_time_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Length(`col_bit_1_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Length(`col_set_utf8_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Oct(`col_bit_1_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Ord(`col_bit_1_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Ord(`col_set_utf8_key`) FROM `table5_utf8_bin_int_autoinc`
SELECT Random_Bytes(`pk`) FROM `table5_utf8_bin_int_autoinc`
SELECT Unhex(`col_set_utf8_key`) FROM `table5_utf8_bin_int_autoinc`

[shizy818]

@XuHuaiyu When I try the first one SELECT ASCII(col_bit_1_key) FROM table5_utf8_bin_int_autoinc``:
TiDB (mocktikv) is: 0 0 1 1 0
MySQL(5.7.20) is: 0 0 0 1 1
so the inconsistence is the order?

[XuHuaiyu]

@colinback Thank you for your feedback.
The order problem can be ignored.
You may use sql like SELECT ASCII(col_bit_1_key) FROM table5_utf8_bin_int_autoinc order by col_bit_1_key; next time to confirm whether it's the order problem.

[shizy818]

@XuHuaiyu I mean if the order is ignored, SELECT ASCII statement returns same result for MySQL and TiDB on my machine. So only part of the above list makes inconsistence?

[XuHuaiyu]

@colinback Yes

[XuHuaiyu]

@colinback I'll try to delete the order inconsistent sqls, and re-upload sql.txt,

[shizy818]

@XuHuaiyu Sorry I do not notice your last response. I can check your re-uploaded sql.txt this weekend.

[shizy818]

I've started work on this since last weekend. First query in problem is SELECT AddTime(col_time_6_key, col_bit_1_key) FROM table5_utf8_bin_int_autoinc.

MySQL result:

Tidb result:

Quit a lot of PRs will be created for this issue.

[jackysp]

PTAL @XuHuaiyu

[zz-jason]

@colinback Yes, It's lot of work to do, and we really appreciate for your help !

[shizy818]

@zz-jason now I can make AddTime(col_time_6_key, col_bit_1_key) FROM table5_utf8_bin_int_autoinc work. But select addtime("2017-01-01 01:01:01", b'1') or select addtime("2017-01-01 01:01:01", 0b1) is still wrong. After I finish it, I will sumbit PR to request XuHuaiyu review.

I'm going to post the difference in the sql.txt.

[shizy818]

@zz-jason @XuHuaiyu I tried to get the a difference list not considering the order using mysql-connector-python. Here is the first part. diff.txt

Then I have utf8 problem when execute fetchall() for this query: SELECT Concat(col_bit_1_key, col_char_utf8_key, col_bit_20_key) FROM table5_utf8_bin_int_autoinc

Traceback (most recent call last):
  File "mysql_test_python2.py", line 34, in <module>
    rows2 = cursorTidb.fetchall()
  File "/home/shi/.local/lib/python2.7/site-packages/mysql/connector/cursor_cext.py", line 489, in fetchall
    rows = self._cnx.get_rows()
  File "/home/shi/.local/lib/python2.7/site-packages/mysql/connector/connection_cext.py", line 281, in get_rows
    row = self._cmysql.fetch_row()
  File "/usr/lib/python2.7/encodings/utf_8.py", line 16, in decode
    return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: 'utf8' codec can't decode byte 0xaf in position 3: invalid start byte

I get stuck here and do you have any suggestion?

BTW, @XuHuaiyu I updated PR #6316 one week ago. Would you help to review again? Thank you!