On the Flakiness of LLM-Generated Tests for Industrial and Open-Source Database Management Systems

Technical Summary: On the Flakiness of LLM-Generated Tests for Industrial and Open-Source Database Management Systems

Problem Statement
Flaky tests, which produce inconsistent results when executed multiple times on the same code state, are a significant challenge in industrial software testing, reducing efficiency and eroding developer trust. While recent work has identified flakiness as a potential issue in Large Language Model (LLM)-generated tests, the prevalence, root causes, and mechanisms of flakiness transfer in this context remain unclear. This study addresses the gap in dedicated research regarding the flakiness of LLM-generated tests, specifically within the context of Database Management Systems (DBMS), where test reliability is critical for business operations.

Methodology
The authors conducted an empirical study involving four DBMS: the closed-source industrial system SAP HANA, and three open-source systems (MySQL, DuckDB, and SQLite). The study focused on two test types: native C++ unit tests and SQL system tests.

• Test Generation Approach: The researchers adopted a test amplification strategy inspired by Alshahwan et al. [5], prompting LLMs to generate extended versions of existing test files to cover additional corner cases.
• Models: Two LLMs were evaluated: GPT-4o (selected for its high performance and ability to handle internal SAP HANA code) and Mistral-Large-Instruct-2407 (selected as an open-source alternative for reproducibility). Both models were run with a temperature of zero to maximize compilation success rates.
• Execution Environment: To isolate flakiness caused by the generated code rather than environmental noise, tests were executed 30 times each within isolated Docker containers. C++ tests were executed individually, while SQL tests were executed in file-order to respect the design of the respective test frameworks.
• Flakiness Detection: A test was classified as flaky if it produced both passing and failing results across the 30 executions.
• Flakiness Transfer Experiment: To assess susceptibility to "flakiness transfer," the authors injected a non-deterministic assertion (failing with 50% probability) into existing test files before prompting the LLMs to generate new tests.

Key Contributions

1. Prevalence Analysis: A quantitative assessment of the proportion of flaky tests in LLM-generated code compared to existing human-written tests across four DBMS.
2. Root Cause Identification: A manual inspection and categorization of the root causes of flakiness in LLM-generated tests, distinguishing between issues arising from the generation process and those transferred from context.
3. Flakiness Transfer Benchmark: An experimental evaluation of the degree to which LLMs transfer flaky patterns from provided in-context examples to newly generated code, comparing performance between closed-source (SAP HANA) and open-source (MySQL) contexts.

Results

• Compilation and Success Rates: LLMs struggled significantly with generating compilable C++ code for the closed-source SAP HANA project (Compilation Success Rate of ~40-46%), attributed to the model's lack of training data on the specific codebase. In contrast, SQL test generation showed high pass rates (81-95%).
• Flakiness Prevalence: The proportion of flaky tests in LLM-generated code was generally slightly higher than in existing tests.
  • For C++ tests, GPT-4o generated 0.29% flaky tests for SAP HANA and 0.18% for MySQL, while Mistral generated slightly higher rates (0.39% and 0.71%, respectively).
  • For SQL tests, where no existing flaky tests were found in the original suites, LLMs generated 96 flaky tests in total (43 by GPT-4o, 53 by Mistral).
• Root Causes: The most prevalent root cause of flakiness in LLM-generated tests was "Unordered Collection" (63% of 115 flaky tests). This occurred when LLMs generated SQL queries assuming a specific result order without using ORDER BY, leading to intermittent failures.
  • NIO (Non-Idempotent Outcome) flakiness was the second most common cause (10%), often resulting from the LLM mimicking existing tests that polluted the execution environment.
  • Concurrency issues were observed primarily in tests generated by Mistral.
• Flakiness Transfer: Both LLMs demonstrated susceptibility to transferring injected flakiness from the prompt context to generated tests.
  • In the SAP HANA context (closed-source, no training data), the transfer rate was higher (~69-78% of generated tests contained the injected flaky assertion).
  • In the MySQL context (open-source, likely present in training data), the transfer rate was lower (~41-58%).
  • This suggests that LLMs rely more heavily on provided context when they lack prior knowledge of the system, potentially adopting spurious correlations (such as flaky assertions) as valid coding patterns.

Significance and Claims
The paper claims to provide the first dedicated insights into the flakiness of LLM-generated tests for database systems. The authors conclude that while LLMs can generate natural-looking test code, they introduce specific flakiness risks, particularly regarding unordered data structures and the transfer of existing defects.

The study highlights that flakiness transfer is a critical issue, especially in closed-source environments where models lack training data and rely heavily on in-context learning. Consequently, the authors advise practitioners to prioritize fixing existing flaky tests before applying LLM-based generation approaches to prevent the propagation of flakiness. They also emphasize the necessity of providing LLMs with tailored context to mitigate root causes like "unordered collection" assumptions, noting that current context windows may be insufficient for large systems like SAP HANA. The findings serve as a cautionary baseline for developers and researchers aiming to integrate LLMs into automated testing pipelines.