Issue: Select column alias fails when alias name == column name
Author: elgow
State: closed
Created: 2018-06-07 01:49:39

Aliasing of columns in a select fails when the alias == the actual DB column name. 

Results from the test case below show the problem. Look at the dict key for the 1st item in each result set. When the alias != the column name it's correct, but when the alias == the column name it's wrong. Instead of the proper alias the field name from the model is used. 

```
Correct alias "AAA":  [{'AAA': 0, 'A_b_alias': 0, 'c': 0}, {'AAA': 1, 'A_b_alias': 1, 'c': 1}, {'AAA': 2, 'A_b_alias': 2, 'c': 2}]
Incorrect alias, should be "a" [{'aa': 0, 'A_b_alias': 0, 'c': 0}, {'aa': 1, 'A_b_alias': 1, 'c': 1}, {'aa': 2, 'A_b_alias': 2, 'c': 2}]
Correct alias "AAA":  [{'AAA': 0, 'A_b_alias': 0, 'c': 0, 'B_d_alias': 0, 'e': 0}, {'AAA': 1, 'A_b_alias': 1, 'c': 1, 'B_d_alias': 1, 'e': 1}, {'AAA': 2, 'A_b_alias': 2, 'c': 2, 'B_d_alias': 2, 'e': 2}]
Incorrect alias, should be "a" [{'aa': 0, 'A_b_alias': 0, 'c': 0, 'B_d_alias': 0, 'e': 0}, {'aa': 1, 'A_b_alias': 1, 'c': 1, 'B_d_alias': 1, 'e': 1}, {'aa': 2, 'A_b_alias': 2, 'c': 2, 'B_d_alias': 2, 'e': 2}]
```


```
from pathlib import Path

from peewee import *
from playhouse.sqlite_ext import *

ADMIN_PASSWORD = 'secret'
APP_DIR = Path(__file__).resolve().parent
DATABASE = APP_DIR/'alias_test.db'

db = SqliteDatabase(str(DATABASE))
db.connect()


class A(Model):
    aa = IntegerField(column_name='a')
    b = IntegerField()
    c = IntegerField()
    class Meta:
        database = db



class B(Model):
    a = IntegerField()
    d = IntegerField()
    e = IntegerField()
    class Meta:
        database = db


db.create_tables([A, B])

data = [(i, i, i) for i in range(3)]

A.insert_many(data, fields=[A.aa, A.b, A.c]).execute()
B.insert_many(data, fields=[B.a, B.d, B.e]).execute()

print('Correct alias "AAA": ', list(A.select(A.aa.alias('AAA'), A.b.alias('A_b_alias'), A.c).dicts().execute()))
print('Incorrect alias, should be "a"', list(A.select(A.aa.alias('a'), A.b.alias('A_b_alias'), A.c).dicts().execute()))

res = A.select(A.aa.alias('AAA'), A.b.alias('A_b_alias'), A.c,
                    B.d.alias('B_d_alias'), B.e) \
           .join(B, on=(A.aa == B.a)).dicts().execute()
print('Correct alias "AAA": ', list(res.iterator()))

res = A.select(A.aa.alias('a'), A.b.alias('A_b_alias'), A.c,
                    B.d.alias('B_d_alias'), B.e) \
           .join(B, on=(A.aa == B.a)).dicts().execute()
print('Incorrect alias, should be "a"', list(res.iterator()))
```

I should mention that this behavior was not present in Peewee 2.x.  I hit it in upgrading to 3.5.0. 

Can you create a more concise example? I'm having a very hard time understanding because there's so much noise and nonsense.

OK, nevermind, after reading more closely I think I see what you're talking about. So, in the example that has the problem, you're expecting "A.aa" to be aliased to "a", but Peewee is turning it into "aa"?

Hi Charles,


I wrote the example as a join to mimic the situation in my production code that exposed the problem. My initial assumption was that the alias failure was related to the join key.  I learned from the test code that the same thing happens on a single table and it stems from the alias being the same as the table name. I could have stripped out the 2nd table and the join queries, but I didn't think they added so much confusion.


The basic point, as you say in your last email, is that the alias of "a" in the 2nd of each of the two pairs of queries (single table and join) is ignored and the name of the model field, "aa" is used instead. The 1st of each pair of queries demonstrates that alias() works if the alias name is not the same as the table name.


  Ed


