Обсуждение: Partition key causes problem for volatile target list query

I have found an odd behavior --- a query in the target list that assigns
to a partitioned column causes queries that would normally be volatile
to return always zero.

In the first query, no partitioning is used:

     d1 | d2
    ----+----
      1 |  0
      2 |  0
      2 |  1
      1 |  0
      1 |  2
      1 |  2
      1 |  0
      0 |  2
      2 |  0
      2 |  2

In the next query, 'd1' is a partition key and it gets a constant value
of zero for all rows:

     d1 | d2
    ----+----
-->      0 |  1
-->      0 |  2
      0 |  2
      0 |  1
      0 |  2
      0 |  1
      0 |  2
      0 |  2
      0 |  2
      0 |  2

The self-contained query is attached.  The value is _always_ zero, which
suggests random() is not being called;  calling setseed() does not
change that.  If I change "SELECT x" with "SELECT 2", the "2" is used. 
I see this behavior back to PG 11.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

Embrace your flaws.  They make you human, rather than perfect,
which you will never be.

Bruce Momjian <bruce@momjian.us> writes:
> I have found an odd behavior --- a query in the target list that assigns
> to a partitioned column causes queries that would normally be volatile
> to return always zero.

Well, if you looked further than the first few rows, it wouldn't be
"always zero".  But the select from the partitioned table will read
the first partition first, and that partition will have the rows
with d1=0, by definition.

=# explain select * from case_test2 limit 10;
                                        QUERY PLAN

--------------------------------------------------------------------------------
-----------
 Limit  (cost=0.00..0.19 rows=10 width=8)
   ->  Append  (cost=0.00..1987.90 rows=102260 width=8)
         ->  Seq Scan on case_test2_0 case_test2_1  (cost=0.00..478.84 rows=3318
4 width=8)
         ->  Seq Scan on case_test2_1 case_test2_2  (cost=0.00..480.86 rows=3328
6 width=8)
         ->  Seq Scan on case_test2_2 case_test2_3  (cost=0.00..484.30 rows=3353
0 width=8)
         ->  Seq Scan on case_test2_3 case_test2_4  (cost=0.00..32.60 rows=2260
width=8)
(6 rows)

The result appears sorted by d1, but that's an implementation artifact.

            regards, tom lane

On Thu, Jan 26, 2023 at 07:21:16PM -0500, Tom Lane wrote:
> Well, if you looked further than the first few rows, it wouldn't be
> "always zero".  But the select from the partitioned table will read
> the first partition first, and that partition will have the rows
> with d1=0, by definition.
> 
> =# explain select * from case_test2 limit 10;
>                                         QUERY PLAN                              
>            
> --------------------------------------------------------------------------------
> -----------
>  Limit  (cost=0.00..0.19 rows=10 width=8)
>    ->  Append  (cost=0.00..1987.90 rows=102260 width=8)
>          ->  Seq Scan on case_test2_0 case_test2_1  (cost=0.00..478.84 rows=3318
> 4 width=8)
>          ->  Seq Scan on case_test2_1 case_test2_2  (cost=0.00..480.86 rows=3328
> 6 width=8)
>          ->  Seq Scan on case_test2_2 case_test2_3  (cost=0.00..484.30 rows=3353
> 0 width=8)
>          ->  Seq Scan on case_test2_3 case_test2_4  (cost=0.00..32.60 rows=2260 
> width=8)
> (6 rows)
> 
> The result appears sorted by d1, but that's an implementation artifact.

Wow, thanks.  Not sure how I missed something so obvious.  I just saw it
myself by generating only 10 rows and noticing the numbers were always
increasing.


-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

Embrace your flaws.  They make you human, rather than perfect,
which you will never be.