A practical guide to SQL white-box testing

Tuya, Javier; Suárez-Cabal, M. José; Riva, Claudio de la

doi:10.1145/1147214.1147221

Cited by 8 publications

(6 citation statements)

References 12 publications

Supporting

Mentioning

Contrasting

Unclassified

Order By: Relevance

“…Moreover, aggregate functions are not considered. Several other articles study SQL query consistency taking into consideration referential integrity, such as [12]. In contrast, our proposal considers embedded SQL and aggregate functions.…”

Section: Related Workmentioning

confidence: 99%

Repairing OLAP queries in databases with referential integrity errors

Garcı́a-Garcı́a

Ordóñez

2010

Proceedings of the ACM 13th International Workshop on Data Warehousing and OLAP

View full text Add to dashboard Cite

Many database applications and OLAP tools dynamically generate SQL queries involving join operators and aggregate functions and send these queries to a database server for execution. This dynamically generated SQL code normally assumes the underlying tables and columns are clean and lacks the necessary robustness to deal with foreign keys with null and invalid or undefined values that are ubiquitous in databases with inconsistent or incomplete content. The outcome is that at query time, several issues arise mostly as inconsistencies in answer sets, difficult to detect and explain by users of OLAP tools. In this article, we present an automated query rewriting method for automatically generated OLAP queries that are executed over tables with foreign key columns having potentially null or invalid values. Our method is applicable in queries that use join operators and aggregate functions obeying the summarizability property (e.g. sum(), count()). If a user of an OLAP tool wants or requests it, using our method the queries that use join operators may be rewritten and he or she may be warned of the referential integrity condition of the underlying database and the answer sets may present alternative consistent results in the case aggregate functions are involved. Preliminary experimental evaluation shows rewritten queries provide valuable information on referential integrity and take almost the same time as original queries, highlighting efficiency is good and overhead is minimal.

show abstract

Section: Related Workmentioning

confidence: 99%

Repairing OLAP queries in databases with referential integrity errors

Garcı́a-Garcı́a

Ordóñez

2010

Proceedings of the ACM 13th International Workshop on Data Warehousing and OLAP

View full text Add to dashboard Cite

show abstract

“…An approach to designing tests for SQL queries based on the MC/DC coverage criterion has been suggested previously in [17]. Using this approach, the tester should design a test database for covering the following situations:…”

Section: Coverage Rulesmentioning

confidence: 99%

Query-aware shrinking test databases

Tuya

Suárez-Cabal

Riva

2009

Proceedings of the Second International Workshop on Testing Database Systems

Self Cite

View full text Add to dashboard Cite

Keeping the test databases as small as possible leads to faster execution of tests and facilitates the task of completing the test cases and evaluating the actual outputs against the expected. In this paper we present an automated approach to database reduction that considers an initial database that may be a copy of a production database and the set of queries that are executed against it. The database is reduced in order to preserve the coverage of the data with respect to the queries attaining large reductions with very similar fault detection ability.

show abstract

“…In a previous work the use of a criterion like Modified Condition Decision Coverage (MCDC) for testing SQL queries was suggested and informally presented [6]. This article focuses on the complete definition and automation of a criterion named SQLFpc that measures the coverage of the test data in relation to the query that is executed.…”

Section: Introductionmentioning

confidence: 99%

Full predicate coverage for testing SQL database queries

Tuya

Suárez-Cabal

Riva

2010

Software Testing Verif & Rel

Self Cite

View full text Add to dashboard Cite

SUMMARYIn the field of database applications a considerable part of the business logic is implemented using a semi-declarative language: the Structured Query Language (SQL). Because of the different semantics of SQL compared to other procedural languages, the conventional coverage criteria for testing are not directly applicable. This paper presents a criterion specifically tailored for SQL queries (SQLFpc). It is based on Masking Modified Condition Decision Coverage (MCDC) or Full Predicate Coverage and takes into account a wide range of the syntax and semantics of SQL, including selection, joining, grouping, aggregations, subqueries, case expressions and null values. The criterion assesses the coverage of the test data in relation to the query that is executed and it is expressed as a set of rules that are automatically generated and efficiently evaluated against a test database. The use of the criterion is illustrated in a case study which includes complex queries.

show abstract

A practical guide to SQL white-box testing

Cited by 8 publications

References 12 publications

Repairing OLAP queries in databases with referential integrity errors

Repairing OLAP queries in databases with referential integrity errors

Query-aware shrinking test databases

Full predicate coverage for testing SQL database queries

Contact Info

Product

Resources

About