Qex: Symbolic SQL Query Explorer

  • Margus Veanes
  • Nikolai Tillmann
  • Jonathan de Halleux
Conference paper
Part of the Lecture Notes in Computer Science book series (LNCS, volume 6355)


We describe a technique and a tool called Qex for generating input tables and parameter values for a given parameterized SQL query. The evaluation semantics of an SQL query is translated into a specific background theory for a satisfiability modulo theories (SMT) solver as a set of equational axioms. Symbolic evaluation of a goal formula together with the background theory yields a model from which concrete tables and values are extracted. We use the SMT solver Z3 in the concrete implementation of Qex and provide an evaluation of its performance.


Function Symbol Table Constraint Input Table Equational Axiom Algebraic Data Type 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
  2. 2.
    Binnig, C., Kossmann, D., Lo, E.: Reverse query processing. In: Proceedings of the 23rd International Conference on Data Engineering (ICDE 2007), pp. 506–515. IEEE, Los Alamitos (2007)CrossRefGoogle Scholar
  3. 3.
    Binnig, C., Kossmann, D., Lo, E., Özsu, M.T.: Qagen: generating query-aware test databases. In: SIGMOD 2007: Proceedings of the 2007 ACM SIGMOD international conference on Management of data, pp. 341–352. ACM, New York (2007)CrossRefGoogle Scholar
  4. 4.
    Boyer, R.S., Moore, J.S.: A computational logic handbook. Academic Press Professional, Inc., San Diego (1988)zbMATHGoogle Scholar
  5. 5.
    Chakravarthy, U.S., Grant, J., Minker, J.: Logic-based approach to semantic query optimization. ACM Trans. Database Syst. 15(2), 162–207 (1990)CrossRefGoogle Scholar
  6. 6.
    Chays, D., Shahid, J., Frankl, P.G.: Query-based test generation for database applications. In: Proceedings of the 1st International Workshop on Testing Database Systems (DBTest 2008), pp. 1–6. ACM, New York (2008)Google Scholar
  7. 7.
    Chinaei, H.R.: An ordered bag semantics of SQL. Master’s thesis, University of Waterloo, Waterloo, Ontario, Canada (2007)Google Scholar
  8. 8.
    Cook, B., Kroening, D., Sharygina, N.: Cogent: Accurate theorem proving for program verification. In: Etessami, K., Rajamani, S.K. (eds.) CAV 2005. LNCS, vol. 3576, pp. 296–300. Springer, Heidelberg (2005)CrossRefGoogle Scholar
  9. 9.
    Dantsin, E., Voronkov, A.: Complexity of query answering in logic databases with complex values. In: Adian, S., Nerode, A. (eds.) LFCS 1997. LNCS, vol. 1234, pp. 56–66. Springer, Heidelberg (1997)CrossRefGoogle Scholar
  10. 10.
    de Moura, L., Bjørner, N.: Z3: An efficient SMT solver. In: Ramakrishnan, C.R., Rehof, J. (eds.) TACAS 2008. LNCS, vol. 4963, pp. 337–340. Springer, Heidelberg (2008)CrossRefGoogle Scholar
  11. 11.
    Dean, J., Ghemawat, S.: MapReduce: a flexible data processing tool. Commun. ACM 53(1), 72–77 (2010)CrossRefGoogle Scholar
  12. 12.
    Deng, Y., Frankl, P., Chays, D.: Testing database transactions with AGENDA. In: ICSE 2005, pp. 78–87. ACM, New York (2005)Google Scholar
  13. 13.
    Emmi, M., Majumdar, R., Sen, K.: Dynamic test input generation for database applications. In: Proceedings of the 2007 International Symposium on Software Testing and Analysis (ISSTA 2007), pp. 151–162. ACM, New York (2007)CrossRefGoogle Scholar
  14. 14.
    Grust, T., Scholl, M.H.: How to comprehend queries functionally. Journal of Intelligent Information Systems 12(2-3), 191–218 (1999)CrossRefGoogle Scholar
  15. 15.
    Hodges, W.: Model theory. Cambridge Univ. Press, Cambridge (1995)Google Scholar
  16. 16.
    Jackson, D.: Automating first-order relational logic. SIGSOFT Softw. Eng. Notes 25(6), 130–139 (2000)CrossRefGoogle Scholar
  17. 17.
    Jackson, D.: Software Abstractions. MIT Press, Cambridge (2006)Google Scholar
  18. 18.
    Khalek, S.A., Elkarablieh, B., Laleye, Y.O., Khurshid, S.: Query-aware test generation using a relational constraint solver. In: ASE, pp. 238–247 (2008)Google Scholar
  19. 19.
    Negri, M., Pelagatti, G., Sbattella, L.: Formal semantics of SQL queries. ACM Transactions on Database Systems 17(3), 513–534 (1991)MathSciNetCrossRefGoogle Scholar
  20. 20.
    Olston, C., Chopra, S., Srivastava, U.: Generating example data for dataflow programs. In: SIGMOD, pp. 245–256. ACM, New York (2009)CrossRefGoogle Scholar
  21. 21.
    Olston, C., Reed, B., Srivastava, U., Kumar, R., Tomkins, A.: Pig latin: a not-so-foreign language for data processing. In: SIGMOD, pp. 1099–1110. ACM, New York (2008)CrossRefGoogle Scholar
  22. 22.
  23. 23.
  24. 24.
    Ranise, S., Tinelli, C.: The SMT-LIB Standard: Version 1.2. Technical report, Department of Computer Science, The University of Iowa (2006),
  25. 25.
    Sheard, T., Stemple, D.: Automatic verification of database transaction safety. ACM Trans. Database Syst. 14(3), 322–368 (1989)CrossRefGoogle Scholar
  26. 26.
    Tsai, W.T., Volovik, D., Keefe, T.F.: Automated test case generation for programs specified by relational algebra queries. IEEE Trans. Softw. Eng. 16(3), 316–324 (1990)CrossRefGoogle Scholar
  27. 27.
    Veanes, M., de Halleux, P., Tillmann, N.: Rex: Symbolic regular expression explorer. In: Cavalli, A., Ghosh, S. (eds.) Third International Conference on Software Testing, Verification and Validation (ICST 2010), Paris, France, IEEE, Los Alamitos (April 2010)Google Scholar
  28. 28.
    Veanes, M., Grigorenko, P., de Halleux, P., Tillmann, N.: Symbolic query exploration. In: Breitman, K., Cavalcanti, A. (eds.) ICFEM 2009. LNCS, vol. 5885, pp. 49–68. Springer, Heidelberg (2009)CrossRefGoogle Scholar
  29. 29.
    Willmor, D., Embury, S.M.: An intensional approach to the specification of test cases for database applications. In: 28th International Conference on Software Engineering, pp. 102–111 (2006)Google Scholar
  30. 30.

Copyright information

© Springer-Verlag Berlin Heidelberg 2010

Authors and Affiliations

  • Margus Veanes
    • 1
  • Nikolai Tillmann
    • 1
  • Jonathan de Halleux
    • 1
  1. 1.Microsoft ResearchRedmondUSA

Personalised recommendations