mysql - 从 View 中选择时无法在子查询中使用参数

Question

系统: MariaDB 10.3.15、python 3.7.2、mysql.connector python 包

在使用如下所述的表结构执行查询时，我无法确定问题的确切原因，可能是 MariaDB/mySQL 中的错误。令人困惑的部分是错误消息

1356 (HY000): View “test_project.denormalized”引用了无效的表、列或函数，或者 View 的定义者/调用者缺乏使用它们的权限

一开始这似乎与问题有关，但我越深入研究为什么会发生这种情况，我就越觉得这个错误消息是一个转移注意力的东西。

重现步骤:

CREATE DATABASE `test_project`;

USE `test_project`;

CREATE TABLE `normalized` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `foreign_key` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `value` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

INSERT INTO `normalized` (`foreign_key`, `name`, `value`) VALUES
(1, 'attr_1', '1'),
(1, 'attr_2', '2'),
(2, 'attr_1', '3'),
(2, 'attr_2', '4');

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `denormalized` AS 
select
    max(`iq`.`foreign_key`) AS `foreign_key`,
    max(`iq`.`attr_1`) AS `attribute_1`,
    max(`iq`.`attr_2`) AS `attribute_2`
from (
    select
        `foreign_key` AS `foreign_key`,
        if(`name` = 'attr_1',`value`,NULL) AS `attr_1`,
        if(`name` = 'attr_2',`value`,NULL) AS `attr_2`
    from `normalized`
) as `iq`
group by `iq`.`foreign_key`;

使用python连接数据库并执行以下查询:

conn = mysql.connector.connect(host="somehost", user="someuser", password="somepassword")
cursor = conn.cursor()
query = """select * from denormalized as d
where d.`foreign_key` in
(
    SELECT distinct(foreign_key)
    FROM normalized
    where value = %s
);"""
cursor.execute(query, ["2"])
results = cursors.fetchall()

更多信息:起初我认为这显然是一个权限问题，但即使使用 root 执行所有操作并仔细检查主机和特定权限也没有改变任何内容。

然后我更深入地研究了所涉及的查询和 View 的作用(上面的测试用例是我们数据库中实际内容的简化版本)并测试了每个部分。从 View 中选择有效。运行 View 的查询是有效的。使用静态子查询从 View 中进行选择是有效的。事实上，将有问题的查询中的 View 替换为其定义也是可行的。

我将其归结为使用 where 子句中的子查询(使用该子查询中的参数)从 View 中进行选择。这会导致出现错误。使用静态子查询或用其定义替换 View 效果很好，只是在这种特定情况下会失败。

我不知道为什么。

Answer 1

group by 没有意义；您真的是指其中之一吗？

这将返回一行:

select  max(`foreign_key`) AS `foreign_key`,
        max(if(`name` = 'attr_1', `value`,NULL)) AS `attribute_1`,
        max(if(`name` = 'attr_2', `value`,NULL)) AS `attribute_2`
    from  `normalized`;

这使用GROUP BY并为每个foreign_key返回一行:

select  `foreign_key`,
        max(if(`name` = 'attr_1', `value`,NULL)) AS `attribute_1`,
        max(if(`name` = 'attr_2', `value`,NULL)) AS `attribute_2`
    from  `normalized` 
    group by  `foreign_key`;

您的 python 查询可能在以下任一公式中更好:

select  d.*
    FROM ( SELECT  distinct(foreign_key)
            FROM  normalized
            where
             value  = %s  )
    JOIN  denormalized as d;

select  d.*
    FROM denormalized as d
    WHERE EXISTS ( SELECT 1
            FROM  normalized
            where foreign_key = d.foreign_key
              AND value  = %s  )

他们将从INDEX(value,foreign_key)中受益。