[MDEV-13673] Bad result in view - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.8
Fix Version/s: 10.2.9
Component/s: Parser, Views
Labels:
None
Environment:
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

Ascending order - Click to sort in descending order

Michal Hucik created issue - 2017-08-29 22:59

Elena Stepanova made changes - 2017-08-29 23:26

Field	Original Value	New Value
Status	Open [ 1 ]	Confirmed [ 10101 ]

Sergei Golubchik made changes - 2017-08-30 07:54

Fix Version/s

10.2 [ 14601 ]

Sergei Golubchik added a comment - 2017-08-30 07:56

elenst is it 10.2 only? not in earlier versions?

Sergei Golubchik added a comment - 2017-08-30 07:56 elenst is it 10.2 only? not in earlier versions?

Elena Stepanova added a comment - 2017-08-30 10:01 - edited

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

Elena Stepanova added a comment - 2017-08-30 10:01 - edited 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

Elena Stepanova made changes - 2017-08-30 10:01

Component/s		Parser [ 10201 ]
Component/s		Views [ 10111 ]
Assignee		Sergei Golubchik [ serg ]
Description	View return bad result "31457280.0000", but correct value is "30": {code:java} 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) {code}	View return bad result "31457280.0000", but correct value is "30": {code:java} 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) {code}

Sergei Golubchik made changes - 2017-09-14 22:10

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2017-09-14 22:45

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Dave Juntgen added a comment - 2017-10-03 20:20

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)

Dave Juntgen added a comment - 2017-10-03 20:20 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)

Dave Juntgen added a comment - 2017-10-25 12:48

Bump - when will this issue be moved to in progress?

Dave Juntgen added a comment - 2017-10-25 12:48 Bump - when will this issue be moved to in progress?

Sergei Golubchik added a comment - 2017-10-25 22:18

Sorry, forgot to update the issue. It was fixed in 10.2.9 with this commit

Sergei Golubchik added a comment - 2017-10-25 22:18 Sorry, forgot to update the issue. It was fixed in 10.2.9 with this commit

Sergei Golubchik made changes - 2017-10-25 22:18

Fix Version/s		10.2.9 [ 22611 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:45

Workflow

MariaDB v3 [ 82308 ]

MariaDB v4 [ 152723 ]

People

Assignee:: Sergei Golubchik

Reporter:: Michal Hucik

Votes:: 2 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2017-08-29 22:59

Updated:: 2017-10-25 22:18

Resolved:: 2017-10-25 22:18

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server