Thursday, 12 September 2019

Query executed through PDO return less records than from PostgreSQL

Query 1.

I try to run it using 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));

And I have next output:

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\
        )

)

Only one record (it is wrong).

But when I try to run it directly in PostgreSQL, this query returns two records. It is correct result.

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

Query 2.

It is similar query, but now I moved part of condition from WHERE to SELECT section:

<?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));

Output is:

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
        )

)

Two records! It is correct result.

Postgres returns two records too (it is correct):

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

Question

I thinked that problem was with tailing slash in 'CHACTECHNOLOGICO\\', but I has rechecked it, and as I see it is correct.

Why Query 1 return only one record from PHP. Is it bug of PDO or I do something wrong?



from Query executed through PDO return less records than from PostgreSQL

No comments:

Post a Comment