JSONIn lookup crashes or returns wrong result for primitives

[Jacob Walls]

On databases besides Postgres (which has a native JSONField), a ​branch is taken that does not check the output_field for a Value expression. (Primitive strings, ints, bools, etc., might have other output_fields than JSONField.)

class Person(models.Model):
    data = models.JSONField(null=True)

def run():
    instance = Person.objects.create(data="primitive")
    qs = Person.objects.filter(data__in=[models.Value("primitive")])
    print(qs)

This query worked(*) on 6.0, but on 6.1 it returns the wrong result (MariaDB) or crashes (SQLite). Notice for SQLite here, the string "primitive" should not be provided to JSON_EXTRACT():

SELECT "app_person"."id", "app_person"."data" FROM "app_person" WHERE (CASE WHEN JSON_TYPE("app_person"."data", $) IN ('null','false','true') THEN JSON_TYPE("app_person"."data", $) ELSE JSON_EXTRACT("app_person"."data", $) END) IN (JSON_EXTRACT(primitive, '$'))

django.db.utils.OperationalError: malformed JSON

(*) -- On SQLite, you'd have to adjust the second query to use extra quotes, because of some quote weirdness, possibly similar to #32491?