MySQL Bugs: #78534: Different results are coming when ran a same Query in two different applications

Bug #78534	Different results are coming when ran a same Query in two different applications
Submitted:	23 Sep 2015 17:29	Modified:	23 Sep 2015 20:44
Reporter:	GaneshPandiaraj Subburajan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hello Everyone,

I ran a MySQL Query in Workbench and Toad for MySQL. Both are returning different results for a same query.

The Query is,
SELECT SortOrder,Group , Id, odate,otime,oset FROM
	(
	SELECT @RowNumber := if(a.id = @RowPrev,@RowNumber+1,1) AS SortOrder,

		@RowPrev := a.id, 

		a.group,a.id, a.odate,a.otime,as oset FROM
	(
	SELECT  
		group,id,Oodate,otime,a.oset

		FROM mytable a

		WHERE DATEDIFF(NOW(),o1date)<=0 and id in ('10088-FX8733951','10454-AA8752054','10454-BC8751015','10454-PG8743967','10566-MC8749932','10566-PG8749909','10882-AA8754000','11218-FR8749166','11218-FR8752525','11379-BC8753466','11379-FX8632206','11591-HW8675179','11749-AA8685235','10088-AR8747754')
	UNION ALL
	SELECT  
		group,id,Oodate,otime,a.oset
				FROM mytable a
				WHERE DATEDIFF(NOW(),o2date)<=0 and id in ('10088-FX8733951','10454-AA8752054','10454-BC8751015','10454-PG8743967','10566-MC8749932','10566-PG8749909','10882-AA8754000','11218-FR8749166','11218-FR8752525','11379-BC8753466','11379-FX8632206','11591-HW8675179','11749-AA8685235','10088-AR8747754')
	UNION ALL
	SELECT  
		group,id,Oodate,otime,a.oset
				FROM mytable a
				WHERE DATEDIFF(NOW(),o3date)<=0 and id in ('10088-FX8733951','10454-AA8752054','10454-BC8751015','10454-PG8743967','10566-MC8749932','10566-PG8749909','10882-AA8754000','11218-FR8749166','11218-FR8752525','11379-BC8753466','11379-FX8632206','11591-HW8675179','11749-AA8685235','10088-AR8747754')
	UNION ALL
	SELECT 
		group,id,Oodate,otime,a.oset
				FROM mytable a
				WHERE DATEDIFF(NOW(),o4date)<=0 and id in ('10088-FX8733951','10454-AA8752054','10454-BC8751015','10454-PG8743967','10566-MC8749932','10566-PG8749909','10882-AA8754000','11218-FR8749166','11218-FR8752525','11379-BC8753466','11379-FX8632206','11591-HW8675179','11749-AA8685235','10088-AR8747754')
	) a
	WHERE PERIOD_DIFF(EXTRACT(DAY FROM NOW()),a.odate)<=0
	ORDER BY a.id
	,a.odate
	,a.otime
	) subQuery,
	(SELECT @RowNumber := 1, @RowPrev := '') AS TableRow;

The output is,
In Workbench,
SortOrder	Group	Id	Odate	Otime	Oset
1	299	10088-ar8747754	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10088-fx8733951	1443225600000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10454-aa8752054	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	10454-bc8751015	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	10454-pg8743967	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	10566-mc8749932	1443225600000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10566-pg8749909	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10882-aa8754000	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	11218-fr8749166	1443052800000	06:00:00 PM-07:00:00 PM	UTC -05:00
1	299	11218-fr8752525	1444003200000	05:00:00 PM-06:00:00 PM	UTC -05:00
1	299	11379-bc8753466	1443225600000	12:00:00 PM-05:00:00 PM	UTC -05:00
1	299	11379-fx8632206	1443225600000	01:00:00 PM-06:00:00 PM	UTC -05:00
1	299	11591-hw8675179	1443225600000	09:00:00 AM-06:00:00 PM	UTC -05:00
1	299	11749-aa8685235	1443225600000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10088-fx8733951	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10454-aa8752054	1443312000000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10454-bc8751015	1443312000000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10454-pg8743967	1443312000000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10566-mc8749932	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10566-pg8749909	1443916800000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10882-aa8754000	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	11218-fr8749166	1444176000000	03:00:00 PM-04:00:00 PM	UTC -05:00
2	299	11218-fr8752525	1445212800000	03:00:00 PM-04:00:00 PM	UTC -05:00
2	299	11379-bc8753466	1443312000000	12:00:00 PM-05:00:00 PM	UTC -05:00
2	299	11379-fx8632206	1443312000000	02:00:00 PM-06:00:00 PM	UTC -05:00
2	299	11591-hw8675179	1443312000000	09:00:00 AM-06:00:00 PM	UTC -05:00
2	299	11749-aa8685235	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
3	299	10566-mc8749932	1443830400000	01:00:00 PM-04:00:00 PM	UTC -05:00
4	299	10566-mc8749932	1443916800000	01:00:00 PM-04:00:00 PM	UTC -05:00

In Toad for MySQL,
SortOrder	group	id	odate	otime	oset
1	299	10088-ar8747754	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10088-fx8733951	1443225600000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10088-fx8733951	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10454-aa8752054	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10454-aa8752054	1443312000000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	10454-bc8751015	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10454-bc8751015	1443312000000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	10454-pg8743967	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10454-pg8743967	1443312000000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	10566-mc8749932	1443225600000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10566-mc8749932	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
3	299	10566-mc8749932	1443830400000	01:00:00 PM-04:00:00 PM	UTC -05:00
4	299	10566-mc8749932	1443916800000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10566-pg8749909	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10566-pg8749909	1443916800000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10882-aa8754000	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10882-aa8754000	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	11218-fr8749166	1443052800000	06:00:00 PM-07:00:00 PM	UTC -05:00
2	299	11218-fr8749166	1444176000000	03:00:00 PM-04:00:00 PM	UTC -05:00
1	299	11218-fr8752525	1444003200000	05:00:00 PM-06:00:00 PM	UTC -05:00
2	299	11218-fr8752525	1445212800000	03:00:00 PM-04:00:00 PM	UTC -05:00
1	299	11379-bc8753466	1443225600000	12:00:00 PM-05:00:00 PM	UTC -05:00
2	299	11379-bc8753466	1443312000000	12:00:00 PM-05:00:00 PM	UTC -05:00
1	299	11379-fx8632206	1443225600000	01:00:00 PM-06:00:00 PM	UTC -05:00
2	299	11379-fx8632206	1443312000000	02:00:00 PM-06:00:00 PM	UTC -05:00
1	299	11591-hw8675179	1443225600000	09:00:00 AM-06:00:00 PM	UTC -05:00
2	299	11591-hw8675179	1443312000000	09:00:00 AM-06:00:00 PM	UTC -05:00
1	299	11749-aa8685235	1443225600000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	11749-aa8685235	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00

The Sortorder is differs in both. Could you please help me to resolve this?

Thanks,
Ganesh

How to repeat:
Hello Everyone,

I ran a MySQL Query in Workbench and Toad for MySQL. Both are returning different results for a same query.

The Query is,
SELECT SortOrder,Group , Id, odate,otime,oset FROM
	(
	SELECT @RowNumber := if(a.id = @RowPrev,@RowNumber+1,1) AS SortOrder,

		@RowPrev := a.id, 

		a.group,a.id, a.odate,a.otime,as oset FROM
	(
	SELECT  
		group,id,Oodate,otime,a.oset

		FROM mytable a

		WHERE DATEDIFF(NOW(),o1date)<=0 and id in ('10088-FX8733951','10454-AA8752054','10454-BC8751015','10454-PG8743967','10566-MC8749932','10566-PG8749909','10882-AA8754000','11218-FR8749166','11218-FR8752525','11379-BC8753466','11379-FX8632206','11591-HW8675179','11749-AA8685235','10088-AR8747754')
	UNION ALL
	SELECT  
		group,id,Oodate,otime,a.oset
				FROM mytable a
				WHERE DATEDIFF(NOW(),o2date)<=0 and id in ('10088-FX8733951','10454-AA8752054','10454-BC8751015','10454-PG8743967','10566-MC8749932','10566-PG8749909','10882-AA8754000','11218-FR8749166','11218-FR8752525','11379-BC8753466','11379-FX8632206','11591-HW8675179','11749-AA8685235','10088-AR8747754')
	UNION ALL
	SELECT  
		group,id,Oodate,otime,a.oset
				FROM mytable a
				WHERE DATEDIFF(NOW(),o3date)<=0 and id in ('10088-FX8733951','10454-AA8752054','10454-BC8751015','10454-PG8743967','10566-MC8749932','10566-PG8749909','10882-AA8754000','11218-FR8749166','11218-FR8752525','11379-BC8753466','11379-FX8632206','11591-HW8675179','11749-AA8685235','10088-AR8747754')
	UNION ALL
	SELECT 
		group,id,Oodate,otime,a.oset
				FROM mytable a
				WHERE DATEDIFF(NOW(),o4date)<=0 and id in ('10088-FX8733951','10454-AA8752054','10454-BC8751015','10454-PG8743967','10566-MC8749932','10566-PG8749909','10882-AA8754000','11218-FR8749166','11218-FR8752525','11379-BC8753466','11379-FX8632206','11591-HW8675179','11749-AA8685235','10088-AR8747754')
	) a
	WHERE PERIOD_DIFF(EXTRACT(DAY FROM NOW()),a.odate)<=0
	ORDER BY a.id
	,a.odate
	,a.otime
	) subQuery,
	(SELECT @RowNumber := 1, @RowPrev := '') AS TableRow;

