[Bug]: Incorrect JSON grouping and array ordering in FOR JSON AUTO

[Teletele-Lin]

What happened?

FOR JSON AUTO: only compares with the last element (the previous row) and appends sequentially.

There are two logical bugs in the checkForDuplicateRows function within forjson.c. These bugs violate the core streaming semantics of FOR JSON AUTO and scramble the output order.

TEST CASES:

DROP TABLE Departments, Employees;

-- 1. Create Departments table
CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName NVARCHAR(50),
    Location NVARCHAR(50)
);

-- 2. Create Employees table
CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    FullName NVARCHAR(50),
    JobTitle NVARCHAR(50),
    Salary DECIMAL(10, 2),
    DeptID INT, -- Foreign Key
    Email NVARCHAR(100)
);

-- 3. Insert test data
INSERT INTO Departments (DeptID, DeptName, Location)
VALUES 
(1, N'R&D Department', N'Beijing'),
(2, N'Marketing Department', N'Shanghai');

INSERT INTO Employees (EmpID, FullName, JobTitle, Salary, DeptID, Email)
VALUES 
(101, N'Zhang San', N'Senior Engineer', 25000.00, 1, 'zhangsan@demo.com'),
(102, N'Li Si', N'Test Engineer', 15000.00, 2, NULL), -- Email is NULL
(103, N'Wang Wu', N'Marketing Manager', 18000.00, 1, 'wangwu@demo.com');

SELECT d.DeptName, d.Location, e.FullName, e.JobTitle 
FROM Departments d 
JOIN Employees e ON d.DeptID = e.DeptID;

SELECT d.DeptName, d.Location, e.FullName, e.JobTitle 
FROM Departments d 
JOIN Employees e ON d.DeptID = e.DeptID 
FOR JSON AUTO; -- without order by

IN SQL Server:

DeptName	Location	FullName	JobTitle
R&D Department	Beijing	Zhang San	Senior Engineer
Marketing Department	Shanghai	Li Si	Test Engineer
R&D Department	Beijing	Wang Wu	Marketing Manager

[
  {
    "DeptName": "R&D Department",
    "Location": "Beijing",
    "e": [
      {
        "FullName": "Zhang San",
        "JobTitle": "Senior Engineer"
      }
    ]
  },
  {
    "DeptName": "Marketing Department",
    "Location": "Shanghai",
    "e": [
      {
        "FullName": "Li Si",
        "JobTitle": "Test Engineer"
      }
    ]
  },
  {
    "DeptName": "R&D Department",
    "Location": "Beijing",
    "e": [
      {
        "FullName": "Wang Wu",
        "JobTitle": "Marketing Manager"
      }
    ]
  }
]

IN BBF:

NOTE: ORDER and Incorrect JSON grouping

R&D Department	Beijing	Zhang San	Senior Engineer
Marketing Department	Shanghai	Li Si	Test Engineer
R&D Department	Beijing	Wang Wu	Marketing Manager

[
  {
    "DeptName": "Marketing Department",
    "Location": "Shanghai",
    "e": [
      {
        "FullName": "Li Si",
        "JobTitle": "Test Engineer"
      }
    ]
  },
  {
    "DeptName": "R&D Department",
    "Location": "Beijing",
    "e": [
      {
        "FullName": "Zhang San",
        "JobTitle": "Senior Engineer"
      },
      {
        "FullName": "Wang Wu",
        "JobTitle": "Marketing Manager"
      }
    ]
  }
]

Version

BABEL_5_X_DEV (Default)

Extension

None

Which flavor of Linux are you using when you see the bug?

No response

Relevant log output

Code of Conduct

• I agree to follow this project's Code of Conduct.

[Teletele-Lin]

