Description:
This is a related issue with the one posted some time ago:
https://bugs.mysql.com/bug.php?id=86220
It's a bit old now and categorized, so created this new one. Don't know how to link issues here.
Anyway... There seems to be a bug where queries not working when using stored functions (SF) in them.
How to repeat:
Here's a SQL listing to recreate DB and then queries with description (sorry, it's a bit complicated but I've simplified it as I could):
CREATE DATABASE bug_mysql;
USE bug_mysql;
# -----------------------------------------------------------------------------------------------
# creating tables
DROP TABLE IF EXISTS account_to_company;
CREATE TABLE account_to_company
(
account_id INT NOT NULL,
company_id INT NOT NULL,
PRIMARY KEY (account_id, company_id)
);
INSERT INTO account_to_company (account_id, company_id) VALUES (1, 1);
INSERT INTO account_to_company (account_id, company_id) VALUES (2, 1);
INSERT INTO account_to_company (account_id, company_id) VALUES (3, 1);
INSERT INTO account_to_company (account_id, company_id) VALUES (4, 1);
INSERT INTO account_to_company (account_id, company_id) VALUES (5, 1);
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts
(
id INT AUTO_INCREMENT
PRIMARY KEY,
username VARCHAR(500) DEFAULT '' NOT NULL,
name VARCHAR(500) NULL,
first_name VARCHAR(500) NULL,
last_name VARCHAR(500) NULL
);
INSERT INTO accounts (username, name, first_name, last_name) VALUES ('admin', 'Se', null, null);
INSERT INTO accounts (username, name, first_name, last_name) VALUES ('editor1', 'Sc', null, null);
INSERT INTO accounts (username, name, first_name, last_name) VALUES ('GA', 'Ga', null, null);
INSERT INTO accounts (username, name, first_name, last_name) VALUES ('GE', 'Ga', null, null);
INSERT INTO accounts (username, name, first_name, last_name) VALUES ('s', 'Ol', null, null);
INSERT INTO accounts (username, name, first_name, last_name) VALUES ('GaA', 'Ga', null, null);
INSERT INTO accounts (username, name, first_name, last_name) VALUES ('someeditor', 'Some', null, null);
INSERT INTO accounts (username, name, first_name, last_name) VALUES ('somemanager', 'Some', null, null);
DROP TABLE IF EXISTS article_workflows;
CREATE TABLE article_workflows
(
id INT AUTO_INCREMENT
PRIMARY KEY,
name VARCHAR(250) NOT NULL
);
INSERT INTO article_workflows (name) VALUES ('ESS');
DROP TABLE IF EXISTS companies;
CREATE TABLE companies
(
id INT AUTO_INCREMENT
PRIMARY KEY,
name VARCHAR(250) NOT NULL
);
INSERT INTO companies (name) VALUES ('AA');
DROP TABLE IF EXISTS custom_field_values;
CREATE TABLE custom_field_values
(
id INT AUTO_INCREMENT
PRIMARY KEY,
custom_fields_id INT NOT NULL,
owner_id INT NOT NULL,
value VARCHAR(4000) NULL
);
DROP TABLE IF EXISTS custom_fields;
CREATE TABLE custom_fields
(
id INT AUTO_INCREMENT
PRIMARY KEY,
name VARCHAR(250) NOT NULL,
field_type VARCHAR(50) NOT NULL,
owner_type VARCHAR(50) NOT NULL,
article_workflow_id INT NULL,
properties VARCHAR(4000) NULL,
strict_name VARCHAR(250) NULL
);
INSERT INTO custom_fields (name, field_type, owner_type, article_workflow_id, properties, strict_name)
VALUES ('Is Editor', 'isMemberOfTeam', 'accounts', 1, '{"teamDefinitionIds":[1]}', 'IsEditor');
DROP TABLE IF EXISTS team_definitions;
CREATE TABLE team_definitions
(
id INT AUTO_INCREMENT
PRIMARY KEY,
name VARCHAR(250) NOT NULL,
article_workflow_id INT DEFAULT '0' NOT NULL,
owner_type VARCHAR(50) NOT NULL,
strict_name VARCHAR(250) NULL
);
INSERT INTO team_definitions (name, article_workflow_id, owner_type, strict_name) VALUES ('Editors', 1, 'companies', 'Editors');
DROP TABLE IF EXISTS team_entries;
CREATE TABLE team_entries
(
id INT AUTO_INCREMENT
PRIMARY KEY,
type VARCHAR(50) NOT NULL,
member_id INT NOT NULL,
team_definition_id INT NOT NULL,
owner_id INT NOT NULL
);
INSERT INTO team_entries (type, member_id, team_definition_id, owner_id) VALUES ('account', 2, 1, 1);
INSERT INTO team_entries (type, member_id, team_definition_id, owner_id) VALUES ('account', 4, 1, 1);
# -----------------------------------------------------------------------------------------------
# creating TD view
CREATE OR REPLACE VIEW team_definitions_to_accounts AS
SELECT
td2a.id AS id,
td2a.account_id AS account_id
FROM (
SELECT
td.id AS id,
(
CASE te.type
WHEN 'account' THEN te.member_id
WHEN 'exclude_account' THEN te.member_id
ELSE NULL
END
) AS account_id,
if((te.type = 'exclude_account'), 0, 1) AS include_in_result
FROM team_definitions td
JOIN team_entries te ON te.team_definition_id = td.id
) td2a
GROUP BY td2a.id, td2a.account_id
HAVING (bit_and(td2a.include_in_result) <> 0);
# -----------------------------------------------------------------------------------------------
# creating SFs
DROP FUNCTION IF EXISTS IsMemberOfTeam;
CREATE FUNCTION IsMemberOfTeam(accountId INT, teamDefinitionIds JSON, companyId INT)
RETURNS VARCHAR(4000)
DETERMINISTIC
READS SQL DATA
BEGIN
IF accountId IS NOT NULL AND json_type(teamDefinitionIds) = 'ARRAY' THEN
RETURN if(
exists(
SELECT id
FROM team_definitions_to_accounts td2a
WHERE json_contains(teamDefinitionIds, cast(td2a.id AS CHAR), '$') AND td2a.account_id = accountId
AND IF(companyId IS NOT NULL AND companyId != 0 AND td2a.owner_type = 'companies', td2a.owner_id = companyId, TRUE)
), 'true', 'false'
);
ELSE RETURN 'false';
END IF;
END;
DROP FUNCTION IF EXISTS GetCustomFieldValue;
CREATE FUNCTION GetCustomFieldValue(ownerObjectId INT, fieldType VARCHAR(50), serializedValue VARCHAR(4000), propertyObject VARCHAR(4000))
RETURNS VARCHAR(4000)
DETERMINISTIC
READS SQL DATA
BEGIN
CASE fieldType
WHEN 'isMemberOfTeam' THEN
RETURN if(json_valid(propertyObject), IsMemberOfTeam(ownerObjectId, propertyObject->>'$.teamDefinitionIds', propertyObject->>'$.companyId'), 'false');
-- any other field type
ELSE RETURN serializedValue;
END CASE;
END;
# -----------------------------------------------------------------------------------------------
# creating NOT bugged view
CREATE OR REPLACE VIEW `team_definitions_to_accounts` AS
SELECT id, owner_type, owner_id, account_id
FROM
(
SELECT
td.id,
td.owner_type,
te.owner_id,
(
CASE te.type
WHEN 'account' THEN te.member_id
WHEN 'company_accounts' THEN a2c.account_id
WHEN 'exclude_account' THEN te.member_id
ELSE NULL
END
) AS account_id,
IF(te.type = 'exclude_account', 0, 1) AS include_in_result
FROM team_definitions td
INNER JOIN team_entries te ON te.team_definition_id = td.id
LEFT JOIN account_to_company a2c ON te.type = 'company_accounts' AND a2c.company_id = te.member_id
) td2a
GROUP BY id, owner_type, owner_id, account_id
HAVING BIT_AND(include_in_result) != 0;
# creating main view
CREATE OR REPLACE VIEW main_view AS
SELECT
a.id AS object_with_custom_fields_id,
a.id AS account_id,
a.username,
cf.id,
cf.name,
cf.strict_name,
cf.field_type,
cf.owner_type,
cf.article_workflow_id AS article_workflow_id,
cf.properties,
a.id AS value_owner_id,
GetCustomFieldValue(a.id, cf.field_type, cfv.`value`, cf.properties) AS `value`
FROM
accounts a
INNER JOIN custom_fields cf ON cf.owner_type = 'accounts'
INNER JOIN article_workflows aw ON aw.id = cf.article_workflow_id
LEFT JOIN custom_field_values cfv ON cf.id = cfv.custom_fields_id AND a.id = cfv.owner_id;
# -----------------------------------------------------------------------------------------------
# reproducing bug
# This is simple query. Works perfect
SELECT object_with_custom_fields_id, acf.`value`->>'$', acf.`value`->>'$'='true'
FROM main_view acf
WHERE strict_name='IsEditor';
# Note the {acf.`value`->>'$'='true'} field in SELECT
# This is the same query but with additional condition {acf.`value`->>'$'='true'} in WHERE that return nothing,
# when it should return 2 entries (you can look at previous query)
SELECT object_with_custom_fields_id, acf.`value`->>'$', acf.`value`->>'$'='true'
FROM main_view acf
WHERE strict_name='IsEditor' And acf.`value`->>'$'='true';
# Both SFs are DETERMINISTIC (and indeed they are) with specified READS SQL DATA.
# THE END
# -----------------------------------------------------------------------------------------------
It would be very nice if somehow you could fix this issue. Makes usage of SFs in VIEWs, queries impossible.
Thank you
