MySQL Bug LIMIT in JOIN-Query change values in records
===

in the following scenario:

    CREATE TABLE `table1` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `text1` varchar(29) NOT NULL,
      `flag` bit(1) DEFAULT NULL,
      `reference` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE ,
      UNIQUE KEY `idx_text` (`text1`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    
    INSERT INTO `table1` (id, text1, flag, reference) VALUES
    (31486, 'YWXH-D6N4-XXH6', 0, NULL), 
    (31487, 'CBH0-UJBC-MFTO', 0, NULL), 
    (31488, 'FRQM-E6MW-6VFE', 1, 1657), 
    (31489, 'LZOS-EYDT-1BBF', 0, NULL), 
    (31490, 'D1XQ-YKAX-XQRC', 0, NULL);
    
    CREATE TABLE `table2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `value1` int(11) NOT NULL,
      `value2` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `value1` (`value1`),
      KEY `value2` (`value2`)
    ) ENGINE=MyISAM AUTO_INCREMENT=20068 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
    
    INSERT INTO table2 (id, value1, value2) VALUES
    (1657, 1891, 1748);

    -- the tables are shortened from "real" tables, i used SHOW CREATE <table> to create this script.

are the results of the following queries different.
here mysql returns for the record with id 31488 and 31490 the wrong value for the bit-field:

Query 1:

    SELECT m.id, m.text1, m.flag, m.reference 
    FROM table1 AS m LEFT JOIN table2 AS v ON v.id = m.reference 
    GROUP BY m.text1 ORDER BY m.text1 DESC LIMIT 0, 5;

returns the correct result:

    id    | text1          | flag | reference
    31487 | CBH0-UJBC-MFTO | 0    | NULL
    31490 | D1XQ-YKAX-XQRC | 0    | NULL
    31488 | FRQM-E6MW-6VFE | 1    | 1657
    31489 | LZOS-EYDT-1BBF | 0    | NULL
    31486 | YWXH-D6N4-XXH6 | 0    | NULL

while Query 2 

    SELECT m.id, m.text1, m.flag, m.reference 
    FROM table1 AS m LEFT JOIN table2 AS v ON v.id = m.reference 
    GROUP BY m.text1 ORDER BY m.text1 DESC LIMIT 0, 4;
returns this:

    id    | text1          | flag | reference
    31487 | CBH0-UJBC-MFTO | 0    | NULL
    31490 | D1XQ-YKAX-XQRC | 1    | NULL
    31488 | FRQM-E6MW-6VFE | 0    | 1657
    31489 | LZOS-EYDT-1BBF | 0    | NULL


so here is my question:

Im using Joomla CMS, and in the code of the component i can change the whole query except the LIMIT-part.
Joomla add the limit part to the query because of the pagination.

Is there a way to change the query that it works with the LIMIT-command?


oh, my MySQL-Version on Server is 5.1.61 (but this bug still exists on my client v5.5.16)