Date: Mon, 6 May 2024 21:42:02 +0200
From: Erik Wienhold <ewie@ewie.name>
To: M Sarwar <sarwarmd02@outlook.com>
Cc: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: Re: ERROR:  return and sql tuple descriptions are incompatible
Message-ID: <3e96cfd1-e6fb-4a25-97c1-bd2c74284f78@ewie.name>
References: 
 <PH2P110MB0922C5109BE107AD82331420FF1EA@PH2P110MB0922.NAMP110.PROD.OUTLOOK.COM>
 <DM4PR19MB59783F61D28BE07B43F68C31D31E2@DM4PR19MB5978.namprd19.prod.outlook.com>
 <vulsb3n6dcd7wzjuhslppt3oij3nkwqh2olagmx2ibtnjzpccu@7c2jvcyjm2x2>
 <DM4PR19MB5978A4265AFFC9F7BAEB848AD31D2@DM4PR19MB5978.namprd19.prod.outlook.com>
 <7kadkskdcixknub2dvc4ovm24q5thdptxj2opu42pninbadonm@h5opztcvzcrh>
 <DM4PR19MB5978F92AECE1ECB4548706D2D31C2@DM4PR19MB5978.namprd19.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <DM4PR19MB5978F92AECE1ECB4548706D2D31C2@DM4PR19MB5978.namprd19.prod.outlook.com>
Archived-At: 
 <https://www.postgresql.org/message-id/3e96cfd1-e6fb-4a25-97c1-bd2c74284f78%40ewie.name>
Precedence: bulk

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