MySQL Bugs: #8218: Join does not pass string from right table

Bug #8218	Join does not pass string from right table
Submitted:	31 Jan 2005 20:13	Modified:	9 Mar 2005 2:51
Reporter:	Vivienne Boogaard	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.9	OS:	Windows (Windows XP)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

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.

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.

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.

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>

Pushed into 4.1.11 and 5.0.3

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?

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.

Noted in 4.1.11, 5.0.3 changelogs.