Repository: web2py/pydal

Issue:
pydal-20241204.1 with_alias + as_list broke backward compatibilty with old code.
The latest versions of pydal introduced a breaking change in the behavior of the `with_alias` method used with `as_list`. In the previous version (20241111.2), when using `with_alias` with `as_list`, the aliased fields were returned as keys of the dict representing the record, but in the latest version, they are only available in the _extra dict.

It seems the different behavior is introduced here: 16c9395f4929ab1f87ea9cbb343585fbc508dbe4

This change breaks backward compatibility with existing code that relied on the old behavior.

```python
from pydal import DAL, Field
db = DAL('sqlite:memory')
db.define_table('test',
                Field('a'),
               )
for i in range(3):
    db.test.insert(a=str(i))
db.commit()  
print(db(db.test.id).select(db.test.a.with_alias('my_col_name')).as_list())
```

pydal-20241111.2

```python
[{'my_col_name': '0', '_extra': {'"test"."a" AS my_col_name': '0'}},
 {'my_col_name': '1', '_extra': {'"test"."a" AS my_col_name': '1'}},
 {'my_col_name': '2', '_extra': {'"test"."a" AS my_col_name': '2'}},
```

pydal-20241204.1
```
[{'_extra': {'my_col_name': '0'}},
 {'_extra': {'my_col_name': '1'}},
 {'_extra': {'my_col_name': '2'}},
```

Discussion:
@laundmo: I found out what changed:
https://github.com/web2py/pydal/blob/16c9395f4929ab1f87ea9cbb343585fbc508dbe4/pydal/adapters/base.py#L295-L296
This now changes colname from `"test"."a" as my_col_name` to `my_col_name`
That means, later, the following regex match evaluates to None:
https://github.com/web2py/pydal/blob/16c9395f4929ab1f87ea9cbb343585fbc508dbe4/pydal/adapters/base.py#L307-L310
And therefore the last line in that code block never runs.

I made a PR which improves the logic a lot, which also fixes this issue for single-table queries.

your query would be parsed as `{"test": {"my_col_name": '0'}}` and then flattened to `{"my_col_name": '0'}` since it now uses the original fields table as a "table name" for the alias.

this means a multi-table query with both column aliased would result in this, which is still a breaking change:
`{"test": {"my_col_name": '0'}, "test2": {"my_other_col_name": '1'}}` which won't be flattened as its 2 tables
@mbelletti: Maybe to don't' break old code could be possibile to add a flag in `.with_alias` method? 

`flatten=True` by default 

or something like that that add the same fake table name just to flat multi-table query?




@laundmo: The intended way to write queries like this would be to either write queries like this:
```py
db(db.table1).select(db.table1.id.with_alias("data.tbl1id"), db.table2.id.with_alias("data.tbl2id"), join=db.table2.on(db.table1.id == db.table2.ref_tbl1))
```
which selects both to a single result table called "data" which will be flattened normally (single table: flat, multi-table: nested, instead of the old "nested but aliases are flat")

or just to access the data in its nested form.

i'm not sure if @mdipierro has any idea how to resolve this without, essentially, reverting back to the old behaviour entirely@mbelletti: This monkey patch could be an easy workaround for existing code that doens't support dot notation:

```python
from pydal import Field

if not hasattr(Field, 'smart_alias'):
    Field.smart_alias = lambda self, fname=None, tname=None: self.with_alias(
        f"_.{fname if fname else self.name}" if not tname 
        else f"{tname}.{fname if fname else self.name}"
    )
```
using it:

```
q = (db.person.id == db.superhero.real_identity)

fields = [
    db.person.id.smart_alias(), 
    db.person.name.smart_alias(), 
    db.superhero.name.smart_alias('superhero'), 
]

db(q).select(*fields).as_list()
```

```
[{'id': 1, 'name': 'Clark Kent', 'superhero': 'Superman'},
 {'id': 2, 'name': 'Peter Park', 'superhero': 'Spiderman'},
 {'id': 3, 'name': 'Bruce Wayne', 'superhero': 'Batman'}]
```

