Document Open Access Logo

Language-Integrated Recursive Queries

Authors Anna Herlihy , Amir Shaikhha , Anastasia Ailamaki , Martin Odersky

PDF

File

Thumbnail PDF
PDF
LIPIcs.ECOOP.2026.12.pdf
  • Filesize: 1.4 MB
  • 30 pages

Document Identifiers

Related Versions

Subject Classification

ACM Subject Classification
  • Theory of computation → Database query languages (principles)
  • Theory of computation → Recursive functions
  • Theory of computation → Type theory
Keywords
  • Language-integrated query
  • embedded DSL
  • SQL
  • Scala
  • fixpoint
  • Datalog

Metrics

Abstract

Performance-critical applications, including large-scale program analyses, graph analyses, and distributed system analyses, rely on fixed-point computations. The introduction of recursion using the WITH RECURSIVE keyword in SQL:1999 extended the ability of relational database systems to handle fixed-point computations, unlocking significant performance advantages by allowing computation to move closer to the data. Yet, with recursion, SQL becomes a Turing-complete programming language with new correctness and safety risks.
Full SQL lacks a fixed semantics, as the SQL specification is written in natural language with ambiguities that database vendors resolve in divergent ways. As a result, reasoning about the correctness of recursive SQL programs must rely on isolated, composable properties of queries rather than wrestling a unified formal model out of a language with notoriously inconsistent implementations across systems. To address these challenges, we propose a calculus, λ_RQL, that derives properties from embedded recursive queries using the host-language type system and, depending on the database backend, rejects queries that may lead to the three classes of recursive query errors: runtime database exceptions, incorrect results, and nontermination. Queries that respect all properties are guaranteed to find the minimal fixed point in a finite number of steps. We introduce TyQL, a practical implementation in Scala for safe, recursive language-integrated query. TyQL uses modern type system features of Scala 3, namely Named-Tuples and type-level pattern matching, to ensure query portability and safety. TyQL shows no performance penalty compared to SQL queries expressed as embedded strings while enabling a three-order-of-magnitude speedup over non-recursive SQL.

Cite As Get BibTex

Anna Herlihy, Amir Shaikhha, Anastasia Ailamaki, and Martin Odersky. Language-Integrated Recursive Queries. In 40th European Conference on Object-Oriented Programming (ECOOP 2026). Leibniz International Proceedings in Informatics (LIPIcs), Volume 372, pp. 12:1-12:30, Schloss Dagstuhl – Leibniz-Zentrum für Informatik (2026) https://doi.org/10.4230/LIPIcs.ECOOP.2026.12

Author Details

Anna Herlihy
  • EPFL, Lausanne, Switzerland
Amir Shaikhha
  • University of Edinburgh, UK
Anastasia Ailamaki
  • EPFL, Lausanne, Switzerland
Martin Odersky
  • EPFL, Lausanne, Switzerland

Funding

This research was funded in part by the Swiss National Science Foundation (SNSF), grant number TMAG-2_209506/1, "Capabilities for Typing Resources and Effects" and by the Swiss State Secretariat for Education, Research and Innovation (SERI) in the framework of the Prodasys project, approved by the European Research Council (ERC) as an Advanced Grant. This work was partly funded by a gift from RelationalAI and the Google Research Scholar Program.

Acknowledgements

We thank the anonymous reviewers, Oliver Bračevac, and Guillaume Martres for the insightful feedback.

Supplementary Materials

