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