[MDEV-27825] PIVOT create not setting default value of the column Created: 2022-02-13  Updated: 2022-05-10  Resolved: 2022-05-10

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Mahesh K Renduchintala Assignee: Michael Widenius
Resolution: Incomplete Votes: 0
Labels: None


 Description   

When I create a pivot using the following SQL, I do not see that default values are set for the pivot columns. The default value is always 0.

CREATE OR REPLACE TABLE sales3Q(
 
region VARCHAR(255) null,
 
country VARCHAR(255) null,
 
item_type VARCHAR(255) null,
 
measures VARCHAR(255) null,
 
Y2016_W06 double(32,2) default null flag=1,
 
Y2016_W07 double(32,2) default null flag=1,
 
Y2016_W08 double(32,2) default 0.5 flag=1,
 
Y2016_W09 double(32,2) default null flag=1
 
) engine=connect table_type=pivot
 
option_list='PivotCol=order_date, Accept=1'
 
SrcDef='select * from <some table>'

In the above example, W06 values exist in the source table, but not W07.
In W07 column, I get zero instead of null and in W08 column, I get zero instead of 0.5

Please check if this is a bug and if there is any workaround.

thanks
Mahesh

 Comments   
Comment by Sergei Golubchik [ 2022-03-21 ]

what MariaDB version was it?

Comment by Anel Husakovic [ 2022-04-11 ]

Do you have null values in order_date column ?
Can you please test on the example of t1 with random values?

Generated at Thu Feb 08 09:55:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.