Bug #298 Left Join with aliases not working correctly
Submitted: 16 Apr 2003 7:57 Modified: 16 May 2003 2:45
Reporter: Jason Burfield Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.12 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[16 Apr 2003 7:57] Jason Burfield 
Description:
The following query:

SELECT s.id, s.title, count(c.book_id) as total_comments, 
ifnull(round(( sum(c.rating) / count(c.book_id) ),2), "n/a") as avg
FROM sponsor_books s 
LEFT JOIN book_comments c on s.id=c.book_id
GROUP BY s.id 
ORDER BY avg desc, total_comments desc 
limit 25

Fails to return the proper results with MySQL 4.0.12. 

On 3.23.47 it worked fine.

My results now look like this:

+-----+------------------------------------------------+----------------+-----+
| id  | title                                          | total_comments | avg |
+-----+------------------------------------------------+----------------+-----+
| 282 | North Dallas Forty                             |              0 | n/a |
| 347 | Prospects: A Portrait of Minor League Baseball |              0 | n/a |
| 412 | Communication Arts                             |              0 | n/a |
+-----+------------------------------------------------+----------------+-----+

It is returning NULL for avg and total_comments.

WIth 4.23.47 it the result set was:

+-----+-----------------------------------------------+----------------+-------+
| id  | title                                         | total_comments | avg   |
+-----+-----------------------------------------------+----------------+-------+
| 273 | Slap Shot                                     |              5 | 10.00 |
|  61 | Truth Needs No Ally: Inside Photojournalism   |              3 | 10.00 |
|  54 | The Photographic Essay: William Albert Allard |              3 | 10.00 |
+-----+-----------------------------------------------+----------------+-------+

Which is correct.

The table structure for the two tables is:
mysql> desc sponsor_books;
+-----------------+----------------------------------------------------------
+------+-----+---------------------+----------------+
| Field           | Type                                                     | Null | Key | Default             | Extra          |
+-----------------+----------------------------------------------------------
+------+-----+---------------------+----------------+
| id              | int(10) unsigned                                         |      | PRI | NULL                | 
auto_increment |
| title           | varchar(100)                                             |      | MUL |                     |                |
| author          | varchar(50)                                              |      | MUL |                     |                |
| isbn            | varchar(20)                                              |      |     |                     |                |
| link            | varchar(150)                                             |      |     |                     |                |
| notes           | text                                                     |      | MUL |                     |                |
| type            | enum('Book','Video','DVD','CD-ROM','AudioCD','Magazine') |      |     | Book                
|                |
| student_reading | enum('no','yes')                                         |      |     | no                  |                |
| image           | varchar(20)                                              |      |     |                     |                |
| date_added      | datetime                                                 |      |     | 0000-00-00 00:00:00 |                
|
+-----------------+----------------------------------------------------------
+------+-----+---------------------+----------------+
10 rows in set (0.00 sec)

and:

mysql> desc book_comments;
+--------------+------------------------------------------------+------+-----
+---------------------+----------------+
| Field        | Type                                           | Null | Key | Default             | Extra          |
+--------------+------------------------------------------------+------+-----
+---------------------+----------------+
| id           | bigint(20) unsigned                            |      | PRI | NULL                | auto_increment |
| book_id      | int(10) unsigned                               |      |     | 0                   |                |
| mem_id       | int(10) unsigned                               |      |     | 0                   |                |
| mem_username | varchar(30)                                    |      |     |                     |                |
| mem_name     | varchar(40)                                    |      |     |                     |                |
| mem_city     | varchar(30)                                    |      |     |                     |                |
| mem_state    | varchar(5)                                     |      |     |                     |                |
| mem_country  | varchar(30)                                    |      |     |                     |                |
| section1     | varchar(20)                                    |      |     |                     |                |
| section2     | varchar(20)                                    |      |     |                     |                |
| comments     | text                                           |      | MUL |                     |                |
| rating       | enum('1','2','3','4','5','6','7','8','9','10') |      | MUL | 5                   |                |
| date_added   | datetime                                       |      | MUL | 0000-00-00 00:00:00 |                |
+--------------+------------------------------------------------+------+-----
+---------------------+----------------+
13 rows in set (0.00 sec)

