Repository: web2py/pydal

Issue:
threading.currentThread has been deprecated in favor of current_thread in Python 3.10
Ref : python/cpython#25174

```
pydal/pydal/base.py:        thread_key = "%s.%s" % (socket.gethostname(), threading.currentThread())
pydal/pydal/base.py:        thread_key = "%s.%s" % (socket.gethostname(), threading.currentThread())
```

Discussion:

Patch:
diff --git a/pydal/adapters/base.py b/pydal/adapters/base.py
index 40bb4470..c4aad6fb 100644
--- a/pydal/adapters/base.py
+++ b/pydal/adapters/base.py
@@ -668,7 +668,18 @@ class SQLAdapter(BaseAdapter):
         cache=None,
         cacheable=None,
         processor=None,
+        cte_collector=None,
     ):
+        if cte_collector is None:
+            cte_collector = dict(
+                stack = [],
+                seen = set(),
+                is_recursive = False
+            )
+            is_toplevel = True
+        else:
+            is_toplevel = False
+
         #: parse tablemap
         tablemap = self.tables(query)
         #: apply common filters if needed
@@ -682,6 +693,7 @@ class SQLAdapter(BaseAdapter):
             tablemap.pop(item, None)
         if len(tablemap) < 1:
             raise SyntaxError("Set: no tables selected")
+
         query_tables = list(tablemap)
         #: check for_update argument
         # [Note - gi0baro] I think this should be removed since useless?
@@ -723,19 +735,19 @@ class SQLAdapter(BaseAdapter):
         if join and not left:
             cross_joins = iexcluded + list(itables_to_merge)
             tokens = [table_alias(cross_joins[0])]
-            tokens += [
+            tokens.extend([
                 self.dialect.cross_join(table_alias(t), query_env)
                 for t in cross_joins[1:]
-            ]
-            tokens += [self.dialect.join(t, query_env) for t in ijoin_on]
+            ])
+            tokens.extend([self.dialect.join(t, query_env) for t in ijoin_on])
             sql_t = " ".join(tokens)
         elif not join and left:
             cross_joins = excluded + list(tables_to_merge)
             tokens = [table_alias(cross_joins[0])]
-            tokens += [
+            tokens.extend([
                 self.dialect.cross_join(table_alias(t), query_env)
                 for t in cross_joins[1:]
-            ]
+            ])
             # FIXME: WTF? This is not correct syntax at least on PostgreSQL
             if join_tables:
                 tokens.append(
@@ -743,7 +755,7 @@ class SQLAdapter(BaseAdapter):
                         ",".join([table_alias(t) for t in join_tables]), query_env
                     )
                 )
-            tokens += [self.dialect.left_join(t, query_env) for t in join_on]
+            tokens.extend([self.dialect.left_join(t, query_env) for t in join_on])
             sql_t = " ".join(tokens)
         elif join and left:
             all_tables_in_query = set(
@@ -754,11 +766,11 @@ class SQLAdapter(BaseAdapter):
                 all_tables_in_query.difference(tables_in_joinon)
             )
             tokens = [table_alias(tables_not_in_joinon[0])]
-            tokens += [
+            tokens.extend([
                 self.dialect.cross_join(table_alias(t), query_env)
                 for t in tables_not_in_joinon[1:]
-            ]
-            tokens += [self.dialect.join(t, query_env) for t in ijoin_on]
+            ])
+            tokens.extend([self.dialect.join(t, query_env) for t in ijoin_on])
             # FIXME: WTF? This is not correct syntax at least on PostgreSQL
             if join_tables:
                 tokens.append(
@@ -766,7 +778,7 @@ class SQLAdapter(BaseAdapter):
                         ",".join([table_alias(t) for t in join_tables]), query_env
                     )
                 )
-            tokens += [self.dialect.left_join(t, query_env) for t in join_on]
+            tokens.extend([self.dialect.left_join(t, query_env) for t in join_on])
             sql_t = " ".join(tokens)
         else:
             sql_t = ", ".join(table_alias(t) for t in query_tables)
