0

While debugging a query, I am seeing unexpected behaviour in the LEFT OUTER JOIN functionality in my Netezza database.

While debugging, I ran this query:

WITH
    AA AS
    (
        SELECT
            '12345' AS PID
    )
    ,
    BB AS
    (
        SELECT
            '000000' AS POS,
            12     AS TransAmt
        UNION ALL
        SELECT
            '55555' AS POS,
            30      AS TransAmt
    )
SELECT
    *
FROM
    AA
LEFT OUTER JOIN
    BB
ON
    AA.PID = BB.POS

But am presented with this error:

[Code: 1100, SQL State: HY000] FATAL 1: GetCCHashFunc: type 705 unsupported as catcache key

Running the same code in MySQL, H2, and BigQuery yields the expected results:

Row PID POS TransAmt     
1   12345   null    null

Is there a nuance in Netezza that I am missing that is preventing such a left join from completing successfully?

Carl K
  • 974
  • 7
  • 18
  • 1
    That sounds like a translation issue based on the some internet sleuthing. Netezza was originally built from postgres 7.2. Dumping this exact same query into a more current version of postgres I get the error *"ERROR: failed to find conversion function from unknown to text"* I get the same with an INNER JOIN suggesting this has more to do with joining on these "unknown" field types than it has to do with the join itself. – JNevill Oct 03 '18 at 13:44
  • Lastly, I'm able to overcome this error by explicitly casting the fields involved in the join to text (in postgres, not sure what the netezza syntax is here): `AA.PID::text = BB.POS::text;` – JNevill Oct 03 '18 at 13:47
  • 1
    @JNevill - I appreciate the help, and explicitly casting them (as above in the postgres syntax) did resolve the error. – Carl K Oct 03 '18 at 16:23

0 Answers0