Unable to define NUll in Ansible set_fact

[aworldofcode]

https://gist.github.com/aworldofcode/35de280946fabcc66e2eea15c8a13dd8

I am not able to pass a value of NULL from a key in set_fact , anyt advise / help appreciated ... I have tried various combinations.

mariadb sql table includes the following column date_of_removal datetime DEFAULT NULL,

The Key Dict is either

 'main_dict.removal_info is defined': '2021-12-19 21:50:43'  # a datetime properly formated  
 'main_dict.removal_info is defined': 'N/A'  # A string 

The goal is to properly provide a Null when 'N/A' is present

Meanwhile in Ansible set_fact

- name: Set the etl_dictionary Key Value Dictionary from main_dict request 
  set_fact:
    etl_dictionary: >-
          {
 
           {% if main_dict.removal_info is defined -%} 
            {% if 'N/A' in main_dict.removal_info  -%}
                "date_of_removal": 'Null' ,
              {% else %}
                "date_of_removal": "{{main_dict.removal_info}}",
            {% endif %} 
          {% endif %} 
            
          }

ERROR Cannot execute SQL 'INSERT INTO .... 'N/A', 'No', 'null', 'N/A' ... The expected behavior to achieve is ... 'N/A', 'No', Null, 'N/A'

[aworldofcode]

TASK [cert_discovery : Debug certs output] ********************************************************************************************************************************************************* Wednesday 15 December 2021 07:32:27 -0500 (0:00:00.058) 0:00:00.998 **** ok: [localhost] => { "etl_dictionary": { "date_of_removal": null } }

TASK [cert_discovery : Print the etl_dictionary Columns and Keys] ********************************************************************************************************************************** Wednesday 15 December 2021 07:32:27 -0500 (0:00:00.050) 0:00:01.048 **** ok: [localhost] => { "msg": "(date_of_removal) VALUES (None)" }

TASK [cert_discovery : Insert etl_dictionary to our etl_in_cert_scanner DataBase] *********************************************************************************************************** Wednesday 15 December 2021 07:32:27 -0500 (0:00:00.056) 0:00:01.105 **** fatal: [localhost]: FAILED! => {"changed": false, "msg": "Cannot execute SQL 'INSERT INTO etl_in_cert_scanner ( date_of_removal ) VALUES ( 'None' )' args [None]: (1292, u"Incorrect datetime value: 'None' for column certmgmt_lst.etl_in_cert_scanner.date_of_removal at row 1")"} ...ignoring

[Andersson007]

@aworldofcode hi, thank you for the question.

I think this is an expected behavior https://github.com/ansible/ansible/issues/57438#issuecomment-499559239

Couple of possible workarounds from me:

   - name: Set fact
     set_fact:
       etl_dictionary: >-
          {

           {% if main_dict.removal_info is defined -%} 
            {% if 'N/A' in main_dict.removal_info  -%}
                "date_of_removal": null,
              {% else %}
                "date_of_removal": "{{main_dict.removal_info}}",
            {% endif %} 
          {% endif %} 
            
          }

   - name: Insert null
     community.mysql.mysql_query:
       query: INSERT INTO test_null_table (test_col) VALUES (%s)
       positional_args:
       - null
     when: not etl_dictionary.date_of_removal
       
   - name: Insert not null
     community.mysql.mysql_query:
       query: INSERT INTO test_null_table (test_col) VALUES (%s)
       positional_args:
       - '{{ etl_dictionary.date_of_removal }}'
     when: etl_dictionary.date_of_removal

OR

   - name: Set fact
     set_fact:
       etl_dictionary: >-
          {

           {% if main_dict.removal_info is defined -%} 
            {% if 'N/A' in main_dict.removal_info  -%}
                "date_of_removal": null,
              {% else %}
                "date_of_removal": "{{main_dict.removal_info}}",
            {% endif %} 
          {% endif %} 
            
          }

   - name: Compose statement when null
     set_fact:
       insert: 'INSERT INTO test_null_table (test_col) VALUES (NULL)'
     when: not etl_dictionary.date_of_removal

   - name: Compose statement when not null
     set_fact:
       insert: "INSERT INTO test_null_table (test_col) VALUES ('{{ etl_dictionary.date_of_removal }}')"
     when: etl_dictionary.date_of_removal

   - name: Insert null
     community.mysql.mysql_query:
       query: '{{ insert }}'

[Andersson007]

Please let me know if this helps

[aworldofcode]

@Andersson007 Thank you ! Let me review and run some test, I had to temporarily add for a 00:00:00: 00:00:00 default entry for now, which is not ideal at all so I look forward to testing these options.