Implementing common table expressions for MariaDB

Galina Shalygina, Boris Novikov

Research output

Abstract

Common Table Expressions (CTEs), introduced in the SQL Standard 1999, are similar to subroutines in programming languages: they can be referenced from multiple places in a query and may refer to themselves, providing recursive queries. Many RDBMSs implemented this feature, yet not in full. Until recently MariaDB did not have this feature either. This article describes CTE overall and looks through some interesting cases that are implemented in MariaDB only, like non-linear recursion and mutual recursion. It also compares optimizations for non-recursive CTEs across different RDBMSs. Finally, the results of experiments comparing computation of recursive CTEs for MariaDB and PostgreSQL are presented.

Original languageEnglish
JournalCEUR Workshop Proceedings
Volume1864
Publication statusPublished - 1 Jan 2017
Event2nd Conference on Software Engineering and Information Management, SEIM 2017 - Saint Petersburg
Duration: 21 Apr 2017 → …

Fingerprint

Subroutines
Computer programming languages
Experiments

Scopus subject areas

  • Computer Science(all)

Cite this

Shalygina, Galina ; Novikov, Boris. / Implementing common table expressions for MariaDB. In: CEUR Workshop Proceedings. 2017 ; Vol. 1864.
@article{e815d625857844519b2688ce695cee08,
title = "Implementing common table expressions for MariaDB",
abstract = "Common Table Expressions (CTEs), introduced in the SQL Standard 1999, are similar to subroutines in programming languages: they can be referenced from multiple places in a query and may refer to themselves, providing recursive queries. Many RDBMSs implemented this feature, yet not in full. Until recently MariaDB did not have this feature either. This article describes CTE overall and looks through some interesting cases that are implemented in MariaDB only, like non-linear recursion and mutual recursion. It also compares optimizations for non-recursive CTEs across different RDBMSs. Finally, the results of experiments comparing computation of recursive CTEs for MariaDB and PostgreSQL are presented.",
keywords = "Common Table Expressions, MariaDB, Optimization",
author = "Galina Shalygina and Boris Novikov",
year = "2017",
month = "1",
day = "1",
language = "English",
volume = "1864",
journal = "CEUR Workshop Proceedings",
issn = "1613-0073",
publisher = "RWTH Aahen University",

}

Implementing common table expressions for MariaDB. / Shalygina, Galina; Novikov, Boris.

In: CEUR Workshop Proceedings, Vol. 1864, 01.01.2017.

Research output

TY - JOUR

T1 - Implementing common table expressions for MariaDB

AU - Shalygina, Galina

AU - Novikov, Boris

PY - 2017/1/1

Y1 - 2017/1/1

N2 - Common Table Expressions (CTEs), introduced in the SQL Standard 1999, are similar to subroutines in programming languages: they can be referenced from multiple places in a query and may refer to themselves, providing recursive queries. Many RDBMSs implemented this feature, yet not in full. Until recently MariaDB did not have this feature either. This article describes CTE overall and looks through some interesting cases that are implemented in MariaDB only, like non-linear recursion and mutual recursion. It also compares optimizations for non-recursive CTEs across different RDBMSs. Finally, the results of experiments comparing computation of recursive CTEs for MariaDB and PostgreSQL are presented.

AB - Common Table Expressions (CTEs), introduced in the SQL Standard 1999, are similar to subroutines in programming languages: they can be referenced from multiple places in a query and may refer to themselves, providing recursive queries. Many RDBMSs implemented this feature, yet not in full. Until recently MariaDB did not have this feature either. This article describes CTE overall and looks through some interesting cases that are implemented in MariaDB only, like non-linear recursion and mutual recursion. It also compares optimizations for non-recursive CTEs across different RDBMSs. Finally, the results of experiments comparing computation of recursive CTEs for MariaDB and PostgreSQL are presented.

KW - Common Table Expressions

KW - MariaDB

KW - Optimization

UR - http://www.scopus.com/inward/record.url?scp=85025173200&partnerID=8YFLogxK

M3 - Conference article

AN - SCOPUS:85025173200

VL - 1864

JO - CEUR Workshop Proceedings

JF - CEUR Workshop Proceedings

SN - 1613-0073

ER -