DROP TABLE friends;
DROP TABLE pets;
CREATE TEMPORARY TABLE friends (
name varchar(255) primary key,
hair_color varchar(255)
);

INSERT INTO friends VALUES ('john','brown');
INSERT INTO friends VALUES ('sarah','brown');
INSERT INTO friends VALUES ('emily','red');

CREATE TEMPORARY TABLE pets(
master_name varchar(255),
pet_name varchar(255)
);

INSERT INTO pets VALUES ('john','fido');
INSERT INTO pets VALUES ('emily','rex');
INSERT INTO pets VALUES ('emily','misty');

-- SELECT * from friends;

-- SELECT * from pets;

-- SELECT * FROM friends LEFT JOIN pets on (friends.name = pets.master_name);

-- This gives the same four rows on both mysql and postgres, with emily listed twice
-- SELECT DISTINCT * FROM friends LEFT JOIN pets on (friends.name = pets.master_name);

-- This gives the same result (emily, john, sarah) on both mysql and postgres, because without the pets listed
-- there are now actual duplicate rows
-- SELECT DISTINCT hair_color, name FROM friends LEFT JOIN pets on (friends.name = pets.master_name);

-- DISTINCT only removes rows if ALL columns of ALL rows are different. 
-- Postgres has non-standard syntax (DISTINCT ON) that does what it seems most people >think< DISTINCT(column_name) 
-- does in MySQL.  DISTINCT ON is essentially the same as GROUP BY master_name, except that you don't have to wrap non-grouped columns in aggreagte functions
-- It must obey part of the same restrictions as GROUP BY, specifically: 
-- the DISTINCT ON must be at the begining of any ORDER BY
-- The following query will return three rows (emily will NOT be listed twice):
-- SELECT DISTINCT ON (name) * FROM friends LEFT JOIN pets on (friends.name = pets.master_name);

-- Now, in BOTH mysql and postgres, the following does NOT return 3 rows as it seems many module autors seem to expect
-- Emily will be returned twice
-- SELECT DISTINCT(name), hair_color, pet_name FROM friends LEFT JOIN pets on (friends.name = pets.master_name);

-- In fact, even in a single table, we have brown twice:
-- SELECT DISTINCT(hair_color), name FROM friends;

-- The fact is, both databases simply interprets it as:
-- SELECT DISTINCT (hair_color), name FROM friends;

-- If you are not convinced, try this, it will give you a syntax error
-- SELECT name, DISTINCT(hair_color) FROM friends;

-- So, (at least in MySql 5.1.37) the code doesn't do what it's supposed to at all, the result is or isn't the expected one depending on which columns are currently selected.

-- The proper way (which works in both myslql and postgres) is ultimately to use GROUP BY.  The problem is that we let hooks modify the query, so it's extremely difficult to ensure that all the following are met:
-- 1- Any column added to the select list is wrapped in some sort of aggregate function.  What function makes sense can only be decided by the calling code
-- 2- Ensure that at all times the ORDER BY query begins with the content of GROUP BY  
-- Such rewriting is highly non-trivial, and shouldn't be done in core, especially since there AREN'T any significant differences between MySQL and postgres (at least between MySql 5.1.37 and postgresql 8.4.1) .
-- SELECT * FROM friends LEFT JOIN pets ON (friends.name = pets.master_name) GROUP BY (name);