Bug #8218 Join does not pass string from right table
Submitted: 31 Jan 2005 21:13 Modified: 9 Mar 2005 3:51
Reporter: Vivienne Boogaard
Status: Closed
Category:Server Severity:S1 (Critical)
Version:4.1.9 OS:Microsoft Windows (Windows XP)
Assigned to: Bugs System Target Version:

[31 Jan 2005 21:13] Vivienne Boogaard 
Description:
Upgraded to 4.1.9. - basic query to join 2 tables to obtain description data from the
right table to apply to the left table.  The numeric columns join OK but the string
columns (such as description or name columns) pass <null> from inside a subquery such
as:

select * from Users join UserGroup on Users.id=Usergroup.userid join 
(select Name, id from Groups) as g on g.id=UserGroup.groupid  

(this query has been simplified from the actual query where a sub_query is necessary)

Uninstalled and reinstalled 4.1.7 - error disappears and string data is joined to the
left table as expected.

Here is the actual query:

	select count(distinct CoursePassingStats.userid) pass,
GroupSTuff			count(CoursePassingStats.courseId) crse, 
			date_format(date, '%b%y') as ColHead				from 	CoursePassingStats  
	join	CourseCategory on CoursePassingStats.courseid = CourseCategory.courseid 
	join 	Users 	on 		Users.id = CoursePassingStats.userid 	Join	
(SELECT UserGroup.userid, 
		       parentid, 
		       parentgroup, 
		       childid, 
		       groupname, 
		       groupTypeId 
			FROM   UserGroup 
			JOIN   (SELECT Groups.id AS parentId, 
						   Groups.name as parentgroup, 
					       Groups.id AS childId, 
					       Groups.name AS groupName, 
					       Groups.groupTypeId 
					FROM   Groups 
					WHERE  Groups.groupKey = 'stationer'
					UNION  
					SELECT parent.id AS parentId, 
						   parent.groupKey as parentgroup, 
					       child.id AS childId, 
					       child.name AS groupName, 
					       child.groupTypeId 
					FROM   Groups AS parent 
					JOIN   GroupGroup	ON     (parent.id=GroupGroup.parentId) 
					JOIN   Groups child	ON     (child.id=GroupGroup.groupId) 
					WHERE  parent.groupKey = 'stationer' and child.name not like 'default%'
					ORDER  BY parentId, childId) AS gin 		ON    	UserGroup.groupid=gin.childid where
userid=5141
			ORDER  BY userid) as GroupStuff 					on 		CoursePassingStats.userid =
GroupStuff.userid 
	left join SASite on SASite.DUNS = Users.siteDUNS
	where 	date BETWEEN date_sub(cast((concat(year(now()),date_format(now(),'%m'), '01'))AS
DATE), INTERVAL 12 month) 
				 AND     date_sub(cast((concat(year(now()),date_format(now(),'%m'), '01'))AS DATE),
INTERVAL 1 second) 
	group by region, GroupStuff.groupname, CourseCategory.categoryid, ColHead 

How to repeat:
I am not sure whether a subquery is needed or not - I suspect it is.  I am not about to
reinstall 4.1.9 to find all the permutations of this error.

Suggested fix:
Return to how 4.1.7 handled these joins.
[1 Feb 2005 21:57] Miguel Solorzano 
Could you please can provide a test case with database schema
with data or a dump file ?. You can upload it into a zip file at:

ftp://ftp.mysql.com/pub/mysql/upload

using a file name identifying this bug report.

Thanks in advance.
[2 Feb 2005 13:44] Miguel Solorzano 
According with your e-mail To: "'Bug Database'" <dev-bugs@mysql.com>

Please feel free for to open this bug report when you will able for
provide the test case.

Thanks in advance.
[8 Feb 2005 6:41] Miguel Solorzano 
Thank you for the bug report I was able to repeat with 4.1.9/4.1.10 BK:

