| Bug #83475 | filter in select query works incorrectly | ||
|---|---|---|---|
| Submitted: | 21 Oct 2016 8:17 | Modified: | 26 Oct 2016 8:26 |
| Reporter: | Alexey Lilichenko | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S2 (Serious) |
| Version: | 5.6.32 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | filter, SELECT, stored function | ||
[25 Oct 2016 12:53]
Andrii Nikitin
It is certainly possible that some problem exists, but it may be difficult to verify it without reproducible test case. So maybe you can e.g. send structure and content of involved tables? Or at least please confirm output of select V.I_VAL, V.I_DICT from DICT_ELEM_VALS V where V.I_DICT_ELEM = 41 and V.I_FIELD = 66; select V.I_VAL, V.I_DICT from DICT_ELEM_VALS V where V.I_DICT_ELEM = 41 and V.I_FIELD = 67;
[25 Oct 2016 13:25]
Alexey Lilichenko
Below is structure and data of involved tables. I hope it will be helpful. Thanks. CREATE TABLE IF NOT EXISTS `DICT_ELEM_VALS` ( `I_COMPANY` bigint(20) NOT NULL COMMENT 'company id', `I_DICT` bigint(20) NOT NULL COMMENT 'dictionary id', `I_DICT_ELEM` bigint(20) NOT NULL COMMENT 'dictionary element id (unique in system)', `I_FIELD` bigint(20) NOT NULL COMMENT 'dictionary id', `I_VAL` bigint(20) DEFAULT NULL COMMENT 'int value', `F_VAL` double DEFAULT NULL COMMENT 'float value' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='dictionary elements data (string values stored in LABELS_USER)'; INSERT INTO `DICT_ELEM_VALS` (`I_COMPANY`, `I_DICT`, `I_DICT_ELEM`, `I_FIELD`, `I_VAL`, `F_VAL`) VALUES (1, 4, 23, 53, 4, NULL), (1, 4, 24, 53, 8, NULL), (1, 6, 41, 66, 1, NULL), (1, 6, 43, 66, 1, NULL), (1, 6, 42, 67, 1, NULL), (1, 6, 43, 67, 1, NULL); ALTER TABLE `DICT_ELEM_VALS` ADD PRIMARY KEY (`I_FIELD`,`I_DICT_ELEM`), ADD KEY `K_DELV_I_COMPANY` (`I_COMPANY`), ADD KEY `K_DELV_I_DICT` (`I_DICT`), ADD KEY `K_DELV_I_FIELD` (`I_FIELD`), ADD KEY `K_DELV_I_DICT_ELEM` (`I_DICT_ELEM`); ALTER TABLE `DICT_ELEM_VALS` ADD CONSTRAINT `FK_DELV_I_COMPANY` FOREIGN KEY (`I_COMPANY`) REFERENCES `COMPANIES` (`I_ID`), ADD CONSTRAINT `FK_DELV_I_DICT` FOREIGN KEY (`I_DICT`) REFERENCES `DICTIONARIES` (`I_ID`), ADD CONSTRAINT `FK_DELV_I_DICT_ELEM` FOREIGN KEY (`I_DICT_ELEM`) REFERENCES `DICT_ELEMS` (`I_ID`), ADD CONSTRAINT `FK_DELV_I_FIELD` FOREIGN KEY (`I_FIELD`) REFERENCES `DICT_FIELDS` (`I_ID`); ---------------- CREATE TABLE IF NOT EXISTS `DICT_ELEMS` ( `I_ID` bigint(20) NOT NULL COMMENT 'dictionary element id (unique in system)', `I_CHILD_NUM_ACT` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'active child elems', `I_CHILD_NUM_TOTAL` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'total child elems', `I_COMPANY` bigint(20) NOT NULL COMMENT 'company id', `I_DICT` bigint(20) NOT NULL COMMENT 'dictionary id', `I_EXPORTED` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'is elem exported', `I_NESTING_LEVEL` tinyint(4) NOT NULL DEFAULT '-1' COMMENT 'elem nesting level', `I_PARENT_ID` bigint(20) DEFAULT NULL COMMENT 'parent element', `I_STATUS` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1-active; 2-inactive; 3-right', `I_STATUS_PREV` tinyint(4) DEFAULT NULL COMMENT 'used to restore', `I_SYNC_ID` bigint(20) DEFAULT '0' COMMENT 'id of element in source sync system', `I_SYNC_CHECKED` tinyint(4) NOT NULL DEFAULT '1', `S_CODE` varchar(30) CHARACTER SET ascii DEFAULT NULL COMMENT 'comment', `S_PICTURE` varchar(50) CHARACTER SET ascii DEFAULT NULL COMMENT 'picture basename', `S_PICTURE_ICON` varchar(50) CHARACTER SET ascii DEFAULT NULL COMMENT 'picture basename' ) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='dictionary elements'; INSERT INTO `DICT_ELEMS` (`I_ID`, `I_CHILD_NUM_ACT`, `I_CHILD_NUM_TOTAL`, `I_COMPANY`, `I_DICT`, `I_EXPORTED`, `I_NESTING_LEVEL`, `I_PARENT_ID`, `I_STATUS`, `I_STATUS_PREV`, `I_SYNC_ID`, `I_SYNC_CHECKED`, `S_CODE`, `S_PICTURE`, `S_PICTURE_ICON`) VALUES (1, 0, 0, 1, 1, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (4, 0, 0, 1, 2, 0, 0, NULL, 1, NULL, 0, 1, 'GIRLS_4', NULL, NULL), (8, 0, 0, 2, 3, 0, 0, NULL, 1, NULL, 4, 1, 'GIRLS_4', NULL, NULL), (15, 0, 0, 1, 2, 0, 0, NULL, 1, NULL, 0, 1, 'GIRLS_15', NULL, NULL), (22, 0, 0, 2, 3, 0, 0, NULL, 1, NULL, 15, 1, 'GIRLS_15', NULL, NULL), (23, 0, 0, 1, 4, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (24, 0, 0, 1, 4, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (25, 0, 0, 1, 2, 0, 0, NULL, 1, NULL, 0, 1, 'GIRLS_25', NULL, NULL), (26, 0, 0, 2, 3, 0, 0, NULL, 1, NULL, 25, 1, 'GIRLS_25', NULL, NULL), (27, 4, 4, 1, 5, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (28, 5, 5, 1, 5, 0, 1, 27, 1, NULL, 0, 1, NULL, NULL, NULL), (29, 0, 0, 1, 5, 0, 1, 27, 1, NULL, 0, 1, NULL, NULL, NULL), (30, 0, 0, 1, 5, 0, 1, 27, 1, NULL, 0, 1, NULL, NULL, NULL), (31, 0, 0, 1, 5, 0, 1, 27, 1, NULL, 0, 1, NULL, NULL, NULL), (32, 2, 2, 1, 5, 0, 2, 28, 1, NULL, 0, 1, NULL, NULL, NULL), (33, 0, 0, 1, 5, 0, 2, 28, 1, NULL, 0, 1, NULL, NULL, NULL), (34, 0, 0, 1, 5, 0, 2, 28, 1, NULL, 0, 1, NULL, NULL, NULL), (35, 2, 2, 1, 5, 0, 2, 28, 1, NULL, 0, 1, NULL, NULL, NULL), (36, 0, 0, 1, 5, 0, 2, 28, 1, NULL, 0, 1, NULL, NULL, NULL), (37, 0, 0, 1, 5, 0, 3, 32, 1, NULL, 0, 1, NULL, NULL, NULL), (38, 0, 0, 1, 5, 0, 3, 32, 1, NULL, 0, 1, NULL, NULL, NULL), (39, 0, 0, 1, 5, 0, 3, 35, 1, NULL, 0, 1, NULL, NULL, NULL), (40, 0, 0, 1, 5, 0, 3, 35, 1, NULL, 0, 1, NULL, NULL, NULL), (41, 0, 0, 1, 6, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (42, 0, 0, 1, 6, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (43, 0, 0, 1, 6, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (44, 0, 0, 1, 6, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL); ALTER TABLE `DICT_ELEMS` ADD PRIMARY KEY (`I_ID`), ADD KEY `K_DELS_I_COMPANY` (`I_COMPANY`), ADD KEY `K_DELS_I_DICT` (`I_DICT`), ADD KEY `K_DELS_I_STATUS` (`I_STATUS`), ADD KEY `K_DELS_I_PARENT_ID` (`I_PARENT_ID`), ADD KEY `K_DELS_I_STATUS_PREV` (`I_STATUS_PREV`), ADD KEY `K_DELS_I_SYNC_ID` (`I_SYNC_ID`), ADD KEY `K_DELS_I_SYNC_CHECKED` (`I_SYNC_CHECKED`), ADD KEY `K_DELS_S_CODE` (`S_CODE`); ALTER TABLE `DICT_ELEMS` MODIFY `I_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'dictionary element id (unique in system)',AUTO_INCREMENT=45; ALTER TABLE `DICT_ELEMS` ADD CONSTRAINT `FK_DELS_I_COMPANY` FOREIGN KEY (`I_COMPANY`) REFERENCES `COMPANIES` (`I_ID`), ADD CONSTRAINT `FK_DELS_I_DICT` FOREIGN KEY (`I_DICT`) REFERENCES `DICTIONARIES` (`I_ID`), ADD CONSTRAINT `FK_DELS_I_PARENT_ID` FOREIGN KEY (`I_PARENT_ID`) REFERENCES `DICT_ELEMS` (`I_ID`);
[26 Oct 2016 8:22]
Andrii Nikitin
simplified test case
Attachment: bug83475.sql (application/octet-stream, text), 6.68 KiB.
[26 Oct 2016 8:26]
Andrii Nikitin
Indeed the problem looks strange. Number of newlines in query changes result. Verified with test case from attached file bug83475.txt : two SELECT queries at the end of script are identical, but produce different number of rows: 1 vs 2 . Reproduced in 5.6.32 and 5.7.17
[26 Oct 2016 9:29]
Andrii Nikitin
Posted by developer:
It looks that it is optimizer's bug: EXPLAIN EXTENDED is lacking select#3 completely:
mysql> explain extended select
-> 1
->
-> from DICT_ELEMS E
->
-> where E.I_DICT = '6'
-> and (SB_VAL_CB( ( select V.I_VAL
-> from DICT_ELEM_VALS V
-> where V.I_DICT_ELEM = E.I_ID
-> and V.I_DICT = E.I_DICT
-> and V.I_FIELD = 66
-> )
->
->
-> ) = 1 and SB_VAL_CB( ( select V.I_VAL
-> from DICT_ELEM_VALS V
-> where V.I_DICT_ELEM = E.I_ID
-> and V.I_DICT = E.I_DICT
-> and V.I_FIELD = 67
-> )
-> ) = 1);
+----+--------------------+-------+--------+---------------------------------------------------------+---------------+---------+-----------------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+--------+---------------------------------------------------------+---------------+---------+-----------------+------+----------+--------------------------+
| 1 | PRIMARY | E | ref | K_DELS_I_DICT | K_DELS_I_DICT | 8 | const | 4 | 100.00 | Using where; Using index |
| 3 | DEPENDENT SUBQUERY | V | eq_ref | PRIMARY,K_DELV_I_DICT,K_DELV_I_FIELD,K_DELV_I_DICT_ELEM | PRIMARY | 16 | const,d1.E.I_ID | 1 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | V | eq_ref | PRIMARY,K_DELV_I_DICT,K_DELV_I_FIELD,K_DELV_I_DICT_ELEM | PRIMARY | 16 | const,d1.E.I_ID | 1 | 100.00 | Using where |
+----+--------------------+-------+--------+---------------------------------------------------------+---------------+---------+-----------------+------+----------+--------------------------+
3 rows in set, 5 warnings (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'd1.E.I_ID' of SELECT #2 was resolved in SELECT #1 |
| Note | 1276 | Field or reference 'd1.E.I_DICT' of SELECT #2 was resolved in SELECT #1 |
| Note | 1276 | Field or reference 'd1.E.I_ID' of SELECT #3 was resolved in SELECT #1 |
| Note | 1276 | Field or reference 'd1.E.I_DICT' of SELECT #3 was resolved in SELECT #1 |
| Note | 1003 | /* select#1 */ select 1 AS `1` from `d1`.`dict_elems` `e` where ((`d1`.`e`.`I_DICT` = 6) and (`SB_VAL_CB`((/* select#2 */ select `d1`.`v`.`I_VAL` from `d1`.`dict_elem_vals` `v` where ((`d1`.`v`.`I_FIELD` = 66) and (`d1`.`v`.`I_DICT_ELEM` = `d1`.`e`.`I_ID`) and (`d1`.`v`.`I_DICT` = `d1`.`e`.`I_DICT`)))) = 1)) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Description: Problem is: 1) I have stored function SB_VAL_CB(), that returns 0 or 1 2) SQL select I_ID, SB_VAL_CB(A) CB1, SB_VAL_CB(B) CB2, ( SB_VAL_CB(A) = 1 and SB_VAL_CB(B) = 1) CB_RESULT from ... where ( SB_VAL_CB(A) = 1 and SB_VAL_CB(B) = 1); gives results ------------------------------ I_ID | CB1 | CB2 | CB_RESULT ------------------------------ 41 | 1 | 0 | 0 <----- why record with CB_RESULT=0 exists? in filter is said that CB_RESULT must be 1! 43 | 1 | 1 | 1 ------------------------------ How to repeat: Below is code of stored function SB_VAL_CB() and full SQL, that I actually have. All columns used is SQL and subquries are integers ================================================================================ create function SB_VAL_CB( P_VAL bigint(20) ) returns bigint(20) deterministic /* function body */ begin /* not checked */ if( P_VAL is NULL or P_VAL = 0 ) then return 0; /* checked */ else return 1; end if; end; ================================================================================ select E.I_ID, SB_VAL_CB( ( select V.I_VAL from DICT_ELEM_VALS V where V.I_DICT_ELEM = E.I_ID and V.I_DICT = E.I_DICT and V.I_FIELD = 66 ) ) CB1, SB_VAL_CB( ( select V.I_VAL from DICT_ELEM_VALS V where V.I_DICT_ELEM = E.I_ID and V.I_DICT = E.I_DICT and V.I_FIELD = 67 ) ) CB2 , (SB_VAL_CB( ( select V.I_VAL from DICT_ELEM_VALS V where V.I_DICT_ELEM = E.I_ID and V.I_DICT = E.I_DICT and V.I_FIELD = 66 ) ) = 1 and SB_VAL_CB( ( select V.I_VAL from DICT_ELEM_VALS V where V.I_DICT_ELEM = E.I_ID and V.I_DICT = E.I_DICT and V.I_FIELD = 67 ) ) = 1) CB_RESULT from DICT_ELEMS E where E.I_DICT = '6' and (SB_VAL_CB( ( select V.I_VAL from DICT_ELEM_VALS V where V.I_DICT_ELEM = E.I_ID and V.I_DICT = E.I_DICT and V.I_FIELD = 66 ) ) = 1 and SB_VAL_CB( ( select V.I_VAL from DICT_ELEM_VALS V where V.I_DICT_ELEM = E.I_ID and V.I_DICT = E.I_DICT and V.I_FIELD = 67 ) ) = 1); ------------------------------ I_ID | CB1 | CB2 | CB_RESULT ------------------------------ 41 | 1 | 0 | 0 <----- why record with CB_RESULT=0 exists? in filter is said that CB_RESULT must be 1! 43 | 1 | 1 | 1 ------------------------------ ================================================================================ Suggested fix: SQL select I_ID, SB_VAL_CB(A) CB1, SB_VAL_CB(B) CB2, ( SB_VAL_CB(A) = 1 and SB_VAL_CB(B) = 1) CB_RESULT from ... where ( SB_VAL_CB(A) = 1 and SB_VAL_CB(B) = 1); gives results ------------------------------ I_ID | CB1 | CB2 | CB_RESULT ------------------------------ 43 | 1 | 1 | 1 <--- in result only records with CB_RESULT=1 ------------------------------