Bug #958 a big table without indices and select with group by doesnt work
Submitted: 30 Jul 2003 8:46 Modified: 6 Aug 2003 6:54
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1, 5.0 OS:Linux (SuSE Linux 8.2)
Assigned to: Konstantin Osipov CPU Architecture:Any

[30 Jul 2003 8:46] [ name withheld ]
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
[30 Jul 2003 8:48] [ name withheld ]
fill.c - proram to fill data

Attachment: fill.c (text/plain), 1.46 KiB.

[30 Jul 2003 10:16] Indrek Siitan
Can you upload a copy of the table on which it occurs to you, to our
FTP site at ftp://support.mysql.com/pub/mysql/secret and let us know
of the filename, so we can try to reproduce it?
[6 Aug 2003 6:54] Konstantin Osipov
fixed in version 4.1.1