operator precedence: table names vs. left join

[alfonsodg]

From r.brian....@gmail.com on March 31, 2010 18:03:32

STEPS:

1. create a schema with two tables:

   db.define_table('user', Field('login', 'string'), Field('password', 'password')) db.define_table('avatar', Field('name', 'string'), Field('user_id', db.user)) db.define_table('account', Field('given_name', 'string'), Field('surname', 'string'), Field('user_id', db.user))

2. do a LEFT OUTER JOIN:

   db((db.user.id == db.avatar.user_id) & (db.avatar.name.like('%e%'))).select(db.account.ALL, db.user.ALL, left=db.user.on(db.account.user_id == db.user.id))

RESULTS:

Sqlite3 works.

MySQL, throws exception:

OperationalError: (1054, "Unknown column 'account.user_id' in 'on clause'")

Replacing select with _select results in the query which the mysql rejects:

SELECT account.id, account.given_name, account.surname, account.user_id, user.id, user.login, 

user.password FROM account, avatar LEFT JOIN user ON account.user_id=user.id WHERE (user.id=avatar.user_id AND avatar.name LIKE '%e%');

Tweaking by adding parens around the FROM tables results in the query which mysql accepts:

SELECT account.id, account.given_name, account.surname, account.user_id, user.id, user.login, 

user.password FROM (account, avatar) LEFT JOIN user ON account.user_id=user.id WHERE (user.id=avatar.user_id AND avatar.name LIKE '%e%');

ADDITIONAL INFO:

1.76.5 on MacOSX 10.6.2 using built-in python2.6 and built-in sqlite3. 1.76.5 on CentOS 5.3 using python2.6 and mysql-5.1.31-enterprise-gpl-pro. http://dev.mysql.com/doc/refman/5.1/en/join.html :

Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)).

This bug is apparently fixed by adding:

        sql_t = '(%s)' % sql_t

in gluon/sql.py:2973. Will need the same idiom other places to be a complete patch.

Original issue: http://code.google.com/p/web2py/issues/detail?id=75

[alfonsodg]

From massimod...@gmail.com on April 02, 2010 21:52:38

Thank you. I have included your patch with minor changes. I cannot find anyway in the docs an example of using (...) in FROM (...) JOIN. I hope this does not break other BDS, it needs to be tested before we can close the ticket. Can you double check it now works for you? Can youplease check both sqlite and mysql?

Status: Accepted

[alfonsodg]

From r.brian....@gmail.com on April 02, 2010 22:22:32

Very recent copy from mercurial fails elsewhere:

db((db.user.id == db.avatar.user_id) & (db.avatar.name.like('%e%'))).select(db.account.ALL, db.user.ALL, left=db.user.on(db.account.user_id == db.user.id)) Traceback (most recent call last): File "", line 1, in File ".../web2py/gluon/sql.py", line 3057, in select rows = response(query) File ".../web2py/gluon/sql.py", line 3052, in response db._execute(query) File ".../web2py/gluon/sql.py", line 895, in self._execute = lambda _a, _b: self._cursor.execute(_a, _b) OperationalError: no such column: user.id

I verified that the exact expressionas seen in comment #0 of this bug report still works in the 1.76.5 release.

[alfonsodg]

From r.brian....@gmail.com on April 02, 2010 22:26:51

My apologies for Google's URL parser bug. The URL is http://dev.mysql.com/doc/refman/5.1/en/join.html (sans colon).

[alfonsodg]

From r.brian....@gmail.com on April 02, 2010 23:08:06

To be clear on two points:

1) The "apparently fixed by" patch was tested prior to submitting the bug report on my branch of web2py with both Sqlite3 and MySQL 5.1. The parenthesization of the FROM tables had no adverse effect in either of my runtime environments for any queries, either left-outer-joining ones or those that do not. I also tested the hand-editted _statement() output in both environments prior to bug reporting.

2) I suspect there are other occurrences in web2py that will require wrapping tables names (i.e. sql_t) in parenthesis in order to use the "FROM table1, table2 JOIN" idiom. More specifically, I would also be concered about:

gluon/dal.py:459: sql_t = ', '.join(excluded)

[alfonsodg]

From massimod...@gmail.com on April 05, 2010 10:59:02

I reverted to this.

        sql_t = ', '.join(excluded)
        if joint:
            sql_t += ' %s %s' % (command, ', '.join(joint))
        for t in joinon:
            sql_t += ' %s %s' % (command, str(t))

Isn't your patch equivalent to:

        sql_t = '(%s)' % ', '.join(excluded)
        if joint:
            sql_t += ' %s %s' % (command, ', '.join(joint))
        for t in joinon:
            sql_t += ' %s %s' % (command, str(t))

perhaps I misunderstood where to put sql_t = '(%s)' % sql_t

This seems to break Jose's code: http://groups.google.com/group/web2py/msg/f8cab75ef8bf709a

[alfonsodg]

From r.brian....@gmail.com on April 05, 2010 11:13:43

No, your patch placement on Friday was not misunderstood; it was correctly placed per visual inspection. My apologies for not giving proper context.

I'm attempting to get SQL dialect details from Jose so we can find a universal solution.

[alfonsodg]

From r.brian....@gmail.com on April 05, 2010 11:19:52

A coworker suggested the alternative statement:

sql_t = ' JOIN '.join(excluded)

which may produce a statement like:

SELECT account.id, account.given_name, account.surname, account.user_id, user.id, user.login, 

user.password FROM account JOIN avatar LEFT JOIN user ON account.user_id=user.id WHERE (user.id=avatar.user_id AND avatar.name LIKE '%e%');

Use at your own risk.

[alfonsodg]

From massimod...@gmail.com on September 12, 2010 20:19:23

Brian. If I remember this was fixed in April even if I did not bark it as "fixed". can you confirm?

[alfonsodg]

From massimo....@gmail.com on January 10, 2011 18:18:04

Status: Fixed