________________________________
From: Charles Leifer <notifications@github.com>
Sent: Thursday, June 7, 2018 5:57:13 AM
To: coleifer/peewee
Cc: Edward L. Gow; Author
Subject: Re: [coleifer/peewee] Select column alias fails when alias name == column name (#1625)


OK, nevermind, after reading more closely I think I see what you're talking about. So, in the example that has the problem, you're expecting "A.aa" to be aliased to "a", but Peewee is turning it into "aa"?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub<https://github.com/coleifer/peewee/issues/1625#issuecomment-395410476>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AK_suazFw47fQZZ5s0mmWbdG4P0x4T1jks5t6SMpgaJpZM4UdnRB>.


For future unit test purposes here's a minimal test case, plus one extra print statement to document the issue. 

```
from peewee import *

COLUMN_NAME = 'column_name'

db = SqliteDatabase(':memory:')
db.connect()

class A(Model):
    a = IntegerField(column_name=COLUMN_NAME)
    class Meta:
        database = db

db.create_tables([A])
A.insert(dict(a=1)).execute()

res = next(A.select( A.a.alias(COLUMN_NAME), A.a.alias('aaa'), A.a.alias('bbb'),).dicts().iterator())

print('Alias(COLUMN_NAME) does not work; gets replaced by field name. Others work.: ', res)
assert res.keys() == {COLUMN_NAME, 'aaa', 'bbb'}
```


This is partially related to a fix I made for #1473.

Peewee uses the following heuristic to map selected values to fields/model-attributes: if the unwrapped node that was selected is a field (in your case this is true, because you have a field "wrapped" in an alias), and the selected value is named either the field name or column name, then peewee will assign it to the given field using that field's name.

This allows things to work in other edge-cases, like primary-keys that are also foreign-keys.

I'm not sure about an easy fix here but will think about it. This seems like a bit of an odd usage of Peewee, however? Confusing, at the very least?

Simplified test-case:

```diff
diff --git a/tests/models.py b/tests/models.py
index 3f364bd..44c00c4 100644
--- a/tests/models.py
+++ b/tests/models.py
@@ -3431,3 +3431,22 @@ class TestModelFieldReprs(BaseTestCase):
 
         f = Foo(id=1337)
         self.assertEqual(repr(f), 'TWEET: 1337')
+
+
+class ColAlias(TestModel):
+    name = TextField(column_name='pname')
+
+
+class TestQueryAliasToColumnName(ModelTestCase):
+    requires = [ColAlias]
+
+    def setUp(self):
+        super(TestQueryAliasToColumnName, self).setUp()
+        for name in ('huey', 'mickey'):
+            ColAlias.create(name=name)
+
+    def test_alias_to_column_name(self):
+        query = (ColAlias
+                 .select(ColAlias.name.alias('pname'))
+                 .order_by(ColAlias.name))
+        self.assertEqual([row.pname for row in query], ['huey', 'mickey'])
```

Hi, @coleifer , is it possible to allow us to custom cursor wrapper? By this way, we can use our own `ModelDictCursorWrapper` which checkout attribute list directly from `self.cursor.description` rather than processed `self.columns`. Most of the time, I only want to use data dicts and alias the foreign key field to its column name (with `_id` suffix), however, since the `_get_cursor_wrapper` is inherit from `_ModelQueryHelper`, I cannot find a easy way to add a customized cursor wrapper.

Hi, @coleifer,  I agree that it's a strange edge case issue. The reason that I hit it was that I migrated code (which I inherited) from having SQL inline to using Peewee so that I could conventiently move the code off of sqlite onto Postgresql. The SQL based code caused the names returned by queries to be the same as column names, and those names were used in code that accessed the result sets. I renamed some fields in the Peewee model to be "nicer" than the column names, but instead of purging the code of the old column name references I relied on alias() in specific queries.  

When you consider my path to the problem, I'd suggest that it's pretty likely that others will tread that same path. For that reason I think that this particular edge case is probably worth worrying about. 

> I'd suggest that it's pretty likely that others will tread that same path. For that reason I think that this particular edge case is probably worth worrying about.

I'm not sure I agree. It seems strange to me to rename a database column explicitly to have a "nicer" name, but then in your code to refer to it using it's original database column name. More-or-less the problem looks like this:

```python
class User(Model):
     # Give column "the_user_name" a nicer, normalized attribute name:
     username = TextField(column_name='the_user_name')

# But wait...actually, we want to use the original column name instead?
query = User.select(User.username.alias('the_user_name'))
```

If that's your workflow, why give it a different field name in the first place? e.g., why not:

```python
class User(Model):
    the_user_name = TextField()
```

I *do agree* that there may be a bug here, and I intend to try and fix it, but I'd suggest that you might be doing something weird :)

Regardless, I have fixed the issue. See 692db22 for details on the changes to the implementation.

My work-around to get past the problem was indeed to change the field name back from my "nicer" name to the DB column name. It makes the ORM class messier, (and having "nice" classes to represent data is one of the reasons I use an ORM in the first place),  but it solved the problem.  An alternative would have been to fix all of the references to the result set dicts, but that involved a lot of jinja2 templates, so it was a bit of a chore. Version 2.x of Peewee had no trouble with the alias name so I hadn't had to worry about it in the couple of years I've had the code running on Peewee and Postgresql.

If you had decided just to document the issue and advise people not to alias to the table name I would have considered that a reasonable response. The only real issue is the surprise factor of the alias being present in the `cursor.description` but not being reflected in the dict keys. I do think that others would have run into it and spent time trying to figure it out as I did , so thank you for fixing the issue. I often recommend Peewee as a simpler alternative to Alchemy, so having it just work helps avoid blowback from those who take that advice.

> The only real issue is the surprise factor of the alias being present in the cursor.description but not being reflected in the dict keys.

Peewee should respect the alias given to the field in the query, so this was definitely a bug, albeit one that I had not anticipated. I appreciate the report and your patience while I investigated it and made the fix.