Bug #3845 wrong FOUND_ROWS result
Submitted: 21 May 2004 11:24 Modified: 28 May 2004 13:52
Reporter: Sergey Frolovichev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.18-standard OS:Linux (Suse Linux 9.0/WinXP)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[21 May 2004 11:24] Sergey Frolovichev
Description:
In some conditions FOUND_ROWS return result "1" but there are no records returned!!!

Here come my select:
-------
select
  SQL_CALC_FOUND_ROWS
  b.oid,
   IF (find_in_set('Email',b.target),5.55,0)+ IF (find_in_set('RegSex',b.target),5.55,0)+ IF (find_in_set('Group',b.target),5.55,0)+ IF (find_in_set('Child',b.target),5.55,0)+ IF (b.constitution='Thin',5.55,0)+ IF (b.circumstance='Average',5.55,0)+ IF (b.circumstance='Huge',5.55,0)+ IF (b.sponsor='Iam',5.55,0)+ IF (b.sponsor='No',5.55,0)+ IF (b.drug='Sometimes',5.55,0)+ IF (b.drug='GiveUp',5.55,0)+ IF (b.drug='Throw',5.55,0)+ IF (b.sexbreast='Middle',5.55,0)+ IF (find_in_set('Kiss',b.sexpriority),5.55,0)+ IF (find_in_set('Massage',b.sexpriority),5.55,0)+ IF (find_in_set('Svingers',b.sexpriority),5.55,0)+ IF (find_in_set('Bondage',b.sexpriority),5.55,0)+ IF (find_in_set('Slave',b.sexpriority),5.55,0) as percent
from
  Search2.extendedSearch_1_FF_M as b
where
   b.language_id=2 and ( find_in_set('Email',b.target) or find_in_set('RegSex',b.target) or find_in_set('Group',b.target) or find_in_set('Child',b.target) or b.constitution='Thin' or b.circumstance='Average' or b.circumstance='Huge' or b.sponsor='Iam' or b.sponsor='No' or b.drug='Sometimes' or b.drug='GiveUp' or b.drug='Throw' or b.sexbreast='Middle' or find_in_set('Kiss',b.sexpriority) or find_in_set('Massage',b.sexpriority) or find_in_set('Svingers',b.sexpriority) or find_in_set('Bondage',b.sexpriority) or find_in_set('Slave',b.sexpriority) ) and b.banned='No'
group by
  b.oid
having percent>30
order by
  percent desc,b.changed desc,b.created desc;
select FOUND_ROWS() as total;
-------
in original it was select with limit 0,10, but with limit or without it doesn't work!

Here answer:
-------
Empty set (0.10 sec)

+-------+
| total |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)
-------