@@ -804,12 +816,22 @@ class SQLAdapter(BaseAdapter):
                     for t in query_tables
                     if not isinstance(tablemap[t], Select)
                     for x in (
-                        hasattr(tablemap[t], "_primarykey")
-                        and tablemap[t]._primarykey
+                        getattr(tablemap[t], "_primarykey", None)
                         or ["_id"]
                     )
                 ]
             )
+
+        #: build CTE
+        [t.cte(cte_collector) for t in tablemap.values() if getattr(t, 'is_cte', None)]
+        if is_toplevel and cte_collector['stack']:
+            with_cte = [
+                cte_collector['is_recursive'],
+                ', '.join(cte_collector['stack'])
+            ]
+        else:
+            with_cte = None
+
         #: build sql using dialect
         return (
             colnames,
@@ -823,6 +845,7 @@ class SQLAdapter(BaseAdapter):
                 limitby,
                 distinct,
                 for_update and self.can_select_for_update,
+                with_cte
             ),
         )
 
@@ -966,8 +989,8 @@ class SQLAdapter(BaseAdapter):
             raise RuntimeError(err % (index_name, str(e), sql))
         return True
 
-    def drop_index(self, table, index_name):
-        sql = self.dialect.drop_index(index_name, table)
+    def drop_index(self, table, index_name, if_exists = False):
+        sql = self.dialect.drop_index(index_name, table, if_exists)
         try:
             self.execute(sql)
             self.commit()
diff --git a/pydal/base.py b/pydal/base.py
index a927321e..597f7fbe 100644
--- a/pydal/base.py
+++ b/pydal/base.py
@@ -379,7 +379,7 @@ class DAL(with_metaclass(MetaDAL, Serializable, BasicStorage)):
     def distributed_transaction_begin(*instances):
         if not instances:
             return
-        thread_key = "%s.%s" % (socket.gethostname(), threading.currentThread())
+        thread_key = "%s.%s" % (socket.gethostname(), threading.current_thread())
         instances = enumerate(instances)
         keys = ["%s.%i" % (thread_key, i) for (i, db) in instances]
         for (i, db) in instances:
@@ -395,7 +395,7 @@ class DAL(with_metaclass(MetaDAL, Serializable, BasicStorage)):
         if not instances:
             return
         instances = enumerate(instances)
-        thread_key = "%s.%s" % (socket.gethostname(), threading.currentThread())
+        thread_key = "%s.%s" % (socket.gethostname(), threading.current_thread())
         keys = ["%s.%i" % (thread_key, i) for (i, db) in instances]
         for (i, db) in instances:
             if not db._adapter.support_distributed_transaction():
diff --git a/pydal/dialects/base.py b/pydal/dialects/base.py
index db176855..e2a03e2c 100644
--- a/pydal/dialects/base.py
+++ b/pydal/dialects/base.py
@@ -166,6 +166,25 @@ class SQLDialect(CommonDialect):
             whr = " %s" % self.where(where)
         return "DELETE FROM %s%s;" % (tablename, whr)
 
+
+    def cte(self, tname, fields, sql, recursive = None):
+        '''
+        recursive:list = [union_type, recursive_sql]
+        '''
+        if recursive:
+            r_sql_parts = ['%s %s'%(union, sql) for union, sql in recursive]
+            recursive = ' '.join(r_sql_parts)
+            cte_select = '{select} {recursive}'
+        else:
+            cte_select = '{select}'
+
+        return ('{tname}({fields}) AS (%s)' % cte_select).format(
+            tname = tname,
+            fields = fields,
+            select = sql,
+            recursive = recursive
+        )
+
     def select(
         self,
         fields,
@@ -177,6 +196,7 @@ class SQLDialect(CommonDialect):
         limitby=None,
         distinct=False,
         for_update=False,
+        with_cte=None  # ['recursive' | '', sql]
     ):
         dst, whr, grp, order, limit, offset, upd = "", "", "", "", "", "", ""
         if distinct is True:
@@ -197,7 +217,14 @@ class SQLDialect(CommonDialect):
             offset = " OFFSET %i" % lmin
         if for_update:
             upd = " FOR UPDATE"
-        return "SELECT%s %s FROM %s%s%s%s%s%s%s;" % (
+        if with_cte:
+            recursive, cte = with_cte
+            recursive = ' RECURSIVE' if recursive else ''
+            with_cte = "WITH%s %s " % (recursive, cte)
+        else:
+            with_cte = ""
+        return "%sSELECT%s %s FROM %s%s%s%s%s%s%s;" % (
+            with_cte,
             dst,
             fields,
             tables,
@@ -606,8 +633,9 @@ class SQLDialect(CommonDialect):
             )
         return rv
 
-    def drop_index(self, name, table):
-        return "DROP INDEX %s;" % self.quote(name)
+    def drop_index(self, name, table, if_exists = False):
+        if_exists = "IF EXISTS " if if_exists else ""
+        return "DROP INDEX %s%s;" % (if_exists, self.quote(name))
 
     def constraint_name(self, table, fieldname):
         return "%s_%s__constraint" % (table, fieldname)
diff --git a/pydal/dialects/db2.py b/pydal/dialects/db2.py
index 0de9add2..fd66af6f 100644
--- a/pydal/dialects/db2.py
+++ b/pydal/dialects/db2.py
@@ -81,6 +81,7 @@ class DB2Dialect(SQLDialect):
         limitby=None,
         distinct=False,
         for_update=False,
+        with_cte=None,
     ):
         dst, whr, grp, order, limit, offset, upd = "", "", "", "", "", "", ""
         if distinct is True:
@@ -100,7 +101,16 @@ class DB2Dialect(SQLDialect):
             limit = " FETCH FIRST %i ROWS ONLY" % lmax
         if for_update:
             upd = " FOR UPDATE"
-        return "SELECT%s %s FROM %s%s%s%s%s%s%s;" % (
+
+        if with_cte:
+            recursive, cte = with_cte
+            recursive = ' RECURSIVE' if recursive else ''
+            with_cte = "WITH%s %s " % (recursive, cte)
+        else:
+            with_cte = ""
+
+        return "%sSELECT%s %s FROM %s%s%s%s%s%s%s;" % (
+            with_cte,
             dst,
             fields,
             tables,
diff --git a/pydal/dialects/firebird.py b/pydal/dialects/firebird.py
index 078bb160..a49bcfc3 100644
--- a/pydal/dialects/firebird.py
+++ b/pydal/dialects/firebird.py
@@ -96,6 +96,7 @@ class FireBirdDialect(SQLDialect):
         limitby=None,
         distinct=False,
         for_update=False,
+        with_cte=None,
     ):
         dst, whr, grp, order, limit, offset, upd = "", "", "", "", "", "", ""
         if distinct is True:
@@ -116,7 +117,16 @@ class FireBirdDialect(SQLDialect):
             offset = " SKIP %i" % lmin
         if for_update:
             upd = " FOR UPDATE"
-        return "SELECT%s%s%s %s FROM %s%s%s%s%s;" % (
+
+        if with_cte:
+            recursive, cte = with_cte
+            recursive = ' RECURSIVE' if recursive else ''
+            with_cte = "WITH%s %s " % (recursive, cte)
+        else:
+            with_cte = ""
+
+        return "%sSELECT%s%s%s %s FROM %s%s%s%s%s;" % (
+            with_cte,
             dst,
             limit,
             offset,
diff --git a/pydal/dialects/mssql.py b/pydal/dialects/mssql.py
index bc5b259a..9d9d5476 100644
--- a/pydal/dialects/mssql.py
+++ b/pydal/dialects/mssql.py
@@ -136,6 +136,7 @@ class MSSQLDialect(SQLDialect):
         limitby=None,
         distinct=False,
         for_update=False,
+        with_cte=None  # ['recursive' | '', sql]
     ):
         dst, whr, grp, order, limit, upd = "", "", "", "", "", ""
         if distinct is True:
@@ -155,7 +156,16 @@ class MSSQLDialect(SQLDialect):
             limit = " TOP %i" % lmax
         if for_update:
             upd = " FOR UPDATE"
-        return "SELECT%s%s %s FROM %s%s%s%s%s;" % (
+
+        if with_cte:
+            recursive, cte = with_cte
+            recursive = ' RECURSIVE' if recursive else ''
+            with_cte = "WITH%s %s " % (recursive, cte)
+        else:
+            with_cte = ""
+
+        return "%sSELECT%s%s %s FROM %s%s%s%s%s;" % (
+            with_cte,
             dst,
             limit,
             fields,
@@ -237,7 +247,7 @@ class MSSQLDialect(SQLDialect):
     def concat_add(self, tablename):
         return "; ALTER TABLE %s ADD " % tablename
 
-    def drop_index(self, name, table):
+    def drop_index(self, name, table, if_exists = False):
         return "DROP INDEX %s ON %s;" % (self.quote(name), table._rname)
 
     def st_astext(self, first, query_env={}):
@@ -359,6 +369,7 @@ class MSSQL3Dialect(MSSQLDialect):
         limitby=None,
         distinct=False,
         for_update=False,
+        with_cte=None  # ['recursive' | '', sql]
     ):
         dst, whr, grp, order, limit, offset, upd = "", "", "", "", "", "", ""
         if distinct is True:
@@ -383,7 +394,16 @@ class MSSQL3Dialect(MSSQLDialect):
                 )
         if for_update:
             upd = " FOR UPDATE"
-        return "SELECT%s %s FROM %s%s%s%s%s%s%s;" % (
+
+        if with_cte:
+            recursive, cte = with_cte
+            recursive = ' RECURSIVE' if recursive else ''
+            with_cte = "WITH%s %s " % (recursive, cte)
+        else:
+            with_cte = ""
+
+        return "%sSELECT%s %s FROM %s%s%s%s%s%s%s;" % (
+            with_cte,
             dst,
             fields,
             tables,
@@ -409,6 +429,7 @@ class MSSQL4Dialect(MSSQL3Dialect):
         limitby=None,
         distinct=False,
         for_update=False,
+        with_cte=None  # ['recursive' | '', sql]
     ):
         dst, whr, grp, order, limit, offset, upd = "", "", "", "", "", "", ""
         if distinct is True:
@@ -436,7 +457,16 @@ class MSSQL4Dialect(MSSQL3Dialect):
                 )
         if for_update:
             upd = " FOR UPDATE"
-        return "SELECT%s %s FROM %s%s%s%s%s%s%s;" % (
+
+        if with_cte:
+            recursive, cte = with_cte
+            recursive = ' RECURSIVE' if recursive else ''
+            with_cte = "WITH%s %s " % (recursive, cte)
+        else:
+            with_cte = ""
+
+        return "%sSELECT%s %s FROM %s%s%s%s%s%s%s;" % (
+            with_cte,
             dst,
             fields,
             tables,
diff --git a/pydal/dialects/mysql.py b/pydal/dialects/mysql.py
index 951f649b..a3861002 100644
--- a/pydal/dialects/mysql.py
+++ b/pydal/dialects/mysql.py
@@ -107,5 +107,5 @@ class MySQLDialect(SQLDialect):
             "SET FOREIGN_KEY_CHECKS=1;",
         ]
 
-    def drop_index(self, name, table):
+    def drop_index(self, name, table, if_exists = False):
         return "DROP INDEX %s ON %s;" % (self.quote(name), table._rname)
diff --git a/pydal/dialects/oracle.py b/pydal/dialects/oracle.py
index 703777c6..3da0b20e 100644
--- a/pydal/dialects/oracle.py
+++ b/pydal/dialects/oracle.py
@@ -195,6 +195,7 @@ class OracleDialect(SQLDialect):
         limitby=None,
         distinct=False,
         for_update=False,
+        with_cte=None
     ):
         dst, whr, grp, order, limit, offset, upd = "", "", "", "", "", "", ""
         if distinct is True:
@@ -209,6 +210,14 @@ class OracleDialect(SQLDialect):
                 grp += " HAVING %s" % having
         if orderby:
             order = " ORDER BY %s" % orderby
+
+        if with_cte:
+            recursive, cte = with_cte
+            recursive = ' RECURSIVE' if recursive else ''
+            with_cte = "WITH%s %s " % (recursive, cte)
+        else:
+            with_cte = ""
+
         if limitby:
             (lmin, lmax) = limitby
             if whr:
@@ -216,12 +225,13 @@ class OracleDialect(SQLDialect):
             else:
                 whr2 = self.where("w_row > %i" % lmin)
             return """
-                SELECT%s * FROM (
+                %sSELECT%s * FROM (
                     SELECT w_tmp.*, ROWNUM w_row FROM (
                         SELECT %s FROM %s%s%s%s
-                    ) w_tmp 
+                    ) w_tmp
                 ) WHERE w_row<=%i and w_row>%i
             """ % (
+                with_cte,
                 dst,
                 fields,
                 tables,
@@ -233,7 +243,8 @@ class OracleDialect(SQLDialect):
             )
         if for_update:
             upd = " FOR UPDATE"
-        return "SELECT%s %s FROM %s%s%s%s%s%s%s;" % (
+        return "%sSELECT%s %s FROM %s%s%s%s%s%s%s;" % (
+            with_cte,
             dst,
             fields,
             tables,
diff --git a/pydal/dialects/sqlite.py b/pydal/dialects/sqlite.py
index 02702c63..b28d4d84 100644
--- a/pydal/dialects/sqlite.py
+++ b/pydal/dialects/sqlite.py
@@ -44,6 +44,7 @@ class SQLiteDialect(SQLDialect):
         limitby=None,
         distinct=False,
         for_update=False,
+        with_cte= ""
     ):
         if distinct and distinct is not True:
             raise SyntaxError("DISTINCT ON is not supported by SQLite")
@@ -57,6 +58,7 @@ class SQLiteDialect(SQLDialect):
             limitby,
             distinct,
             for_update,
+            with_cte
         )
 
     def truncate(self, table, mode=""):
diff --git a/pydal/dialects/teradata.py b/pydal/dialects/teradata.py
index df5a7def..6204d6d7 100644
--- a/pydal/dialects/teradata.py
+++ b/pydal/dialects/teradata.py
@@ -86,6 +86,7 @@ class TeradataDialect(SQLDialect):
         limitby=None,
         distinct=False,
         for_update=False,
+        with_cte=None,
     ):
         dst, whr, grp, order, limit, offset, upd = "", "", "", "", "", "", ""
         if distinct is True:
@@ -105,7 +106,16 @@ class TeradataDialect(SQLDialect):
             limit = " TOP %i" % lmax
         if for_update:
             upd = " FOR UPDATE"
-        return "SELECT%s%s %s FROM %s%s%s%s%s%s;" % (
+
+        if with_cte:
+            recursive, cte = with_cte
+            recursive = ' RECURSIVE' if recursive else ''
+            with_cte = "WITH%s %s " % (recursive, cte)
+        else:
+            with_cte = ""
+
+        return "%sSELECT%s%s %s FROM %s%s%s%s%s%s;" % (
+            with_cte,
             dst,
             limit,
             fields,
diff --git a/pydal/objects.py b/pydal/objects.py
index 16ed6715..ad9d03c0 100644
--- a/pydal/objects.py
+++ b/pydal/objects.py
@@ -1221,8 +1221,8 @@ class Table(Serializable, BasicStorage):
     def create_index(self, name, *fields, **kwargs):
         return self._db._adapter.create_index(self, name, *fields, **kwargs)
 
-    def drop_index(self, name):
-        return self._db._adapter.drop_index(self, name)
+    def drop_index(self, name, if_exists = False):
+        return self._db._adapter.drop_index(self, name, if_exists)
 
 
 class Select(BasicStorage):
@@ -1242,6 +1242,7 @@ class Select(BasicStorage):
         self.virtualfields = []
         self._sql_cache = None
         self._colnames_cache = None
+        self._cte_recursive = None
         fieldcheck = set()
 
         for item in fields:
@@ -1321,13 +1322,15 @@ class Select(BasicStorage):
             raise SyntaxError("Subselect must be aliased for use in a JOIN")
         return Expression(self._db, self._db._adapter.dialect.on, self, query)
 
-    def _compile(self, outer_scoped=[], with_alias=False):
+    def _compile(self, outer_scoped=[], with_alias=False, cte_collector = None):
         if not self._correlated:
             outer_scoped = []
         if outer_scoped or not self._sql_cache:
             adapter = self._db._adapter
             attributes = self._attributes.copy()
             attributes["outer_scoped"] = outer_scoped
+            attributes["cte_collector"] = cte_collector
+            attributes.pop('cte', None)
             colnames, sql = adapter._select_wcols(
                 self._query, self._qfields, **attributes
             )
@@ -1341,9 +1344,55 @@ class Select(BasicStorage):
             sql = self._db._adapter.dialect.alias(sql, self._tablename)
         return colnames, sql
 
+    def union(self, recursive, union_type = 'UNION'):
+        if callable(recursive):
+            recursive = recursive(self)
+        if not isinstance(recursive, (list, tuple)):
+            recursive = [recursive]
+        if not self._cte_recursive:
+            self._cte_recursive = []
+        self._cte_recursive.extend([[union_type, r] for r in recursive])
+        return self
+
+    def union_all(self, recursive):
+        return self.union(recursive, 'UNION ALL')
+
+    def cte(self, cte_collector):
+        if not self._tablename:
+            raise SyntaxError("cte must be aliased for use in a query/select")
+        if cte_collector:
+            if self._tablename in cte_collector['seen']:
+                return
+            else:
+                # must be here to prevent infinite recursion
+                cte_collector['seen'].add(self._tablename)
+        colnames, sql = self._compile(cte_collector = cte_collector)
+        sql = sql[:-1]
+        #sql_fields = ", ".join(adapter._geoexpand(x, {}) for x in self._qfields)
+
+        sql_fields = ", ".join([
+            getattr(f, 'alias', None) or getattr(f, 'name', None) or str(f)
+            for f in self._qfields
+        ])
+        recursive = self._cte_recursive
+        if recursive:
+            for r in recursive:
+                if isinstance(r[1], self.__class__):
+                    _, r_sql = r[1]._compile(cte_collector = cte_collector)
+                    r[1] = r_sql[:-1]
+        sql = self._db._adapter.dialect.cte(self._tablename, sql_fields, sql, recursive)
+        if cte_collector:
+            cte_collector['stack'].append(sql)
+            # cte_collector['seen'].add(self._tablename) - see above
+            if not cte_collector['is_recursive']:
+                cte_collector['is_recursive'] = recursive and True or False
+        return sql
+
     def query_name(self, outer_scoped=[]):
         if self._tablename is None:
-            raise SyntaxError("Subselect must be aliased for use in a JOIN")
+            raise SyntaxError("Subselect/cte must be aliased for use in a JOIN")
+        if self._attributes.get('cte'):
+            return (self.sql_shortref,)
         colnames, sql = self._compile(outer_scoped, True)
         # This method should also return list of placeholder values
         # in the future
@@ -1352,7 +1401,7 @@ class Select(BasicStorage):
     @property
     def sql_shortref(self):
         if self._tablename is None:
-            raise SyntaxError("Subselect must be aliased for use in a JOIN")
+            raise SyntaxError("Subselect/cte must be aliased for use in a JOIN")
         return self._db._adapter.dialect.quote(self._tablename)
 
     def _filter_fields(self, record, id=False):
@@ -1364,6 +1413,10 @@ class Select(BasicStorage):
             ]
         )
 
+    @property
+    def is_cte(self):
+        return self._attributes.get('cte')
+
 
 def _expression_wrap(wrapper):
     def wrap(self, *args, **kwargs):
@@ -1502,7 +1555,7 @@ class Expression(object):
 
     def __add__(self, other):
         return Expression(self.db, self._dialect.add, self, other, self.type)
-    
+
     def __radd__(self, other):
         if not hasattr(other, "type"):
             if isinstance(other, str):
@@ -2686,6 +2739,21 @@ class Set(Serializable):
         fields = adapter.expand_all(fields, tablenames)
         return adapter.nested_select(self.query, fields, attributes)
 
+    def cte(self, name, *fields, **attributes):
+        adapter = self.db._adapter
+        tablenames = adapter.tables(
+            self.query,
+            attributes.get("join", None),
+            attributes.get("left", None),
+            attributes.get("orderby", None),
+            attributes.get("groupby", None),
+        )
+        attributes['cte'] = True
+        fields = adapter.expand_all(fields, tablenames)
+        return adapter.nested_select(self.query, fields, attributes).with_alias(name)
+
+
+
     def delete(self):
         db = self.db
         table = db._adapter.get_table(self.query)


Test patch:
diff --git a/tests/sql.py b/tests/sql.py
index 782ab1c9..c614a5ad 100644
--- a/tests/sql.py
+++ b/tests/sql.py
@@ -778,6 +778,50 @@ class TestSubselect(DALtest):
         self.assertEqual(sub.sql_shortref, db._adapter.dialect.quote("foo"))
         self.assertIsInstance(sub.on(sub.aa != None), Expression)
 
+    def testCTE(self):
+        db = self.connect()
+        db.define_table('org', Field('name'), Field('boss', 'reference org'))
+        org = db.org
+        def insert_workers(boss, *names):
+            return [org.insert(name = name, boss = boss) for name in names]
+        alice = org.insert(name = 'Alice')
+        jim, tim = insert_workers(alice, 'Jim', 'Tim')
+        jessy, jenny =  insert_workers(jim, 'Jessy', 'Jenny')
+        insert_workers(tim, 'Tom')
+        insert_workers(jessy, 'John', 'Jacob')
+
+        works_for = db(org.name == 'Alice').cte(
+            'works_for',
+            org.id,
+            org.name.with_alias('top_boss'),  # i.e. Alice is top_boss
+            org.name,
+            org.boss,
+            Expression(db, '0', type = 'integer').with_alias('xdepth'),
+            Expression(db, '" "', type = 'string').with_alias('boss_chain')
+        ).union( lambda works_for: \
+            db((org.boss == works_for.id) & (org.id != org.boss)).nested_select(
+                org.id,
+                works_for.top_boss,
+                org.name,
+                org.boss,
+                (works_for.xdepth + 1).with_alias('xdepth'),
+                (' ' + works_for.name + works_for.boss_chain).with_alias('boss_chain')
+            )
+        )
+        rows = db().select(works_for.ALL).as_dict()
+        #  reconstruct boss_chain/depth and test them against query result
+        for row in rows.values():
+            r = row
+            boss_chain = []
+            while True:
+                r = rows.get(r['boss'])
+                if not r:
+                    break
+                boss_chain.append(r['name'])
+            depth = len(boss_chain)
+            self.assertEqual(depth, row['xdepth'])
+            self.assertEqual(' '.join(boss_chain), row['boss_chain'].strip())
+
     def testSelectArguments(self):
         db = self.connect()
         db.define_table("tt", Field("aa", "integer"), Field("bb"))