I am using the RPM's supplied on the MySQL web site. It is running on RedHat 8.0. Kernel is: 
2.4.18-27 (smp)

Please note, I also submitted this as a comment to bug # 278

Thanks.

  --  Jason

How to repeat:
This happens each and every time I run a query that contains a LEFT JOIN and some type of 
function such as count(), sum(), etc. Every time I have tried it I have also used aliases for table 
names. However, I don't have any other option because the tables are used inside the functions 
and then sorted on, so aliases are needed.

Suggested fix:
No suggested fix.
[16 Apr 2003 8:13] MySQL Verification Team 
Can you post a dump of the tables ??
[16 Apr 2003 8:29] Jason Burfield 
Ok...I have uploaded them to the 'secret' directory of your ftp server.

The files are:

BUG_298_book_comments.sql
and
BUG_298_sponsor_books.sql

That was the correct place to put them, yeah?

Thanks!

  --  Jason
[16 Apr 2003 12:16] MySQL Verification Team 
It is a bug caused by ORDER BY ..
[16 Apr 2003 12:58] MySQL Verification Team 
This is not a bug !!

If non-serted and without limit, results are correct:

id	total_comments	avg
18	9	9.11
19	8	8.88
21	1	10.00
22	2	10.00
23	1	9.00
24	0	n/a
25	1	10.00
26	0	n/a
27	1	10.00
28	0	n/a
29	0	n/a
30	0	n/a
31	1	10.00
32	1	7.00
33	3	8.67
34	4	9.50
35	0	n/a
36	0	n/a
37	2	8.50
38	0	n/a
39	1	9.00
41	0	n/a
42	4	9.75
43	1	9.00
44	1	10.00
45	2	9.50
46	6	7.17
47	4	9.50
48	0	n/a
49	1	9.00
50	0	n/a
51	1	1.00
52	2	10.00
53	1	9.00
54	3	10.00
55	0	n/a
56	0	n/a
58	0	n/a
59	1	10.00
60	2	8.50
61	3	10.00
62	1	9.00
63	1	10.00
64	0	n/a
65	2	8.00
66	1	10.00
67	0	n/a
68	0	n/a
69	1	9.00
70	0	n/a
71	1	2.00
72	0	n/a
73	0	n/a
74	5	9.80
75	0	n/a
76	1	10.00
77	0	n/a
78	2	9.00
79	0	n/a
80	0	n/a
81	0	n/a
82	0	n/a
83	3	9.33
84	0	n/a
85	0	n/a
86	0	n/a
87	1	9.00
88	0	n/a
89	0	n/a
90	1	5.00
91	0	n/a
93	0	n/a
94	0	n/a
95	0	n/a
96	0	n/a
97	0	n/a
98	0	n/a
99	2	9.50
100	0	n/a
101	0	n/a
102	0	n/a
103	0	n/a
104	0	n/a
105	0	n/a
106	0	n/a
107	0	n/a
108	1	10.00
109	0	n/a
110	0	n/a
111	1	9.00
112	1	9.00
113	0	n/a
114	0	n/a
115	0	n/a
116	2	9.50
117	0	n/a
118	1	5.00
119	0	n/a
120	0	n/a
121	0	n/a
122	0	n/a
123	0	n/a
124	1	9.00
125	0	n/a
126	0	n/a
127	0	n/a
128	0	n/a
129	0	n/a
130	1	10.00
132	0	n/a
133	0	n/a
134	1	9.00
135	0	n/a
136	0	n/a
137	0	n/a
138	0	n/a
139	1	9.00
140	0	n/a
141	0	n/a
142	0	n/a
143	0	n/a
144	0	n/a
145	0	n/a
146	0	n/a
147	0	n/a
148	0	n/a
149	0	n/a
150	1	10.00
151	0	n/a
152	1	9.00
153	0	n/a
154	0	n/a
155	0	n/a
156	0	n/a
157	1	10.00
158	1	10.00
159	0	n/a
160	2	9.50
161	1	9.00
162	0	n/a
163	0	n/a
164	0	n/a
165	0	n/a
166	0	n/a
167	0	n/a
169	0	n/a
170	1	8.00
171	0	n/a
172	0	n/a
173	1	10.00
174	0	n/a
175	1	10.00
176	0	n/a
177	1	7.00
178	0	n/a
179	1	9.00
180	0	n/a
181	0	n/a
182	0	n/a
183	0	n/a
184	1	9.00
185	0	n/a
186	1	10.00
187	1	10.00
188	0	n/a
189	0	n/a
190	1	10.00
191	1	9.00
192	1	10.00
193	0	n/a
194	1	10.00
195	0	n/a
196	0	n/a
197	0	n/a
198	0	n/a
199	0	n/a
200	0	n/a
201	1	10.00
202	0	n/a
203	0	n/a
204	0	n/a
205	0	n/a
206	1	10.00
207	0	n/a
208	1	10.00
209	0	n/a
210	1	10.00
211	0	n/a
212	2	4.00
213	1	10.00
214	0	n/a
215	0	n/a
216	0	n/a
217	0	n/a
218	0	n/a
219	0	n/a
220	0	n/a
221	0	n/a
222	0	n/a
223	0	n/a
224	1	10.00
225	1	10.00
226	0	n/a
227	0	n/a
228	0	n/a
229	1	9.00
230	0	n/a
231	0	n/a
232	0	n/a
233	0	n/a
234	0	n/a
235	0	n/a
237	0	n/a
238	0	n/a
239	0	n/a
240	0	n/a
241	2	5.00
242	0	n/a
243	0	n/a
244	0	n/a
245	1	9.00
246	0	n/a
247	0	n/a
248	0	n/a
249	3	9.67
250	0	n/a
251	0	n/a
252	1	8.00
253	1	4.00
254	1	8.00
255	1	7.00
256	1	6.00
257	0	n/a
258	3	9.67
259	1	10.00
260	0	n/a
261	0	n/a
262	2	10.00
263	0	n/a
267	0	n/a
268	0	n/a
269	0	n/a
270	2	9.50
271	0	n/a
272	0	n/a
273	5	10.00
274	1	10.00
275	2	9.50
276	0	n/a
277	0	n/a
278	0	n/a
279	1	9.00
280	0	n/a
281	0	n/a
282	0	n/a
283	1	9.00
284	1	9.00
285	0	n/a
286	1	9.00
287	3	8.33
288	0	n/a
289	1	7.00
290	0	n/a
291	0	n/a
292	1	9.00
293	0	n/a
294	0	n/a
295	2	9.00
296	0	n/a
297	0	n/a
298	0	n/a
299	0	n/a
300	0	n/a
301	0	n/a
302	1	10.00
303	0	n/a
304	0	n/a
305	0	n/a
306	0	n/a
307	0	n/a
308	0	n/a
309	1	8.00
310	0	n/a
311	0	n/a
312	1	10.00
313	1	10.00
314	0	n/a
315	0	n/a
316	0	n/a
317	0	n/a
318	0	n/a
319	0	n/a
320	1	7.00
321	0	n/a
322	0	n/a
323	0	n/a
324	0	n/a
325	0	n/a
326	1	6.00
327	0	n/a
328	0	n/a
329	0	n/a
330	0	n/a
331	0	n/a
332	0	n/a
333	1	5.00
334	0	n/a
335	2	10.00
336	1	8.00
338	0	n/a
339	0	n/a
340	0	n/a
341	0	n/a
342	1	8.00
343	1	9.00
344	1	9.00
345	0	n/a
346	0	n/a
347	0	n/a
348	2	9.50
349	0	n/a
350	0	n/a
351	1	10.00
352	0	n/a
353	0	n/a
354	0	n/a
355	1	10.00
356	0	n/a
357	0	n/a
358	0	n/a
359	0	n/a
361	2	10.00
362	0	n/a
363	3	9.67
364	6	6.50
365	4	9.00
366	0	n/a
367	0	n/a
368	0	n/a
369	1	10.00
370	0	n/a
371	0	n/a
372	0	n/a
373	0	n/a
374	0	n/a
375	0	n/a
376	0	n/a
377	0	n/a
378	0	n/a
379	0	n/a
380	0	n/a
381	0	n/a
382	1	10.00
383	0	n/a
384	0	n/a
385	0	n/a
386	0	n/a
387	0	n/a
388	0	n/a
389	0	n/a
390	1	8.00
391	3	3.33
392	1	7.00
393	0	n/a
394	1	10.00
395	0	n/a
396	0	n/a
397	0	n/a
398	0	n/a
399	0	n/a
400	0	n/a
401	0	n/a
402	0	n/a
403	0	n/a
404	0	n/a
405	0	n/a
406	0	n/a
407	0	n/a
408	2	9.00
409	0	n/a
410	8	6.50
411	0	n/a
412	0	n/a
413	0	n/a
414	0	n/a
415	0	n/a
416	0	n/a
417	0	n/a
418	0	n/a
419	0	n/a
420	0	n/a
421	0	n/a
422	0	n/a
423	0	n/a
424	0	n/a
425	0	n/a
426	0	n/a
427	1	10.00
428	0	n/a
429	0	n/a
430	1	8.00
431	1	10.00
432	0	n/a
433	0	n/a
434	0	n/a
435	1	10.00

