Description:
While trying to join a "virtual" column by LEFT OUTER JOIN I noticed the removing of :
, COUNT( DISTINCT topic.id ) AS topicCount, `topic`.`id` AS `t1_c0`
makes my query returns normally all the rows, otherwise I get only 1 record.
How to repeat:
Use the following dump :
-- phpMyAdmin SQL Dump
-- version 3.3.3
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1:3306
-- Generation Time: Jun 05, 2010 at 05:23 PM
-- Server version: 5.1.47
-- PHP Version: 5.3.2
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `web3cms`
--
-- --------------------------------------------------------
--
-- Table structure for table `w3_forum_sections`
--
CREATE TABLE `w3_forum_sections` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parentId` int(10) unsigned NOT NULL DEFAULT '0',
`name` tinytext NOT NULL,
`description` text,
`position` tinyint(3) unsigned NOT NULL,
`isActive` tinyint(1) DEFAULT '1',
`accessLevel` tinyint(3) unsigned DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `w3_forum_sections`
--
INSERT INTO `w3_forum_sections` VALUES(1, 0, 'sexion', '', 1, 0, 0);
INSERT INTO `w3_forum_sections` VALUES(2, 0, 'sexion2', '', 2, 0, 0);
-- --------------------------------------------------------
--
-- Table structure for table `w3_forum_topics`
--
CREATE TABLE `w3_forum_topics` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sectionId` int(10) unsigned NOT NULL,
`postedBy` int(10) unsigned NOT NULL,
`created` int(10) unsigned NOT NULL,
`replyCount` int(10) unsigned DEFAULT '0',
`viewCount` int(10) unsigned DEFAULT '0',
`closed` tinyint(1) DEFAULT '0',
`sticky` tinyint(1) DEFAULT '0',
`hasPoll` tinyint(1) DEFAULT '0',
`isActive` tinyint(1) DEFAULT '1',
`accessLevel` tinyint(3) unsigned DEFAULT '0',
PRIMARY KEY (`id`,`sectionId`,`postedBy`),
KEY `w3_forum_topics_FKIndex1` (`sectionId`),
KEY `w3_forum_topics_FKIndex2` (`postedBy`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
--
-- Dumping data for table `w3_forum_topics`
--
Then try (only 1 record) :
SELECT `t`.`id` AS `t0_c0` , `t`.`parentId` AS `t0_c1` , `t`.`name` AS `t0_c2` , `t`.`description` AS `t0_c3` , `t`.`position` AS `t0_c6` , `t`.`isActive` AS `t0_c7` , `t`.`accessLevel` AS `t0_c8` , COUNT( DISTINCT topic.id ) AS topicCount, `topic`.`id` AS `t1_c0` , `topic`.`sectionId` AS `t1_c1` , `topic`.`postedBy` AS `t1_c2`
FROM `w3_forum_sections` `t`
LEFT
OUTER JOIN `w3_forum_topics` `topic` ON ( `topic`.`sectionId` = `t`.`id` )
ORDER BY position
LIMIT 0 , 300
and without "COUNT( DISTINCT topic.id ) AS topicCount" (2 records) :
SELECT `t`.`id` AS `t0_c0` , `t`.`parentId` AS `t0_c1` , `t`.`name` AS `t0_c2` , `t`.`description` AS `t0_c3` , `t`.`position` AS `t0_c6` , `t`.`isActive` AS `t0_c7` , `t`.`accessLevel` AS `t0_c8` , `topic`.`id` AS `t1_c0` , `topic`.`sectionId` AS `t1_c1` , `topic`.`postedBy` AS `t1_c2`
FROM `w3_forum_sections` `t`
LEFT
OUTER JOIN `w3_forum_topics` `topic` ON ( `topic`.`sectionId` = `t`.`id` )
ORDER BY position
LIMIT 0 , 300
