Elixir fails during insert and update on MySQL 5.x when using the list extension.

[guest]

MySQL 5.x requires that sub-selects be given unique aliases to prevent confusion, whereas I assume older versions of MySQL did not enforce this. Attempting to use the elixir.ext.list acts_as_list functionality, I ran into the following Exception: (wrapped with data removed)

sqlalchemy.exceptions.OperationalError: (OperationalError) (1248,
    'Every derived table must have its own alias')
    u'INSERT INTO objects (commission_id, creator_id, location_id, uuid, title, description, filename, mime, created, updated, published, position)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, (
        SELECT max(value) AS max_1
            FROM (SELECT max(objects.position) + %s AS value
                FROM objects
                WHERE objects.location_id IS NULL UNION SELECT %s AS value)))'

Attached to this ticket is a patch which solves this problem using an alias of the sub-select.

[guest]

Simplest possible patch to solve problem.

[ged]

fixed in r309. Thanks.

[guest]

I've run into this problem again (OP here) with UPDATE statements related to use of the move_to_bottom method.

I get the following exception: (wrapped for sanity)

OperationalError: (OperationalError)
    (1093, "You can't specify target table 'objects' for update in FROM clause")
    u'''UPDATE objects SET position=(SELECT max(objects.position) + %s AS anon_1
        FROM objects
        WHERE objects.location_id = %s) WHERE objects.id = %s'''
    [1, 1157L, 17890L]

I will submit a patch shortly.

[guest]

Patch for "You can't specify target table 'objects' for update in FROM clause"

[ged]

I've finally decided to commit the patch anyway (r418).