Bug #11382 Incorrect record count for FEDERATED tables
Submitted: 16 Jun 2005 13:38 Modified: 17 Jun 2005 6:37
Reporter: [ name withheld ] (Basic Quality Contributor) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.6 OS:Windows (WindowsXP)
Assigned to: CPU Architecture:Any

[16 Jun 2005 13:38] [ name withheld ]
Description:
I've created a table with ENGINE=FEDERATED, then looked at it through INFORMATION_SCHEMA and it displays an incorrect value for TABLE_ROWS.

How to repeat:
Create table:

USE southwind;
CREATE TABLE "clienti" (
  "IDCliente" varchar(5) NOT NULL default '',
  "NomeSocietà" varchar(40) default NULL,
  "Contatto" varchar(30) default NULL,
  "Posizione" varchar(30) default NULL,
  "Indirizzo" varchar(60) default NULL,
  "Città" varchar(15) default NULL,
  "Zona" varchar(15) default NULL,
  "CAP" varchar(10) default NULL,
  "Paese" varchar(15) default NULL,
  "Telefono" varchar(24) default NULL,
  "Fax" varchar(24) default NULL,
  PRIMARY KEY  ("IDCliente"),
  KEY "CLIENTI_CAP" ("CAP"),
  KEY "CLIENTI_PAESE" ("Paese")
) ENGINE=InnoDB;

insert into it some records (mine had 91) then create table based on FEDERATED engine
USE test;
CREATE TABLE "clienti" (
  "IDCliente" varchar(5) NOT NULL default '',
  "NomeSocietà" varchar(40) default NULL,
  "Contatto" varchar(30) default NULL,
  "Posizione" varchar(30) default NULL,
  "Indirizzo" varchar(60) default NULL,
  "Città" varchar(15) default NULL,
  "Zona" varchar(15) default NULL,
  "CAP" varchar(10) default NULL,
  "Paese" varchar(15) default NULL,
  "Telefono" varchar(24) default NULL,
  "Fax" varchar(24) default NULL,
  PRIMARY KEY  ("IDCliente")
)
ENGINE=FEDERATED
COMMENT='mysql://root:password@localhost:3306/federated/test_table';

Then check the INFORMATION_SCHEMA:

SELECT * FROM information_schema."TABLES" T where table_schema = 'test' and table_name = 'clienti'

and you'll see that the TABLE_ROWS value is 10000

Suggested fix:
Make ANALYZE TABLE able to update such a value (the query optimizer should benefit of this) or set the TABLE_ROWS value to NULL for those tables.
[17 Jun 2005 6:37] Vasily Kishkin
Sorry...but I was not able to reproduce the bug on 5.0.7.
[17 Jun 2005 6:38] Vasily Kishkin
sql file

Attachment: test.sql (text/plain), 8.20 KiB.