1311150 - Prevent multiple highlights appearing from the same domain

Status: RESOLVED FIXED

(Firefox for iOS :: Home screen, defect, P2)

Description

[Stephan Leroux [:sleroux]]

Instead of displaying potentially many sites from the same domain, we should GROUP BY domain to produce unique domain results to give some more variety to the highlights algorithm.

Desktop: https://github.com/mozilla/activity-stream/blob/master/addon/PlacesProvider.js#L462

Comment 1

[Richard Newman [:rnewman]]

This is your regularly scheduled reminder that GROUP BY in SQL is tricksy (e.g., individual values in a non-grouped column don't necessarily come from the same row!), and so careful review is worthwhile.

Comment 2

[Richard Newman [:rnewman]]

Let's assume data like this:

CREATE TABLE peeps (
  age INTEGER,
  name TEXT,
  candy TEXT,
  shoesize INTEGER
);

INSERT INTO peeps VALUES
  (15, 'Alice', 'Gobstoppers', 7),
  (15, 'Barbara', 'Gummy worms', 6),
  (16, 'Carly', 'Red vines', 8),
  (16, 'Davina', 'Candy corn', 6);

Let's say you want a representative of each age: GROUP BY leaps to mind, just as it might when you're implementing this bug. So you write:

SELECT age, name, candy, shoesize
FROM peeps
GROUP BY age;

What you expect is this:

age         name        candy        shoesize
----------  ----------  -----------  ----------
15          Barbara     Gummy worms  6
16          Davina      Candy corn   6

What a compliant SQL engine can give you is this:

age         name        candy        shoesize
----------  ----------  -----------  ----------
15          Alice       Gummy worms  6
16          Davina      Candy corn   8

That is: there is no rule to say that values produced by grouping must come from the same contributing row. Alice is 15, yes, but she doesn't like gummy worms. Another member of the set of 15-year-olds does.

SQLite will almost always do what you expect, but when you throw joins, virtual tables, assorted index walks, aggregate operators, etc., you cannot rely on it, and that behavior might change in the future.

Comment 3

[Richard Newman [:rnewman]]

Here's how you do it correctly:

http://www.techfounder.net/2010/03/12/fetching-specific-rows-from-a-group-with-mysql/
https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

SQL: it ain't easy.

Comment 4

[Bryan Munar (:bkmunar)]

Attachment: PR

Comment 5

[Bryan Munar (:bkmunar)]

https://github.com/mozilla-mobile/firefox-ios/pull/2317/commits/cfd97ff94715d78857e3627b78515f36a59a3324