If we sort them, they are like this:

id	total_comments	avg
30	0	n/a
38	0	n/a
55	0	n/a
64	0	n/a
72	0	n/a
80	0	n/a
88	0	n/a
97	0	n/a
105	0	n/a
113	0	n/a
121	0	n/a
129	0	n/a
138	0	n/a
146	0	n/a
154	0	n/a
162	0	n/a
171	0	n/a
195	0	n/a
203	0	n/a
211	0	n/a
219	0	n/a
227	0	n/a
235	0	n/a
244	0	n/a
260	0	n/a
271	0	n/a
303	0	n/a
311	0	n/a
319	0	n/a
327	0	n/a
352	0	n/a
377	0	n/a
385	0	n/a
393	0	n/a
401	0	n/a
409	0	n/a
417	0	n/a
425	0	n/a
433	0	n/a
48	0	n/a
56	0	n/a
73	0	n/a
81	0	n/a
89	0	n/a
98	0	n/a
106	0	n/a
114	0	n/a
122	0	n/a
147	0	n/a
155	0	n/a
163	0	n/a
172	0	n/a
180	0	n/a
188	0	n/a
196	0	n/a
204	0	n/a
220	0	n/a
228	0	n/a
237	0	n/a
261	0	n/a
272	0	n/a
280	0	n/a
288	0	n/a
296	0	n/a
304	0	n/a
328	0	n/a
345	0	n/a
353	0	n/a
362	0	n/a
370	0	n/a
378	0	n/a
386	0	n/a
402	0	n/a
418	0	n/a
426	0	n/a
434	0	n/a
24	0	n/a
41	0	n/a
58	0	n/a
82	0	n/a
107	0	n/a
115	0	n/a
123	0	n/a
132	0	n/a
140	0	n/a
148	0	n/a
156	0	n/a
164	0	n/a
181	0	n/a
189	0	n/a
197	0	n/a
205	0	n/a
221	0	n/a
238	0	n/a
246	0	n/a
281	0	n/a
297	0	n/a
305	0	n/a
321	0	n/a
329	0	n/a
338	0	n/a
346	0	n/a
354	0	n/a
371	0	n/a
379	0	n/a
387	0	n/a
395	0	n/a
403	0	n/a
411	0	n/a
419	0	n/a
50	0	n/a
67	0	n/a
75	0	n/a
91	0	n/a
100	0	n/a
133	0	n/a
141	0	n/a
149	0	n/a
165	0	n/a
174	0	n/a
182	0	n/a
198	0	n/a
214	0	n/a
222	0	n/a
230	0	n/a
239	0	n/a
247	0	n/a
263	0	n/a
282	0	n/a
290	0	n/a
298	0	n/a
306	0	n/a
314	0	n/a
322	0	n/a
330	0	n/a
339	0	n/a
347	0	n/a
372	0	n/a
380	0	n/a
388	0	n/a
396	0	n/a
404	0	n/a
412	0	n/a
420	0	n/a
428	0	n/a
26	0	n/a
68	0	n/a
84	0	n/a
93	0	n/a
101	0	n/a
109	0	n/a
117	0	n/a
125	0	n/a
142	0	n/a
166	0	n/a
183	0	n/a
199	0	n/a
207	0	n/a
215	0	n/a
223	0	n/a
231	0	n/a
240	0	n/a
248	0	n/a
267	0	n/a
291	0	n/a
299	0	n/a
307	0	n/a
315	0	n/a
323	0	n/a
331	0	n/a
340	0	n/a
356	0	n/a
373	0	n/a
381	0	n/a
389	0	n/a
397	0	n/a
405	0	n/a
413	0	n/a
421	0	n/a
429	0	n/a
35	0	n/a
77	0	n/a
85	0	n/a
94	0	n/a
102	0	n/a
110	0	n/a
126	0	n/a
135	0	n/a
143	0	n/a
151	0	n/a
159	0	n/a
167	0	n/a
176	0	n/a
200	0	n/a
216	0	n/a
232	0	n/a
257	0	n/a
268	0	n/a
276	0	n/a
300	0	n/a
308	0	n/a
316	0	n/a
324	0	n/a
332	0	n/a
341	0	n/a
349	0	n/a
357	0	n/a
366	0	n/a
374	0	n/a
398	0	n/a
406	0	n/a
414	0	n/a
422	0	n/a
28	0	n/a
36	0	n/a
70	0	n/a
86	0	n/a
95	0	n/a
103	0	n/a
119	0	n/a
127	0	n/a
136	0	n/a
144	0	n/a
169	0	n/a
185	0	n/a
193	0	n/a
209	0	n/a
217	0	n/a
233	0	n/a
242	0	n/a
250	0	n/a
269	0	n/a
277	0	n/a
285	0	n/a
293	0	n/a
301	0	n/a
317	0	n/a
325	0	n/a
350	0	n/a
358	0	n/a
367	0	n/a
375	0	n/a
383	0	n/a
399	0	n/a
407	0	n/a
415	0	n/a
423	0	n/a
29	0	n/a
79	0	n/a
96	0	n/a
104	0	n/a
120	0	n/a
128	0	n/a
137	0	n/a
145	0	n/a
153	0	n/a
178	0	n/a
202	0	n/a
218	0	n/a
226	0	n/a
234	0	n/a
243	0	n/a
251	0	n/a
278	0	n/a
294	0	n/a
310	0	n/a
318	0	n/a
334	0	n/a
359	0	n/a
368	0	n/a
376	0	n/a
384	0	n/a
400	0	n/a
416	0	n/a
424	0	n/a
432	0	n/a
74	5	9.80
42	4	9.75
363	3	9.67
249	3	9.67
258	3	9.67
47	4	9.50
34	4	9.50
99	2	9.50
116	2	9.50
275	2	9.50
348	2	9.50
45	2	9.50
160	2	9.50
270	2	9.50
83	3	9.33
18	9	9.11
365	4	9.00
295	2	9.00
78	2	9.00
408	2	9.00
179	1	9.00
279	1	9.00
344	1	9.00
23	1	9.00
39	1	9.00
139	1	9.00
245	1	9.00
49	1	9.00
229	1	9.00
124	1	9.00
43	1	9.00
134	1	9.00
191	1	9.00
283	1	9.00
69	1	9.00
184	1	9.00
284	1	9.00
292	1	9.00
53	1	9.00
62	1	9.00
111	1	9.00
152	1	9.00
87	1	9.00
112	1	9.00
161	1	9.00
286	1	9.00
343	1	9.00
19	8	8.88
33	3	8.67
60	2	8.50
37	2	8.50
287	3	8.33
65	2	8.00
252	1	8.00
336	1	8.00
254	1	8.00
390	1	8.00
430	1	8.00
309	1	8.00
342	1	8.00
170	1	8.00
46	6	7.17
320	1	7.00
32	1	7.00
289	1	7.00
255	1	7.00
177	1	7.00
392	1	7.00
410	8	6.50
364	6	6.50
256	1	6.00
326	1	6.00
241	2	5.00
90	1	5.00
118	1	5.00
333	1	5.00
212	2	4.00
253	1	4.00
391	3	3.33
71	1	2.00
273	5	10.00
61	3	10.00
54	3	10.00
22	2	10.00
335	2	10.00
361	2	10.00
262	2	10.00
52	2	10.00
187	1	10.00
369	1	10.00
31	1	10.00
130	1	10.00
312	1	10.00
394	1	10.00
66	1	10.00
173	1	10.00
213	1	10.00
313	1	10.00
427	1	10.00
435	1	10.00
25	1	10.00
59	1	10.00
108	1	10.00
157	1	10.00
190	1	10.00
206	1	10.00
274	1	10.00
355	1	10.00
76	1	10.00
150	1	10.00
158	1	10.00
175	1	10.00
27	1	10.00
44	1	10.00
192	1	10.00
208	1	10.00
224	1	10.00
382	1	10.00
201	1	10.00
225	1	10.00
431	1	10.00
21	1	10.00
63	1	10.00
186	1	10.00
194	1	10.00
210	1	10.00
259	1	10.00
302	1	10.00
351	1	10.00
51	1	1.00

Now if we take only first 25, as per a query, we get this:

id      total_comments  avg
30      0       n/a
38      0       n/a
55      0       n/a
64      0       n/a
72      0       n/a
80      0       n/a
88      0       n/a
97      0       n/a
105     0       n/a
113     0       n/a
121     0       n/a
129     0       n/a
138     0       n/a
146     0       n/a
154     0       n/a
162     0       n/a
171     0       n/a
195     0       n/a
203     0       n/a
211     0       n/a
219     0       n/a
227     0       n/a
235     0       n/a
244     0       n/a
260     0       n/a

which is 100 % correct.
[16 Apr 2003 13:20] Jason Burfield 
How is that correct? It is trying to sort on avg and total_comments. Both 
of which have values unless we add an order by statement along with it.

So without the ORDER BY everything is good. But, once the ORDER BY is 
added it drops the values for avg and total_comments. 

That is correct?

Why would it work on 3.23.47 and not 4.0.12?

Thanks!

  --  Jason
[17 Apr 2003 2:16] MySQL Verification Team 
It is correct as it is ordering in descending order, hence n/a comes befor numerics ...

3.23.47 had a bug.
[17 Apr 2003 4:50] Jason Burfield 
Even though it is doing ORDER BY avg before the order by 
total_comments? The values in avg are numeric, so 10,0 should show up 
before 0.0, then it should order on the total_comments field.

