Concat missing a cast

Narayan Newton - January 13, 2009 - 00:44
Project:URL list
Version:6.x-1.0
Component:Code
Category:bug report
Priority:normal
Assigned:Unassigned
Status:closed
Description

One of the concats used in this module is missing a cast on nid, this implicitly converts the string part of the concat to binary and means this can't use the index on the table. Here is a before and after the patch:

mysql> explain SELECT n.nid, n.type, n.status, n.promote, n.changed, u.dst FROM node n LEFT JOIN url_alias u ON u.src=CONCAT('node/',n.nid);
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | n | ALL | NULL | NULL | NULL | NULL | 4246 | |
| 1 | SIMPLE | u | ALL | src | NULL | NULL | NULL | 8152 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

change to

mysql> explain SELECT n.nid, n.type, n.status, n.promote, n.changed, u.dst FROM node n LEFT JOIN url_alias u ON u.src=CONCAT('node/',CAST(n.nid as CHAR));
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | n | ALL | NULL | NULL | NULL | NULL | 4246 | |
| 1 | SIMPLE | u | ref | src | src | 386 | func | 1 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

Let me know if I'm missing something obvious here :)

AttachmentSize
urllist.module.diff772 bytes

#1

deekayen - January 13, 2009 - 05:14
Status:needs review» fixed

Thanks. Committed to 46, 47, 5, 6, and HEAD.

#2

System Message - January 27, 2009 - 05:20
Status:fixed» closed

Automatically closed -- issue fixed for 2 weeks with no activity.

 
 

Drupal is a registered trademark of Dries Buytaert.