Usar $wpdb para consultar posts con meta valores que contengan el post_id actual

En el código que publicaste, no recuperas una 'lista de publicaciones' como mencionas, sino una lista de filas en la tabla meta. Si realmente deseas recuperar una lista de publicaciones, utiliza WP_Query con el parámetro meta_query.

Algo como:

$id = '10'; // valor deserializado

$args = array(
  'post_type' => 'post',
  'post_status' => 'publish',
  'posts_per_page' => -1,
  'meta_query' => array(
    array(
      'key' => 'roles_%_production',
      'value' => $id,
      'compare' => 'LIKE'
    )
  )
);
$query = new WP_Query( $args );
$rows = $query->get_posts();

Si deseas usar $wpdb (no sé por qué razón), la consulta correcta sería algo como:

<?php
$id = '10'; // valor deserializado

global $wpdb;
$rows = $wpdb->get_col( $wpdb->prepare(
  "SELECT DISTINCT $wpdb->posts.ID FROM $wpdb->posts, $wpdb->postmeta
  WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id AND
  $wpdb->posts.post_status = 'publish' AND
  $wpdb->posts.post_type = 'post' AND
  $wpdb->postmeta.meta_key = %s AND
  meta_value = %s",
  'roles_%_production',
  $id
) );
?>

$rows contendrá un array de IDs de publicaciones. He hecho este cambio para que tenga sentido el uso de $wpdb. Si deseas recuperar todos los campos, usa SELECT * en lugar de SELECT DISTINCT $wpdb->posts.ID, utiliza $wpdb->get_results en vez de $wpdb->get_col y añade la línea GROUP BY $wpdb->posts.ID a la consulta.

Dos notas:

Búsqueda de metadatos

La regla es simple: Los metadatos serializados (como un array convertido a a:1:{i:0;s:2:"10";}) no están diseñados para ser buscables. Tendrás que convertir tu conjunto de datos a valores individuales para poder realizar búsquedas adecuadas con meta_query.

La única forma realmente funcional de buscar esto es consultar todos los datos, luego deserializarlos, mostrarlos si coinciden con tus criterios, u omitirlos. Hay suficientes discusiones y preguntas/respuestas en SO que cubren este tema.

Declaraciones preparadas

Existe like_escape(), que debe usarse así:

"%".like_escape( $string )."%"

La razón por la que necesitas agregar los caracteres % antes y después es simple: Puedes decidir si el LIKE debe aplicarse en ambos extremos o solo en uno (inicio, final o ambos lados).

Las comillas invertidas son para asegurar que 10 no coincida también con 100, etc.

Puedo hacerlo funcionar cuando pego el valor exacto del meta, es decir: a:1:{i:0;s:2:"10";}

Esto se debe a que en tu consulta WHERE, usaste = donde el valor contiene %. Usar LIKE en su lugar resolvería este problema en particular. Cambia meta_value = %s por meta_value LIKE %s, y tu consulta debería funcionar igual que si usaras la cadena serializada completa.

También está devolviendo todas las revisiones, no solo la publicación más reciente.

Asegúrate de especificar el tipo de publicación y el estado correctos al consultar tus resultados. Si deseas incluir todas las publicaciones que no sean revisiones, entonces necesitarás asegurarte de unir la tabla de publicaciones (antes de tu declaración WHERE).

LEFT JOIN $wpdb->posts ON $wpdb->posts.ID = $wpdb->postmeta.post_id

e incluir la siguiente línea en tu declaración WHERE (vinculada por un AND).

$wpdb->posts.post_type NOT IN ('revision')