the result of query mysql output "if exists" in store procedure with "select exist&qu-CodePudding

I want to check if this record is already on the list.

l have 3 query. 1 procedure "if exists". 1 procedure "if (select count(*) from tbl ) > 0". 1 "select exists()".

but 2 procedures have the same result. different from the "select exists" query

query procedure "if exists"

CREATE DEFINER=`admin`@`localhost` PROCEDURE `ins_upd_price_cust`(IN buyerid2 VARCHAR(100),IN kdbrg VARCHAR(100),IN satuan VARCHAR(100),IN sellerid2 VARCHAR(100))
BEGIN
    IF EXISTS(SELECT * FROM tblHrgCustDtl WHERE BuyerID2=buyerid2 AND KdBrg=kdbrg AND Satuan=satuan and SellerID2=sellerid2) THEN
            select CONCAT("'",buyerid2,"'");
    ELSE
            select CONCAT("'",sellerid2,"'");
    END IF;

END

query procedure if (select count(*) from tbl ) > 0

CREATE DEFINER=`admin`@`localhost` PROCEDURE `test_proc`(IN buyerid2 VARCHAR(100),IN kdbrg VARCHAR(100),IN satuan VARCHAR(100),IN sellerid2 VARCHAR(100))
BEGIN
    IF (SELECT count(*) FROM tblHrgCustDtl WHERE BuyerID2=buyerid2 AND KdBrg=kdbrg AND Satuan=satuan and SellerID2=sellerid2)>0 THEN
    SELECT '1';
ELSE
    SELECT '0';
END IF;

END

and query "select exists()"

SELECT EXISTS(SELECT * FROM tblHrgCustDtl WHERE BuyerID2='10730a6aca58' AND KdBrg='A0050' AND Satuan='PCK' and SellerID2='0ec6926b09c8');

the following are all records in the table

the following is the table & data structure

CREATE TABLE `tblHrgCustDtl`  (
  `BuyerID2` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Line` int NOT NULL AUTO_INCREMENT,
  `KdBrg` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `Tgl` datetime NULL DEFAULT NULL,
  `Qty` decimal(18, 2) NULL DEFAULT NULL,
  `Satuan` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `Hrg` decimal(19, 4) NOT NULL,
  `HpUnit` decimal(18, 2) NULL DEFAULT NULL,
  `HpTotal` decimal(18, 2) NULL DEFAULT NULL,
  `Berat` decimal(19, 4) NULL DEFAULT NULL,
  `PrsDisc1` decimal(19, 4) NULL DEFAULT NULL,
  `PrsDisc2` decimal(19, 4) NULL DEFAULT NULL,
  `PrsDisc3` decimal(19, 4) NULL DEFAULT NULL,
  `Protected` tinyint NULL DEFAULT NULL,
  `HrgIncPpn` decimal(19, 4) NOT NULL,
  `SellerID2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`BuyerID2`, `Line`, `SellerID2`) USING BTREE,
  UNIQUE INDEX `dor`(`BuyerID2` ASC, `Line` ASC) USING BTREE,
  UNIQUE INDEX `dur`(`Line` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 102776 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tblHrgCustDtl
-- ----------------------------
INSERT INTO `tblHrgCustDtl` VALUES ('10730a6aca58', 102779, 'C011M', '2022-08-11 08:07:31', NULL, 'PCK', 0.0000, NULL, NULL, NULL, 0.0000, 0.0000, 0.0000, 0, 0.0000, '0ec6926b09c8');
INSERT INTO `tblHrgCustDtl` VALUES ('10730a6aca58', 102781, 'C011M', '2022-08-11 06:29:01', NULL, 'PCK', 116300.0000, NULL, NULL, NULL, 0.0000, 0.0000, 0.0000, 0, 129093.0000, '0ec6926b09c8');
INSERT INTO `tblHrgCustDtl` VALUES ('10730a6aca58', 102784, 'A0023', '2022-08-11 06:38:02', NULL, 'KG', 267800.0000, NULL, NULL, NULL, 0.0000, 0.0000, 0.0000, 0, 267800.0000, '0ec6926b09c8');
INSERT INTO `tblHrgCustDtl` VALUES ('10730a6aca58', 102786, 'A004M', '2022-08-11 06:43:08', NULL, 'BTL', 126818.1800, NULL, NULL, NULL, 0.0000, 0.0000, 0.0000, 0, 140768.1800, '0ec6926b09c8');
INSERT INTO `tblHrgCustDtl` VALUES ('c1fb99dd2637', 102780, 'N789M', '2022-08-11 03:30:59', NULL, 'PCK', 0.0000, NULL, NULL, NULL, 0.0000, 0.0000, 0.0000, 0, 0.0000, '0ec6926b09c8');
INSERT INTO `tblHrgCustDtl` VALUES ('c1fb99dd2637', 102782, 'C011M', '2022-08-11 06:29:01', NULL, 'PCK', 116300.0000, NULL, NULL, NULL, 0.0000, 0.0000, 0.0000, 0, 129093.0000, '0ec6926b09c8');
INSERT INTO `tblHrgCustDtl` VALUES ('c1fb99dd2637', 102785, 'A0023', '2022-08-11 06:38:02', NULL, 'KG', 267800.0000, NULL, NULL, NULL, 0.0000, 0.0000, 0.0000, 0, 267800.0000, '0ec6926b09c8');
INSERT INTO `tblHrgCustDtl` VALUES ('c1fb99dd2637', 102787, 'A004M', '2022-08-11 06:43:08', NULL, 'BTL', 126818.1800, NULL, NULL, NULL, 0.0000, 0.0000, 0.0000, 0, 140768.1800, '0ec6926b09c8');
INSERT INTO `tblHrgCustDtl` VALUES ('eb5ce263769d', 102783, 'C021M', '2022-08-11 06:33:42', NULL, 'PCK', 0.0000, NULL, NULL, NULL, 0.0000, 0.0000, 0.0000, 0, 0.0000, '0ec6926b09c8');

SET FOREIGN_KEY_CHECKS = 1;

the results of each query

"if exists"

"if (select count(*) from tble )>0"

"select exist"

in the list table I have checked there is no kdbrg=A0050. but 2 procedures produce different output with "select exists". is there something wrong in using the procedure ?

CodePudding user response：

Your variable names are the same as your unqualified column names (please note that they are both case insensitive). In this situation, MySQL will use the variable instead of the column, see Local Variable Scope and Resolution:

A local variable should not have the same name as a table column. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable. [...] MySQL interprets xname in the SELECT statement as a reference to the xname variable rather than the xname column. Consequently, when the procedure sp1()is called, the newname variable returns the value 'bob' regardless of the value of the table1.xname column.

So in

... WHERE ... AND KdBrg=kdbrg ...

and a variable (procedure parameter) kdbrg='A0050', you are actually comparing

... WHERE ... AND 'A0050'='A0050' ...

which is trivially true unless kdbrg is null.

Just rename your procedure parameters to e.g. _kdbrg. Technically, you can also add a table qualifier infront of the column names, e.g. AND tblHrgCustDtl.KdBrg = kdbrg, but this is usually considered less clear and clean.