case sensistive bug

------_=_NextPart_001_01C2F266.B844A3C9
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I have mysql version 3.23.49.
=20
I have a table:
=20
mysql> describe test;
--------------
describe test
--------------
=20
+-----------+-----------+------+-----+---------+------------ ----+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+---------+------------ ----+
| int_id | int(11) | | PRI | NULL | auto_increment |
| string_id | char(255) | YES | | NULL | |
+-----------+-----------+------+-----+---------+------------ ----+
2 rows in set (0.00 sec)

it has the following values:
=20
mysql> select * from test;
--------------
select * from test
--------------
=20
+--------+------------------------------------------+
| int_id | string_id |
+--------+------------------------------------------+
| 1 | AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyoQwAAA== |
| 2 | AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyOQwAAA== |
+--------+------------------------------------------+
2 rows in set (0.00 sec)

If I perform the following search (using the value from the first row) :
=20
mysql> select * from test where string_id =3D
'AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyoQwAAA==';
--------------
select * from test where string_id =3D
'AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyoQwAAA=='
--------------
=20
+--------+------------------------------------------+
| int_id | string_id |
+--------+------------------------------------------+
| 1 | AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyoQwAAA== |
| 2 | AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyOQwAAA== |
+--------+------------------------------------------+
2 rows in set (0.00 sec)
=20
To verify:
=20
mysql> select * from test where string_id =3D
'aquaaaaaaaUVAAAAL9XsbZ6rnWFDFwoyoQwAAA==';
--------------
select * from test where string_id =3D
'aquaaaaaaaUVAAAAL9XsbZ6rnWFDFwoyoQwAAA=='
--------------
=20
+--------+------------------------------------------+
| int_id | string_id |
+--------+------------------------------------------+
| 1 | AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyoQwAAA== |
| 2 | AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyOQwAAA== |
+--------+------------------------------------------+
2 rows in set (0.00 sec)


This does not occur if you use STRCMP.
=20
Is this a bug ?
=20
What stable version is it fixed in ?

------_=_NextPart_001_01C2F266.B844A3C9--

Re: case sensistive bug

On Tue, 2003-03-25 at 03:37, Rohan Hawthorne wrote:
> I have mysql version 3.23.49.


Dear Rohan,

This is not a bug. By default MySQL uses case insensitive matching to
the strings so "A"="a";

If you need some fields to be treated as case sensitive
you shall define them with BINARY attribute:

mysql> create table cs(c char(5) binary);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into cs values("a"),("A");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from cs where c="a";
+------+
| c |
+------+
| a |
+------+
1 row in set (0.00 sec)



>
> I have a table:
>
> mysql> describe test;
> --------------
> describe test
> --------------
>
> +-----------+-----------+------+-----+---------+------------ ----+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+-----------+------+-----+---------+------------ ----+
> | int_id | int(11) | | PRI | NULL | auto_increment |
> | string_id | char(255) | YES | | NULL | |
> +-----------+-----------+------+-----+---------+------------ ----+
> 2 rows in set (0.00 sec)
>
> it has the following values:
>
> mysql> select * from test;
> --------------
> select * from test
> --------------
>
> +--------+------------------------------------------+
> | int_id | string_id |
> +--------+------------------------------------------+
> | 1 | AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyoQwAAA== |
> | 2 | AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyOQwAAA== |
> +--------+------------------------------------------+
> 2 rows in set (0.00 sec)
>
> If I perform the following search (using the value from the first row) :
>
> mysql> select * from test where string_id =
> 'AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyoQwAAA==';
> --------------
> select * from test where string_id =
> 'AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyoQwAAA=='
> --------------
>
> +--------+------------------------------------------+
> | int_id | string_id |
> +--------+------------------------------------------+
> | 1 | AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyoQwAAA== |
> | 2 | AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyOQwAAA== |
> +--------+------------------------------------------+
> 2 rows in set (0.00 sec)
>
> To verify:
>
> mysql> select * from test where string_id =
> 'aquaaaaaaaUVAAAAL9XsbZ6rnWFDFwoyoQwAAA==';
> --------------
> select * from test where string_id =
> 'aquaaaaaaaUVAAAAL9XsbZ6rnWFDFwoyoQwAAA=='
> --------------
>
> +--------+------------------------------------------+
> | int_id | string_id |
> +--------+------------------------------------------+
> | 1 | AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyoQwAAA== |
> | 2 | AQUAAAAAAAUVAAAAL9XsbZ6rnWFDFwoyOQwAAA== |
> +--------+------------------------------------------+
> 2 rows in set (0.00 sec)
>
>
> This does not occur if you use STRCMP.
>
> Is this a bug ?
>
> What stable version is it fixed in ?
--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org