View Revisions: Issue #1009
| Summary | 0001009: Market hub filtering option has a variety of failures and bugs |
|---|---|
| Revision | 2021-01-17 21:34 by Successor |
| Steps To Reproduce |
Open market hubs page Set sorting to player sell volume Set average over days to anything Set number of rows to anything Watch SQL fireworks fly |
| Revision | 2021-01-16 23:07 by anonymous |
| Steps To Reproduce |
Open market hubs page Set sorting to player sell volume Set average over days to anything Set number of rows to anything Watch SQL fireworks fly |
| Revision | 2021-01-17 21:34 by Successor |
| Description |
Error: Expression 0000002 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a4e_eve_dump.mapSolarSystems.solarSystemName' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT a.solSysID, a.solarSystemName, a.security, a.regionID, a.regionName, a.pc_buy_orders, a.pc_buy_volume, a.pc_sell_orders, a.pc_sell_volume, a.typeName FROM ( SELECT COALESCE(`mapSolarSystems`.`solarSystemID`, esiSys.`solarSystemID`) AS solSysID, COALESCE(`mapSolarSystems`.`solarSystemName`, esiSys.`solarSystemName`) AS solarSystemName, COALESCE(`mapSolarSystems`.`security`, esiSys.`security`) AS security, COALESCE(`mapRegions`.`regionID`, esiSys.`regionID`) AS regionID, COALESCE(`mapRegions`.`regionName`, esiRegion.`regionName`) AS regionName, SUM(`MarketStationMetricsHistory`.`pc_buy_orders`) AS pc_buy_orders, SUM(`MarketStationMetricsHistory`.`pc_buy_volume`) AS pc_buy_volume, SUM(`MarketStationMetricsHistory`.`pc_sell_orders`) AS pc_sell_orders, SUM(`MarketStationMetricsHistory`.`pc_sell_volume`) AS pc_sell_volume, `invTypes`.`typeName` FROM `a4e_shared`.`MarketStationMetricsHistory` LEFT JOIN `a4e_eve_dump`.`staStations` ON `staStations`.`stationID` = `MarketStationMetricsHistory`.`stationID` LEFT JOIN `a4e_eve_dump`.`mapSolarSystems` ON `mapSolarSystems`.`solarSystemID` = `staStations`.`solarSystemID` LEFT JOIN `a4e_eve_dump`.`mapRegions` ON `mapRegions`.`regionID` = `mapSolarSystems`.`regionID` LEFT JOIN `a4e_shared`.`EsiStructureDetails` ON `EsiStructureDetails`.`structureID` = `MarketStationMetricsHistory`.`stationID` LEFT JOIN `a4e_eve_dump`.`mapSolarSystems` AS esiSys ON esiSys.`solarSystemID` = `EsiStructureDetails`.`solarSystemID` LEFT JOIN `a4e_eve_dump`.`mapRegions` AS esiRegion ON esiRegion.`regionID` = esiSys.`regionID` LEFT JOIN a4e_eve_dump.`invTypes` ON `invTypes`.`typeID` = `EsiStructureDetails`.`typeID` WHERE `MarketStationMetricsHistory`.`date` > '2021-01-02' GROUP BY date, solSysID ) a GROUP BY solSysID ORDER BY pc_buy_volume DESC LIMIT 50 |
| Revision | 2021-01-16 23:07 by anonymous |
| Description |
Error: Expression 0000002 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a4e_eve_dump.mapSolarSystems.solarSystemName' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT a.solSysID, a.solarSystemName, a.security, a.regionID, a.regionName, a.pc_buy_orders, a.pc_buy_volume, a.pc_sell_orders, a.pc_sell_volume, a.typeName FROM ( SELECT COALESCE(`mapSolarSystems`.`solarSystemID`, esiSys.`solarSystemID`) AS solSysID, COALESCE(`mapSolarSystems`.`solarSystemName`, esiSys.`solarSystemName`) AS solarSystemName, COALESCE(`mapSolarSystems`.`security`, esiSys.`security`) AS security, COALESCE(`mapRegions`.`regionID`, esiSys.`regionID`) AS regionID, COALESCE(`mapRegions`.`regionName`, esiRegion.`regionName`) AS regionName, SUM(`MarketStationMetricsHistory`.`pc_buy_orders`) AS pc_buy_orders, SUM(`MarketStationMetricsHistory`.`pc_buy_volume`) AS pc_buy_volume, SUM(`MarketStationMetricsHistory`.`pc_sell_orders`) AS pc_sell_orders, SUM(`MarketStationMetricsHistory`.`pc_sell_volume`) AS pc_sell_volume, `invTypes`.`typeName` FROM `a4e_shared`.`MarketStationMetricsHistory` LEFT JOIN `a4e_eve_dump`.`staStations` ON `staStations`.`stationID` = `MarketStationMetricsHistory`.`stationID` LEFT JOIN `a4e_eve_dump`.`mapSolarSystems` ON `mapSolarSystems`.`solarSystemID` = `staStations`.`solarSystemID` LEFT JOIN `a4e_eve_dump`.`mapRegions` ON `mapRegions`.`regionID` = `mapSolarSystems`.`regionID` LEFT JOIN `a4e_shared`.`EsiStructureDetails` ON `EsiStructureDetails`.`structureID` = `MarketStationMetricsHistory`.`stationID` LEFT JOIN `a4e_eve_dump`.`mapSolarSystems` AS esiSys ON esiSys.`solarSystemID` = `EsiStructureDetails`.`solarSystemID` LEFT JOIN `a4e_eve_dump`.`mapRegions` AS esiRegion ON esiRegion.`regionID` = esiSys.`regionID` LEFT JOIN a4e_eve_dump.`invTypes` ON `invTypes`.`typeID` = `EsiStructureDetails`.`typeID` WHERE `MarketStationMetricsHistory`.`date` > '2021-01-02' GROUP BY date, solSysID ) a GROUP BY solSysID ORDER BY pc_buy_volume DESC LIMIT 50 |
