| Bug #1491 | no case sensitivity for binary columns | ||
|---|---|---|---|
| Submitted: | 5 Oct 2003 23:49 | Modified: | 7 Oct 2003 1:55 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.0-alpha | OS: | Linux (Linux) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[6 Oct 2003 3:46]
Alexander Barkov
Serge,
Please check what SHOW CREATE TABLE, SHOW FULL FIELDS return for you.
I can't reproduce it. I got this output looking correct:
mysql> create table test ( string varchar(255) binary not null, primary
-> key (string) );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values("hallo"), ("Hallo");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test where string = "Hallo";
+--------+
| string |
+--------+
| Hallo |
+--------+
1 row in set (0.02 sec)
mysql> select * from test where string = "hallo";
+--------+
| string |
+--------+
| hallo |
+--------+
1 row in set (0.00 sec)
[6 Oct 2003 3:58]
[ name withheld ]
Thanks for your fast reply here is the output of the commands: mysql> show create table test; +-------+----------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `string` varchar(255) binary NOT NULL default '', PRIMARY KEY (`string`) ) TYPE=MyISAM CHARSET=binary | +-------+----------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show full fields from test; +--------+---------------------+-----------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--------+---------------------+-----------+------+-----+---------+-------+---------------------------------+---------+ | string | varchar(255) binary | binary | | PRI | | | select,insert,update,references | | +--------+---------------------+-----------+------+-----+---------+-------+---------------------------------+---------+ 1 row in set (0.00 sec) > ./mysql --version ./mysql Ver 13.5 Distrib 4.1.0-alpha, for pc-linux (i686) Thorsten
[6 Oct 2003 4:23]
Alexander Barkov
This is probably 4.1.0 specific bug. Everything works fine in the latest 4.1.1. Do you have a chance to try with 4.1.1? It can be downloaded from BitKeeper: http://www.mysql.com/doc/en/Installing_source_tree.html
[6 Oct 2003 6:31]
[ name withheld ]
After compiling I have checked with 4.1.1 and there it works without problems. When will this version be released? Thorsten
[7 Oct 2003 1:55]
Alexander Barkov
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Good to know the newer version works fine for you too.
Concerning the date 4.1.1 is to be out: is not know yet :(
Description: I like to have case senitiv selections so I do as in the documentation under A.5.1 Case-Sensitivity in Searches "If you want a column always to be treated in case-sensitive fashion, declare it as BINARY. See section 6.5.3 CREATE TABLE Syntax. " but still I get the a string written in uppercase when I search for a string with a lower case. How to repeat: mysql> create table test ( string varchar(255) binary not null, primary key (string) ); Query OK, 0 rows affected (0.01 sec) mysql> insert into test values("hallo"), ("Hallo"); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test where string = "Hallo"; +--------+ | string | +--------+ | Hallo | +--------+ 1 row in set (0.00 sec) mysql> select * from test where string = "hallo"; +--------+ | string | +--------+ | Hallo | +--------+ 1 row in set (0.00 sec)