Re: LIKE problem?

[delta]

select count(*) from user where username like 'a%';
select count(*) from user where username not like 'a%' or username is null;

is not the same for all letters of the alphabet:

letter like not-like sum

n   2304 59317 61621
o  0 60797 60797
p   3048 58573 61621


Sounds like a corrupt index.  Try CHECK TABLE and REPAIR TABLE.


Tried that, tried myisamchk as well, everything seems to be healthy, still 
the problem exists.


Zoltan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

LIKE problem?

[delta]

Recently I ran into a problem with 'LIKE' in mysql on Debian Sarge:

mysql select VERSION();
+---+
| VERSION() |
+---+
| 4.1.11-Debian_4sarge2-log |
+---+
1 row in set (0.00 sec)

with the following table:

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(64) collate latin2_hungarian_ci default NULL,
  ...
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci

I get the following outputs:

mysql select count(*) from user where username like 'o%';
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.00 sec)

mysql select count(*) from user where username like 'or%';
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.00 sec)

mysql select count(*) from user where username like 'ors%';
+--+
| count(*) |
+--+
|   89 |
+--+
1 row in set (0.00 sec)

So the number of usernames which match like 'o%' is zero, while the 
number of matching lines for like 'ors%' is 89.


Moreover, the sum of the results of these two queries

select count(*) from user where username like 'a%';
select count(*) from user where username not like 'a%' or username is 
null;


is not the same for all letters of the alphabet:

letter like not-like sum

n   2304 59317 61621
o  0 60797 60797
p   3048 58573 61621

Any ideas?

Zoltan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]