Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.Consider the following dynamic query:
$query = db_select('users', 'u');
$query->addField('u', 'name');
$query->distinct();
$query->join('node_revision', 'n');
$results = $query->execute();In Drupal 7.9 this produces the following SQL:
SELECT DISTINCT u.name AS name
FROM users u
INNER JOIN node_revision nSince no join condition is specified, this is a natural join, that is, an inner join on whatever columns in the tables have the same name. In this case that would be equivalent to
WHERE u.uid = n.uid
Some people frown on natural joins because they are a potential software maintenance headache; adding a column to one table which has the same name as a column in the other table changes the effective WHERE condition and therefore the results of the query, without generating any kind of warning. Other people use natural joins because they are convenient. The Drupal SQL coding conventions are silent on this subject.
The natural join SQL produces the expected result on MySQL, but produces a syntax error on PostgresSQL. The reason is that PostgresSQL requires the following:
SELECT DISTINCT u.name AS name
FROM users u
NATURAL JOIN node_revision nto perform a natural join.
The database tests in modules/simpletest/tests do not try any natural joins, so this problem is not detected by the unit tests. However, natural joins appear in certain contributed modules, causing them to break when using PostgresSQL.
Comments
Comment #1
steinmb CreditAttribution: steinmb commentedComment #2
bzrudi71 CreditAttribution: bzrudi71 commentedI just stumbled upon this issue. Adding tags and parent issue. Also going to add this to the nice to have section in the parent issue.
Comment #3
Crell CreditAttribution: Crell at Palantir.net commentedI have vague memories of natural joins not working right in MySQL when we first wrote the DB layer (circa 2009), which is why we avoided them. If they're reliable in modern MySQL versions I've no problem with adding support for them. Most likely it would be an addition to the SelectQuery class/interface of the form
And then whether it uses INNER JOIN or NATURAL JOIN is left up to the DB-specific compilation method. Shouldn't be too hard to do. The question is whether that's safe for 8.1, since it's modifying an interface even if it's in an easy to support way and there's only about 4 people that would be impacted by it.
Comment #5
daffie CreditAttribution: daffie commentedThis issue creates new functionality and that is allowed. You are not allowed to change the existing functionality, but adding new functionality is allowed. So adding the function naturalJoin() with the desired functionality is no problem.
Comment #8
RoSk0