php - 通过 PDO 执行的查询返回的记录少于从 PostgreSQL 返回的记录

Question

查询 1.

我尝试使用 PHP 运行它:

<?php

$pdo = new \PDO('pgsql:host=localhost;dbname=postgres', 'postgres', 'postgres');

$sql = <<<SQL
SELECT *
FROM (
  SELECT 'CHAC TECHNOLOG*' as alias
  UNION 
  SELECT 'KINDERY LIGHTING SALES DE?T*'
) m 
JOIN (
  SELECT 'CHACTECHNOLOGICO\\' as ie_clean
  UNION 
  SELECT 'KINDERYLIGHTINGSALESDEPT' as ie_clean
) t ON t.ie_clean ILIKE REPLACE(REPLACE(REPLACE(m.alias, '*', '%'), '?', '_'), ' ', '') 
ORDER BY ie_clean;
SQL;

echo $sql . PHP_EOL . PHP_EOL;

$stmt = $pdo->query($sql);

print_r($stmt->fetchAll(PDO::FETCH_ASSOC));

我有下一个输出:

SELECT * 
FROM (
  SELECT 'CHAC TECHNOLOG*' as alias
  UNION 
  SELECT 'KINDERY LIGHTING SALES DE?T*'
) m 
JOIN (
  SELECT 'CHACTECHNOLOGICO\' as ie_clean
  UNION 
  SELECT 'KINDERYLIGHTINGSALESDEPT' as ie_clean
) t ON t.ie_clean ILIKE REPLACE(REPLACE(REPLACE(m.alias, '*', '%'), '?', '_'), ' ', '') 
ORDER BY ie_clean;

Array
(
    [0] => Array
        (
            [alias] => CHAC TECHNOLOG*
            [ie_clean] => CHACTECHNOLOGICO\
        )

)

只有一条记录(错误)

但是当我尝试在 PostgreSQL 中直接运行它时，这个查询返回了两条记录。这是正确的结果。

https://www.db-fiddle.com/f/qNZY5SauB87na2pWf8uwxm/0

查询 2.

这是类似的查询，但现在我将部分条件从 WHERE 移动到 SELECT 部分:

<?php

$pdo = new \PDO('pgsql:host=localhost;dbname=postgres', 'postgres', 'postgres');

$sql = <<<SQL
SELECT *
FROM (
  SELECT REPLACE(REPLACE(REPLACE('CHAC TECHNOLOG*', '*', '%'), '?', '_'), ' ', '') as alias
  UNION
  SELECT REPLACE(REPLACE(REPLACE('KINDERY LIGHTING SALES DE?T*', '*', '%'), '?', '_'), ' ', '')
) m
JOIN (
  SELECT 'CHACTECHNOLOGICO\\' as ie_clean
  UNION
  SELECT 'KINDERYLIGHTINGSALESDEPT' as ie_clean
) t ON t.ie_clean ILIKE m.alias
ORDER BY ie_clean;
SQL;

echo $sql . PHP_EOL . PHP_EOL;

$stmt = $pdo->query($sql);

print_r($stmt->fetchAll(PDO::FETCH_ASSOC));

输出是:

SELECT *
FROM (
  SELECT REPLACE(REPLACE(REPLACE('CHAC TECHNOLOG*', '*', '%'), '?', '_'), ' ', '') as alias
  UNION
  SELECT REPLACE(REPLACE(REPLACE('KINDERY LIGHTING SALES DE?T*', '*', '%'), '?', '_'), ' ', '')
) m
JOIN (
  SELECT 'CHACTECHNOLOGICO\' as ie_clean
  UNION
  SELECT 'KINDERYLIGHTINGSALESDEPT' as ie_clean
) t ON t.ie_clean ILIKE m.alias
ORDER BY ie_clean;

Array
(
    [0] => Array
        (
            [alias] => CHACTECHNOLOG%
            [ie_clean] => CHACTECHNOLOGICO\
        )

    [1] => Array
        (
            [alias] => KINDERYLIGHTINGSALESDE_T%
            [ie_clean] => KINDERYLIGHTINGSALESDEPT
        )

)

两项纪录!这是正确的结果。

Postgres 也返回两条记录(这是正确的):

https://www.db-fiddle.com/f/nSv1Tg9YJMgfUUhn7urFyF/0

问题

我认为问题出在 'CHACTECHNOLOGICO\\' 中的尾部斜线，但我重新检查了一下，我认为它是正确的。

为什么查询 1 只从 PHP 返回一条记录。是 PDO 的错误还是我做错了什么？

UPD

https://bugs.php.net/bug.php?id=78534

Answer 1

问题的根源是 REPLACE 函数中的一个问号。

到达服务器的实际查询看起来像

SELECT *
FROM (
  SELECT 'CHAC TECHNOLOG*' as alias
  UNION
  SELECT 'KINDERY LIGHTING SALES DE?T*'
) m
JOIN (
  SELECT 'CHACTECHNOLOGICO\' as ie_clean
  UNION
  SELECT 'KINDERYLIGHTINGSALESDEPT' as ie_clean
) t ON t.ie_clean ILIKE REPLACE(REPLACE(REPLACE(m.alias, '*', '%'), '$1', '_'), ' ', '')
ORDER BY ie_clean;

因此 PDO 将 ? 替换为 $1 并且查询的第二部分变得无效，导致 1 行

您必须以某种方式绑定(bind)该值(这很棘手并且可能行不通)或使用 ? 更改您的查询工作。

试试这个看看实际结果(不推荐，这里有很多关于这个选项的讨论)

$pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);