Abstract
Language-integrated query adds to database query the power of high-level programming languages such as abstraction, compositionality, and nested data structures. Cheney et al. designed a two-level typed language for it and showed that any closed term of suitable type can be normalized to a single SQL query which does not have nested data structures nor nested SELECT clauses.
This paper extends their language to cover the GROUP BY clause in SQL to express grouping and aggregate functions. Although the GROUP BY clause is frequently used, it is not covered by existing studies on efficient implementation of language-integrated queries. In fact, it seems impossible to express composition of two aggregate functions by a single aggregate function, therefore, there exists a query with nested GROUP BY clauses which has no equivalent query without nested one. However, since several database engines such as PostgreSQL allow nested queries, we can still ask if it is possible to convert an arbitrary query with grouping and aggregation to a single query in SQL which allows nested queries, but disallows nested data structures such as a table of tables.
This paper solves the latter question affirmatively. Our key observation is that the GROUP BY clause in SQL does two different kinds of things: manipulating input/output data and grouping with aggregation, the former can be transformed, but may have complex types, while the latter cannot be transformed, but has simple types. Hence, we decouple the GROUP BY clause and introduce primitives into our language-integrated query to obtain a calculus which can express GROUP BY. We then show our language has the normalization property that every query is converted to a single query which does not have nested data structures. We conduct simple benchmarks which show that queries in our language can be transformed to efficient SQL queries.
This is a preview of subscription content, log in via an institution.
Buying options
Tax calculation will be finalised at checkout
Purchases are for personal use only
Learn about institutional subscriptionsNotes
- 1.
- 2.
We say that a table type (a bag type of a record type) is not nested, if each component type of the record is a basic type such as string, integer, or floating-point number.
- 3.
Queries that has a for construct inside another for construct are called queries with nested control structures.
- 4.
- 5.
An SQL-convertible query must compute a bag of records whose fields are of base types. Hence, we normalize queries of such types only.
- 6.
In this study the set of aggregate functions is left unspecified, but we assume that they must operate on simple types. See the type system.
- 7.
When L is an empty bag, we can transform the whole expression, but it is a special case which does not contribute general patterns.
- 8.
In the introduction of the present paper, we already explained it against SQL’s GROUP BY.
References
Meijer, E., Beckman, B., Bierman, G.M.: LINQ: reconciling object, relations and XML in the .net framework. In: Proceedings of the ACM SIGMOD International Conference on Management of Data, Chicago, Illinois, USA, 27–29 June 2006, p. 706 (2006)
Grust, T., Rittinger, J., Schreiber, T.: Avalanche-safe LINQ compilation. PVLDB 3(1), 162–172 (2010)
Cooper, E.: The script-writer’s dream: how to write great SQL in your own language, and be sure it will succeed. In: Gardner, P., Geerts, F. (eds.) DBPL 2009. LNCS, vol. 5708, pp. 36–51. Springer, Heidelberg (2009). https://doi.org/10.1007/978-3-642-03793-1_3
Cheney, J., Lindley, S., Wadler, P.: A practical theory of language-integrated query. In: ACM SIGPLAN International Conference on Functional Programming, ICFP 2013, Boston, MA, USA, 25–27 September 2013, pp. 403–416 (2013)
Suzuki, K., Kiselyov, O., Kameyama, Y.: Finally, safely-extensible and efficient language-integrated query. In: Proceedings of the 2016 ACM SIGPLAN Workshop on Partial Evaluation and Program Manipulation, PEPM 2016, St. Petersburg, FL, USA, 20–22 January 2016, pp. 37–48 (2016)
Carette, J., Kiselyov, O., Shan, C.: Finally tagless, partially evaluated: tagless staged interpreters for simpler typed languages, vol. 19, pp. 509–543 (2009)
Wong, L.: Normal forms and conservative extension properties for query languages over collection types. J. Comput. Syst. Sci. 52(3), 495–505 (1996)
Libkin, L., Wong, L.: Aggregate functions, conservative extension, and linear orders. In: Beeri, C., Ohori, A., Shasha, D.E. (eds.) Database Programming Languages (DBPL-4). Workshops in Computing, pp. 282–294. Springer, London (1994). https://doi.org/10.1007/978-1-4471-3564-7_16
Peyton Jones, S.L., Wadler, P.: Comprehensive comprehensions. In: Proceedings of the ACM SIGPLAN Workshop on Haskell, Haskell 2007, Freiburg, Germany, 30 September 2007, pp. 61–72 (2007)
Cheney, J., Lindley, S., Wadler, P.: Query shredding: efficient relational evaluation of queries over nested multisets. In: International Conference on Management of Data, SIGMOD 2014, Snowbird, UT, USA, 22–27 June 2014, pp. 1027–1038 (2014)
Kiselyov, O., Katsushima, T.: Sound and efficient language-integrated query. In: Chang, B.-Y.E. (ed.) APLAS 2017. LNCS, vol. 10695, pp. 364–383. Springer, Cham (2017). https://doi.org/10.1007/978-3-319-71237-6_18
Acknowledgments
We would like to thank Oleg Kiselyov and Kenichi Suzuki for development of Quel and its tagless-final implementation. The second author is supported in part by JSPS Grant-in-Aid for Scientific Research (B) No. 18H03218.
Author information
Authors and Affiliations
Corresponding author
Editor information
Editors and Affiliations
A Normalization Rules of Quel
A Normalization Rules of Quel
Normalization rules of Quel are given as follows:
Normalization rules of Quela
Rights and permissions
Copyright information
© 2020 Springer Nature Switzerland AG
About this paper
Cite this paper
Okura, R., Kameyama, Y. (2020). Language-Integrated Query with Nested Data Structures and Grouping. In: Nakano, K., Sagonas, K. (eds) Functional and Logic Programming. FLOPS 2020. Lecture Notes in Computer Science(), vol 12073. Springer, Cham. https://doi.org/10.1007/978-3-030-59025-3_9
Download citation
DOI: https://doi.org/10.1007/978-3-030-59025-3_9
Published:
Publisher Name: Springer, Cham
Print ISBN: 978-3-030-59024-6
Online ISBN: 978-3-030-59025-3
eBook Packages: Computer ScienceComputer Science (R0)