Styjun

I have this stored procedure in MySQL database, but when I test it like below, I get Operand should contain 1 column(s) error:

CALL `get_strongest_policy_from_all_involved_accounts_for_user`(57);

And this is my stored procedure. The code from line 6 to 20 works fine and it returns a table of 0 or more records.

Can somebody please help me fix the erroras I don't know what causes it. Many thanks!

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_strongest_policy_from_all_involved_accounts_for_user` (
 IN app_user_id INT
)
BEGIN
 CREATE TEMPORARY TABLE `ParentAccountPolicies` ENGINE=MEMORY SELECT (
 SELECT UAS.`password_rotation_active`, UAS.`password_rotation_days`, UAS.`Password_minimum_lenght`,
 UAS.`password_must_contain_numeric_char`, UAS.`password_must_contain_lowercase_char`,
 UAS.`password_must_contain_upercase_char`, UAS.`password_must_contain_special_char`
 FROM `user_account` UA, `user_account_settings` UAS
 WHERE UA.`account_id` = UAS.`account_id`
 AND UA.`primary_user_id` = app_user_id
 UNION
 SELECT UAS.`password_rotation_active`, UAS.`password_rotation_days`, UAS.`Password_minimum_lenght`,
 UAS.`password_must_contain_numeric_char`, UAS.`password_must_contain_lowercase_char`,
 UAS.`password_must_contain_upercase_char`, UAS.`password_must_contain_special_char`
 FROM `user_account` UA, `business` B, `app_user_n_business` AUB, `user_account_settings` UAS
 WHERE UA.`business_id` = B.`business_id`
 AND B.`business_id` = AUB.`business_id`
 AND UAS.`account_id` = UA.`account_id`
 AND AUB.`app_user_id` = app_user_id
);

 SET @RotationActive = B'0', @HaveNum = B'0', @HaveLowCase = B'0', @HaveUpCase = B'0', @HaveSpecial = B'0';
 SET @RotationDays = 0, @MinLength = 0;

 IF (SELECT DISTINCT `password_rotation_active` FROM `ParentAccountPolicies` WHERE `password_rotation_active` = TRUE) IS NOT NULL THEN
 SET @RotationActive = B'1';
 END IF;

 IF (SELECT DISTINCT `password_must_contain_numeric_char` FROM `ParentAccountPolicies` WHERE `password_must_contain_numeric_char` = TRUE) IS NOT NULL THEN
 SET @HaveNum = B'1';
 END IF;

 IF (SELECT DISTINCT `password_must_contain_lowercase_char` FROM `ParentAccountPolicies` WHERE `password_must_contain_lowercase_char` = TRUE) IS NOT NULL THEN
 SET @HaveLowCase = B'1';
 END IF;

 IF (SELECT DISTINCT `password_must_contain_upercase_char` FROM `ParentAccountPolicies` WHERE `password_must_contain_upercase_char` = TRUE) IS NOT NULL THEN
 SET @HaveUpCase = B'1';
 END IF;

 IF (SELECT DISTINCT `password_must_contain_special_char` FROM `ParentAccountPolicies` WHERE `password_must_contain_special_char` = TRUE) IS NOT NULL THEN
 SET @HaveSpecial = B'1';
 END IF; 

 SELECT MAX(`password_rotation_days`) INTO @RotationDays FROM `ParentAccountPolicies`;
 SELECT MAX(`Password_minimum_lenght`) INTO @RotationDays FROM `ParentAccountPolicies`;

 CREATE TEMPORARY TABLE `FinalPolicy` (
 `RotationActive` BIT,
 `RotationDays` INT,
 `MinimumLength` INT,
 `ShouldHaveDigit` BIT,
 `ShouldHaveLowerCaseChar` BIT,
 `ShouldHaveUpperCaseChar` BIT,
 `ShouldHaveSpecialChar` BIT
 );

 INSERT INTO `FinalPolicy` VALUES (@RotationActive, @RotationDays, @MinLength, @HaveNum, @HaveLowCase, @HaveUpCase, @HaveSpecial);

 SELECT `FinalPolicy`;
END;

You do something like this:

CREATE TEMPORARY TABLE `ParentAccountPolicies` ENGINE=MEMORY SELECT (
 SELECT 'a' as x, 'b' as y
 FROM dual
 UNION
 SELECT 'c' as x, 'd' as y
 FROM dual
);

This results in the following error:

ER_OPERAND_COLUMNS: Operand should contain 1 column(s)

Demo

The reason is that you have a subselect (subquery in SELECT clause) which must return exactly one column and at most one row. But your subselect returns multiple columns.

But you don't need to use a subselect at all. Just remove the outer SELECT like here:

CREATE TEMPORARY TABLE `ParentAccountPolicies` ENGINE=MEMORY 
 SELECT 'a' as x, 'b' as y
 FROM dual
 UNION
 SELECT 'c' as x, 'd' as y
 FROM dual
;

Demo