join, sorting and limit/offset issue

There is a very strange behavior with table joins and limit/offset: example:

from sqlalchemy import *

db = create_engine("mysql://tezzt:tezzt@localhost/tezzt")
m = BoundMetaData(db)
db.engine.echo = True

class T1(object):pass
class T2(object):pass


t1 = Table("a", m, Column("id", Integer, primary_key=True), Column("b_id", Integer, ForeignKey("b.id")))
t2 = Table("b", m, Column("id", Integer, primary_key=True), Column("name", String(10)))

T1.mapper = mapper(T1, t1, properties={"b":relation(T2, lazy=False, order_by=None)})
T2.mapper = mapper(T2, t2)

m.drop_all()
m.create_all()


session = create_session()

for a in xrange(1,10):
    aa = T1()
    bb = T2()
    bb.name = "B%d"%a
    aa.b = bb
    session.save(aa)
    session.save(bb)

session.flush()
session.clear()

for a in session.query(T1).select(order_by=[T2.c.name](T2.c.name), limit=10, offset=0):
    print a.id, a.b.name


for a in session.query(T1).select(order_by=[asc(T2.c.name)](asc(T2.c.name)), limit=10, offset=0):
    print a.id, a.b.name

results:

2006-11-15 17:20:26,450 INFO sqlalchemy.engine.base.Engine.0x..d4 SELECT b_1b29.id AS b_1b29_id, b_1b29.name AS b_1b29_name, a.id AS a_id, a.b_id AS a_b_id
FROM (SELECT a.id AS a_id, b.name AS b_name
FROM a, b ORDER BY b.name
 LIMIT 10 OFFSET 0) AS tbl_row_count, a LEFT OUTER JOIN b AS b_1b29 ON b_1b29.id = a.b_id
WHERE a.id = tbl_row_count.a_id ORDER BY b_1b29.name
2006-11-15 17:20:26,452 INFO sqlalchemy.engine.base.Engine.0x..d4 [B1
2 B2
3 B3
4 B4
5 B5
6 B6
7 B7
8 B8
9 B9
2006-11-15 17:20:26,512 INFO sqlalchemy.engine.base.Engine.0x..d4 SELECT b_1b29.id AS b_1b29_id, b_1b29.name AS b_1b29_name, a.id AS a_id, a.b_id AS a_b_id
FROM (SELECT a.id AS a_id, b.name AS b_name
FROM a, b ORDER BY b.name ASC
 LIMIT 10 OFFSET 0) AS tbl_row_count, a LEFT OUTER JOIN b AS b_1b29 ON b_1b29.id = a.b_id
WHERE a.id = tbl_row_count.a_id ORDER BY tbl_row_count.b_name ASC
2006-11-15 17:20:26,514 INFO sqlalchemy.engine.base.Engine.0x..d4 [](]
1)
3 B3
6 B6
9 B9
2 B2
5 B5
8 B8
1 B1
4 B4
7 B7

I have expected the save results in the both cases.

Comments (2)