[MDEV-16398] Spider Creates Query With Non-Existent Function - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3.7
Fix Version/s: 10.3.10, 10.4.0
Component/s: Storage Engine - Spider
Labels:
None
Environment:
Windows Server 2012 R2 64-bit

Description

First time submitting a bug so please let me know what additional information I should include.

Query with a GROUP BY on a timestamp datatype executed against a Spider table results in a query that contains a non-existent function on the remote end. Remote tables are partitioned by the SiteID field. SiteID=43 resolves on tr_00043 on the remote server.

Query errors out with: `SQL Error (1305): FUNCTION trendrecord.add_time does not exist`

Executed Query on Main: No inclusion of a function called 'add_time'

SELECT PointID, DATE_SUB(DATE_FORMAT(TIMESTAMP(datevalue,timevalue), '%Y-%m-%d %H:%i:%s'), INTERVAL IF(MOD(UNIX_TIMESTAMP(timevalue),30)>(30/2),-(30- MOD(UNIX_TIMESTAMP(timevalue),30)), MOD(UNIX_TIMESTAMP(timevalue),30)) SECOND) AS 'ts', ROUND(NumericValue,0) AS 'value'

		FROM tr

		WHERE SiteID=43 AND PointID IN(88236,88240,490361,88234,88235,88239,490360,88242,1971427,1971428) AND datevalue = '2018-06-04' AND timevalue >= '02:36:08'

GROUP BY PointID, UNIX_TIMESTAMP(TIMESTAMP(datevalue,timevalue)) DIV 30

Resultant Query on Remote Database Table: Function Called 'add_time' appears

SELECT 0,`SiteID`,`PointID`,`datevalue`,`timevalue`,`NumericValue`

FROM `trendrecord`.`tr_00043`

WHERE `PointID` = 88234 AND `datevalue` = '2018-06-04' AND `timevalue` >= '02:36:08' AND ((`SiteID` = 43) AND (`datevalue` = DATE'2018-06-04') AND (`PointID` IN(88236, 88240, 490361, 88234, 88235, 88239, 490360, 88242, 1971427, 1971428)) AND (`timevalue` >= '02:36:08'))

GROUP BY `PointID`,((UNIX_TIMESTAMP((add_time(`datevalue`, `timevalue`)))) DIV 30)

