Bug #24352 "=" operator failed rather than "LIKE" operator in GROUP BY statement
Submitted: 16 Nov 2006 6:11 Modified: 20 Nov 2007 9:12
Reporter: Rafinal Rafik Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.22, 4.1.9-nt-max-log OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any
Tags: "=", "LIKE, GROUP BY, operator

[16 Nov 2006 6:11] Rafinal Rafik
Description:
I need this query to simply get a records count of any sql from my php function 

(SELECT @count:=0) 
UNION 
(SELECT @count:=@count+1 
FROM pustaka 
LEFT JOIN penulis_pustaka pp ON (pustaka.kd_grup_pustaka=pp.kd_grup_pustaka) 
LEFT JOIN penulis ON (pp.kd_penulis=penulis.kd_penulis) 
LEFT JOIN penerbit ON (pustaka.kd_penerbit=penerbit.kd_penerbit) 
WHERE owner = 1 
GROUP BY kd_pustaka 
ORDER BY 1 DESC LIMIT 1) 

ORDER BY 1 DESC; 

I expected MySQL to return 1839 but it returned 2404. 
But when I change 
WHERE owner = 1 
to ... 
WHERE owner LIKE 1 
it returned 1839.

It looks like a GROUP BY clause issue but how come that "LIKE" operator works fine while "=" is failed? 

Is that a bug? 

I sent u my database (about 400kb). 

Thanks in advance. 

regards, 

RafRaf

How to repeat:
load my database
run above query
run this query
SELECT COUNT(*) FROM pustaka;
both query should return 1839
[16 Nov 2006 6:17] Rafinal Rafik
my database

Attachment: database.zip (application/x-zip-compressed, text), 4.37 KiB.

[24 Nov 2006 14:58] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 4.1.21, and inform about the results.
[13 Dec 2006 10:20] Rafinal Rafik
I installed and run server version: 4.1.22-community-max-nt on the other computer and I experienced the same bug.
[14 Dec 2006 20:31] Valeriy Kravchuk
Your attachment, http://bugs.mysql.com/file.php?id=4914, does not contain data, only CREATE statements. So your first query returns:

mysql> (SELECT @count:=0)
    -> UNION
    -> (SELECT @count:=@count+1
    -> FROM pustaka
    -> LEFT JOIN penulis_pustaka pp ON (pustaka.kd_grup_pustaka=pp.kd_grup_pust
aka)
    -> LEFT JOIN penulis ON (pp.kd_penulis=penulis.kd_penulis)
    -> LEFT JOIN penerbit ON (pustaka.kd_penerbit=penerbit.kd_penerbit)
    -> WHERE owner = 1
    -> GROUP BY kd_pustaka
    -> ORDER BY 1 DESC LIMIT 1)
    ->
    -> ORDER BY 1 DESC;
+-----------+
| @count:=0 |
+-----------+
|         0 |
+-----------+
1 row in set (0.02 sec)

