[BUG] mysql ext_pillar as_json not merging

[supfors]

Description
I've setup an external mysql pillar and I'm having trouble using the as_json option introduced in 3005 (#59777). It returns an error when trying to merge.

ERROR:

[ERROR   ] Exception caught loading ext_pillar 'mysql':
  File "salt/pillar/__init__.py", line 1188, in ext_pillar
    ext = self._external_pillar_data(pillar, val, key)
  File "salt/pillar/__init__.py", line 1108, in _external_pillar_data
    ext = self.ext_pillars[key](self.minion_id, pillar, **val)
  File "salt/loader/lazy.py", line 149, in __call__
    return self.loader.run(run_func, *args, **kwargs)
  File "salt/loader/lazy.py", line 1228, in run
    return self._last_context.run(self._run_as, _func_or_method, *args, **kwargs)
  File "salt/loader/lazy.py", line 1243, in _run_as
    return _func_or_method(*args, **kwargs)
  File "/opt/saltstack/salt/run/salt/pillar/mysql.py", line 146, in ext_pillar
    return MySQLExtPillar().fetch(minion_id, pillar, *args, **kwargs)
  File "salt/pillar/sql_base.py", line 482, in fetch
    self.process_results(cursor.fetchall())
  File "salt/pillar/sql_base.py", line 354, in process_results
    crd = update(crd, ret[0], merge_lists=self.as_list)
  File "salt/utils/dictupdate.py", line 38, in update
    raise TypeError("Cannot update using non-dict types in dictupdate.update()")

Setup

ext_pillar:
  - mysql:
      mypillar:
        query: "SELECT json_pillar FROM minions WHERE minion_id = %s"
        as_json: True

mysql> show variables like "%_database";
+------------------------+--------------------+
| Variable_name          | Value              |
+------------------------+--------------------+
| character_set_database | utf8mb4            |
| collation_database     | utf8mb4_unicode_ci |
+------------------------+--------------------+

mysql> show full columns from minions;
+-------------+--------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field       | Type               | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------------+--------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id          | mediumint unsigned | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| minion_id   | varchar(200)       | utf8mb4_unicode_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| json_pillar | json               | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
+-------------+--------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+

mysql> select * from minions;
+----+-----------+--------------------+
| id | minion_id | json_pillar        |
+----+-----------+--------------------+
|  1 | localhost | {"key": "myvalue"} |
+----+-----------+--------------------+

Please be as specific as possible and give set-up details.

• on-prem machine
• VM (Virtualbox, KVM, etc. please specify)
• VM running on a cloud service, please be explicit and add details
• container (Kubernetes, Docker, containerd, etc. please specify)
• or a combination, please be explicit
• jails if it is FreeBSD
• classic packaging
• onedir packaging
• used bootstrap to install

Steps to Reproduce the behavior
salt localhost pillar.items

Expected behavior
I expect the mysql ext_pillar to merge without error.

Versions Report

salt --versions-report

Salt Version:
          Salt: 3005.1
 
Dependency Versions:
          cffi: 1.14.6
      cherrypy: unknown
      dateutil: 2.8.2
     docker-py: 5.0.3
         gitdb: Not Installed
     gitpython: Not Installed
        Jinja2: 3.1.0
       libgit2: 1.5.0
      M2Crypto: Not Installed
          Mako: Not Installed
       msgpack: 1.0.2
  msgpack-pure: Not Installed
  mysql-python: Not Installed
     pycparser: 2.21
      pycrypto: Not Installed
  pycryptodome: 3.9.8
        pygit2: 1.10.1
        Python: 3.9.14 (main, Oct  3 2022, 21:22:21)
  python-gnupg: 0.4.8
        PyYAML: 5.4.1
         PyZMQ: 23.2.0
         smmap: Not Installed
       timelib: 0.2.4
       Tornado: 4.5.3
           ZMQ: 4.3.4
 
System Versions:
          dist: ubuntu 22.04 jammy
        locale: utf-8
       machine: x86_64
       release: 5.15.0-58-generic
        system: Linux
       version: Ubuntu 22.04 jammy

root@ubuntu22:~# dpkg -l |grep python3-mysqldb
ii  python3-mysqldb                       1.4.6-1build1

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 8.0.32    |
+-----------+

Additional context
I've tested the same query on a json column in a postgres database without any issue.

[Ch3LL]

ping @aaannz any ideas here ^?

[aaannz]

Honestly I did not try it with mysql, just postgres. Is it possible pymysql does not return json data as dict?