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:Microsoft Windows (Windows XP)
Assigned to: Sergey Petrunya CPU Architecture:Any

[31 Jan 2005 20: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 20: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 12: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 5: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 22:09] Sergey Petrunya
Pushed into 4.1.11 and 5.0.3
[18 Feb 2005 17: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 17: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 2:51] Paul Dubois
Noted in 4.1.11, 5.0.3 changelogs.