How to repeat:
Here table create:
--------
CREATE TABLE `extendedSearch_1_FF_M` (
  `oid` int(10) unsigned NOT NULL default '0',
  `user_id` int(10) unsigned NOT NULL default '0',
  `language_id` int(10) unsigned NOT NULL default '0',
  `photo_oid` int(10) unsigned NOT NULL default '0',
  `photo_extension` varchar(255) NOT NULL default '',
  `photo_adult` enum('Yes','No') NOT NULL default 'No',
  `iam` enum('N','M','F','T','Self','MF','MM','FF') NOT NULL default 'N',
  `lookfor` set('N','M','F','T','MF','MM','FF') NOT NULL default '',
  `type` enum('Free','Pay') NOT NULL default 'Free',
  `name` varchar(255) NOT NULL default '',
  `birthdate` date NOT NULL default '0000-00-00',
  `gender` enum('None','M','F','MTF','FTM') NOT NULL default 'None',
  `country_id` int(10) unsigned NOT NULL default '0',
  `country_name` varchar(255) NOT NULL default '',
  `region_id` int(10) unsigned NOT NULL default '0',
  `region_name` varchar(255) NOT NULL default '',
  `city_id` int(10) unsigned NOT NULL default '0',
  `city_name` varchar(255) NOT NULL default '',
  `metro_id` int(10) unsigned NOT NULL default '0',
  `metro_name` varchar(255) NOT NULL default '',
  `aboutme` varchar(255) NOT NULL default '',
  `photos` int(10) unsigned NOT NULL default '0',
  `banned` enum('No','Yes') default 'No',
  `sign` enum('none','oven','telez','bliznezi','rak','lev','deva','vesi','skorpion','strelez','kozerog','vodoley','ribi') NOT NULL default 'none',
  `target` set('Friendship','Email','Love','RegSex','SomeSex','Group','Virt','Family','Child','Fiktive','Rent','Sport','Other') NOT NULL default '',
  `constitution` enum('None','Thin','Normal','Sports','Athletic','Thick','Stout','Other') NOT NULL default 'None',
  `hairshead` enum('Light','Dark','Red','Melirovanie','Rich','Grey','Clean','None') NOT NULL default 'None',
  `marital` enum('Together','Apart','Fictive','No','None') NOT NULL default 'None',
  `children` enum('No','Want','Together','Apart','None') NOT NULL default 'None',
  `circumstance` enum('None','NonStable','Stable','Average','Huge') NOT NULL default 'None',
  `sponsor` enum('LookFor','Iam','No','None') NOT NULL default 'None',
  `music` set('Russian','Foreign','Bard','RussianRock','Blues','Etnic','Rap','Classic','Dance','Rock','Electronic','Other') NOT NULL default '',
  `sport` set('Run','Walk','Fitness','Swim','ByCicle','SkateBoard','Roller','Ski','Daiving','Serfing','Fighting','Ioga','Other') NOT NULL default '',
  `smoke` enum('No','Yes','Sometimes','GiveUp','None') NOT NULL default 'None',
  `drink` enum('No','Sometimes','Yes','None') NOT NULL default 'None',
  `drug` enum('No','Never','Sometimes','Light','Yes','GiveUp','Recover','Throw','None') NOT NULL default 'None',
  `aids` enum('Yes','No','Unknown','None') NOT NULL default 'None',
  `sexexcitation` set('Women','Socks','Foots','Clothes','Underclothes','Uniform','Jeans','Skin','Latex','Metall','Smell','Hairiness','Dark','TatuPirsing','Other') NOT NULL default '',
  `sexpenis` enum('10','10-15','16-20','20','None') NOT NULL default 'None',
  `sexbreast` enum('Small','Middle','Big','None') NOT NULL default 'None',
  `sexpriority` set('Kiss','Massage','Breast','Petting','Masturbation','MinetOffer','MinetGet','KunnilingusGet','KunnilingusOffer','Rimming','Vaginal','AnalGet','AnalOffer','Group','Svingers','Public','Photo','Restling','Bondage','Dominate','Slave','Role','Sado','Mazo','Porka','Izmazivanie','Sperma','Fingering','Fisting','Vampir','Urologania','Pikansizm','Klizma','Porno','Abuse','Toys','Other') NOT NULL default '',
  `orientation` enum('None','Getero','Bi','Gay','Lesbi') NOT NULL default 'None',
  `age` set('16-20','21-25','26-30','31-35','36-40','41-60','61-80') NOT NULL default '',
  `height` int(10) unsigned NOT NULL default '0',
  `weight` int(10) unsigned NOT NULL default '0',
  `changed` timestamp(14) NOT NULL,
  `created` timestamp(14) NOT NULL default '00000000000000',
  PRIMARY KEY  (`oid`,`language_id`)
) TYPE=MyISAM

--------

Here comes data in table:
--------
INSERT INTO extendedSearch_1_FF_M VALUES (15400,15399,2,15412,'jpg','No','FF','M','Free','Îëüãóí÷èê','1984-05-16','F',3159,'Pîññèÿ',4312,'Ìîñêâà è Ìîñêîâñêàÿ îáë.',4400,'Ìîñêâà',0,'','Âåñåëàÿ, äîáðÿ, ðîìàíòè÷íàÿ, íåæíàÿ, ñ ÷óâñòâîì þìîðà',4,'No','telez','Friendship,Email,Love,Family,Child,Sport','Thick','None','None','No','Average','None','Foreign,Bard,RussianRock,Etnic,Classic,Dance,Electronic','Run,Walk,ByCicle','No','Sometimes','None','None','Jeans,Skin,Smell','None','Small','Kiss,Massage','Getero','21-25,26-30,31-35,36-40,41-60',168,52,00000000000000,20040205165107);
--------
[28 May 2004 12:32] Ramil Kalimullin
ChangeSet
  1.1860 04/05/28 15:01:16 ram@gw.mysql.r18.ru +3 -0
[28 May 2004 13:52] Ramil Kalimullin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html