Bug #2496 COUNT(*) returning incorrect values with 2 func. comparison

• Submitted: 23 Jan 2004 12:07
• Modified: 23 Jan 2004 12:18
• Reporter: James Farley
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.17-standard-log
• OS: Linux (RedHat 7.3)
• Assigned to: Dean Ellis
• CPU Architecture: Any

[23 Jan 2004 12:07] James Farley

Description:
Doing a count(*) on a table using a WHERE clause with UPPER() and LOWER() is returning an incorrect value.  In the full example script below in "How to repeat" the server should return 6 for this query:

      select count(*) from foo where upper(a) <> lower(a);

but it always returns 0.  Only tested on InnoDB.

Thanks!
James

How to repeat:
(root@sagoqa) [3:01:49]> create table foo (a varchar(10)) Type=INNODB;
Query OK, 0 rows affected (0.00 sec)

(root@sagoqa) [3:02:1]> insert into foo (a) values ('a');
Query OK, 1 row affected (0.00 sec)

(root@sagoqa) [3:02:8]> insert into foo (a) values ('b');
Query OK, 1 row affected (0.00 sec)

(root@sagoqa) [3:02:10]> insert into foo (a) values ('c');
Query OK, 1 row affected (0.00 sec)

(root@sagoqa) [3:02:11]> select * from foo;
+------+
| a    |
+------+
| a    |
| b    |
| c    |
+------+
3 rows in set (0.00 sec)

(root@sagoqa) [3:02:15]> insert into foo (a) values ('A');
Query OK, 1 row affected (0.00 sec)

(root@sagoqa) [3:02:51]> insert into foo (a) values ('B');
Query OK, 1 row affected (0.00 sec)

(root@sagoqa) [3:02:53]> insert into foo (a) values ('C');
Query OK, 1 row affected (0.00 sec)

(root@sagoqa) [3:02:55]> select * from foo;
+------+
| a    |
+------+
| a    |
| b    |
| c    |
| A    |
| B    |
| C    |
+------+
6 rows in set (0.00 sec)

(root@sagoqa) [3:02:56]> select upper(a) from foo;
+----------+
| upper(a) |
+----------+
| A        |
| B        |
| C        |
| A        |
| B        |
| C        |
+----------+
6 rows in set (0.00 sec)

(root@sagoqa) [3:03:2]> select lower(a) from foo;
+----------+
| lower(a) |
+----------+
| a        |
| b        |
| c        |
| a        |
| b        |
| c        |
+----------+
6 rows in set (0.01 sec)

(root@sagoqa) [3:03:5]> select upper(a), lower(a) from foo;
+----------+----------+
| upper(a) | lower(a) |
+----------+----------+
| A        | a        |
| B        | b        |
| C        | c        |
| A        | a        |
| B        | b        |
| C        | c        |
+----------+----------+
6 rows in set (0.00 sec)

(root@sagoqa) [3:03:10]> select count(*) from foo where upper(a) <> lower(a);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

(root@sagoqa) [3:03:19]> select VERSION();
+---------------------+
| VERSION()           |
+---------------------+
| 4.0.17-standard-log |
+---------------------+
1 row in set (0.00 sec)

[23 Jan 2004 12:18] Dean Ellis

String comparisons are case insensitive by default, where possible.

SELECT 'A' = 'a';
SELECT BINARY 'A' = 'a';
SELECT UPPER('a') = LOWER('A');
SELECT BINARY UPPER('a') = LOWER('A');