sfdx force:data:soql:query -r csv -q returns null string for null field instead of blank

[gtiancai]

Summary

sfdx force:data:soql:query -r csv -q command returns null string for field which is null, instead of blank

Steps To Reproduce:

1. Create a object AA_Object

2. Create a restrict picklist field Type (Not required, 3 values: TA, TB, TC)

3. Create another text field Desc

4. Create a record with value: Type is null, Desc is "null"
   

5. run command: sfdx force:data:soql:query -r csv -q "SELECT Id, Type__c, Desc__c FROM AA_Object__c"

6. Save above CSV into a csv file (AARecords.csv)

7. run command sfdx force:data:bulk:upsert -s AA_Object__c -f .\AARecords.csv -i Id

Expected result

1. for the Type field, it should be empty
2. The csv should be able to import back into SF

Actual result

1. Both Type and Desc have same value: null
   

2. error: bad value for picklist
   

System Information

SFDX version:
{
"cliVersion": "sfdx-cli/7.142.1",
"architecture": "win32-x64",
"nodeVersion": "node-v16.14.0",
"pluginVersions": [
"@oclif/plugin-autocomplete 0.3.0 (core)",
"@oclif/plugin-commands 1.3.0 (core)",
"@oclif/plugin-help 3.3.1 (core)",
"@oclif/plugin-not-found 1.2.6 (core)",
"@oclif/plugin-plugins 1.10.11 (core)",
"@oclif/plugin-update 1.5.0 (core)",
"@oclif/plugin-warn-if-update-available 1.7.3 (core)",
"@oclif/plugin-which 1.0.4 (core)",
"@salesforce/sfdx-plugin-lwc-test 0.1.7 (core)",
"alias 1.2.1 (core)",
"apex 0.11.0 (core)",
"auth 1.8.1 (core)",
"community 1.1.4 (core)",
"config 1.3.23 (core)",
"custom-metadata 1.0.12 (core)",
"data 0.6.9 (core)",
"generator 1.2.2 (core)",
"info 1.3.0 (core)",
"limits 1.3.0 (core)",
"org 1.11.2 (core)",
"salesforce-alm 54.0.1 (core)",
"schema 1.1.0 (core)",
"sfdx-cli 7.142.1 (core)",
"source 1.8.18 (core)",
"telemetry 1.4.0 (core)",
"templates 54.2.0 (core)",
"trust 1.1.0 (core)",
"user 1.7.1 (core)"
],
"osVersion": "Windows_NT 10.0.19042"
}

[github-actions]

Thank you for filing this issue. We appreciate your feedback and will review the issue as soon as possible. Remember, however, that GitHub isn't a mechanism for receiving support under any agreement or SLA. If you require immediate assistance, contact Salesforce Customer Support.

[maggiben]

Hi, I'm having trouble repro, I followed your steps but the query returns no results, are you able to create a demo org with the object, field and record combination that are failing for you and then post it here ? (you can use dreamhouse-lwc as a template)

[gtiancai]

@maggiben - Thanks for the update. I don't think this is related to the Org. You may run the command in another shell, or save the result into CSV files. If you are using Powershell, you may try to save the result to a variable and Write-Host it.

[WillieRuemmele]

Hi @gtiancai,

I was unable to reproduce the error as well. I created the AA_Object as described, but the only question I had around that was the "restricted pick-list field" which I assumed meant checking this box

I then queried like you with

➜  dreamhouse-lwc git:(main) ✗  hub:(null) scratch:([email protected])
 ➜  sfdx force:data:soql:query -r csv -q "SELECT Id, picklist__c, Desc__c FROM AA_Object__c"
Id,picklist__c,Desc__c
a021h000004F3yHAAS,null,null
Querying Data... done

then saved that as you

➜  dreamhouse-lwc git:(main) ✗  hub:(null) scratch:([email protected])
 ➜  more AARecords.csv               
Id,picklist__c,Desc__c
a021h000004F3yHAAS,null,null

and then was able to upsert it

 ➜  sfdx force:data:bulk:upsert -s AA_Object__c -f AARecords.csv -i Id   
Check batch #1’s status with the command: 
sfdx force:data:bulk:status -i 7501h000003uemQAAQ -b 7511h000004ij7GAAQ
Bulk Upsert... done

I'm having a colleague try this out on his windows machine

update: on the windows machine this worked as well

[gtiancai]

@WillieRuemmele - Thanks for the update! Did you check the batch result in Org? I believe above message just indicates the job is submitted successfully, not the actual result.

[WillieRuemmele]

oops, my bad. I just checked the status and it was successful

 ➜  sfdx force:data:bulk:status -i 7501h000003ugUeAAI -b 7511h000004iku0AAA -u [email protected]

=== Batch Status
jobId:                   7501h000003ugUeAAI
state:                   Completed
createdDate:             2022-03-25T14:53:08.000Z
systemModstamp:          2022-03-25T14:53:09.000Z
numberRecordsProcessed:  1
numberRecordsFailed:     1
totalProcessingTime:     361
apiActiveProcessingTime: 230
apexProcessingTime:      0
Getting Status... done

and the bulk status from the windows attempt

 ➜  sfdx force:data:bulk:status -u [email protected] -i 7501h000003uenOAAQ -b 7511h000004ij8dAAA

=== Batch Status
jobId:                   7501h000003uenOAAQ
state:                   Completed
createdDate:             2022-03-24T15:12:43.000Z
systemModstamp:          2022-03-24T15:12:44.000Z
numberRecordsProcessed:  1
numberRecordsFailed:     1
totalProcessingTime:     61
apiActiveProcessingTime: 10
apexProcessingTime:      0
Getting Status... done

[gtiancai]

@WillieRuemmele - Thanks for the update! Now you can see the issue, right. Login to SF and download the batch result, you'll see the details.

[WillieRuemmele]

ahhh yes, it was right in front of me too numberRecordsFailed: 1 😬 and if the .csv file is

Id,picklist__c,Desc__c
a021h000004F3yHAAS,,null

It starts working

thank you for reporting this

@W-10909335@

[ghost]

This bug is affecting me too. I think it's a fairly recent regression... within last 2 months? I ocassionally run a scripted export using sfdx and it failed today due to precence of "null" strings in export csv where empty fields were expected.

sfdx-cli/7.142.1 win32-x64 node-v16.14.0

[WillieRuemmele]

FYI: here's the fix typeof null === 'object'

[ghost]

@WillieRuemmele I think this can be closed? I've been using an older version of sfdx due to #1543 also affecting soql exports but that's now closed and fixed in 7.156.1 my soql exports are behaving as expected