Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.8
-
None
-
Centos 7.3
Description
View return bad result "31457280.0000", but correct value is "30":
MariaDB [test]> CREATE TABLE `t1` (bps INT);
|
Query OK, 0 rows affected (0.01 sec) |
|
|
MariaDB [test]> INSERT INTO t1 (bps) VALUES(31457280); |
Query OK, 1 row affected (0.00 sec) |
|
|
MariaDB [test]> SELECT (t1.bps / (1024 * 1024)) AS Mbit FROM t1; |
+---------+
|
| Mbit |
|
+---------+
|
| 30.0000 | |
+---------+
|
1 row in set (0.00 sec) |
|
|
MariaDB [test]> CREATE VIEW v1 AS SELECT (t1.bps / (1024 * 1024)) AS Mbit FROM t1; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> SELECT * FROM v1;
|
+---------------+
|
| Mbit |
|
+---------------+
|
| 31457280.0000 | |
+---------------+
|
1 row in set (0.00 sec) |
Attachments
Activity
The problem was introduced in 10.2.3 by this revision:
commit 180065ebb0db78ea5c955b54c9f7997dbcba3121
|
Author: Sergei Golubchik <serg@mariadb.org>
|
Date: Sun Nov 27 19:50:10 2016 +0100
|
|
|
Item::print(): remove redundant parentheses
|
|
by introducing new Item::precedence() method and using it
|
to decide whether parentheses are required
|
The resulting view structure is this:
View: v1 |
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`bps` / 1024 * 1024 AS `Mbit` from `t1` |
character_set_client: utf8
|
collation_connection: utf8_general_ci
|
I'm experiencing issues simpler to this because of the precedence change and parentheses optimization, in my example, parentheses are being stripped.
Focus issue is with the outer parenteses being stripped b/c of optimization:
((
|
((a.left2 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 2000)) - (a_left.left2 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_left.test_datetime,p.birth_date)/365)), 2000)))
|
+ ((a.left3 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 3000)) - (a_left.left3 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_left.test_datetime,p.birth_date)/365)), 3000)))
|
+ ((a.left4 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 4000)) - (a_left.left4 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_left.test_datetime,p.birth_date)/365)), 4000)))
|
) / 3) AS 'left_age_corrected_shift', |
Example SQL View:
SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'; |
DROP VIEW IF EXISTS audio_shifts_v; |
CREATE ALGORITHM = MERGE |
DEFINER='root'@'localhost' |
SQL SECURITY INVOKER
|
VIEW audio_shifts_v AS |
SELECT
|
`a`.`audio_id`,
|
`a`.`revision_num`,
|
`a`.`doc_id`,
|
`a`.`left05`,
|
`a`.`left1`,
|
`a`.`left2`,
|
`a`.`left3`,
|
`a`.`left4`,
|
`a`.`left6`,
|
`a`.`left8`,
|
`a`.`right05`,
|
`a`.`right1`,
|
`a`.`right2`,
|
`a`.`right3`,
|
`a`.`right4`,
|
`a`.`right6`,
|
`a`.`right8`,
|
`a`.`manual`,
|
`a`.`hcp`,
|
`a`.`left_sts`,
|
`a`.`right_sts`,
|
`a`.`left_osha_recordable`,
|
`a`.`right_osha_recordable`,
|
`a`.`left_baseline`,
|
`a`.`right_baseline`,
|
`a`.`invalid`,
|
`a`.`test_reason`,
|
`a`.`comment`,
|
`a`.`outside_provider`,
|
`a`.`outside_location`,
|
`a`.`audio_model`,
|
`a`.`audio_serial`,
|
`a`.`calibration_date`,
|
/* The left and right shift are the average shift from the baseline at the 2k,3k,and 4k ranges */
|
((
|
(a.left2 - a_left.left2)
|
+ (a.left3 - a_left.left3)
|
+ (a.left4 - a_left.left4)
|
) / 3) AS 'left_shift', |
((
|
(a.right2 - a_right.right2)
|
+ (a.right3 - a_right.right3)
|
+ (a.right4 - a_right.right4)
|
) / 3) AS 'right_shift', |
/* The age corrected shift is the average shift from the baseline at the 2k, 3k, and 4k ranges after the test
|
and baseline have been adjusted for the patient's age, adjustments are found in wc_audio_age_adjustment_value */
|
((
|
((a.left2 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 2000)) - (a_left.left2 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_left.test_datetime,p.birth_date)/365)), 2000)))
|
+ ((a.left3 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 3000)) - (a_left.left3 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_left.test_datetime,p.birth_date)/365)), 3000)))
|
+ ((a.left4 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 4000)) - (a_left.left4 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_left.test_datetime,p.birth_date)/365)), 4000)))
|
) / 3) AS 'left_age_corrected_shift', |
((
|
((a.right2 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 2000)) - (a_right.right2 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_right.test_datetime,p.birth_date)/365)), 2000)))
|
+ ((a.right3 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 3000)) - (a_right.right3 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_right.test_datetime,p.birth_date)/365)), 3000)))
|
+ ((a.right4 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 4000)) - (a_right.right4 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_right.test_datetime,p.birth_date)/365)), 4000)))
|
) / 3) AS 'right_age_corrected_shift' |
FROM audio a
|
INNER JOIN documents d ON a.doc_id=d.doc_id
|
INNER JOIN patients p ON d.pat_id=p.pat_id
|
|
|
/* Using sub selects here instead of the routine wc_audio_get_baseline so that indexes can be used */
|
LEFT JOIN audio a_left ON a_left.audio_id=(SELECT a2.audio_id
|
FROM audio a2
|
INNER JOIN documents d2 ON a2.doc_id=d2.doc_id
|
WHERE d.pat_id=d2.pat_id AND a2.test_datetime < a.test_datetime AND a2.left_baseline='Yes' |
ORDER BY a2.test_datetime DESC
|
LIMIT 1)
|
LEFT JOIN audio a_right ON a_right.audio_id=(SELECT a3.audio_id
|
FROM audio a3
|
INNER JOIN documents d3 ON a3.doc_id=d3.doc_id
|
WHERE d.pat_id=d3.pat_id AND a3.test_datetime < a.test_datetime AND a3.right_baseline='Yes' |
ORDER BY a3.test_datetime DESC |
LIMIT 1)
|
Sorry, forgot to update the issue. It was fixed in 10.2.9 with this commit
elenst is it 10.2 only? not in earlier versions?