Right?

(thanks for your quick replies  by the way!)
[17 Apr 2003 4:54] Jason Burfield 
Wow...I'm not sure how that makes sense...but I changed the ifnull 
statement to equal '0' if it is null and it works now. But I'm still confused 
as to why it would not order by the avg column first, followed by the 
total_comments field.

Thanks!
[17 Apr 2003 4:55] MySQL Verification Team 
avg is not numberic bug CHARACTER, as "n/a" can't be handled as numberic.

And, this is not a forum for free support but for reports on the real bugs.
[16 May 2003 2:45] Michael Widenius 
A last followup on this bug report.

The problem comes from the fact that the function:

ifnull(round(( sum(c.rating) / count(c.book_id) ),2), "n/a")

in some cases returns a number and in some cases a string.
(This is actually a wrong usage of the function but allowed in MySQL)

We did between MySQL 3.23 and 4.0 change how IFNULL() works in this case.

In MySQL 3.23 we threated the result of IFNULL() as of the same type as the first arguement, which is a number in this case.
In MySQL 4.x we changed this to threat the value as a string if either of the arguments to IFNULL() where strings.  We did to make MySQL more like other databases and also to fix some bugs/confusion the old behaviour caused.

The reson for the different results is thus that in 3.23 "n/a" would be regarded as number 0 would this be sorted last when used with DESC

In MySQL 4.x we compare the strings "10.00" with "n/a" and in this case "n/a" will show up before 10.00 when sorting with DESC

The solution to this probelm is to use:

ORDER BY sum(c.rating) / count(c.book_id)

instead of the alias