View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0017609 | phplist application | Message Send Process | public | 14-02-15 08:56 | 06-07-15 18:04 |
| Reporter | duncanc | ||||
| Priority | normal | Severity | minor | Reproducibility | always |
| Status | resolved | Resolution | fixed | ||
| Product Version | 3.0.12 | ||||
| Target Version | 3.0.X | Fixed in Version | 3.2.0 | ||
| Summary | 0017609: Use of join without an ON clause | ||||
| Description | There is an sql query that uses JOIN without an ON clause, which means that it is joining every row from one table with every row from the second table. The use of DISTINCT hides this problem. See file actions/processqueue.php line 795 $query = ' select distinct u.id' . ' from %s as listuser' . ' cross join %s as u' . ' cross join %s as listmessage' . ' left join %s as um' . ' on (um.messageid = ? and um.userid = listuser.userid)' . ' where true' . ' and listmessage.messageid = ?' . ' and listmessage.listid = listuser.listid' . ' and u.id = listuser.userid' . ' and um.userid IS NULL' . ' and u.confirmed and !u.blacklisted and !u.disabled' . ' %s %s'; Also, it would be clearer to use INNER JOIN instead of CROSS JOIN. Although these are equivalent in mysql, in other sql they have different meanings. | ||||
| Tags | No tags attached. | ||||
|
|
interesting. Have you done any tests to see if there's a speed difference? |
|
|
hang on, there is an ON in that query, isn't there? |
|
|
There are three joins, the last of which has an ON condition, the first two do not. But you have put the ON conditions in the WHERE clause of the query and it looks like mysql uses those to avoid the cartesian product that I was thinking about. But it is a really confusing approach and likely to be error prone by not putting the conditions in the JOIN itself I suggest moving conditions to the joins join %s as u on listuser.userid = u.id join %s as listmessage on listuser.listid = listmessage.listid |
|
|
https://github.com/michield/phplist/commit/ba5a576c7d6e0406b6f5d69d067505e406d30be5 |
