[MDEV-16398] Spider Creates Query With Non-Existent Function Created: 2018-06-05  Updated: 2018-09-18  Resolved: 2018-08-13

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.3.7
Fix Version/s: 10.3.10, 10.4.0

Type: Bug Priority: Major
Reporter: Adam Hilton Assignee: Jacob Mathew (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

Windows Server 2012 R2 64-bit

Issue Links:
Relates
relates to MDEV-16878 Functions ADDTIME and SUBTIME get wro... Closed
relates to MDEV-17139 Spider crash when selecting literal Closed

 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`



 Comments   
Comment by Jacob Mathew (Inactive) [ 2018-08-01 ]

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

Comment by Alexander Barkov [ 2018-08-02 ]

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!

Comment by Jacob Mathew (Inactive) [ 2018-08-02 ]

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.

Comment by Jacob Mathew (Inactive) [ 2018-08-03 ]

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

Comment by Kentoku Shiba (Inactive) [ 2018-08-06 ]

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`

Comment by Jacob Mathew (Inactive) [ 2018-08-09 ]

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

Comment by Jacob Mathew (Inactive) [ 2018-08-13 ]

Fix is pushed to 10.3.

Comment by Jacob Mathew (Inactive) [ 2018-08-13 ]

Fix is pushed to 10.4.

Comment by Jacob Mathew (Inactive) [ 2018-09-18 ]

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.

Generated at Thu Feb 08 08:28:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.