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

Spider Creates Query With Non-Existent Function

    XMLWordPrintable

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16878 Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17139 Spider crash when selecting literal

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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