MySQL :: Re: Order by with group by, different results from different versions of mysql server

New Topic

Re: Order by with group by, different results from different versions of mysql server

Posted by: Lars Gustafsson
Date: August 26, 2011 02:50PM

locally:

CREATE TABLE `publisher_keyword_bids` (
  `publisher_keyword_bid_id` int(10) NOT NULL AUTO_INCREMENT,
  `ad_group_id` int(10) unsigned NOT NULL,
  `publisher_id` tinyint(3) unsigned NOT NULL,
  `ad_group_keyword_id` int(10) unsigned NOT NULL,
  `max_cpc` mediumint(9) unsigned NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`publisher_keyword_bid_id`),
  KEY `INDEX_AD_GROUP_ID_PUBLISHER_ID_AD_GROUP_KEYWORD_ID` (`ad_group_id`,`publisher_id`,`ad_group_keyword_id`),
  CONSTRAINT `FK_PUBLISHER_KEYWORD_BIDS_PUBLISHER_KEYWORDS` FOREIGN KEY (`ad_group_id`, `publisher_id`,
`ad_group_keyword_id`) REFERENCES `publisher_keywords` (`ad_group_id`, `publisher_id`, `ad_group_keyword_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=462884 DEFAULT CHARSET=utf8 |

server:

CREATE TABLE `publisher_keyword_bids` (
 `publisher_keyword_bid_id` int(10) NOT NULL AUTO_INCREMENT,
 `ad_group_id` int(10) unsigned NOT NULL,
 `publisher_id` tinyint(3) unsigned NOT NULL,
 `ad_group_keyword_id` int(10) unsigned NOT NULL,
 `max_cpc` mediumint(9) unsigned NOT NULL,
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`publisher_keyword_bid_id`),
 KEY `INDEX_AD_GROUP_ID_PUBLISHER_ID_AD_GROUP_KEYWORD_ID` (`ad_group_id`,`publisher_id`,`ad_group_keyword_id`),
 CONSTRAINT `FK_PUBLISHER_KEYWORD_BIDS_PUBLISHER_KEYWORDS` FOREIGN KEY (`ad_group_id`, `publisher_id`, `ad_group_keyword_id`) REFERENCES `publisher_keywords` (`ad_group_id`, `publisher_id`, `ad_group_keyword_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=473918 DEFAULT CHARSET=utf8

I use this query to se that all 36 rows exists in both servers:

SELECT * FROM `publisher_keyword_bids` WHERE `publisher_id` = 79 AND `created` > '2011-08-08 07:00:00' AND `created` < '2011-08-08 13:00:00' order by created desc;

From PhpMyAdmin at the production server:
Ändra == Edit Radera == Delete
(36 rows)

publisher_keyword_bid_id 	ad_group_id 	publisher_id 	ad_group_keyword_id 	max_cpc 	created 
	Ändra 	Radera 	453807 	35299 	79 	3626909 	1400 	2011-08-08 12:41:56
	Ändra 	Radera 	453806 	35299 	79 	3626924 	1200 	2011-08-08 12:41:19
	Ändra 	Radera 	453805 	35299 	79 	3626908 	1200 	2011-08-08 12:41:00
	Ändra 	Radera 	453804 	35299 	79 	3626906 	1000 	2011-08-08 12:40:12
	Ändra 	Radera 	453803 	35299 	79 	3626904 	1600 	2011-08-08 12:39:51
	Ändra 	Radera 	453802 	35345 	79 	3630400 	2000 	2011-08-08 12:37:20
	Ändra 	Radera 	453801 	35345 	79 	3630407 	1600 	2011-08-08 12:36:57
	Ändra 	Radera 	453800 	35345 	79 	3630417 	1500 	2011-08-08 12:36:15
	Ändra 	Radera 	453799 	35345 	79 	3630402 	2000 	2011-08-08 12:36:02
	Ändra 	Radera 	453798 	35298 	79 	3626383 	2400 	2011-08-08 12:35:21
	Ändra 	Radera 	453797 	35298 	79 	3626368 	2400 	2011-08-08 12:34:41
	Ändra 	Radera 	453796 	35298 	79 	3626367 	2400 	2011-08-08 12:33:05
	Ändra 	Radera 	453795 	35298 	79 	3626522 	1500 	2011-08-08 12:32:34
	Ändra 	Radera 	453794 	35298 	79 	3626368 	2000 	2011-08-08 12:31:43
	Ändra 	Radera 	453793 	35298 	79 	3626367 	2000 	2011-08-08 12:31:11
	Ändra 	Radera 	453792 	35298 	79 	3626367 	2000 	2011-08-08 12:31:03
	Ändra 	Radera 	453791 	35292 	79 	3623696 	900 	2011-08-08 12:22:34
	Ändra 	Radera 	453790 	35292 	79 	3623749 	1500 	2011-08-08 12:22:11
	Ändra 	Radera 	453789 	35292 	79 	3623959 	1400 	2011-08-08 12:21:16
	Ändra 	Radera 	453788 	35292 	79 	3623913 	1200 	2011-08-08 12:21:07
	Ändra 	Radera 	453787 	35292 	79 	3624830 	1400 	2011-08-08 12:19:15
	Ändra 	Radera 	453786 	35292 	79 	3623917 	1500 	2011-08-08 12:18:56
	Ändra 	Radera 	453785 	35292 	79 	3623668 	1100 	2011-08-08 12:18:46
	Ändra 	Radera 	453784 	35292 	79 	3623679 	1300 	2011-08-08 12:18:11
	Ändra 	Radera 	453783 	35292 	79 	3623680 	1800 	2011-08-08 12:17:33
	Ändra 	Radera 	453782 	35295 	79 	3625087 	2500 	2011-08-08 12:15:51
	Ändra 	Radera 	453777 	35295 	79 	3625025 	3500 	2011-08-08 12:13:25
	Ändra 	Radera 	453748 	35295 	79 	3625238 	2000 	2011-08-08 12:11:28
	Ändra 	Radera 	453746 	35295 	79 	3625061 	2000 	2011-08-08 12:10:42
	Ändra 	Radera 	453745 	35295 	79 	3625039 	2500 	2011-08-08 12:08:32

From my local PC (a dump of the production server):
(36 rows)

453807	35299	79	3626909	1400	2011-08-08 12:41:56
453806	35299	79	3626924	1200	2011-08-08 12:41:19
453805	35299	79	3626908	1200	2011-08-08 12:41:00
453804	35299	79	3626906	1000	2011-08-08 12:40:12
453803	35299	79	3626904	1600	2011-08-08 12:39:51
453802	35345	79	3630400	2000	2011-08-08 12:37:20
453801	35345	79	3630407	1600	2011-08-08 12:36:57
453800	35345	79	3630417	1500	2011-08-08 12:36:15
453799	35345	79	3630402	2000	2011-08-08 12:36:02
453798	35298	79	3626383	2400	2011-08-08 12:35:21
453797	35298	79	3626368	2400	2011-08-08 12:34:41
453796	35298	79	3626367	2400	2011-08-08 12:33:05
453795	35298	79	3626522	1500	2011-08-08 12:32:34
453794	35298	79	3626368	2000	2011-08-08 12:31:43
453793	35298	79	3626367	2000	2011-08-08 12:31:11
453792	35298	79	3626367	2000	2011-08-08 12:31:03
453791	35292	79	3623696	900	2011-08-08 12:22:34
453790	35292	79	3623749	1500	2011-08-08 12:22:11
453789	35292	79	3623959	1400	2011-08-08 12:21:16
453788	35292	79	3623913	1200	2011-08-08 12:21:07
453787	35292	79	3624830	1400	2011-08-08 12:19:15
453786	35292	79	3623917	1500	2011-08-08 12:18:56
453785	35292	79	3623668	1100	2011-08-08 12:18:46
453784	35292	79	3623679	1300	2011-08-08 12:18:11
453783	35292	79	3623680	1800	2011-08-08 12:17:33
453782	35295	79	3625087	2500	2011-08-08 12:15:51
453777	35295	79	3625025	3500	2011-08-08 12:13:25
453748	35295	79	3625238	2000	2011-08-08 12:11:28
453746	35295	79	3625061	2000	2011-08-08 12:10:42
453745	35295	79	3625039	2500	2011-08-08 12:08:32
453744	35295	79	3625029	3000	2011-08-08 12:07:25
453743	35295	79	3625026	3000	2011-08-08 12:07:06
453742	35296	79	3626001	2500	2011-08-08 12:05:58
453741	35296	79	3626002	2500	2011-08-08 12:05:23
453740	35296	79	3625999	1600	2011-08-08 12:00:57
453739	35296	79	3625999	1600	2011-08-08 12:00:55

The 32 desired rows: (locally on my PC)

453807	35299	79	3626909	1400	2011-08-08 12:41:56
453806	35299	79	3626924	1200	2011-08-08 12:41:19
453805	35299	79	3626908	1200	2011-08-08 12:41:00
453804	35299	79	3626906	1000	2011-08-08 12:40:12
453803	35299	79	3626904	1600	2011-08-08 12:39:51
453802	35345	79	3630400	2000	2011-08-08 12:37:20
453801	35345	79	3630407	1600	2011-08-08 12:36:57
453800	35345	79	3630417	1500	2011-08-08 12:36:15
453799	35345	79	3630402	2000	2011-08-08 12:36:02
453798	35298	79	3626383	2400	2011-08-08 12:35:21
453797	35298	79	3626368	2400	2011-08-08 12:34:41
453796	35298	79	3626367	2400	2011-08-08 12:33:05
453795	35298	79	3626522	1500	2011-08-08 12:32:34
453791	35292	79	3623696	900	2011-08-08 12:22:34
453790	35292	79	3623749	1500	2011-08-08 12:22:11
453789	35292	79	3623959	1400	2011-08-08 12:21:16
453788	35292	79	3623913	1200	2011-08-08 12:21:07
453787	35292	79	3624830	1400	2011-08-08 12:19:15
453786	35292	79	3623917	1500	2011-08-08 12:18:56
453785	35292	79	3623668	1100	2011-08-08 12:18:46
453784	35292	79	3623679	1300	2011-08-08 12:18:11
453783	35292	79	3623680	1800	2011-08-08 12:17:33
453782	35295	79	3625087	2500	2011-08-08 12:15:51
453777	35295	79	3625025	3500	2011-08-08 12:13:25
453748	35295	79	3625238	2000	2011-08-08 12:11:28
453746	35295	79	3625061	2000	2011-08-08 12:10:42
453745	35295	79	3625039	2500	2011-08-08 12:08:32
453744	35295	79	3625029	3000	2011-08-08 12:07:25
453743	35295	79	3625026	3000	2011-08-08 12:07:06
453742	35296	79	3626001	2500	2011-08-08 12:05:58
453741	35296	79	3626002	2500	2011-08-08 12:05:23
453740	35296	79	3625999	1600	2011-08-08 12:00:57

The 11 rows at the production server:

	Ändra 	Radera 	453802 	35345 	79 	3630400 	2000 	2011-08-08 12:37:20
	Ändra 	Radera 	453801 	35345 	79 	3630407 	1600 	2011-08-08 12:36:57
	Ändra 	Radera 	453800 	35345 	79 	3630417 	1500 	2011-08-08 12:36:15
	Ändra 	Radera 	453799 	35345 	79 	3630402 	2000 	2011-08-08 12:36:02
	Ändra 	Radera 	453795 	35298 	79 	3626522 	1500 	2011-08-08 12:32:34
	Ändra 	Radera 	453791 	35292 	79 	3623696 	900 	2011-08-08 12:22:34
	Ändra 	Radera 	453748 	35295 	79 	3625238 	2000 	2011-08-08 12:11:28
	Ändra 	Radera 	453742 	35296 	79 	3626001 	2500 	2011-08-08 12:05:58
	Ändra 	Radera 	453741 	35296 	79 	3626002 	2500 	2011-08-08 12:05:23
	Ändra 	Radera 	453740 	35296 	79 	3625999 	1600 	2011-08-08 12:00:57

Navigate: Previous Message• Next Message

Options: Reply• Quote

Subject

Views

Written By

Posted

Order by with group by, different results from different versions of mysql server

3721

Lars Gustafsson

August 26, 2011 09:19AM

Re: Order by with group by, different results from different versions of mysql server

1723

Lars Gustafsson

August 26, 2011 02:50PM

Re: Order by with group by, different results from different versions of mysql server

1953

mmtstraintimings.in ,

September 04, 2011 08:45AM

Re: Order by with group by, different results from different versions of mysql server

1267

phoebe D

September 07, 2011 02:07AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.