ORDER BY `timevalue`,`SiteID`) UNION ALL(

SELECT 1,`SiteID`,`PointID`,`datevalue`,`timevalue`,`NumericValue`

FROM `trendrecord`.`tr_00043`

WHERE `PointID` = 88235 AND `datevalue` = '2018-06-04' AND `timevalue` >= '02:36:08' AND ((`SiteID` = 43) AND (`datevalue` = DATE'2018-06-04') AND (`PointID` IN(88236, 88240, 490361, 88234, 88235, 88239, 490360, 88242, 1971427, 1971428)) AND (`timevalue` >= '02:36:08'))

GROUP BY `PointID`,((UNIX_TIMESTAMP((add_time(`datevalue`, `timevalue`)))) DIV 30)

ORDER BY `timevalue`,`SiteID`) UNION ALL(

SELECT 2,`SiteID`,`PointID`,`datevalue`,`timevalue`,`NumericValue`

FROM `trendrecord`.`tr_00043`

WHERE `PointID` = 88236 AND `datevalue` = '2018-06-04' AND `timevalue` >= '02:36:08' AND ((`SiteID` = 43) AND (`datevalue` = DATE'2018-06-04') AND (`PointID` IN(88236, 88240, 490361, 88234, 88235, 88239, 490360, 88242, 1971427, 1971428)) AND (`timevalue` >= '02:36:08'))

GROUP BY `PointID`,((UNIX_TIMESTAMP((add_time(`datevalue`, `timevalue`)))) DIV 30)

ORDER BY `timevalue`,`SiteID`) UNION ALL(

SELECT 3,`SiteID`,`PointID`,`datevalue`,`timevalue`,`NumericValue`

FROM `trendrecord`.`tr_00043`

WHERE `PointID` = 88239 AND `datevalue` = '2018-06-04' AND `timevalue` >= '02:36:08' AND ((`SiteID` = 43) AND (`datevalue` = DATE'2018-06-04') AND (`PointID` IN(88236, 88240, 490361, 88234, 88235, 88239, 490360, 88242, 1971427, 1971428)) AND (`timevalue` >= '02:36:08'))

GROUP BY `PointID`,((UNIX_TIMESTAMP((add_time(`datevalue`, `timevalue`)))) DIV 30)

ORDER BY `timevalue`,`SiteID`) UNION ALL(

SELECT 4,`SiteID`,`PointID`,`datevalue`,`timevalue`,`NumericValue`

FROM `trendrecord`.`tr_00043`

WHERE `PointID` = 88240 AND `datevalue` = '2018-06-04' AND `timevalue` >= '02:36:08' AND ((`SiteID` = 43) AND (`datevalue` = DATE'2018-06-04') AND (`PointID` IN(88236, 88240, 490361, 88234, 88235, 88239, 490360, 88242, 1971427, 1971428)) AND (`timevalue` >= '02:36:08'))

GROUP BY `PointID`,((UNIX_TIMESTAMP((add_time(`datevalue`, `timevalue`)))) DIV 30)

ORDER BY `timevalue`,`SiteID`) UNION ALL(

SELECT 5,`SiteID`,`PointID`,`datevalue`,`timevalue`,`NumericValue`

FROM `trendrecord`.`tr_00043`

WHERE `PointID` = 88242 AND `datevalue` = '2018-06-04' AND `timevalue` >= '02:36:08' AND ((`SiteID` = 43) AND (`datevalue` = DATE'2018-06-04') AND (`PointID` IN(88236, 88240, 490361, 88234, 88235, 88239, 490360, 88242, 1971427, 1971428)) AND (`timevalue` >= '02:36:08'))

GROUP BY `PointID`,((UNIX_TIMESTAMP((add_time(`datevalue`, `timevalue`)))) DIV 30)

ORDER BY `timevalue`,`SiteID`) UNION ALL(

SELECT 6,`SiteID`,`PointID`,`datevalue`,`timevalue`,`NumericValue`

FROM `trendrecord`.`tr_00043`

WHERE `PointID` = 490360 AND `datevalue` = '2018-06-04' AND `timevalue` >= '02:36:08' AND ((`SiteID` = 43) AND (`datevalue` = DATE'2018-06-04') AND (`PointID` IN(88236, 88240, 490361, 88234, 88235, 88239, 490360, 88242, 1971427, 1971428)) AND (`timevalue` >= '02:36:08'))

GROUP BY `PointID`,((UNIX_TIMESTAMP((add_time(`datevalue`, `timevalue`)))) DIV 30)

ORDER BY `timevalue`,`SiteID`) UNION ALL(

SELECT 7,`SiteID`,`PointID`,`datevalue`,`timevalue`,`NumericValue`

FROM `trendrecord`.`tr_00043`

WHERE `PointID` = 490361 AND `datevalue` = '2018-06-04' AND `timevalue` >= '02:36:08' AND ((`SiteID` = 43) AND (`datevalue` = DATE'2018-06-04') AND (`PointID` IN(88236, 88240, 490361, 88234, 88235, 88239, 490360, 88242, 1971427, 1971428)) AND (`timevalue` >= '02:36:08'))

GROUP BY `PointID`,((UNIX_TIMESTAMP((add_time(`datevalue`, `timevalue`)))) DIV 30)

ORDER BY `timevalue`,`SiteID`) UNION ALL(

SELECT 8,`SiteID`,`PointID`,`datevalue`,`timevalue`,`NumericValue`

FROM `trendrecord`.`tr_00043`

WHERE `PointID` = 1971427 AND `datevalue` = '2018-06-04' AND `timevalue` >= '02:36:08' AND ((`SiteID` = 43) AND (`datevalue` = DATE'2018-06-04') AND (`PointID` IN(88236, 88240, 490361, 88234, 88235, 88239, 490360, 88242, 1971427, 1971428)) AND (`timevalue` >= '02:36:08'))

GROUP BY `PointID`,((UNIX_TIMESTAMP((add_time(`datevalue`, `timevalue`)))) DIV 30)

ORDER BY `timevalue`,`SiteID`) UNION ALL(

SELECT 9,`SiteID`,`PointID`,`datevalue`,`timevalue`,`NumericValue`

FROM `trendrecord`.`tr_00043`

WHERE `PointID` = 1971428 AND `datevalue` = '2018-06-04' AND `timevalue` >= '02:36:08' AND ((`SiteID` = 43) AND (`datevalue` = DATE'2018-06-04') AND (`PointID` IN(88236, 88240, 490361, 88234, 88235, 88239, 490360, 88242, 1971427, 1971428)) AND (`timevalue` >= '02:36:08'))

GROUP BY `PointID`,((UNIX_TIMESTAMP((add_time(`datevalue`, `timevalue`)))) DIV 30)

ORDER BY `timevalue`,`SiteID`)

