Hehehe. I didn't even notice the floating point equality test Richard, so here
we go:
Try this one as 1e-13 is 1 ulp at a scale of 1000.0 ... which should be more
than accurate enough in this case ...
WITH
GbC AS (
SELECT c.Country,
c.CustomerId,
SUM(i.total) as spent
FROM Customer as c
JOIN Invoice as i
ON i.CustomerId = c.CustomerId
GROUP BY c.Country, c.CustomerID
),
MbC AS (
SELECT Country,
MAX(spent) as spent
FROM GbC
GROUP BY Country
)
SELECT *
FROM GbC
WHERE abs(spent - (SELECT spent
FROM MbC
WHERE Country = GbC.Country)) < 1e-13
ORDER BY Country, spent desc;
that will get around the floating point comparison issue (I have modified long
ago my version of SQLite3 to "improve" the floating point accuracy in a number
of places by doing aggregate arithmetic using 128-bit floats to avoid this
issue).
I also have an extension builtin called feq(a, b[, u]) (also for all the
comparison operations fne/fgt/fge/flt/fle) that compares that a and b are equal
within u ulps of a where the default for u if not provided is 5 ... this avoids
having to compute the epsilon for the expected scale of the comparison
operation ...
WITH
GbC AS (
SELECT c.Country,
c.CustomerId,
SUM(i.total) as spent
FROM Customer as c
JOIN Invoice as i
ON i.CustomerId = c.CustomerId
GROUP BY c.Country, c.CustomerID
),
MbC AS (
SELECT Country,
MAX(spent) as spent
FROM GbC
GROUP BY Country
)
SELECT *
FROM GbC
WHERE feq(spent, (SELECT spent
FROM MbC
WHERE Country = GbC.Country))
ORDER BY Country, spent desc;
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[email protected]] On Behalf Of Edson Poderoso
>Sent: Monday, 3 September, 2018 07:32
>To: [email protected]
>Subject: [sqlite] Incorrect result when using WHERE with with
>correlated sub-select query
>
>Using the chinook database available at:
>https://github.com/lerocha/chinook-database
>
>The following query should return 26 rows, instead I returns 15.
>
>
>WITH GbC AS ( SELECT c.Country, c.CustomerId, SUM(i.total) spent
>FROM Customer c
>JOIN Invoice i
>ON i.CustomerId = c.CustomerId
>GROUP BY 1, 2),
>MbC AS ( SELECT Country, MAX(spent) spent
>FROM GbC GROUP BY 1)
>SELECT outerr.*
>FROM GbC outerr
>WHERE outerr.spent =
>(SELECT spent
>FROM MbC WHERE Country = outerr.Country)
>ORDER BY 1, 3 DESC
>
>
>--
>
>Edson Poderoso
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