The output is,
In Workbench,
SortOrder	Group	Id	Odate	Otime	Oset
1	299	10088-ar8747754	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10088-fx8733951	1443225600000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10454-aa8752054	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	10454-bc8751015	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	10454-pg8743967	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	10566-mc8749932	1443225600000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10566-pg8749909	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10882-aa8754000	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	11218-fr8749166	1443052800000	06:00:00 PM-07:00:00 PM	UTC -05:00
1	299	11218-fr8752525	1444003200000	05:00:00 PM-06:00:00 PM	UTC -05:00
1	299	11379-bc8753466	1443225600000	12:00:00 PM-05:00:00 PM	UTC -05:00
1	299	11379-fx8632206	1443225600000	01:00:00 PM-06:00:00 PM	UTC -05:00
1	299	11591-hw8675179	1443225600000	09:00:00 AM-06:00:00 PM	UTC -05:00
1	299	11749-aa8685235	1443225600000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10088-fx8733951	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10454-aa8752054	1443312000000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10454-bc8751015	1443312000000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10454-pg8743967	1443312000000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10566-mc8749932	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10566-pg8749909	1443916800000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10882-aa8754000	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	11218-fr8749166	1444176000000	03:00:00 PM-04:00:00 PM	UTC -05:00
2	299	11218-fr8752525	1445212800000	03:00:00 PM-04:00:00 PM	UTC -05:00
2	299	11379-bc8753466	1443312000000	12:00:00 PM-05:00:00 PM	UTC -05:00
2	299	11379-fx8632206	1443312000000	02:00:00 PM-06:00:00 PM	UTC -05:00
2	299	11591-hw8675179	1443312000000	09:00:00 AM-06:00:00 PM	UTC -05:00
2	299	11749-aa8685235	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
3	299	10566-mc8749932	1443830400000	01:00:00 PM-04:00:00 PM	UTC -05:00
4	299	10566-mc8749932	1443916800000	01:00:00 PM-04:00:00 PM	UTC -05:00

In Toad for MySQL,
SortOrder	group	id	odate	otime	oset
1	299	10088-ar8747754	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10088-fx8733951	1443225600000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10088-fx8733951	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10454-aa8752054	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10454-aa8752054	1443312000000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	10454-bc8751015	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10454-bc8751015	1443312000000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	10454-pg8743967	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10454-pg8743967	1443312000000	01:00:00 PM-03:00:00 PM	UTC -05:00
1	299	10566-mc8749932	1443225600000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10566-mc8749932	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
3	299	10566-mc8749932	1443830400000	01:00:00 PM-04:00:00 PM	UTC -05:00
4	299	10566-mc8749932	1443916800000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10566-pg8749909	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	10566-pg8749909	1443916800000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	10882-aa8754000	1443225600000	01:00:00 PM-03:00:00 PM	UTC -05:00
2	299	10882-aa8754000	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00
1	299	11218-fr8749166	1443052800000	06:00:00 PM-07:00:00 PM	UTC -05:00
2	299	11218-fr8749166	1444176000000	03:00:00 PM-04:00:00 PM	UTC -05:00
1	299	11218-fr8752525	1444003200000	05:00:00 PM-06:00:00 PM	UTC -05:00
2	299	11218-fr8752525	1445212800000	03:00:00 PM-04:00:00 PM	UTC -05:00
1	299	11379-bc8753466	1443225600000	12:00:00 PM-05:00:00 PM	UTC -05:00
2	299	11379-bc8753466	1443312000000	12:00:00 PM-05:00:00 PM	UTC -05:00
1	299	11379-fx8632206	1443225600000	01:00:00 PM-06:00:00 PM	UTC -05:00
2	299	11379-fx8632206	1443312000000	02:00:00 PM-06:00:00 PM	UTC -05:00
1	299	11591-hw8675179	1443225600000	09:00:00 AM-06:00:00 PM	UTC -05:00
2	299	11591-hw8675179	1443312000000	09:00:00 AM-06:00:00 PM	UTC -05:00
1	299	11749-aa8685235	1443225600000	01:00:00 PM-04:00:00 PM	UTC -05:00
2	299	11749-aa8685235	1443312000000	01:00:00 PM-04:00:00 PM	UTC -05:00

The Sortorder is differs in both. Could you please help me to resolve this?

Thanks,
Ganesh

Please check against the mysql command client. Thanks.