| Bug #79097 | Grouping unix_timestamp(Time) div 7200 miscalculation | ||
|---|---|---|---|
| Submitted: | 3 Nov 2015 15:04 | Modified: | 11 Nov 2015 19:38 |
| Reporter: | Tim Unger | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.6.26 | OS: | Windows (Win64) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | div, GROUP BY, unix_timestamp | ||
[3 Nov 2015 15:05]
Tim Unger
reported picture with corrections
Attachment: 03-11-2015 15-38-29_2.jpg (image/jpeg, text), 203.56 KiB.
[3 Nov 2015 15:49]
MySQL Verification Team
Please provide a complete test case with data insert and print the result with the command client. Thanks.
[4 Nov 2015 15:23]
Tim Unger
This is the Datapool.
Attachment: Daten.zip (application/x-zip-compressed, text), 362.25 KiB.
[4 Nov 2015 15:24]
Tim Unger
This is the final Query.
Attachment: Query.sql (application/octet-stream, text), 1.09 KiB.
[4 Nov 2015 15:24]
Tim Unger
This is the Create Statement.
Attachment: Create.sql (application/octet-stream, text), 458 bytes.
[4 Nov 2015 15:25]
Tim Unger
This is the MySQL Command Line Result.
Attachment: MySQL Command Line Result.jpg (image/jpeg, text), 102.25 KiB.
[4 Nov 2015 15:25]
Tim Unger
This is the MySQL Workbench Result.
Attachment: MySQL Workbench Result.jpg (image/jpeg, text), 321.91 KiB.
[5 Nov 2015 15:59]
MySQL Verification Team
Hi !!
Thank you for your bug report. However, as you were already asked, we need a complete test case. That means that, beside CREATE TABLE statement, that you have already provided, we need a full mysqldump output. We need this in order to be able to reproduce your problem. Otherwise, our hands are fully tied.
Also, as you have been properly informed, we need all info from you in the pure text format, no pictures.
As a further info, grouping by expressions is not allowed by SQL standards, but we still support it.
You should also supply us with information about what do you think is wrong with the result(s) of these queries.
Beside full mysqldump output, we would like you to change your query, in order to make behavior fully visible. Change your query like this:
SELECT ID_Asset, 'H2' as 'ZE', date_format(min(Zeit),'%Y-%m-%d %H:%i:00') as Zeit,
substring_index(group_concat(`Open` order by Zeit asc),',',1) as `Open`,
max(High) as High,
min(Low) as Low,
substring_index(group_concat(`Close` order by Zeit desc),',',1) as `Close`,
by unix_timestamp(Zeit) DIV 7200 as xx
FROM FXM.APP_Analyse_CS
group by xx
union all
SELECT ID_Asset, 'H3' as 'ZE', date_format(min(Zeit),'%Y-%m-%d %H:%i:00') as Zeit,
substring_index(group_concat(`Open` order by Zeit asc),',',1) as `Open`,
max(High) as High,
min(Low) as Low,
substring_index(group_concat(`Close` order by Zeit desc),',',1) as `Close`,
by unix_timestamp(Zeit) DIV 10800
FROM FXM.APP_Analyse_CS
group by xx
union all
SELECT ID_Asset, 'H4' as 'ZE', date_format(min(Zeit),'%Y-%m-%d %H:%i:00') as Zeit,
substring_index(group_concat(`Open` order by Zeit asc),',',1) as `Open`,
max(High) as High,
min(Low) as Low,
substring_index(group_concat(`Close` order by Zeit desc),',',1) as `Close`,
by unix_timestamp(Zeit) DIV 14400 as xx
FROM FXM.APP_Analyse_CS
group by xx;
Also, we would like to see the output of all the individual queries separately. Hence, we need to see the output of all 4 (four) queries.
I also repeat that we need table with all data in order to be able to reproduce the problem, if any !!!!
[5 Nov 2015 16:04]
MySQL Verification Team
I just noted that you use Zeit for two different fields in the query. Change your queries like this:
SELECT ID_Asset, 'H2' as 'ZE', date_format(min(Zeit),'%Y-%m-%d
%H:%i:00') as Datum,
substring_index(group_concat(`Open` order by Datum asc),',',1) as
`Open`,
max(High) as High,
min(Low) as Low,
substring_index(group_concat(`Close` order by Datum desc),',',1)
as `Close`,
by unix_timestamp(Zeit) DIV 7200 as xx
FROM FXM.APP_Analyse_CS
group by xx
So, you can not have several fields with the same name. As we do not know your logic, please change where query should have Datum and where Zeit.
[5 Nov 2015 16:17]
MySQL Verification Team
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > " Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.28 Source distribution PULL: 2015-OCT-24 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.6 > USE FXM Database changed mysql 5.6 > SET SQL_MODE = ONLY_FULL_GROUP_BY; Query OK, 0 rows affected (0.00 sec) mysql 5.6 > SOURCE C:/DOWN/BUGFILE/query.sql ERROR 1055 (42000): 'fxm.APP_Analyse_CS.ID_Asset' isn't in GROUP BY mysql 5.6 > exit Bye C:\dbs>57 C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > " Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.10 Source distribution PULL: 2015-OCT-24 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > USE FXM Database changed mysql 5.7 > SOURCE C:/DOWN/BUGFILE/query.sql ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fxm.APP_Analyse_CS.ID_Asset' which is not functionally dependent on c olumns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by mysql 5.7 >
[5 Nov 2015 16:49]
MySQL Verification Team
Thank you, Miguel ... It is important to note that ONLY_FULL_GROUP_BY is default in 5.7 !!!
[6 Nov 2015 12:56]
Tim Unger
This is not an SQL-mode problem as explained by Sinisa Milivojevic. And the query-update from Miguel Solorzano has the same effect in the query-statement. All source what is needed to help me is in this Bug-Report attached.
The mistake by this SQL-statement is not the statement itself. It is the result-set.
The last used SQL-statement:
SELECT 'A', ID_Asset, 'H3' as 'ZE', date_format(min(Zeit),'%Y-%m-%d %H:%i:00') as Zeit,
substring_index(group_concat(`Open` order by Zeit asc),',',1) as `Open`,
max(High) as High,
min(Low) as Low,
substring_index(group_concat(`Close` order by Zeit desc),',',1) as `Close`,
unix_timestamp(Zeit) DIV 10800 as xx
FROM FXM.APP_Analyse_CS
where Zeit between '2015-01-01 23:00:00.000' and '2015-01-02 12:00:00.000'
group by xx
union all
SELECT 'B', ID_Asset, 'H3' as 'ZE', date_format(min(Zeit),'%Y-%m-%d %H:%i:00') as Zeit,
substring_index(group_concat(`Open` order by Zeit asc),',',1) as `Open`,
max(High) as High,
min(Low) as Low,
substring_index(group_concat(`Close` order by Zeit desc),',',1) as `Close`,
'xx'
FROM FXM.APP_Analyse_CS
where Zeit between '2015-01-01 23:00:00.000' and '2015-01-02 12:00:00.000'
group by unix_timestamp(Zeit) DIV 10800, ID_Asset
The actually result is:
A 10 H3 2015-01-01 23:00:00 1.20975 1.21069 1.20779 1.20866 131495
A 10 H3 2015-01-02 01:00:00 1.20866 1.20883 1.20483 1.2058 131496
A 10 H3 2015-01-02 04:00:00 1.20579 1.20618 1.20453 1.20514 131497
A 10 H3 2015-01-02 07:00:00 1.20515 1.20689 1.20342 1.20488 131498
A 10 H3 2015-01-02 10:00:00 1.20463 1.20592 1.20384 1.20495 131499
B 10 H3 2015-01-01 23:00:00 1.20975 1.21069 1.20779 1.20866 xx
B 10 H3 2015-01-02 01:00:00 1.20866 1.20883 1.20483 1.2058 xx
B 10 H3 2015-01-02 04:00:00 1.20579 1.20618 1.20453 1.20514 xx
B 10 H3 2015-01-02 07:00:00 1.20515 1.20689 1.20342 1.20488 xx
B 10 H3 2015-01-02 10:00:00 1.20463 1.20592 1.20384 1.20495 xx
The correct result for Column "Zeit" have to be this values:
2015-01-01 23:00:00
2015-01-02 02:00:00
2015-01-02 05:00:00
2015-01-02 08:00:00
2015-01-02 11:00:00
2015-01-01 23:00:00
2015-01-02 02:00:00
2015-01-02 05:00:00
2015-01-02 08:00:00
2015-01-02 11:00:00
It takes miscalculations effekt on every day --> next day time step. So I need help from your developers crew, because it must be an internal calculation problem.
Thank you very much for helping me.
[6 Nov 2015 14:45]
MySQL Verification Team
HI,
You have not sent us all data that we have asked you to. We asked for a full output to be sent in text format, from MySQL CLI. We asked for so many other things, but you have not sent us any.
Next, I have made a mistake when I was re-writing your queries, so each SELECT node in the UNION should look like this:
SELECT ID_Asset, 'H2' as 'ZE', date_format(min(Zeit),'%Y-%m-%d %H:%i:00') as Datum,
substring_index(group_concat(`Open` order by Datum asc),',',1) as `Open`,
max(High) as High,
min(Low) as Low,
unix_timestamp(Zeit) DIV 7200 as xx,
substring_index(group_concat(`Close` order by Zeit desc),',',1) as `Close`
FROM FXM.APP_Analyse_CS
where `Status` = 1
group by xx;
Replace 7200 by appropriate value for each node in the UNION.
Also , send us the output for one of the nodes with a smallest result set without GROUP BY clause !!!
You are not liable for a help from developers. However, once we have all data as we have asked for, then if all is sent properly, we will attempt to reproduce the bug. If we conclude that this is a bug, we shall verify it, which will then move the bug into the queue for developer's analysis.
[7 Nov 2015 9:15]
Tim Unger
Hi, a mysqldump is very unpractical because the amount of data is to much und to big for your 3 MB upload restrictions. All files i attached to this bug report list is absolute enough to repeat this issue and to solve this problem. However. Good luck. I am out.
[9 Nov 2015 14:50]
MySQL Verification Team
Hi Mr. Unger, You seem to miss the meaning of the verb to repeat. It means that we run the same SELECT commands as you do, on the same data that you query, and see the same behavior. How can we run the query without data and get the same behavior as you do ???? So, we shall wait for your data. You do not have to send them all. It is sufficient if you can repeat the same behavior on the subset of your data, then send us the subset of your data. Meanwhile, we can make one more attempt. Can you send us the output from the following queries: First query: --------------------------------------------- SELECT ID_Asset, Zeit FROM FXM.APP_Analyse_CS; Next: SELECT ID_Asset, unix_timestamp(Zeit) FROM FXM.APP_Analyse_CS; Next: SELECT ID_Asset, unix_timestamp(Zeit) DIV 7200 as xx FROM FXM.APP_Analyse_CS; and last: SELECT ID_Asset, max(High) as High, min(Low) as Low, unix_timestamp(Zeit) DIV 7200 as xx FROM FXM.APP_Analyse_CS group by xx; ------------------------------------------------ Please, let us have the output in the text format (no graphics !!!!) like from mysql CLI. Also, do not change the titles of the columns. Thanks in advance.
[10 Nov 2015 9:23]
Tim Unger
Hi Mr. Milivojevic, the needed data you asked for can you find in my post from [4 Nov 15:23] in compressed form by zip. The relevant query can you find in my post from [4 Nov 15:24]. The create statement can you find in my post from [4 Nov 15:24]. And the result from the command line can you find in my post from [4 Nov 15:25] as a picture. Sorry, for the picture. It is my first time to report a bug to mysql. Thank you.
[11 Nov 2015 19:38]
MySQL Verification Team
I ran the query on your table and your data and do not see any bug. /mysql bug -e "SELECT ID_Asset, max(High) as High, min(Low) as Low, Zeit, date_format(min(Zeit),'%Y-%m-%d %H:%i:00') as Zeit, unix_timestamp(Zeit), unix_timestamp(Zeit) DIV 7200 as xx FROM APP_Analyse_CS group by xx" +----------+---------+---------+-------------------------+---------------------+----------------------+--------+ | ID_Asset | High | Low | Zeit | Zeit | unix_timestamp(Zeit) | xx | +----------+---------+---------+-------------------------+---------------------+----------------------+--------+ | 10 | 1.13597 | 1.13508 | 2015-10-18 23:00:07.440 | 2015-10-18 23:00:00 | 1445198407.440 | 200722 | | 10 | 1.13745 | 1.13515 | 2015-10-19 01:00:10.249 | 2015-10-19 01:00:00 | 1445205610.249 | 200723 | | 10 | 1.13773 | 1.13595 | 2015-10-19 03:00:01.592 | 2015-10-19 03:00:00 | 1445212801.592 | 200724 | | 10 | 1.13644 | 1.13533 | 2015-10-19 05:00:00.835 | 2015-10-19 05:00:00 | 1445220000.835 | 200725 | | 10 | 1.13782 | 1.13614 | 2015-10-19 07:00:00.722 | 2015-10-19 07:00:00 | 1445227200.722 | 200726 | | 10 | 1.13742 | 1.13387 | 2015-10-19 09:00:00.116 | 2015-10-19 09:00:00 | 1445234400.116 | 200727 | | 10 | 1.13592 | 1.13222 | 2015-10-19 11:00:00.316 | 2015-10-19 11:00:00 | 1445241600.316 | 200728 | +----------+---------+---------+-------------------------+---------------------+----------------------+--------+ New column Zeit is indeed the minimum of the old column Zeit. You are probably making the error that my colleague pointed out. You have an undefined output for a GROUP BY that you devised. Simply, there are columns, like ID_Asset, then new alias Zeit for which MySQL, and all SQL standard, consider as undefined if not a part of GROUP BY. You are also failing a rule of keeping all identifiers distinct, so new Zeit alias is conflicting with a column Zeit. Hence, for all these reasons your query can not work as you wished and you must completely rewrite it.
Description: Hello, the following query produce a mistake in certain result set as seen in the reported picture. The Query: SELECT ID_Asset, 'H2' as 'ZE', date_format(min(Zeit),'%Y-%m-%d %H:%i:00') as Zeit, substring_index(group_concat(`Open` order by Zeit asc),',',1) as `Open`, max(High) as High, min(Low) as Low, substring_index(group_concat(`Close` order by Zeit desc),',',1) as `Close` FROM FXM.APP_Analyse_CS where `Status` = 1 group by unix_timestamp(Zeit) DIV 7200 How to repeat: Create the following table and fill it with data who start at 23:00 a clock one day and end on the next day at in this case at 09:00 a clock. CREATE TABLE `APP_Analyse_CS` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `ID_Asset` int(10) unsigned DEFAULT NULL, `Zeit` timestamp(3) NULL DEFAULT NULL, `Open` double unsigned DEFAULT NULL, `High` double unsigned DEFAULT NULL, `Low` double unsigned DEFAULT NULL, `Close` double unsigned DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `ID_UNIQUE` (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;