Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16398

Spider Creates Query With Non-Existent Function

    Details

      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

            Activity

              People

              • Assignee:
                jacob-mathew Jacob Mathew (Inactive)
                Reporter:
                adamh Adam Hilton
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: