Oracle: conversión implícita y ORA-01722: invalid number

Hoy han dejado de funcionar unos informes de Jasper basados en consultas a una BDD Oracle. No habíamos modificado nada en las queries o el código, así que supuse que habría alguna influencia externa. La query era ésta:

SELECT
TO_CHAR(SYSDATE,'YYYY/MM/DD') AS "created",
(SELECT TO_CHAR("START",'YYYY/MM/DD') FROM mcp_ad_campaign WHERE id = $P{campaign_id} ) AS "start",
(SELECT TO_CHAR("END",'YYYY/MM/DD') FROM mcp_ad_campaign WHERE id = $P{campaign_id} ) AS "end",
(SELECT to_number(extract(day from (SYSDATE-"START"))) FROM mcp_ad_campaign WHERE id = $P{campaign_id} ) AS "emited",
(SELECT NVL(SUM( p.counter * (CASE pa.numScreens WHEN 0 THEN 1 ELSE pa.numScreens END )), 0) FROM mcp_pop p, mcp_ad_spot s, mcp_player pa WHERE p.spot_id = s.id AND pa.identifier = p.player_ip2long AND s.idCampaign = $P{campaign_id} and (p.play_date between (select mac."START" from MCP_AD_CAMPAIGN mac where id = $P{campaign_id}) and (select mac."END" from MCP_AD_CAMPAIGN mac where id = $P{campaign_id})) GROUP BY s.idCampaign) AS "hits",
'logo.png' AS "logo",
$P{campaign_id} AS "idCampaign",
(SELECT c.name name FROM mcp_ad_campaign c join mcp_ad_group g on (c.idgroup = g.id) WHERE c.id = $P{campaign_id} ) AS "insercion",
(SELECT g.name name FROM mcp_ad_campaign c join mcp_ad_group g on (c.idgroup = g.id) WHERE c.id = $P{campaign_id} ) AS "campaign"
FROM dual

$P{} es un parámetro de Jasper. Sustituí todos por un valor para probarlo directamente en Oracle. Al ejecutarlo, obtuve el error:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

Intentando acotar, reduje la query a :

SELECT *
FROM mcp_pop p, mcp_ad_spot s, mcp_player pa
WHERE p.spot_id = s.id AND pa.identifier = p.player_ip2long;

El error era de tipo de datos. mcp_player.identifier es un VARCHAR2(255 CHAR), y mcp_pop.player_ip2long un NUMBER(10,0). Lo «solventé» convirtiendo player_ip2long a char, TO_CHAR(p.player_ip2long). También se podría convertir a número mcp_player.identifier. Esta diferencia viene por razones tecnológicas: mcp_player.identifier es un identificador del protocolo XMPP, que efectivamente es una cadena. Posteriormente, a nivel de aplicación, se decidió utilizar números únicamente. Sin embargo, quedaba una pregunta inquietante sin responder: ¿por qué había estado funcionando hasta hace poco? Esta página me dio la respuesta: la conversión implícita de datos. Oracle intenta convertir de cadena a número al comparar estos dos tipos de datos. Funciona bien siempre que la columna de tipo cadena tenga todas las filas con todos sus caracteres numéricos. Basta con que haya una fila que no cumpla este requisito (espacios, letras…) para que falle. Efectivamente, éste era mi caso. Buscando:

SELECT *
FROM mcp_player
WHERE regexp_like(identifier,'[^0-9]+');

Me devolvió dos filas, una con espacio_numero y otra con numero_descripción. Al cambiarlas a sólo numero, las queries que dependían de la conversión implícita volvieron a funcionar. Evidentemente esto es malo porque cualquier dato «espúreo» en la BDD fastidia todo. Dos curiosidades:

Funcionaba de manera alternante, una vez devolviendo resultados y a la siguiente el error anteriormente mencionado. Por experiencia con esta empresa cliente, sabía que utilizaban varios nodos en cluster, que aparentemente no funcionan exactamente igual. Buscando en Internet ORA-01722 encontré este enlace en el que se describe un problema muy similar, en el que una query funciona correctamente con un usuario y con otro devuelve ORA-01722. La respuesta que se da en ese foro es que el error es verdadero, y que puede que a veces funcione bien y otras no por el optimizador de Oracle, CBO, o porque los datos sean distintos. Espero que esto último no esté ocurriendo porque si de verdad están en cluster los datos deberían ser iguales.
Alternando el orden de las condiciones del AND funcionaba correctamente, es decir:WHERE pa.identifier = p.player_ip2long AND p.spot_id = s.id;en lugar de:WHERE p.spot_id = s.id AND pa.identifier = p.player_ip2long; Esto también se puede ver en este enlace:
This error seems to creep into queries in the strangest ways. A change in the order of a predicate can make it come and go — depending on the order of evaluation in the predicate.

Enlaces interesantes: http://intermediatesql.com/oracle/how-oracle-implicit-type-conversion-works-part-2/comment-page-1/