References

  1. Supun Abeysinghe, Anxhelo Xhebraj, and Tiark Rompf. Flan: An expressive and efficient datalog compiler for program analysis. Proc. ACM Program. Lang., 8(POPL), 2024. URL: https://doi.org/10.1145/3632928.
  2. Mahmoud Abo Khamis, Hung Q. Ngo, Reinhard Pichler, Dan Suciu, and Yisu Remy Wang. Convergence of datalog over (pre-) semirings. In Proceedings of the 41st ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems, PODS '22, pages 105-117, New York, NY, USA, 2022. Association for Computing Machinery. URL: https://doi.org/10.1145/3517804.3524140.
  3. Renzo Angles, János Benjamin Antal, Alex Averbuch, Peter Boncz, Orri Erling, Andrey Gubichev, Vlad Haprian, Moritz Kaufmann, Josep Lluís Larriba-Pey, Norbert Martínez-Bazan, József Marton, Marcus Paradies, Minh-Duc Pham, Arnau Prat-Pérez, Mirko Spasic, Benjamin A. Steer, Gábor Szárnyas, and Jack Waudby. The LDBC social network benchmark. CoRR, abs/2001.02299, 2020. URL: https://arxiv.org/abs/2001.02299.
  4. Michael Arntzenius and Neelakantan R. Krishnaswami. Datafun: a functional datalog. In Proceedings of the 21st ACM SIGPLAN International Conference on Functional Programming, ICFP 2016, pages 214-227, New York, NY, USA, 2016. Association for Computing Machinery. URL: https://doi.org/10.1145/2951913.2951948.
  5. John Backes, Sam Bayless, Byron Cook, Catherine Dodge, Andrew Gacek, Alan J. Hu, Temesghen Kahsai, Bill Kocik, Evgenii Kotelnikov, Jure Kukovec, Sean McLaughlin, Jason Reed, Neha Rungta, John Sizemore, Mark A. Stalzer, Preethi Srinivasan, Pavle Subotic, Carsten Varming, and Blake Whaley. Reachability analysis for aws-based networks. In Isil Dillig and Serdar Tasiran, editors, Computer Aided Verification - 31st International Conference, CAV 2019, New York City, NY, USA, July 15-18, 2019, Proceedings, Part II, Lecture Notes in Computer Science, pages 231-241. Springer, 2019. URL: https://doi.org/10.1007/978-3-030-25543-5_14.
  6. Francois Bancilhon and Raghu Ramakrishnan. An amateur’s introduction to recursive query processing strategies. In Proceedings of the 1986 ACM SIGMOD International Conference on Management of Data, SIGMOD '86, pages 16-52, New York, NY, USA, 1986. Association for Computing Machinery. URL: https://doi.org/10.1145/16894.16859.
  7. Luigi Bellomarini, Emanuel Sallinger, and Georg Gottlob. The vadalog system: datalog-based reasoning for knowledge graphs. Proc. VLDB Endow., 11(9):975-987, 2018. URL: https://doi.org/10.14778/3213880.3213888.
  8. Olivier Blanvillain, Jonathan Immanuel Brachthäuser, Maxime Kjaer, and Martin Odersky. Type-level programming with match types. Proc. ACM Program. Lang., 6(POPL), 2022. URL: https://doi.org/10.1145/3498698.
  9. Peter Buneman, Shamim Naqvi, Val Tannen, and Limsson Wong. Principles of programming with complex objects and collection types. Theoretical Computer Science, 149(1):3-48, 1995. Fourth International Conference on Database Theory (ICDT '92). URL: https://doi.org/10.1016/0304-3975(95)00024-Q.
  10. James Cheney, Sam Lindley, and Philip Wadler. A practical theory of language-integrated query. SIGPLAN Not., 48(9):403-416, 2013. URL: https://doi.org/10.1145/2544174.2500586.
  11. Shumo Chu, Konstantin Weitz, Alvin Cheung, and Dan Suciu. Hottsql: proving query rewrites with univalent sql semantics. In Proceedings of the 38th ACM SIGPLAN Conference on Programming Language Design and Implementation, PLDI 2017, pages 510-524, New York, NY, USA, 2017. Association for Computing Machinery. URL: https://doi.org/10.1145/3062341.3062348.
  12. Ezra Cooper. The script-writer’s dream: How to write great SQL in your own language, and be sure it will succeed. In Philippa Gardner and Floris Geerts, editors, Database Programming Languages - DBPL 2009, 12th International Symposium, Lyon, France, August 24, 2009. Proceedings, Lecture Notes in Computer Science, pages 36-51. Springer, 2009. URL: https://doi.org/10.1007/978-3-642-03793-1_3.
  13. Adnan Darwiche. Three modern roles for logic in ai. In Proceedings of the 39th ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems, PODS'20, pages 229-243, New York, NY, USA, 2020. Association for Computing Machinery. URL: https://doi.org/10.1145/3375395.3389131.
  14. Ariyam Das, Youfu Li, Jin Wang, Mingda Li, and Carlo Zaniolo. Bigdata applications from graph analytics to machine learning by aggregates in recursion. Electronic Proceedings in Theoretical Computer Science, 306:273-279, September 2019. URL: https://doi.org/10.4204/EPTCS.306.32.
  15. Diesel is a safe, extensible orm and query builder for rust, 2025. URL: https://diesel.rs/.
  16. Duckdb is a fast in-process analytical database, 2019. URL: https://duckdb.org/.
  17. Christian Duta, Denis Hirn, and Torsten Grust. Compiling pl/sql away. ArXiv, abs/1909.03291, 2019. URL: https://arxiv.org/abs/1909.03291.
  18. Zhiwei Fan, Jianqiao Zhu, Zuyu Zhang, Aws Albarghouthi, Paraschos Koutris, and Jignesh M. Patel. Scaling-up in-memory datalog processing: observations and techniques. Proc. VLDB Endow., 12(6):695-708, 2019. URL: https://doi.org/10.14778/3311880.3311886.
  19. Tim Fischer, Denis Hirn, and Torsten Grust. Snakes on a plan: Compiling python functions into plain sql queries. Proceedings of the 2022 International Conference on Management of Data, 2022. URL: https://api.semanticscholar.org/CorpusID:249579095.
  20. Thomas Goldschmidt, Ralf Reussner, and Jochen Winzen. A case study evaluation of maintainability and performance of persistency techniques. In Proceedings of the 30th International Conference on Software Engineering, ICSE '08, pages 401-410, New York, NY, USA, 2008. Association for Computing Machinery. URL: https://doi.org/10.1145/1368088.1368143.
  21. G. Gottlob, S. Ceri, and L. Tanca. What you always wanted to know about datalog (and never dared to ask). IEEE Transactions on Knowledge and Data Engineering, 1(01):146-166, January 1989. URL: https://doi.org/10.1109/69.43410.
  22. Sergio Greco and Cristian Molinaro. Datalog and logic databases. Synthesis Lectures on Data Management, 10:1-169, October 2015. URL: https://doi.org/10.2200/S00648ED1V01Y201505DTM041.
  23. Paolo Guagliardo and Leonid Libkin. A formal semantics of sql queries, its validation, and applications. Proc. VLDB Endow., 11(1):27-39, 2017. URL: https://doi.org/10.14778/3151113.3151116.
  24. Denis Hirn and Torsten Grust. One with recursive is worth many gotos. In Proceedings of the 2021 International Conference on Management of Data, SIGMOD '21, pages 723-735, New York, NY, USA, 2021. Association for Computing Machinery. URL: https://doi.org/10.1145/3448016.3457272.
  25. Denis Hirn and Torsten Grust. A fix for the fixation on fixpoints. In 13th Conference on Innovative Data Systems Research, CIDR 2023, Amsterdam, The Netherlands, January 8-11, 2023. www.cidrdb.org, 2023. URL: https://www.cidrdb.org/cidr2023/papers/p14-hirn.pdf.
  26. Recursion example: bill of materials, 2021. URL: https://www.ibm.com/docs/en/db2-event-store/2.0.0?topic=expression-recursion-example-bill-materials.
  27. International Organization for Standardization. ISO/IEC 9075-1:1999, Information technology — Database languages — SQL — Part 1: Framework (SQL/Framework). https://www.iso.org/standard/25498.html, 1999. Accessed: 2024-10-19.
  28. Herbert Jordan, Bernhard Scholz, and Pavle Subotic. Soufflé: On synthesis of program analyzers. In Swarat Chaudhuri and Azadeh Farzan, editors, Computer Aided Verification - 28th International Conference, CAV 2016, Toronto, ON, Canada, July 17-23, 2016, Proceedings, Part II, Lecture Notes in Computer Science, pages 422-430. Springer, 2016. URL: https://doi.org/10.1007/978-3-319-41540-6_23.
  29. Vojin Jovanovic, Amir Shaikhha, Sandro Stucki, Vladimir Nikolaev, Christoph Koch, and Martin Odersky. Yin-yang: concealing the deep embedding of dsls. In Ulrik Pagh Schultz and Matthew Flatt, editors, Generative Programming: Concepts and Experiences, GPCE'14, Vasteras, Sweden, September 15-16, 2014, pages 73-82. ACM, 2014. URL: https://doi.org/10.1145/2658761.2658771.
  30. Npm linq, 2025. URL: https://www.npmjs.com/package/linq.
  31. David Klopp, Sebastian Erdweg, and André Pacak. A typed multi-level datalog IR and its compiler framework. Proc. ACM Program. Lang., 8(OOPSLA2):1586-1614, 2024. URL: https://doi.org/10.1145/3689767.
  32. Magnus Madsen and Ondrej Lhoták. Fixpoints for the masses: programming with first-class datalog constraints. Proc. ACM Program. Lang., 4(OOPSLA):125:1-125:28, 2020. URL: https://doi.org/10.1145/3428193.
  33. Magnus Madsen and Ondřej Lhoták. Flix: A design for language-integrated datalog. Proc. ACM Program. Lang., 9(OOPSLA2), 2025. URL: https://doi.org/10.1145/3763126.
  34. Magnus Madsen, Ming-Ho Yee, and Ondřej Lhoták. From datalog to flix: a declarative language for fixed points on lattices. In Proceedings of the 37th ACM SIGPLAN Conference on Programming Language Design and Implementation, PLDI '16, pages 194-208, New York, NY, USA, 2016. Association for Computing Machinery. URL: https://doi.org/10.1145/2908080.2908096.
  35. Michael J. Maher and Raghu Ramakrishnan. Déjà vu in fixpoints of logic programs. In Ewing L. Lusk and Ross A. Overbeek, editors, Logic Programming, Proceedings of the North American Conference 1989, Cleveland, Ohio, USA, October 16-20, 1989. 2 Volumes, pages 963-980. MIT Press, 1989. Google Scholar
  36. Gregory Malecha, Greg Morrisett, Avraham Shinnar, and Ryan Wisnesky. Toward a verified relational database management system. In Proceedings of the 37th Annual ACM SIGPLAN-SIGACT Symposium on Principles of Programming Languages, POPL '10, pages 237-248, New York, NY, USA, 2010. Association for Computing Machinery. URL: https://doi.org/10.1145/1706299.1706329.
  37. Erik Meijer, Brian Beckman, and Gavin Bierman. Linq: reconciling object, relations and xml in the .net framework. In Proceedings of the 2006 ACM SIGMOD International Conference on Management of Data, SIGMOD '06, page 706, New York, NY, USA, 2006. Association for Computing Machinery. URL: https://doi.org/10.1145/1142473.1142552.
  38. Inderpal Singh Mumick, Hamid Pirahesh, and Raghu Ramakrishnan. The magic of duplicates and aggregates. In Dennis McLeod, Ron Sacks-Davis, and Hans-Jörg Schek, editors, 16th International Conference on Very Large Data Bases, August 13-16, 1990, Brisbane, Queensland, Australia, Proceedings, pages 264-277. Morgan Kaufmann, 1990. URL: http://www.vldb.org/conf/1990/P264.PDF.
  39. M. Negri, G. Pelagatti, and L. Sbattella. Formal semantics of sql queries. ACM Trans. Database Syst., 16(3):513-534, 1991. URL: https://doi.org/10.1145/111197.111212.
  40. Thomas Neumann and Viktor Leis. A critique of modern sql and a proposal towards a simple and expressive query language. In 14th Conference on Innovative Data Systems Research, CIDR 2024, Chaminade, USA, 2024. www.cidrdb.org, 2024. URL: https://www.cidrdb.org/cidr2024/papers/p48-neumann.pdf.
  41. Martin Odersky. Scala By Example. Programming Methods Laboratory, 2005. Google Scholar
  42. André Pacak and Sebastian Erdweg. Functional programming with datalog. In European Conference on Object-Oriented Programming, 2022. URL: https://api.semanticscholar.org/CorpusID:249961682.
  43. Zio quill, 2016. URL: https://zio.dev/zio-quill/.
  44. Karthik Ramachandra, Kwanghyun Park, K. Venkatesh Emani, Alan Halverson, César Galindo-Legaria, and Conor Cunningham. Froid: Optimization of imperative programs in a relational database. Proc. VLDB Endow., 11(4):432-444, October 2018. URL: https://doi.org/10.1145/3164135.3164140.
  45. R. Ramakrishnan and J. Gehrke. Database Management Systems. McGraw-Hill higher education. McGraw-Hill Education, 2003. URL: https://books.google.ch/books?id=JSVhe-WLGZ0C.
  46. Wilmer Ricciotti and James Cheney. A formalization of sql with nulls. J. Autom. Reason., 66(4):989-1030, 2022. URL: https://doi.org/10.1007/s10817-022-09632-4.
  47. Arash Sahebolamri, Thomas Gilray, and Kristopher Micinski. Seamless deductive inference via macros. In Proceedings of the 31st ACM SIGPLAN International Conference on Compiler Construction, CC 2022, pages 77-88, New York, NY, USA, 2022. Association for Computing Machinery. URL: https://doi.org/10.1145/3497776.3517779.
  48. Scalasql, January 2024. URL: https://github.com/com-lihaoyi/scalasql.
  49. Bernhard Scholz, Herbert Jordan, Pavle Subotić, and Till Westmann. On fast large-scale program analysis in datalog. In Proceedings of the 25th International Conference on Compiler Construction, CC '16, pages 196-206, New York, NY, USA, 2016. Association for Computing Machinery. URL: https://doi.org/10.1145/2892208.2892226.
  50. Amir Shaikhha. An embedded query language in Scala. Master’s thesis, EPFL, 2013. Google Scholar
  51. Amir Shaikhha, Dan Suciu, Maximilian Schleich, and Hung Q. Ngo. Optimizing nested recursive queries. Proc. ACM Manag. Data, 2(1):16:1-16:27, 2024. URL: https://doi.org/10.1145/3639271.
  52. G. V. Maximova Shalygina and Boris Novikov. Implementing common table expressions for mariadb. In Proceedings of the Second Conference on Software Engineering and Information Management (SEIM-2017), 2017. URL: https://api.semanticscholar.org/CorpusID:11023928.
  53. Aleksey Shipilev, Sergey Kuksenko, Anders Astrand, Staan Friberg, and Henrik Loef. Openjdk code tools: Jmh, 2022. URL: http://openjdk.java.net/projects/code-tools/jmh/.
  54. Slick: Scala langauge-integrated connection kit, 2011. URL: https://scala-slick.org/.
  55. Souffle: Logic defined static analysis, 2019. URL: https://souffle-lang.github.io/index.html.
  56. Daniel Spiewak and Tian Zhao. Scalaql: Language-integrated database queries for scala. In Mark van den Brand, Dragan Gasevic, and Jeff Gray, editors, Software Language Engineering, Second International Conference, SLE 2009, Denver, CO, USA, October 5-6, 2009, Revised Selected Papers, Lecture Notes in Computer Science, pages 154-163. Springer, 2009. URL: https://doi.org/10.1007/978-3-642-12107-4_12.
  57. Jonathan Lindegaard Starup, Magnus Madsen, and Ondřej Lhoták. Breaking the negative cycle: Exploring the design space of stratification for first-class datalog constraints. In European Conference on Object-Oriented Programming, 2023. URL: https://api.semanticscholar.org/CorpusID:259765749.
  58. Dan Suciu. Bounded fixpoints for complex objects. Theoretical Computer Science, 176(1):283-328, 1997. URL: https://doi.org/10.1016/S0304-3975(96)00293-9.
  59. Margus Veanes, Nikolai Tillmann, and Jonathan de Halleux. Qex: Symbolic SQL query explorer. In Edmund M. Clarke and Andrei Voronkov, editors, Logic for Programming, Artificial Intelligence, and Reasoning - 16th International Conference, LPAR-16, Dakar, Senegal, April 25-May 1, 2010, Revised Selected Papers, Lecture Notes in Computer Science, pages 425-446. Springer, 2010. URL: https://doi.org/10.1007/978-3-642-17511-4_24.
  60. Jonas Vistrup. An expert system for danish traffic law cases (student abstract). Proceedings of the AAAI Conference on Artificial Intelligence, 39(28):29519-29520, April 2025. URL: https://doi.org/10.1609/aaai.v39i28.35310.
  61. Jin Wang, Jiacheng Wu, Mingda Li, Jiaqi Gu, Ariyam Das, and Carlo Zaniolo. Formal semantics and high performance in declarative machine learning using datalog. VLDB J., 30(5):859-881, 2021. URL: https://doi.org/10.1007/S00778-021-00665-6.
  62. Jin Wang, Guorui Xiao, Jiaqi Gu, Jiacheng Wu, and Carlo Zaniolo. Rasql: A powerful language and its system for big data applications. In Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data, SIGMOD '20, pages 2673-2676, New York, NY, USA, 2020. Association for Computing Machinery. URL: https://doi.org/10.1145/3318464.3384677.
  63. Kai Wang, Aftab Hussain, Zhiqiang Zuo, Guoqing Xu, and Ardalan Amiri Sani. Graspan: A single-machine disk-based graph system for interprocedural static analyses of large-scale systems code. In Proceedings of the Twenty-Second International Conference on Architectural Support for Programming Languages and Operating Systems, ASPLOS '17, pages 389-404, New York, NY, USA, 2017. Association for Computing Machinery. URL: https://doi.org/10.1145/3037697.3037744.
  64. Ben Wiedermann and William R. Cook. Extracting queries by static analysis of transparent persistence. SIGPLAN Not., 42(1):199-210, 2007. URL: https://doi.org/10.1145/1190215.1190248.
  65. Markus Winand. With recursive (top level), October 2024. URL: https://modern-sql.com/caniuse/with_recursive_(top_level).
  66. Limsoon Wong. Kleisli, a functional query system. J. Funct. Program., 10:19-56, January 2000. URL: https://doi.org/10.1017/S0956796899003585.
  67. Jiacheng Wu, Jin Wang, and Carlo Zaniolo. Optimizing parallel recursive datalog evaluation on multicore machines. In Proceedings of the 2022 International Conference on Management of Data, SIGMOD '22, pages 1433-1446, New York, NY, USA, 2022. Association for Computing Machinery. URL: https://doi.org/10.1145/3514221.3517853.
  68. Guoqiang Zhang, Benjamin Mariano, Xipeng Shen, and Işıl Dillig. Automated translation of functional big data queries to sql. Proc. ACM Program. Lang., 7(OOPSLA1), 2023. URL: https://doi.org/10.1145/3586047.
Any Issues?
X

Feedback on the Current Page

CAPTCHA

Thanks for your feedback!

Feedback submitted to Dagstuhl Publishing

Could not send message

Please try again later or send an E-mail
© 2023-2026 Schloss Dagstuhl – LZI GmbH Schloss Dagstuhl – LZI GmbH About DROPS Imprint Privacy Contact