ORDER BY `timevalue`,`SiteID`

Attachments

Issue Links

relates to

MDEV-16878 Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer

Closed

MDEV-17139 Spider crash when selecting literal

Closed

Activity

Ascending order - Click to sort in descending order

Adam Hilton created issue - 2018-06-05 00:18

Elena Stepanova made changes - 2018-06-05 09:39

Field	Original Value	New Value
Fix Version/s		10.3 [ 22126 ]
Assignee		Jacob Mathew [ jacob-mathew ]

Jacob Mathew (Inactive) made changes - 2018-07-31 02:17

Status

Open [ 1 ]

Confirmed [ 10101 ]

Jacob Mathew (Inactive) made changes - 2018-07-31 02:17

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Jacob Mathew (Inactive) added a comment - 2018-08-01 17:40

Bar, please review my fix for this problem in commit 0f0148d on my branch.

Jacob Mathew (Inactive) added a comment - 2018-08-01 17:40 Bar, please review my fix for this problem in commit 0f0148d on my branch .

Jacob Mathew (Inactive) made changes - 2018-08-01 17:40

Assignee	Jacob Mathew [ jacob-mathew ]	Alexander Barkov [ bar ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Alexander Barkov added a comment - 2018-08-02 04:23

I don't like the idea of adding a new virtual method sql_func_name().
Can you please try to do the following:

1. Fix Item_func_add_time::func_name() to return the exact function name, "timestamp", "addtime" or "subtime".
2. Remove Item_func_add_time::print(). It won't be needed any more. The inherited version will work.

After this change, these functions should be correctly handled in the Item_func::UNKNOWN_FUNC branch in spider_db_mysql_util::open_item_func().

Also, please make sure to add mtr test!

Alexander Barkov added a comment - 2018-08-02 04:23 I don't like the idea of adding a new virtual method sql_func_name(). Can you please try to do the following: 1. Fix Item_func_add_time::func_name() to return the exact function name, "timestamp", "addtime" or "subtime". 2. Remove Item_func_add_time::print(). It won't be needed any more. The inherited version will work. After this change, these functions should be correctly handled in the Item_func::UNKNOWN_FUNC branch in spider_db_mysql_util::open_item_func(). Also, please make sure to add mtr test!

Alexander Barkov made changes - 2018-08-02 04:24

Assignee	Alexander Barkov [ bar ]	Jacob Mathew [ jacob-mathew ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexander Barkov made changes - 2018-08-02 05:28

Link

This issue relates to ~~MDEV-16878~~ [ ~~MDEV-16878~~ ]

Jacob Mathew (Inactive) made changes - 2018-08-02 23:15

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Jacob Mathew (Inactive) added a comment - 2018-08-02 23:17

After the fix for bug ~~MDEV-16878~~ was pushed to 10.3, the changes outside of Spider are no longer necessary to fix the reported problem. The remaining changes are inside Spider to fix a related problem that I observed while testing.

Jacob Mathew (Inactive) added a comment - 2018-08-02 23:17 After the fix for bug MDEV-16878 was pushed to 10.3, the changes outside of Spider are no longer necessary to fix the reported problem. The remaining changes are inside Spider to fix a related problem that I observed while testing.

Jacob Mathew (Inactive) added a comment - 2018-08-03 20:07

Kentoku, please review my fix for the related problem in commit 9b6720e on my branch.

Jacob Mathew (Inactive) added a comment - 2018-08-03 20:07 Kentoku, please review my fix for the related problem in commit 9b6720e on my branch .

Jacob Mathew (Inactive) made changes - 2018-08-03 20:07

Assignee	Jacob Mathew [ jacob-mathew ]	Kentoku [ kentoku ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Kentoku Shiba (Inactive) added a comment - 2018-08-06 14:27

It looks TIMESTAMP function didn't push down to data node like the followings. I think it should be checked about pushing down in this test.

connection child2_1;
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
argument
select `col_d`,`col_t` from `ts_test_remote`.`tbl_f`
select `col_d`,`col_t` from `ts_test_remote`.`tbl_f`
select `col_d`,`col_t` from `ts_test_remote`.`tbl_f`
select `col_d`,`col_t` from `ts_test_remote`.`tbl_f`
select `col_d`,`col_t` from `ts_test_remote`.`tbl_f`

Kentoku Shiba (Inactive) added a comment - 2018-08-06 14:27 It looks TIMESTAMP function didn't push down to data node like the followings. I think it should be checked about pushing down in this test. connection child2_1; SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; argument select `col_d`,`col_t` from `ts_test_remote`.`tbl_f` select `col_d`,`col_t` from `ts_test_remote`.`tbl_f` select `col_d`,`col_t` from `ts_test_remote`.`tbl_f` select `col_d`,`col_t` from `ts_test_remote`.`tbl_f` select `col_d`,`col_t` from `ts_test_remote`.`tbl_f`

Kentoku Shiba (Inactive) made changes - 2018-08-06 14:27

Assignee

Kentoku [ kentoku ]

Jacob Mathew [ jacob-mathew ]

Jacob Mathew (Inactive) added a comment - 2018-08-09 07:11

Kentoku, please review my fix for the related problems, including the additional problem that you observed above, in commit 4b6dccc on my branch.

Jacob Mathew (Inactive) added a comment - 2018-08-09 07:11 Kentoku, please review my fix for the related problems, including the additional problem that you observed above, in commit 4b6dccc on my branch .

Jacob Mathew (Inactive) made changes - 2018-08-09 07:11

Assignee

Jacob Mathew [ jacob-mathew ]

Kentoku [ kentoku ]

Jacob Mathew (Inactive) made changes - 2018-08-13 20:04

Assignee

Kentoku [ kentoku ]

Jacob Mathew [ jacob-mathew ]

Jacob Mathew (Inactive) made changes - 2018-08-13 20:04

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Jacob Mathew (Inactive) added a comment - 2018-08-13 20:32

Fix is pushed to 10.3.

Jacob Mathew (Inactive) added a comment - 2018-08-13 20:32 Fix is pushed to 10.3.

Jacob Mathew (Inactive) made changes - 2018-08-13 20:32

Fix Version/s		10.3.9 [ 23114 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Jacob Mathew (Inactive) added a comment - 2018-08-13 21:35

Fix is pushed to 10.4.

Jacob Mathew (Inactive) added a comment - 2018-08-13 21:35 Fix is pushed to 10.4.

Jacob Mathew (Inactive) made changes - 2018-08-30 04:09

Fix Version/s

10.4.0 [ 23115 ]

Jacob Mathew (Inactive) added a comment - 2018-09-18 00:35

The fix for bug ~~MDEV-16878~~ was included in 10.3.9. However, the other related fixes did not make it into 10.3.9; they will be released in 10.3.10.

Jacob Mathew (Inactive) added a comment - 2018-09-18 00:35 The fix for bug MDEV-16878 was included in 10.3.9. However, the other related fixes did not make it into 10.3.9; they will be released in 10.3.10.

Jacob Mathew (Inactive) made changes - 2018-09-18 00:35

Fix Version/s		10.3.10 [ 23140 ]
Fix Version/s	10.3.9 [ 23114 ]

Jacob Mathew (Inactive) made changes - 2018-09-18 00:38

Link

This issue relates to ~~MDEV-17139~~ [ ~~MDEV-17139~~ ]

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

Workflow

MariaDB v3 [ 87680 ]

MariaDB v4 [ 154475 ]

People

Assignee:: Jacob Mathew (Inactive)

Reporter:: Adam Hilton

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2018-06-05 00:18

Updated:: 2018-09-18 00:38

Resolved:: 2018-08-13 20:32

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration