v1-0001-fix-jsonb-compare.patch

From 54ab7c97cf6ccea090b5b69dfc5c00467fcf1b10 Mon Sep 17 00:00:00 2001
From: yanchengpeng <chengpeng.yan@protonbase.io>
Date: Mon, 18 Nov 2024 13:48:32 +0000
Subject: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays

---
 src/backend/utils/adt/jsonb_util.c  | 10 ++--
 src/test/regress/expected/jsonb.out | 88 +++++++++++++++++++++++++++++
 src/test/regress/sql/jsonb.sql      | 56 ++++++++++++++++++
 3 files changed, 150 insertions(+), 4 deletions(-)

diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c
index 9941daad2b..8b29b312d2 100644
--- a/src/backend/utils/adt/jsonb_util.c
+++ b/src/backend/utils/adt/jsonb_util.c
@@ -239,14 +239,16 @@ compareJsonbContainers(JsonbContainer *a, JsonbContainer *b)
 					case jbvArray:
 
 						/*
-						 * This could be a "raw scalar" pseudo array.  That's
+						 * This could be a "raw scalar" pseudo array. That's
 						 * a special case here though, since we still want the
-						 * general type-based comparisons to apply, and as far
-						 * as we're concerned a pseudo array is just a scalar.
+						 * general type-based comparisons to apply. As far
+						 * as we're concerned, a pseudo array is just a scalar.
+						 * If both are either raw scalars or both are arrays,
+						 * compare the number of elements.
 						 */
 						if (va.val.array.rawScalar != vb.val.array.rawScalar)
 							res = (va.val.array.rawScalar) ? -1 : 1;
-						if (va.val.array.nElems != vb.val.array.nElems)
+						else if (va.val.array.nElems != vb.val.array.nElems)
 							res = (va.val.array.nElems > vb.val.array.nElems) ? 1 : -1;
 						break;
 					case jbvObject:
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 7d163a156e..1043b8eee1 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5715,3 +5715,91 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
  12345
 (1 row)
 
+-- Create a table for JSONB ordering tests
+CREATE TABLE jsonb_test_order (j jsonb PRIMARY KEY);
+-- Insert JSONB values for scalar types
+INSERT INTO jsonb_test_order VALUES
+    ('null'),
+    ('true'),
+    ('false'),
+    ('0'),
+    ('3.14159'),
+    ('-3.14159'),
+    ('1E-10'),
+    ('1E+10'),
+    ('"A"'),
+    ('"a"'),
+    ('""');
+-- Insert JSONB values for arrays
+INSERT INTO jsonb_test_order VALUES
+    ('[]'),
+    ('[null]'),
+    ('[true]'),
+    ('[false]'),
+    ('[0]'),
+    ('[1, 2, 3]'),
+    ('[3, 2, 1]'),
+    ('["a", "b", "c"]'),
+    ('["c", "b", "a"]'),
+    ('[[1, 2], [3, 4]]'),
+    ('[[]]');
+-- Insert JSONB values for objects
+INSERT INTO jsonb_test_order VALUES
+    ('{}'),
+    ('{"a": null}'),
+    ('{"a": true}'),
+    ('{"a": false}'),
+    ('{"a": 1}'),
+    ('{"b": 1}'),
+    ('{"a": "a"}'),
+    ('{"a": {"nested": true}}'),
+    ('{"a": [1, 2, 3]}');
+-- Insert mixed JSONB values
+INSERT INTO jsonb_test_order VALUES
+    ('[{}, [], null, ""]'),
+    ('[{"a": 1}, [2, 3], false]'),
+    ('{"nested": {"key": "value"}}'),
+    ('[{"array": [1, 2]}, {"array": [3, 4]}]');
+-- Select all rows, ordered by the JSONB column
+SELECT * FROM jsonb_test_order ORDER BY j;
+                   j                    
+----------------------------------------
+ null
+ ""
+ "A"
+ "a"
+ -3.14159
+ 0
+ 0.0000000001
+ 3.14159
+ 10000000000
+ false
+ true
+ []
+ [null]
+ [0]
+ [false]
+ [true]
+ [[]]
+ [[1, 2], [3, 4]]
+ [{"array": [1, 2]}, {"array": [3, 4]}]
+ ["a", "b", "c"]
+ ["c", "b", "a"]
+ [1, 2, 3]
+ [3, 2, 1]
+ [{"a": 1}, [2, 3], false]
+ [{}, [], null, ""]
+ {}
+ {"a": null}
+ {"a": "a"}
+ {"a": 1}
+ {"a": false}
+ {"a": true}
+ {"a": [1, 2, 3]}
+ {"a": {"nested": true}}
+ {"b": 1}
+ {"nested": {"key": "value"}}
+(35 rows)
+
+-- Clean up the test table
+DROP TABLE jsonb_test_order;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 5f0190d5a2..e2ff06c4a9 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1559,3 +1559,59 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- Create a table for JSONB ordering tests
+CREATE TABLE jsonb_test_order (j jsonb PRIMARY KEY);
+
+-- Insert JSONB values for scalar types
+INSERT INTO jsonb_test_order VALUES
+    ('null'),
+    ('true'),
+    ('false'),
+    ('0'),
+    ('3.14159'),
+    ('-3.14159'),
+    ('1E-10'),
+    ('1E+10'),
+    ('"A"'),
+    ('"a"'),
+    ('""');
+
+-- Insert JSONB values for arrays
+INSERT INTO jsonb_test_order VALUES
+    ('[]'),
+    ('[null]'),
+    ('[true]'),
+    ('[false]'),
+    ('[0]'),
+    ('[1, 2, 3]'),
+    ('[3, 2, 1]'),
+    ('["a", "b", "c"]'),
+    ('["c", "b", "a"]'),
+    ('[[1, 2], [3, 4]]'),
+    ('[[]]');
+
+-- Insert JSONB values for objects
+INSERT INTO jsonb_test_order VALUES
+    ('{}'),
+    ('{"a": null}'),
+    ('{"a": true}'),
+    ('{"a": false}'),
+    ('{"a": 1}'),
+    ('{"b": 1}'),
+    ('{"a": "a"}'),
+    ('{"a": {"nested": true}}'),
+    ('{"a": [1, 2, 3]}');
+
+-- Insert mixed JSONB values
+INSERT INTO jsonb_test_order VALUES
+    ('[{}, [], null, ""]'),
+    ('[{"a": 1}, [2, 3], false]'),
+    ('{"nested": {"key": "value"}}'),
+    ('[{"array": [1, 2]}, {"array": [3, 4]}]');
+
+-- Select all rows, ordered by the JSONB column
+SELECT * FROM jsonb_test_order ORDER BY j;
+
+-- Clean up the test table
+DROP TABLE jsonb_test_order;
-- 
2.39.3 (Apple Git-145)