Bug #92969 Unable to query non-English chars - ONLY via Connector.

• Submitted: 26 Oct 2018 14:19
• Modified: 28 Oct 2018 18:57
• Reporter: Itamar Shoham
• Status: Not a Bug
• Category: MySQL Visual Studio Integration
• Severity: S2 (Serious)
• Version: 8.0.13
• OS: Microsoft Windows (10)
• CPU Architecture: x86
• Tags: Unicode

[26 Oct 2018 14:19] Itamar Shoham

Description:
When querying a column contains non-English chars, the Connector completely ignores the rows.

for example:
1. 
SELECT	*
FROM 		test_table
WHERE    NON_ENG_WORD ='Привет';

2.
SELECT	*
FROM 		test_table
WHERE    ENG_WORD = 'Hello';

both queries returns the same row when using a 3rd party client (HeidiSQL).
but, when using via Visual Studio integration - only the second query returns the row, while the first query returns 0 rows!

How to repeat:
create a table:
CREATE TABLE `test_table` (
	`ID` INT(11) NOT NULL AUTO_INCREMENT,
	`NON_ENG_WORD` VARCHAR(50) NOT NULL COLLATE 'utf8_bin',
	`ENG_WORD` VARCHAR(50) NOT NULL COLLATE 'utf8_bin',
	PRIMARY KEY (`ID`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
AUTO_INCREMENT=12
;

add few rows:
INSERT INTO `test_table` (`NON_ENG_WORD`, `ENG_WORD`) VALUES ('低', 'let droop,hang down,lower');
INSERT INTO `test_table` (`NON_ENG_WORD`, `ENG_WORD`) VALUES ('دان', 'Dan');
INSERT INTO `test_table` (`NON_ENG_WORD`, `ENG_WORD`) VALUES ('رسم', 'draw');
INSERT INTO `test_table` (`NON_ENG_WORD`, `ENG_WORD`) VALUES ('الطاقة', 'energy');
INSERT INTO `test_table` (`NON_ENG_WORD`, `ENG_WORD`) VALUES ('Dépayser', 'leave your comfort zone');
INSERT INTO `test_table` (`NON_ENG_WORD`, `ENG_WORD`) VALUES ('Привет', 'Hello');
INSERT INTO `test_table` (`NON_ENG_WORD`, `ENG_WORD`) VALUES ('Радость', 'Happiness');
INSERT INTO `test_table` (`NON_ENG_WORD`, `ENG_WORD`) VALUES ('Улыбаться', 'Smile');
INSERT INTO `test_table` (`NON_ENG_WORD`, `ENG_WORD`) VALUES ('推出', 'present to public');
INSERT INTO `test_table` (`NON_ENG_WORD`, `ENG_WORD`) VALUES ('გამარჯობა', 'Hello');
INSERT INTO `test_table` (`NON_ENG_WORD`, `ENG_WORD`) VALUES ('ნახვამდის', 'Bye');

I've tested it on MySQL 5.7.23

[26 Oct 2018 16:33] Miguel Solorzano

Thank you for the bug report. Please provide the test code file (attach here using the Files tab). Thanks.

[27 Oct 2018 17:58] Itamar Shoham

Since no code is needed to reproduce the bug, I'll describe the steps in greater detail:

1. use 3rd party to connect to the db. (I used HeidiSQL - https://www.heidisql.com/)
2. create test table, add test rows:

-- create the test table
CREATE TABLE `test_table` (
	`ID` INT(11) NOT NULL AUTO_INCREMENT,
	`NON_ENG_WORD` VARCHAR(50) NOT NULL COLLATE 'utf8_bin',
	`ENG_WORD` VARCHAR(50) NOT NULL COLLATE 'utf8_bin',
	PRIMARY KEY (`ID`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
AUTO_INCREMENT=12;

-- insert test row. 
INSERT INTO `test_table` (`NON_ENG_WORD`, `ENG_WORD`) VALUES ('Привет', 'Hello');

3. Open Visual studio 2017
4. menu -> View -> toolbars -> MySQL
5. on the new toolbar, click on "MySQL script with new connection"
6. provide connection details
7. on the new query window, run the following commands:

-- this query will return 1 row. 
SELECT	*
FROM 		test_table
WHERE    ENG_WORD = 'Hello';

-- this query will return 0 rows! and this is WRONG.
SELECT	*
FROM 		test_table
WHERE    NON_ENG_WORD = 'Привет';

[27 Oct 2018 20:11] Itamar Shoham

Tested with Development Release Version 2.0.5 m4, and had the exact same issue.

please help.

[28 Oct 2018 18:05] Miguel Solorzano

Thank you for the bug report. What you wrote as workaround isn't a workaround it's the correct way to handle handle non-English characters which is to use
the correct character set and collation, so !bug.