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);
--------
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); --------