The plot thickens...
I noticed that I have similar queries that work as expected. The difference appears to be
that every query that is broken uses " WITH ROLLUP", and removing this makes
them behave as expected.
Is this a known bug? Should I submit it as such?
If someone would be so kind as to point me to the bug system, I'll crawl around in
there to see if it's a known problem.
Jan
Begin forwarded message:
From: Jan Steinman <Jan(a)Bytesmiths.com>
Date: 3 November 2013 18:35:47 PST
MySQL 5.0.92-log
I'm trying to form a clickable link using CONCAT, but the link as displayed points to
the NEXT row's URL, not the one from the same row as the other data displayed!
Is there something I don't understand about this?
Below is the query. "{{{1}}}" is replaced by a year, like "2013".
The second column is the problem one. When the table is displayed, the link in the
`Product` field points to the NEXT SEQUENTIAL product row! In other words, if you click on
the link for "garlic," you'll get the page for "gherkins."
Live example is at:
http://www.EcoReality.org/wiki/2013_harvest
If you hover over the link in the `Product` column, you can clearly see that the page at
the link is not the same as that in the `ID` column, but is in fact the same `ID` as the
next sequential row. I am so confused.
SELECT
harvest.product AS ID,
CONCAT('<a
href="http://www.EcoReality.org/wiki/Product/'uct/',
s_product.ID, '">', COALESCE(s_product.name, 'TOTAL:'),
'</a>') AS `Product`,
FORMAT(sum(harvest.quantity), 3) AS `<a
href="http://www.EcoReality.org/wiki/Harvest">Harvest</a>…;Harvest</a>`,
harvest.units AS Units,
CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value,
prices.market_type AS `R-W`,
COUNT(*) AS Harvests,
DATE(MIN(harvest.date)) AS Begin,
DATE(MAX(harvest.date)) AS End
FROM
s_product_harvest harvest
INNER JOIN
s_product on s_product.ID = harvest.product AND
s_product.units = harvest.units
LEFT OUTER JOIN
s_product_market_prices prices ON prices.product_ID = harvest.product AND
prices.units = harvest.units AND
year(prices.price_date) = year(harvest.date)
WHERE
year(harvest.date) = {{{1}}}
GROUP BY
s_product.name WITH ROLLUP
:::: Some days I wonder if it might not be better to culturally engineer humans to enjoy
small scale garden farming than to genetically engineer weeds to save large scale
agribusiness. -- Gene Logsdon
:::: Jan Steinman, EcoReality Co-op ::::
:::: The competition for grain between the wealthy car drivers of the world and the
poorest people who are trying to survive is a moral issue that we should not ignore. The
continued increase in biofuels production will result in a continued decrease in food
availability, which we could someday consider to be a crime against humanity. -- Pat
Murphy
:::: Jan Steinman, EcoReality Co-op ::::