Обсуждение: ERROR: return and sql tuple descriptions are incompatible

Hi all,

SELECT *

 FROM CROSSTAB (

'

SELECT

                                PART.SERIAL_NUMBER,

        TESTC.TEST_NUMBER,

                                TRESULT.TEST_RESULT

FROM      bronx.TEST_PART_DETAILS_ALL_MCM_INIT PART,

                                bronx.TEST_RESULTS_ALL_MCM_INIT TRESULT,

                                bronx.TEST_TEST_DETAILS_ALL_MCM_INIT TESTC

WHERE PART.TEST_PART_DET_ALL_MCM_ID                                = TRESULT.TEST_PART_DETAILS_ALL_MCM_ID

                AND TRESULT.TEST_TEST_DETAILS_ALL_MCM_ID          = TESTC.TEST_TEST_DETAILS_ALL_MCM_ID

                and PART.STAGE                                                                                                                        = ''FT''

               AND SPLIT_PART (SERIAL_NUMBER, '':'', 1 )                                                                  = ''B7307631''

                And TESTC.TEST_NUMBER                                                                                          = ''TEST1P1''

ORDER BY PART.SERIAL_NUMBER , TESTC.TEST_NUMBER 

'

)  as ConcatenatedResults (

                                                                                                                                SERIALNUMBER  character varying ,

TEST1P1  character

--              TEST_RESULT numeric

);

Error Encountered:

ERROR: return and sql tuple descriptions are incompatible SQL state: 42601

bronxdb1=> \d bronx.TEST_RESULTS_ALL_MCM_INIt

                                      Table "bronx.test_results_all_mcm_init"

            Column            |           Type           | Collation | Nullable |             Default

------------------------------+--------------------------+-----------+----------+----------------------------------

test_results_all_mcm_id      | integer                  |           | not null | generated by default as identity

test_part_details_all_mcm_id | bigint                   |           |          |

test_result                  | numeric                  |           |          |

test_test_details_all_mcm_id | integer                  |           |          |

bronxdb1=> \d  bronx.TEST_TEST_DETAILS_ALL_MCM_INIT

                                   Table "bronx.test_test_details_all_mcm_init"

            Column            |           Type           | Collation | Nullable |             Default

------------------------------+--------------------------+-----------+----------+----------------------------------

test_test_details_all_mcm_id | integer                  |           | not null | generated by default as identity

stage                        | character(50)            |           |          |

test_number                  | character(10)            |           |          |

This is known issue and googled almost half day today and could not succeed.

There are lab works available on the internet but they are not helping in solving my issue.

Postgres Version 13.5

AWS RDS platform.

Thanks,

Sarwar

On 2024-05-04 20:01 +0200, M Sarwar wrote:
> SELECT *
> 
>  FROM CROSSTAB (
> 
> '
> 
> SELECT
> 
>                                 PART.SERIAL_NUMBER,
> 
>         TESTC.TEST_NUMBER,
> 
>                                 TRESULT.TEST_RESULT
> 
> FROM      bronx.TEST_PART_DETAILS_ALL_MCM_INIT PART,
> 
>                                 bronx.TEST_RESULTS_ALL_MCM_INIT TRESULT,
> 
>                                 bronx.TEST_TEST_DETAILS_ALL_MCM_INIT TESTC
> 
> WHERE PART.TEST_PART_DET_ALL_MCM_ID                                = TRESULT.TEST_PART_DETAILS_ALL_MCM_ID
> 
>                 AND TRESULT.TEST_TEST_DETAILS_ALL_MCM_ID          = TESTC.TEST_TEST_DETAILS_ALL_MCM_ID
> 
>                 and PART.STAGE
                               = ''FT''
 
> 
>                AND SPLIT_PART (SERIAL_NUMBER, '':'', 1 )
   = ''B7307631''
 
> 
>                 And TESTC.TEST_NUMBER
        = ''TEST1P1''
 
> 
> ORDER BY PART.SERIAL_NUMBER , TESTC.TEST_NUMBER
> 
> '
> 
> )  as ConcatenatedResults (
> 
>
         SERIALNUMBER  character varying ,
 
> 
> TEST1P1  character
> 
> --              TEST_RESULT numeric
> 
> );
> 
> 
> 
> Error Encountered:
> 
> ERROR: return and sql tuple descriptions are incompatible SQL state: 42601

