Bug #98316 select result changes when order of selected columns changes
Submitted: 21 Jan 2020 18:33 Modified: 24 Jan 2020 4:40
Reporter: Zhou Ji Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S2 (Serious)
Version:2.2.9 OS:Any
Assigned to: CPU Architecture:Any

[21 Jan 2020 18:33] Zhou Ji
Description:
Execute a simple select, not all the rows are returned. It became obviously a bug when everything else is the same but the order of some columns is changed.

How to repeat:
Use the simple code attached,
"SELECT kana, kanji FROM tbl1" (correctly) returns all 21 rows;
"SELECT kanji, kana FROM tbl1" returns all 13 rows.

It does not happen for any databases, so the test database called 'bug_test' is attached as well. It has been simplified to include only 3 columns and 21 rows.
[21 Jan 2020 18:36] Zhou Ji
test database to reproduce the bug

Attachment: bug_test.sql (application/octet-stream, text), 2.41 KiB.

[21 Jan 2020 18:37] Zhou Ji
python code to reproduce the bug

Attachment: buggy.py (application/octet-stream, text), 538 bytes.

[22 Jan 2020 6:23] MySQL Verification Team
Hello Zhou Ji,

Thank you for the report and test case.
Observed that issue is not seen with latest MySQL Connector/Python Release i.e 8.0.19 but seen only on 2.2.9(pip install mysql-connector installed version 2.2.9) and I could be wrong but don't recall whether we have any GA release with that version. In any case please note that MySQL Connector/Python 8.0 is highly recommended for use with MySQL Server 8.0, 5.7, 5.6, and 5.5. I suggest you to please upgrade to MySQL Connector/Python 8.0. 

--
--
Python version: 3.7.5

Driver: mysql.connector 8.0.19

 Query: SELECT kana, kanji FROM tbl1

[('ああ', ''), ('あう', '会う'), ('あお', '青'), ('あおい', '青い'), ('あか', '赤'), ('あかい', '赤い'), ('あかるい', ' 明るい'), ('あき', '秋'), ('あく', '開く'), ('あける', '開ける'), ('あげる', '上げる'), ('あさ', '朝'), ('あさごはん', '朝御飯'), ('あさって', ''), ('あし', '足'), ('あした', ''), ('あそこ', ''), ('あそぶ', '遊ぶ'), ('あたたかい', '暖かい'), ('あたま', '頭'), ('あたらしい', '新しい')] 21
Press any key to continue . . .

Python version: 3.7.5

Driver: mysql.connector 8.0.19

 Query: SELECT kanji, kana FROM tbl1

[('', 'ああ'), ('会う', 'あう'), ('青', 'あお'), ('青い', 'あおい'), ('赤', 'あか'), ('赤い', 'あかい'), ('明るい', 'あ かるい'), ('秋', 'あき'), ('開く', 'あく'), ('開ける', 'あける'), ('上げる', 'あげる'), ('朝', 'あさ'), ('朝御飯', 'あさごはん'), ('', 'あさって'), ('足', 'あし'), ('', 'あした'), ('', 'あそこ'), ('遊ぶ', 'あそぶ'), ('暖かい', 'あたたかい'), ('頭', 'あたま'), ('新しい', 'あたらしい')] 21
Press any key to continue . . .

##
Python version: 2.7.16

Driver: mysql.connector 8.0.19

 Query: SELECT kanji, kana FROM tbl1

([('', u'\u3042\u3042'), (u'\u4f1a\u3046', u'\u3042\u3046'), (u'\u9752', u'\u3042\u304a'), (u'\u9752\u3044', u'\u3042\u304a\u3044'), (u'\u8d64', u'\u3042\u304b'), (u'\u8d64\u3044', u'\u3042\u304b\u3044'), (u'\u660e\u308b\u3044', u'\u3042\u304b\u308b\u3044'), (u'\u79cb', u'\u3042\u304d'), (u'\u958b\u304f', u'\u3042\u304f'), (u'\u958b\u3051\u308b', u'\u3042\u3051\u308b'), (u'\u4e0a\u3052\u308b', u'\u3042\u3052\u308b'), (u'\u671d', u'\u3042\u3055'), (u'\u671d\u5fa1\u98ef', u'\u3042\u3055\u3054\u306f\u3093'), ('', u'\u3042\u3055\u3063\u3066'), (u'\u8db3', u'\u3042\u3057'), ('', u'\u3042\u3057\u305f'), ('', u'\u3042\u305d\u3053'), (u'\u904a\u3076', u'\u3042\u305d\u3076'), (u'\u6696\u304b\u3044', u'\u3042\u305f\u305f\u304b\u3044'), (u'\u982d', u'\u3042\u305f\u307e'), (u'\u65b0\u3057\u3044', u'\u3042\u305f\u3089\u3057\u3044')], 21)
Press any key to continue . . .

