MySQL Bugs: #85463: varchar(64) varchar(255) lead to bug

Bug #85463	varchar(64) varchar(255) lead to bug
Submitted:	15 Mar 2017 11:29	Modified:	16 Mar 2017 1:27
Reporter:	mi ke	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.7	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	count(disticnt), type varchar(255) varchar(64)

Description:

when i create table  use varchar(255),i have two different results;

#way_1
select count(distinct uid)
from 
(SELECT distinct  uid FROM temp) as a;

different 

##way_2

SELECT count(distinct uid) FROM temp;

however , when  i create table use varchar(255),the way_1 is equal to way_2;

so magic

How to repeat:
mysql> drop table if exists temp;
create table temp
(uid varchar(255) null);
###load file
LOAD DATA LOCAL INFILE  'C:\\Users\\Acer\\Desktop\\uid_utf8.csv'
INTO TABLE temp  
CHARACTER SET utf8
fields terminated by ',' optionally enclosed by '"' ESCAPED BY '"'
lines terminated by '\n'  ignore 1 lines;
Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 167778 rows affected
Records: 167778  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select count(distinct uid)
from 
(SELECT distinct  uid FROM temp) as a;
+---------------------+
| count(distinct uid) |
+---------------------+
|               35758 |
+---------------------+
1 row in set

mysql> SELECT count(distinct uid) FROM temp;
+---------------------+
| count(distinct uid) |
+---------------------+
|               45224 |
+---------------------+
1 row in set

Suggested fix:
i have laod file into baidu_server,you can download;

downloads:

https://pan.baidu.com/s/1c2xM0zI;

by the way:

when i create table use varcahr(64) ,the results i equal; but when i use varchar(255),i have two results;

i have to use varchar(255);

however , when  i create table use varchar(64),the way_1 is equal to way_2;

Thank you for the bug report. I couldn't repeat with recent source server:

c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --local-infile test  --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.19 Source distribution PULL: 2017-MAR-07

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > LOAD DATA LOCAL INFILE  'C:\\tmp\\uid_utf8.csv'
    -> INTO TABLE temp
    -> CHARACTER SET utf8
    -> fields terminated by ',' optionally enclosed by '"' ESCAPED BY '"'
    -> lines terminated by '\n'  ignore 1 lines;
Query OK, 167778 rows affected (3.82 sec)
Records: 167778  Deleted: 0  Skipped: 0  Warnings: 0

mysql 5.7 > select count(distinct uid)
    -> from
    -> (SELECT distinct  uid FROM temp) as a;
+---------------------+
| count(distinct uid) |
+---------------------+
|               35758 |
+---------------------+
1 row in set (0.22 sec)

mysql 5.7 > SELECT count(distinct uid) FROM temp;
+---------------------+
| count(distinct uid) |
+---------------------+
|               35758 |
+---------------------+
1 row in set (0.09 sec)

mysql 5.7 > show create table temp;
+-------+------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                   |
+-------+------------------------------------------------------------------------------------------------+
| temp  | CREATE TABLE `temp` (
  `uid` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

hello,

when i use ENGINE=InnoDB DEFAULT CHARSET=latin1  , i have a equal results;

however,when i user ENGINE=InnoDB DEFAULT CHARSET=utf8;, i have two differernt results;

can you try again to  user ENGINE=InnoDB DEFAULT CHARSET=utf8 ?

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set

Thank you for the feedback:

mysql 5.7 > SHOW GLOBAL  VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | c:\dbs\5.7\share\charsets\ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql 5.7 > create table temp (uid varchar(255) null)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.20 sec)

mysql 5.7 > show create table temp;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                 |
+-------+----------------------------------------------------------------------------------------------+
| temp  | CREATE TABLE `temp` (
  `uid` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.7 > LOAD DATA LOCAL INFILE  'C:\\tmp\\uid_utf8.csv'
    -> INTO TABLE temp
    -> CHARACTER SET utf8
    -> fields terminated by ',' optionally enclosed by '"' ESCAPED BY '"'
    -> lines terminated by '\n'  ignore 1 lines;
Query OK, 167778 rows affected (2.18 sec)
Records: 167778  Deleted: 0  Skipped: 0  Warnings: 0

mysql 5.7 > select count(distinct uid)
    -> from
    -> (SELECT distinct  uid FROM temp) as a;
+---------------------+
| count(distinct uid) |
+---------------------+
|               35758 |
+---------------------+
1 row in set (0.48 sec)

mysql 5.7 > SELECT count(distinct uid) FROM temp;
+---------------------+
| count(distinct uid) |
+---------------------+
|               35758 |
+---------------------+
1 row in set (0.20 sec)

mysql 5.7 > SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------------------------+
| Variable_name           | Value                                 |
+-------------------------+---------------------------------------+
| innodb_version          | 5.7.19                                |
| protocol_version        | 10                                    |
| slave_type_conversions  |                                       |
| tls_version             | TLSv1,TLSv1.1                         |
| version                 | 5.7.19                                |
| version_comment         | Source distribution PULL: 2017-MAR-07 |
| version_compile_machine | x86_64                                |
| version_compile_os      | Win64                                 |
+-------------------------+---------------------------------------+
8 rows in set (0.00 sec)

mysql 5.7 >