Solution patch:
diff --git a/.gitignore b/.gitignore
index b84b4aece..d53e4aee0 100644
--- a/.gitignore
+++ b/.gitignore
@@ -22,4 +22,4 @@ build/*
 .settings
 .idea
 .vslick
-
+.venv
diff --git a/pydal/adapters/base.py b/pydal/adapters/base.py
index eabb8663e..3770df37d 100644
--- a/pydal/adapters/base.py
+++ b/pydal/adapters/base.py
@@ -157,6 +157,8 @@ def tables(self, *queries):
             elif isinstance(query, (Expression, Query)):
                 tmp = [x for x in (query.first, query.second) if x is not None]
                 tables = merge_tablemaps(tables, self.tables(*tmp))
+            elif isinstance(query, Select):
+                tables = merge_tablemaps(tables, self.tables(*query.fields))
         return tables
 
     def get_table(self, *queries):
@@ -285,7 +287,7 @@ def _parse(
                         self._add_reference_sets_to_parsed_row(
                             value, table, tablename, colset
                         )
-            #: otherwise we set the value in extras
+            #: otherwise, alias/expression logic
             else:
                 #: fields[j] may be None if only 'colnames' was specified in db.executesql()
                 field = fields[j]
@@ -293,13 +295,26 @@ def _parse(
                 value = self.parse_value(value, f_itype, ftype, blob_decode)
                 # for aliased fields use the aliased name
                 if isinstance(field, Expression) and field.op == self.dialect._as:
-                    colname = field.second
-                    # if the alias is a tablename.fieldname add the column to the table
-                    if field.tablename:
-                        if field.tablename not in new_row:
-                            new_row[field.tablename] = self.db.Row()
-                        new_row[field.tablename][colname] = value
+                    alias_colname = field.second
+
+                    # will be None if the alias doesn't contain a dot
+                    tablename = field.tablename
+
+                    # if alias doesn't contain a dot
+                    # and the epxression mentions a single table
+                    # use that.
+                    if not tablename:
+                        tables = self.tables(field)
+                        if len(tables) == 1:
+                            table = tables.popitem()[1]
+                            tablename = table._tablename
+
+                    # if the alias lets us determine a correct table name
+                    # add it to that table.
+                    if tablename:
+                        new_row[tablename][alias_colname] = value
                         continue
+
                 extras[colname] = value
                 if not fields[j]:
                     new_row[colname] = value


Test patch:
diff --git a/tests/sql.py b/tests/sql.py
index d75a3cd26..de52aa373 100644
--- a/tests/sql.py
+++ b/tests/sql.py
@@ -753,9 +753,10 @@ def testMethods(self):
         # Check result from executing the query
         result = sub()
         self.assertEqual(len(result), len(data))
-        for idx, row in enumerate(data):
+        # recreate expected query data
+        data_with_exp = [{**d, "exp": d["aa"] + 1} for d in data]
+        for idx, row in enumerate(data_with_exp):
             self.assertEqual(result[idx]["tt"].as_dict(), row)
-            self.assertEqual(result[idx]["exp"], row["aa"] + 1)
         result = db.executesql(str(sub))
         for idx, row in enumerate(data):
             tmp = [row["aa"], row["bb"], row["aa"] + 2, row["aa"] + 1]
@@ -773,9 +774,8 @@ def testMethods(self):
         # Alias checks
         sub = sub.with_alias("foo")
         result = sub()
-        for idx, row in enumerate(data):
+        for idx, row in enumerate(data_with_exp):
             self.assertEqual(result[idx]["tt"].as_dict(), row)
-            self.assertEqual(result[idx]["exp"], row["aa"] + 1)
         # Check query expansion methods again
         self.assertEqual(sub._rname, None)
         self.assertEqual(sub._raw_rname, None)
@@ -881,8 +881,8 @@ def testSelectArguments(self):
         self.assertEqual(len(result), len(expected))
         for idx, val in enumerate(expected):
             self.assertEqual(result[idx]["t1"]["aa"], val[0])
-            self.assertEqual(result[idx]["total"], val[1])
-            self.assertEqual(result[idx]["cnt"], val[2])
+            self.assertEqual(result[idx]["t2"]["total"], val[1])
+            self.assertEqual(result[idx]["t2"]["cnt"], val[2])
 
         # Check again when nested inside another query
         # Also check that the alias will not conflict with existing table
@@ -1495,6 +1495,96 @@ def testOps(self):
         op = sum / count
         # self.assertEqual(db(t0).select(op).first()[op], 2)
 
+    def testAlias(self):
+        db = self.connect()
+        db.define_table(
+            "t0",
+            Field("c0", type="integer"),
+        )
+        db.define_table(
+            "t1",
+            Field("c1", type="integer"),
+        )
+        amount = 3
+        for i in range(amount):
+            db.t0.insert(c0=i)
+            db.t1.insert(c1=i + 4)
+        db.commit()
+        self.assertEqual(
+            db(db.t0).select(db.t0.id, db.t0.c0).first().as_dict(),
+            {"id": 1, "c0": 0},
+        )
+        # alias one column of table
+        self.assertEqual(
+            db(db.t0).select(db.t0.id, db.t0.c0.with_alias("alias")).first().as_dict(),
+            {"id": 1, "alias": 0},
+        )
+        # rename table entirely
+        self.assertEqual(
+            db(db.t0)
+            .select(
+                db.t0.id.with_alias("data.id"),
+                db.t0.c0.with_alias("data.alias"),
+            )
+            .first()
+            .as_dict(),
+            {"id": 1, "alias": 0},
+        )
+        # mixed dotted and non-dotted alias
+        self.assertEqual(
+            db(db.t0)
+            .select(
+                db.t0.id.with_alias("t0.id"),
+                db.t0.c0.with_alias("alias"),
+            )
+            .first()
+            .as_dict(),
+            {"id": 1, "alias": 0},
+        )
+        # alias with different dotted names
+        self.assertEqual(
+            db(db.t0)
+            .select(
+                db.t0.id.with_alias("data.id"),
+                db.t0.c0.with_alias("otherdata.alias"),
+            )
+            .first()
+            .as_dict(),
+            {"data": {"id": 1}, "otherdata": {"alias": 0}},
+        )
+        # alias with different dotted name from nondotted detected
+        self.assertEqual(
+            db(db.t0)
+            .select(
+                db.t0.id.with_alias("data.id"),
+                db.t0.c0.with_alias("alias"),
+            )
+            .first()
+            .as_dict(),
+            {"data": {"id": 1}, "t0": {"alias": 0}},
+        )
+        # separate tables no alias
+        self.assertEqual(
+            db(db.t0).select(db.t0.id, db.t1.c1).first().as_dict(),
+            {"t0": {"id": 1}, "t1": {"c1": 4}},
+        )
+        # separate tables with one alias making them act as the same
+        self.assertEqual(
+            db(db.t0)
+            .select(db.t0.id, db.t1.c1.with_alias("t0.alias"))
+            .first()
+            .as_dict(),
+            {"id": 1, "alias": 4},
+        )
+        # infer table name through nested expression
+        self.assertEqual(
+            db(db.t0)
+            .select(db.t0.id, db.t0.c0.count().with_alias("alias"))
+            .first()
+            .as_dict(),
+            {"id": 1, "alias": amount},
+        )
+
 
 class TestTableAliasing(DALtest):
     def testRun(self):