Python version: 2.7.16

Driver: mysql.connector 8.0.19

 Query: SELECT kana, kanji FROM tbl1

([(u'\u3042\u3042', ''), (u'\u3042\u3046', u'\u4f1a\u3046'), (u'\u3042\u304a', u'\u9752'), (u'\u3042\u304a\u3044', u'\u9752\u3044'), (u'\u3042\u304b', u'\u8d64'), (u'\u3042\u304b\u3044', u'\u8d64\u3044'), (u'\u3042\u304b\u308b\u3044', u'\u660e\u308b\u3044'), (u'\u3042\u304d', u'\u79cb'), (u'\u3042\u304f', u'\u958b\u304f'), (u'\u3042\u3051\u308b', u'\u958b\u3051\u308b'), (u'\u3042\u3052\u308b', u'\u4e0a\u3052\u308b'), (u'\u3042\u3055', u'\u671d'), (u'\u3042\u3055\u3054\u306f\u3093', u'\u671d\u5fa1\u98ef'), (u'\u3042\u3055\u3063\u3066', ''), (u'\u3042\u3057', u'\u8db3'), (u'\u3042\u3057\u305f', ''), (u'\u3042\u305d\u3053', ''), (u'\u3042\u305d\u3076', u'\u904a\u3076'), (u'\u3042\u305f\u305f\u304b\u3044', u'\u6696\u304b\u3044'), (u'\u3042\u305f\u307e', u'\u982d'), (u'\u3042\u305f\u3089\u3057\u3044', u'\u65b0\u3057\u3044')], 21)
Press any key to continue . . .

--
Python version: 3.7.5

Driver: mysql.connector 2.2.9

 Query: SELECT kanji, kana FROM tbl1

[('', 'ああ'), ('会う', 'あう'), ('青', 'あお'), ('青い', 'あおい'), ('赤', 'あか'), ('赤い', 'あかい'), ('明るい', 'あ かるい'), ('秋', 'あき'), ('開く', 'あく'), ('開ける', 'あける'), ('上げる', 'あげる'), ('朝', 'あさ'), ('朝御飯', 'あさごはん')] 13
Press any key to continue . . .

Python version: 3.7.5

Driver: mysql.connector 2.2.9

 Query: SELECT kana,kanji FROM tbl1

[('ああ', ''), ('あう', '会う'), ('あお', '青'), ('あおい', '青い'), ('あか', '赤'), ('あかい', '赤い'), ('あかるい', ' 明るい'), ('あき', '秋'), ('あく', '開く'), ('あける', '開ける'), ('あげる', '上げる'), ('あさ', '朝'), ('あさごはん', '朝御飯'), ('あさって', ''), ('あし', '足'), ('あした', ''), ('あそこ', ''), ('あそぶ', '遊ぶ'), ('あたたかい', '暖かい'), ('あたま', '頭'), ('あたらしい', '新しい')] 21
Press any key to continue . . .

regards,
Umesh
[24 Jan 2020 4:40] MySQL Verification Team
Hello Zhou Ji,

Per internal discussion with Conn/Python lead, confirmed that we aren't the maintainer of https://pypi.org/project/mysql-connector/, we
took over for redirecting to the correct package. Our latest 2.2.x release
was v2.2.3. The package supported by us in PyPi is
https://pypi.org/project/mysql-connector-python/

Also, as demonstrated in previous post, we weren't able to reproduce the bug in our latest release (8.0.19). So, we suggest you to use our official package.

Closing the bug report for now but if are able to reproduce using latest/GA available official Connector/Python build then please report us back along with exact test case and change the status back to 'Open'.  

Thank you for your interest in MySQL.

regards,
Umesh