query returning odd result
am 13.03.2006 18:01:06 von Joelle TegwenI have a table:
CREATE TABLE `youthhood`.`link` (
`linkID` int(10) unsigned NOT NULL default '0',
`url` varchar(255) NOT NULL default '',
`sectionID` int(10) unsigned NOT NULL default '0',
`title` varchar(255) default NULL,
`description` longtext,
`organization` varchar(255) default NULL,
`locationID` int(10) unsigned NOT NULL default '0',
`isActive` tinyint(1) unsigned NOT NULL default '1',
`ordinal` int(10) unsigned NOT NULL default '0',
`dateUpdated` datetime NOT NULL default '0000-00-00 00:00:00',
`dateCreated` datetime NOT NULL default '0000-00-00 00:00:00',
`changeDescription` longtext,
PRIMARY KEY (`linkID`),
KEY `url` (`url`),
KEY `locationID` (`locationID`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='connectionString';
If I run this query:
SELECT url, sectionID, title, description, organization FROM link WHERE
sectionID=16 AND isActive = true AND locationID= 0
I get three rows:
'http://www.highschoolace.com/ace/ace.cfm', 16, 'High School Ace',
'Homework help, online learning quizzes, college prep resources, and
more!', ''
'http://school.discovery.com/students/', 16, 'Discovery's Homework
Help', 'Study tools, fun and games, and cool learning adventures.',
'Discovery Education'
'http://www.multcolib.org/homework/', 16, 'Homework Center', 'Youth can
chat with an online tutor through this site for homework assistance on a
variety of topics from 2p.m. - 10p.m.
', 'Multnomah County Library'
If I run this query:
SELECT url, sectionID, title, description, organization FROM link WHERE
sectionID=16 AND isActive = true AND locationID= 0 ORDER BY Ordinal
I get three different (but the same) rows
'http://www.doitnow.org/pages/180.html', 83, 'Ceasefire: What We Can Do
to Stop Violence in our Schools', 'Thoughts about school shootings and
how to stop them.', 'Do It Now Foundation'
'http://www.doitnow.org/pages/180.html', 83, 'Ceasefire: What We Can Do
to Stop Violence in our Schools', 'Thoughts about school shootings and
how to stop them.', 'Do It Now Foundation'
'http://www.doitnow.org/pages/180.html', 83, 'Ceasefire: What We Can Do
to Stop Violence in our Schools', 'Thoughts about school shootings and
how to stop them.', 'Do It Now Foundation'
If I run
SELECT * FROM (SELECT url, sectionID, title, description, organization,
ordinal FROM link WHERE sectionID=16 AND isActive = true AND locationID=
0 ) as l ORDER BY Ordinal
I get the correct results
If I run the same query on the host server I get the desired results.
Permissions for the federated table login are SELECT only, restricted to
only certain tables.
Is this a bug in the federated table engine?
Thanks
Joelle
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org