Please, upload dump with data (to the bug report, or, if it is > 200K in size even compressed, to our ftp server, with 24352 in a file name. Inform when you'll do it.
[18 Dec 2006 5:26] Rafinal Rafik
Actually, my attachment was contain data. You can see (for example) this line:
INSERT INTO `pustaka` VALUES (1,NULL,NULL,1,'2005-12-02','','2001-01-01','second','os','Modern Operating .....
[28 Jan 2007 12:57] Valeriy Kravchuk
Yes, you do have data in your dump. But, anyway, I can not repeat the behaviour described. Look:

mysql> SELECT count(*)  FROM pustaka  LEFT JOIN penulis_pustaka pp ON (pustaka.
kd_grup_pustaka=pp.kd_grup_pustaka)  LEFT JOIN penulis ON (pp.kd_penulis=penuli
s.kd_penulis)  LEFT JOIN penerbit ON (pustaka.kd_penerbit=penerbit.kd_penerbit)
  WHERE owner = 1  GROUP BY kd_pustaka  ORDER BY 1 DESC LIMIT 1;
Empty set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.23    |
+-----------+
1 row in set (0.00 sec)

So, your original query gives me the result I already sent in my previous comment - nothing similar to yours.
[29 May 2007 9:10] Rafinal Rafik
Actually, your last checked query should not return an "Empty set". Maybe it was your configuration issue?

"SELECT count(*) FROM pustaka WHERE owner = 1"
should definitely returns rows.

Please do troubleshooting your self with the query. Start with replace "WHERE owner = 1" with "WHERE owner LIKE '1'". Also. please try to remove the joining part from the query one by one.

Now, I thought, I just get the similar behaviour with server on:
Linux Fedora Core
Mysql: 4.1.10a-log

with table:

CREATE TABLE biaya_bug (
  idBiaya int(10) unsigned NOT NULL auto_increment,
  niu int(10) unsigned default NULL,
  idJenisBiaya smallint(5) unsigned default NULL,
  jumlah int(10) unsigned default NULL,
  idPeriode int(10) unsigned default NULL,
  angsuran varchar(4) default NULL,
  idStatusBayar smallint(5) unsigned default NULL,
  tanggalBayar datetime default NULL,
  noKuitansi varchar(15) default NULL,
  idRekening int(10) unsigned default NULL,
  caraBayar tinyint(1) default NULL,
  userUbah int(10) unsigned default NULL,
  tanggalUbah datetime default NULL,
  noTest varchar(15) default NULL,
  tempNiu int(6) default '0',
  isSyarat tinyint(1) default '0',
  isAngsuran tinyint(1) default '0',
  PRIMARY KEY  (idBiaya),
  UNIQUE KEY notest_jenisbiaya_periode (noTest,idJenisBiaya,idPeriode),
  KEY biaya_FKIndex1 (idJenisBiaya),
  KEY biaya_FKIndex2 (noKuitansi),
  KEY biaya_FKIndex3 (idStatusBayar),
  KEY biaya_FKIndex4 (niu),
  KEY Rel_148 (userUbah),
  KEY noTest (noTest),
  KEY idRekening (idRekening),
  KEY idPeriode (idPeriode),
  KEY tanggalBayar (tanggalBayar)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO biaya_bug VALUES("1480414", NULL, "2", "2500000", "13", "1", NULL, NULL, NULL, "88", NULL, NULL, NULL, "07PB2SAK-373", "0", "1", "0");
INSERT INTO biaya_bug VALUES("1481575", NULL, "1", "500000", "13", NULL, "10", NULL, NULL, "88", NULL, "1", "2007-05-29 11:42:03", "07PB2SAK-373", "0", "1", "0");
INSERT INTO biaya_bug VALUES("1482011", NULL, "4", "2000000", "13", NULL, "10", NULL, NULL, "88", NULL, "1", "2007-05-29 11:44:07", "07PB2SAK-373", "0", "1", "0");
INSERT INTO biaya_bug VALUES("1482447", NULL, "18", "280000", "13", NULL, "10", NULL, NULL, "88", NULL, "1", "2007-05-29 11:47:35", "07PB2SAK-373", "0", "1", "0");

# this returned rows:
SELECT * FROM biaya_bug
WHERE notest LIKE '07PB%' AND notest = '07PB2SAK-373'
;

# while this can not return rows because i changed '07PB%' to '07pb%'
SELECT * FROM biaya_bug
WHERE notest LIKE '07pb%' AND notest = '07PB2SAK-373'
;

Thanks in advance
(sorry for not responding so long)
[29 May 2007 9:14] Rafinal Rafik
# while this was just fine:
SELECT * FROM biaya_bug
WHERE notest LIKE '07pb%'
;
[29 Sep 2007 11:54] Valeriy Kravchuk
Sorry, but still can not repeat:

openxs@linux:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.24

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE biaya_bug (
    ->   idBiaya int(10) unsigned NOT NULL auto_increment,
    ->   niu int(10) unsigned default NULL,
    ->   idJenisBiaya smallint(5) unsigned default NULL,
    ->   jumlah int(10) unsigned default NULL,
    ->   idPeriode int(10) unsigned default NULL,
    ->   angsuran varchar(4) default NULL,
    ->   idStatusBayar smallint(5) unsigned default NULL,
    ->   tanggalBayar datetime default NULL,
    ->   noKuitansi varchar(15) default NULL,
    ->   idRekening int(10) unsigned default NULL,
    ->   caraBayar tinyint(1) default NULL,
    ->   userUbah int(10) unsigned default NULL,
    ->   tanggalUbah datetime default NULL,
    ->   noTest varchar(15) default NULL,
    ->   tempNiu int(6) default '0',
    ->   isSyarat tinyint(1) default '0',
    ->   isAngsuran tinyint(1) default '0',
    ->   PRIMARY KEY  (idBiaya),
    ->   UNIQUE KEY notest_jenisbiaya_periode (noTest,idJenisBiaya,idPeriode),
    ->   KEY biaya_FKIndex1 (idJenisBiaya),
    ->   KEY biaya_FKIndex2 (noKuitansi),
    ->   KEY biaya_FKIndex3 (idStatusBayar),
    ->   KEY biaya_FKIndex4 (niu),
    ->   KEY Rel_148 (userUbah),
    ->   KEY noTest (noTest),
    ->   KEY idRekening (idRekening),
    ->   KEY idPeriode (idPeriode),
    ->   KEY tanggalBayar (tanggalBayar)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO biaya_bug VALUES("1480414", NULL, "2", "2500000", "13", "1",
 NULL, NULL, NULL,
    -> "88", NULL, NULL, NULL, "07PB2SAK-373", "0", "1", "0");
Query OK, 1 row affected (0.00 sec)

Imysql> INSERT INTO biaya_bug VALUES("1481575", NULL, "1", "500000", "13", NULL,

 "10", NULL, NULL,
    -> "88", NULL, "1", "2007-05-29 11:42:03", "07PB2SAK-373", "0", "1", "0");
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO biaya_bug VALUES("1482011", NULL, "4", "2000000", "13", NULL
, "10", NULL,
    -> NULL, "88", NULL, "1", "2007-05-29 11:44:07", "07PB2SAK-373", "0", "1",
"0");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO biaya_bug VALUES("1482447", NULL, "18", "280000", "13", NULL
, "10", NULL,
    -> NULL, "88", NULL, "1", "2007-05-29 11:47:35", "07PB2SAK-373", "0", "1",
"0");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM biaya_bug
    -> WHERE notest LIKE '07PB%' AND notest = '07PB2SAK-373'
    -> ;
+---------+------+--------------+---------+-----------+----------+--------------
-+--------------+------------+------------+-----------+----------+--------------
-------+--------------+---------+----------+------------+
| idBiaya | niu  | idJenisBiaya | jumlah  | idPeriode | angsuran | idStatusBayar
 | tanggalBayar | noKuitansi | idRekening | caraBayar | userUbah | tanggalUbah
       | noTest       | tempNiu | isSyarat | isAngsuran |
+---------+------+--------------+---------+-----------+----------+--------------
-+--------------+------------+------------+-----------+----------+--------------
-------+--------------+---------+----------+------------+
| 1481575 | NULL |            1 |  500000 |        13 | NULL     |            10
 | NULL         | NULL       |         88 |      NULL |        1 | 2007-05-29 11
:42:03 | 07PB2SAK-373 |       0 |        1 |          0 |
| 1480414 | NULL |            2 | 2500000 |        13 | 1        |          NULL
 | NULL         | NULL       |         88 |      NULL |     NULL | NULL
       | 07PB2SAK-373 |       0 |        1 |          0 |
| 1482011 | NULL |            4 | 2000000 |        13 | NULL     |            10
 | NULL         | NULL       |         88 |      NULL |        1 | 2007-05-29 11
:44:07 | 07PB2SAK-373 |       0 |        1 |          0 |
| 1482447 | NULL |           18 |  280000 |        13 | NULL     |            10
 | NULL         | NULL       |         88 |      NULL |        1 | 2007-05-29 11
:47:35 | 07PB2SAK-373 |       0 |        1 |          0 |
+---------+------+--------------+---------+-----------+----------+--------------
-+--------------+------------+------------+-----------+----------+--------------
-------+--------------+---------+----------+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM biaya_bug WHERE notest LIKE '07pb%' AND notest = '07PB2SAK
-373';
+---------+------+--------------+---------+-----------+----------+--------------
-+--------------+------------+------------+-----------+----------+--------------
-------+--------------+---------+----------+------------+
| idBiaya | niu  | idJenisBiaya | jumlah  | idPeriode | angsuran | idStatusBayar
 | tanggalBayar | noKuitansi | idRekening | caraBayar | userUbah | tanggalUbah
       | noTest       | tempNiu | isSyarat | isAngsuran |
+---------+------+--------------+---------+-----------+----------+--------------
-+--------------+------------+------------+-----------+----------+--------------
-------+--------------+---------+----------+------------+
| 1481575 | NULL |            1 |  500000 |        13 | NULL     |            10
 | NULL         | NULL       |         88 |      NULL |        1 | 2007-05-29 11
:42:03 | 07PB2SAK-373 |       0 |        1 |          0 |
| 1480414 | NULL |            2 | 2500000 |        13 | 1        |          NULL
 | NULL         | NULL       |         88 |      NULL |     NULL | NULL
       | 07PB2SAK-373 |       0 |        1 |          0 |
| 1482011 | NULL |            4 | 2000000 |        13 | NULL     |            10
 | NULL         | NULL       |         88 |      NULL |        1 | 2007-05-29 11
:44:07 | 07PB2SAK-373 |       0 |        1 |          0 |
| 1482447 | NULL |           18 |  280000 |        13 | NULL     |            10
 | NULL         | NULL       |         88 |      NULL |        1 | 2007-05-29 11
:47:35 | 07PB2SAK-373 |       0 |        1 |          0 |
+---------+------+--------------+---------+-----------+----------+--------------
-+--------------+------------+------------+-----------+----------+--------------
-------+--------------+---------+----------+------------+
4 rows in set (0.00 sec)

mysql> show variables like 'colla%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
[20 Nov 2007 9:12] Rafinal Rafik
I don't know how come u can't repeat both of my bug query results. Is there any configuration issue can cause these happened happening in my environment? Even I have been tried both of them on Window$ and linux too?