Misma consulta, diferentes plataformas, diferentes resultados con REGEXP

Question

Tengo esta consulta en PHP. Sin error y sin valores.

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

Si utilizo cualquier cliente mysql. Esto devolverá mi recuento de registros. Todo bien.

¿Por qué esto no funciona con PHP? ¿Tengo que escapar del patrón REGEX? ¿Tiene sentido?

Answer 1

Supongo que uno de los siguientes funcionaría

$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");