Fix (Same as #4614 ):

/*
 * checkForDuplicateRows inserts the given row into the json array 
 * nested based on the root object
 */
static void
checkForDuplicateRows(JsonbValue *jsonbArray, JsonbValue* row, int maxDepth, int currDepth, int* minInsertDepth)
{
    JsonbPair *arrRowPairs;
    JsonbPair *rowPairs;

    /* Reached max depth, insert directly as a leaf node at the end */
    if(currDepth == maxDepth) {
        jsonbArray->val.array.nElems++;
        jsonbArray->val.array.elems = (JsonbValue *) repalloc(jsonbArray->val.array.elems, sizeof(JsonbValue) * (jsonbArray->val.array.nElems));
        jsonbArray->val.array.elems[jsonbArray->val.array.nElems - 1] = *row;
        *minInsertDepth = currDepth;
        return;
    }
    else {
        bool matchedLast = false;

        if(jsonbArray->val.array.nElems > 0) {
            int lastIdx = jsonbArray->val.array.nElems - 1;
            int numPairsToCheck;
            bool sameElem = true;
            int j;
            
            arrRowPairs = (jsonbArray->val.array.elems[lastIdx]).val.object.pairs;
            
            /* Assume the last key-value pair is the child nested array, 
             * and all preceding pairs are parent attributes.
             */
            numPairsToCheck = (jsonbArray->val.array.elems[lastIdx]).val.object.nPairs - 1;

            for(j = 0; j < numPairsToCheck; j++) {
                rowPairs = row->val.object.pairs;
                
                if(arrRowPairs[j].value.type != rowPairs[j].value.type) {
                    sameElem = false;
                    break;
                }
                
                switch(rowPairs[j].value.type) {
                    case jbvNull:
                        sameElem = true;
                        break;
                    case jbvString:
                        sameElem = (strcmp(arrRowPairs[j].value.val.string.val, rowPairs[j].value.val.string.val) == 0);
                        break;
                    case jbvNumeric:
                        sameElem = (compareNumeric(arrRowPairs[j].value.val.numeric, rowPairs[j].value.val.numeric) == 0);
                        break;
                    case jbvBool:
                        sameElem = arrRowPairs[j].value.val.boolean == rowPairs[j].value.val.boolean;
                        break;
                    case jbvDatetime:
                        sameElem = true;
                        if(arrRowPairs[j].value.val.datetime.value != rowPairs[j].value.val.datetime.value ||
                           arrRowPairs[j].value.val.datetime.typid != rowPairs[j].value.val.datetime.typid ||
                           arrRowPairs[j].value.val.datetime.typmod != rowPairs[j].value.val.datetime.typmod ||
                           arrRowPairs[j].value.val.datetime.tz != rowPairs[j].value.val.datetime.tz) {
                            sameElem = false;
                        }
                        break;
                    default:
                        break;
                }
                
                if(!sameElem)
                    break;
            }

            /*
             * If numPairsToCheck == 0 (all parent attrs were NULL/omitted), 
             * the loop won't run, but sameElem remains true. 
             * We must still acknowledge a match and recurse into the child array.
             */
            if (sameElem) {
                matchedLast = true;
                
                /* Dynamic Depth Lock from our previous fix */
                if(*minInsertDepth == currDepth) {
                    *minInsertDepth = currDepth + 1;
                }
                
                checkForDuplicateRows(&(arrRowPairs[numPairsToCheck].value), 
                                      (row->val.object.pairs[numPairsToCheck].value).val.array.elems, 
                                      maxDepth, currDepth + 1, minInsertDepth);
            }
        }

        /* If it does not match the previous row, append to the end */
        if(currDepth == *minInsertDepth && !matchedLast) {
            jsonbArray->val.array.nElems++;
            jsonbArray->val.array.elems = (JsonbValue *) repalloc(jsonbArray->val.array.elems, sizeof(JsonbValue) * (jsonbArray->val.array.nElems));
            jsonbArray->val.array.elems[jsonbArray->val.array.nElems - 1] = *row;
        }
        
        return;
    }
}

[tanscorpio7]

@Teletele-Lin If you've already root-caused this, we'd love to see a pull request! 😄

[tanscorpio7]

BABEL-6455