#1755 Cardinality violation from Natural Join

[Richard Thomas]

Hello,
I have a query which normally works fine, but with the current data is giving me a cardinality violation, but I am struggling to see why; as far as I can see it should just return an empty result:

SELECT * FROM
(SELECT TEST_TYPE, FOLDER_NAME, TEST_NAME FROM TEST_RESULT_EXT WHERE RESULT='F' AND LATEST=TRUE)
NATURAL JOIN
(SELECT LATEST_SUCCESS_SESSION_TIMESTAMP, (SELECT DISTINCT BUILD FROM TEST_RESULT WHERE SESSION_TIMESTAMP=LATEST_SUCCESS_SESSION_TIMESTAMP) AS LATEST_SUCCESS_BUILD, TEST_TYPE, FOLDER_NAME, TEST_NAME FROM (SELECT MAX(SESSION_TIMESTAMP) AS LATEST_SUCCESS_SESSION_TIMESTAMP, TEST_TYPE, FOLDER_NAME, TEST_NAME FROM TEST_RESULT WHERE RESULT='S' GROUP BY TEST_TYPE, FOLDER_NAME, TEST_NAME))

The two subqueries being naturally joined both run correctly, so I can't see why joining them could give a cardinality violation.
Thanks

[Richard Thomas]

The query is the view "current_failures" in the script.

[Fred Toussi]

Try this:

SELECT * FROM
(SELECT TEST_TYPE, FOLDER_NAME, TEST_NAME FROM TEST_RESULT_EXT WHERE RESULT='F' AND LATEST=TRUE)
NATURAL JOIN
(SELECT LATEST_SUCCESS_SESSION_TIMESTAMP, (SELECT max( BUILD) FROM TEST_RESULT WHERE SESSION_TIMESTAMP=LATEST_SUCCESS_SESSION_TIMESTAMP) AS LATEST_SUCCESS_BUILD, TEST_TYPE, FOLDER_NAME, TEST_NAME FROM (SELECT MAX(SESSION_TIMESTAMP) AS LATEST_SUCCESS_SESSION_TIMESTAMP, TEST_TYPE, FOLDER_NAME, TEST_NAME FROM TEST_RESULT WHERE RESULT='S' GROUP BY TEST_TYPE, FOLDER_NAME, TEST_NAME))

[Richard Thomas]

Thanks a lot.