+------+--------+----------+-------------+---------+-----------+-------------+------+------------+----------+---------+
| pass | userid | parentid | parentgroup | childid | groupname | groupTypeId | crse |
categoryid | courseid | ColHead |
+------+--------+----------+-------------+---------+-----------+-------------+------+------------+----------+---------+
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     5 |       12 | Aug04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     1 |       41 | Aug04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     2 |       52 | Aug04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     2 |       53 | Aug04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     3 |       51 | Oct04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     1 |       86 | Oct04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     1 |       87 | Oct04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     2 |       88 | Oct04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     2 |       89 | Oct04   |
+------+--------+----------+-------------+---------+-----------+-------------+------+------------+----------+---------+
9 rows in set (0.09 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.9-nt  |
+-----------+
1 row in set (0.00 sec)

+------+--------+----------+-------------+---------+-----------+-------------+------+------------+----------+---------+
| pass | userid | parentid | parentgroup | childid | groupname | groupTypeId | crse |
categoryid | courseid | ColHead |
+------+--------+----------+-------------+---------+-----------+-------------+------+------------+----------+---------+
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     5 |       12 | Aug04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     1 |       41 | Aug04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     2 |       52 | Aug04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     2 |       53 | Aug04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     3 |       51 | Oct04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     1 |       86 | Oct04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     1 |       87 | Oct04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     2 |       88 | Oct04   |
|    1 |   5141 |        9 | NULL        |      12 | NULL      |           5 |    1 |    
     2 |       89 | Oct04   |
+------+--------+----------+-------------+---------+-----------+-------------+------+------------+----------+---------+
9 rows in set (0.28 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.10-debug |
+--------------+
1 row in set (0.01 sec)

+------+--------+----------+-------------+---------+--------------+-------------+------+------------+----------+---------+
| pass | userid | parentid | parentgroup | childid | groupname    | groupTypeId | crse |
categoryid | courseid | ColHead |
+------+--------+----------+-------------+---------+--------------+-------------+------+------------+----------+---------+
|    1 |   5141 |        9 | stationer   |      12 | boisecascade |           5 |    1 | 
        5 |       12 | Aug04   |
|    1 |   5141 |        9 | stationer   |      12 | boisecascade |           5 |    1 | 
        1 |       41 | Aug04   |
|    1 |   5141 |        9 | stationer   |      12 | boisecascade |           5 |    1 | 
        2 |       52 | Aug04   |
|    1 |   5141 |        9 | stationer   |      12 | boisecascade |           5 |    1 | 
        2 |       53 | Aug04   |
|    1 |   5141 |        9 | stationer   |      12 | boisecascade |           5 |    1 | 
        3 |       51 | Oct04   |
|    1 |   5141 |        9 | stationer   |      12 | boisecascade |           5 |    1 | 
        1 |       86 | Oct04   |
|    1 |   5141 |        9 | stationer   |      12 | boisecascade |           5 |    1 | 
        1 |       87 | Oct04   |
|    1 |   5141 |        9 | stationer   |      12 | boisecascade |           5 |    1 | 
        2 |       88 | Oct04   |
|    1 |   5141 |        9 | stationer   |      12 | boisecascade |           5 |    1 | 
        2 |       89 | Oct04   |
+------+--------+----------+-------------+---------+--------------+-------------+------+------------+----------+---------+
9 rows in set (0.25 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.7-nt-max |
+--------------+
1 row in set (0.00 sec)

mysql>
[14 Feb 2005 23:09] Sergey Petrunya 
Pushed into 4.1.11 and 5.0.3
[18 Feb 2005 18:33] Vivienne Boogaard 
Where do we stand on this bug?  Will there be a patch we can apply to 4.1.9
implementations or do we have to go to a higher level?

Can we can 4.1.7 for Linux to install instead?
[18 Feb 2005 18:44] Miguel Solorzano 
hi,

It will be present in the next release 4.1.11/5.0.3. You have also the
option for to build it from source yourself according instructions you
can get from our Manual.
[9 Mar 2005 3:51] Paul DuBois 
Noted in 4.1.11, 5.0.3 changelogs.