Stessa query, piattaforme diverse, risultati diversi con REGEXP

Question

Ho questa query in PHP. Nessun errore e nessun valore.

$query = $connection->prepare("SELECT main.a as pkg, COUNT(1) as count
FROM
(SELECT node, JSON_EXTRACT(packages, JSON_UNQUOTE(JSON_EXTRACT(PATH, '$[0]'))) AS a FROM (SELECT packages, JSON_SEARCH(packages, 'all', 'openstack-nova-api-%', NULL, '$.packages[*]') AS PATH, cloud, node FROM Node WHERE JSON_SEARCH(packages, 'all',  'openstack-nova-api-%', NULL, '$.packages[*]') IS NOT NULL AND cloud = 'ch-dc-os-dhn-56') AS sub UNION SELECT node, JSON_EXTRACT(packages, JSON_UNQUOTE(JSON_EXTRACT(PATH, '$[1]'))) AS a FROM 
(SELECT packages, JSON_SEARCH(packages, 'all', 'openstack-nova-api-%', NULL, '$.packages[*]') AS PATH, cloud, node FROM Node WHERE JSON_SEARCH(packages, 'all', 'openstack-nova-api-%', NULL, '$.packages[*]') IS NOT NULL AND cloud = 'ch-dc-os-dhn-56') AS sub 
UNION
) AS main
WHERE main.a is not null AND main.a REGEXP '((?i)openstack-nova-api-(?-i))\\d.*'
GROUP BY main.a");


 $query->execute(); $result = $query->fetchAll(\PDO::FETCH_ASSOC); echo json_encode($result);

Se utilizzo un client mysql. Questo restituirà il mio conteggio record. Tutto bene.

Perché questo non funziona con PHP ?? Devo uscire dal pattern REGEX ?? Ha senso?

Answer 1

Immagino che uno dei seguenti funzionerebbe

$query = $connection->prepare('SELECT main.a as pkg, COUNT(1) as count
FROM
(SELECT node, JSON_EXTRACT(packages, JSON_UNQUOTE(JSON_EXTRACT(PATH, \'$[0]\'))) AS a FROM (SELECT packages, JSON_SEARCH(packages, \'all\', \'openstack-nova-api-%\', NULL, \'$.packages[*]\') AS PATH, cloud, node FROM Node WHERE JSON_SEARCH(packages, \'all\',  \'openstack-nova-api-%\', NULL, \'$.packages[*]\') IS NOT NULL AND cloud = \'ch-dc-os-dhn-56\') AS sub UNION SELECT node, JSON_EXTRACT(packages, JSON_UNQUOTE(JSON_EXTRACT(PATH, \'$[1]\'))) AS a FROM 
(SELECT packages, JSON_SEARCH(packages, \'all\', \'openstack-nova-api-%\', NULL, \'$.packages[*]\') AS PATH, cloud, node FROM Node WHERE JSON_SEARCH(packages, \'all\', \'openstack-nova-api-%\', NULL, \'$.packages[*]\') IS NOT NULL AND cloud = \'ch-dc-os-dhn-56\') AS sub 
UNION
) AS main
WHERE main.a is not null AND main.a REGEXP \'((?i)openstack-nova-api-(?-i))\\d.*\'
GROUP BY main.a');

o

$query = $connection->prepare("SELECT main.a as pkg, COUNT(1) as count
FROM
(SELECT node, JSON_EXTRACT(packages, JSON_UNQUOTE(JSON_EXTRACT(PATH, '\$[0]'))) AS a FROM (SELECT packages, JSON_SEARCH(packages, 'all', 'openstack-nova-api-%', NULL, '\$.packages[*]') AS PATH, cloud, node FROM Node WHERE JSON_SEARCH(packages, 'all',  'openstack-nova-api-%', NULL, '\$.packages[*]') IS NOT NULL AND cloud = 'ch-dc-os-dhn-56') AS sub UNION SELECT node, JSON_EXTRACT(packages, JSON_UNQUOTE(JSON_EXTRACT(PATH, '\$[1]'))) AS a   FROM 
 (SELECT packages, JSON_SEARCH(packages, 'all', 'openstack-nova-api-%', NULL, '\$.packages[*]') AS PATH, cloud, node FROM Node WHERE JSON_SEARCH(packages, 'all', 'openstack-nova-api-%', NULL, '\$.packages[*]') IS NOT NULL AND cloud = 'ch-dc-os-dhn-56') AS sub 
UNION
) AS main
WHERE main.a is not null AND main.a REGEXP '((?i)openstack-nova-api-(?-i))\\d.*'
GROUP BY main.a");