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 <j...@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/', 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.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 :::: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql