Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.7
-
None
-
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
-
-
- Closed
-
-
-
- Closed
-
Activity
Kentoku, please review my fix for the related problems, including the additional problem that you observed above, in commit 4b6dccc on my branch.
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.
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`