Description:
in case, when rr_from_cache is used to read records, select with group by fails.
in 4.0 all works okay
How to repeat:
start mysqld with standard options.
create a big table without indices, for example with this C program:
--cut/*
build it with
cc -I /usr/include/mysql/ fill.c -L /usr/lib/mysql/ -lz -lmysqlclient
*/
#include <stdio.h>
#include <mysql.h>
#include <stdlib.h>
#include <unistd.h>
#include <time.h>
int main()
{
enum { ROW_COUNT = 10000000, GROUP_COUNT= 128 };
srand(time(0));
MYSQL *mysql= mysql_init(0);
if (!mysql)
{
printf("mysql_init(0) failed\n");
return 1;
}
if (mysql_real_connect(mysql, 0, 0, 0, "test", 0,
"/opt/local/var/mysql/mysql.sock", 0) == 0)
{
printf("mysql_real_connect failed\n");
return 1;
}
if (mysql_query(mysql, "DROP TABLE IF EXISTS distinct_test"))
{
printf("mysql_query(DROP TABLE) failed\n");
return 1;
}
if (mysql_query(mysql, "CREATE TABLE distinct_test ("
" id INTEGER, "
" grp TINYINT, "
" id_rev INTEGER)"
))
{
printf("mysql_query(CREATE TABLE) failed\n");
return 1;
}
const char *statement= "INSERT INTO distinct_test (id, grp, id_rev)"
" VALUES (%d, %d, %d)";
int i;
for (i= 0; i < ROW_COUNT; ++i)
{
int id= rand();
int id_rev= RAND_MAX-id;
int grp=(int) (1.0 * GROUP_COUNT*id/(RAND_MAX+1.0));
char buf[512];
sprintf(buf, statement, id, grp, id_rev);
if (mysql_query(mysql, buf))
{
printf("mysql_query(INSERT INTO) failed\n");
return 1;
}
}
mysql_close(mysql);
return 0;
}
--end cut
then run:
select count(distinct id) from distinct_test group by grp;
you'll get:
ERROR 1032: Can't find record in 'distinct_test'
Suggested fix:
compare what 4.0 and 4.1 sources - 4.0 seem not to have that bug
Description: in case, when rr_from_cache is used to read records, select with group by fails. in 4.0 all works okay How to repeat: start mysqld with standard options. create a big table without indices, for example with this C program: --cut/* build it with cc -I /usr/include/mysql/ fill.c -L /usr/lib/mysql/ -lz -lmysqlclient */ #include <stdio.h> #include <mysql.h> #include <stdlib.h> #include <unistd.h> #include <time.h> int main() { enum { ROW_COUNT = 10000000, GROUP_COUNT= 128 }; srand(time(0)); MYSQL *mysql= mysql_init(0); if (!mysql) { printf("mysql_init(0) failed\n"); return 1; } if (mysql_real_connect(mysql, 0, 0, 0, "test", 0, "/opt/local/var/mysql/mysql.sock", 0) == 0) { printf("mysql_real_connect failed\n"); return 1; } if (mysql_query(mysql, "DROP TABLE IF EXISTS distinct_test")) { printf("mysql_query(DROP TABLE) failed\n"); return 1; } if (mysql_query(mysql, "CREATE TABLE distinct_test (" " id INTEGER, " " grp TINYINT, " " id_rev INTEGER)" )) { printf("mysql_query(CREATE TABLE) failed\n"); return 1; } const char *statement= "INSERT INTO distinct_test (id, grp, id_rev)" " VALUES (%d, %d, %d)"; int i; for (i= 0; i < ROW_COUNT; ++i) { int id= rand(); int id_rev= RAND_MAX-id; int grp=(int) (1.0 * GROUP_COUNT*id/(RAND_MAX+1.0)); char buf[512]; sprintf(buf, statement, id, grp, id_rev); if (mysql_query(mysql, buf)) { printf("mysql_query(INSERT INTO) failed\n"); return 1; } } mysql_close(mysql); return 0; } --end cut then run: select count(distinct id) from distinct_test group by grp; you'll get: ERROR 1032: Can't find record in 'distinct_test' Suggested fix: compare what 4.0 and 4.1 sources - 4.0 seem not to have that bug