The output value columns (TEST1P1 and TEST_RESULT) must be of the same
type.  And it must be the same type as the third result column
(TRESULT.TEST_RESULT).

-- 
Erik

> On May 4, 2024, at 6:38 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> Are you suggesting me to use the type either TEXT or some other data type in the below clause of the SQL.

No, but if you want them both in the same column of your output, then one or the other must be cast so the column type
isconsistent. 

On 2024-05-05 02:38 +0200, M Sarwar wrote:
> TEST1P1 data is coming from the category column, TESTC.TEST_NUMBER.
> TRESULT.TEST_RESULT column is giving value data.

Column TESTC.TEST_NUMBER (i.e. the second column) is ignored by crosstab
and it only requires the use of ORDER BY 1,2 to get correct results.
Output columns TEST1P1 and TEST_RESULT are two of the many value columns
which are only filled by the third result column.

I now remember it's the same issue from your very first crosstab mail:

https://www.postgresql.org/message-id/q2cdz27dkwel73k27ckt2i6g6ce55wzodaogbgfnoqccupqndx%40vz3cnoihz4j5

> As I stated in my first email which has  results of
> '\d bronx.TEST_RESULTS_ALL_MCM_INIt' and
> '\d bronx.TEST_TEST_DETAILS_ALL_MCM_INIT', columns TESTC.TEST_NUMBER
> and TRESULT.TEST_RESULT  data types are  numeric and   character(10) .
> This is as per table definitions.
> 
> Are you suggesting me to use the type either TEXT or some other data
> type in the below clause of the SQL.

I'd say use numeric for TEST1P1 and TEST_RESULT.  But the column names
and types suggest that you expect the second result column in the
crosstab output.  Which isn't possible to my knowledge.

-- 
Erik

On 2024-05-06 17:04 +0200, M Sarwar wrote:
> Now I have changed from ORDER BY 1,2 to ORDER BY 1.

This works because your input query only returns one category.  But then
you don't need crosstab because you effectively only execute:

    SELECT * FROM crosstab($$
        SELECT PART.SERIAL_NUMBER, TESTC.TEST_NUMBER, TRESULT.TEST_RESULT
        FROM ...
        WHERE TESTC.TEST_NUMBER = 'TEST1P1'
        ORDER BY 1
    $$) AS ConcatenatedResults (
        SERIALNUMBER varchar,
        TEST_RESULT numeric
    )

Which is the same as this plain query:

    SELECT PART.SERIAL_NUMBER AS SERIALNUMBER, TRESULT.TEST_RESULT
    FROM ...
    WHERE TESTC.TEST_NUMBER = 'TEST1P1'
    ORDER BY 1

Generally, you want something like ORDER BY 1,2 in the input query to
control how crosstab assigns the up to N different categories to N
value output columns *in a predictable manner*.  With just ORDER BY 1
you rely on the unspecified order (at least when it comes to column 2)
in which the query returns rows.

> Sorting TEST_NUMBER is taken care while generating TEST_NUMBER columns data.

Do you mean the order of inserts into test_test_details_all_mcm_init?
You can't rely on that ordering in your queries unless you use ORDER BY.
The unspecified ordering depends (among other things) on the tuple order
on disk.  Updating any of those rows affects that order because Postgres
updates rows by writing new tuples.

> Reference from the link.
> 
> AS concatenated (
> SERIAL_NUMBER character varying(18),
> TEST_RESULT_1 NUMERIC,
> TEST_RESULT_2 NUMERIC
> )
> 
> 
> Question on this: As per this, there is no need to define the column
> TEST_NUMBER format. That means, database is identifying the category
> column ( TEST_NUMBER ) on it's own. Am I right?

With "TEST_NUMBER format" do you mean "output column"?  If so, then yes,
you don't specify an output column for that.  Values in columns 1 and 3
are the only ones you see in the crosstab output.  Column 2 (with the
ORDER BY) only specifies whether test number 1 and 2 should be assigned
to TEST_RESULT_1 and TEST_RESULT_2 or the other way round.

This all is summarized in the docs[1]:

"In practice the SQL query should always specify ORDER BY 1,2 to ensure
 that the input rows are properly ordered, that is, values with the same
 row_name are brought together and correctly ordered within the row.
 Notice that crosstab itself does not pay any attention to the second
 column of the query result; it's just there to be ordered by, to control
 the order in which the third-column values appear across the page."

[1] https://www.postgresql.org/docs/current/tablefunc.html#TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT

-- 
Erik