Thank you everyone for looking into this. Looks like the problem is that SQLAlchemy is generating query with 'order by' part inside nested subquery, which is not supported by mariadb.
https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/ SqlAlchemy generated query for getting moderators of selected mailing list: SELECT anon_1.anon_2_member_id AS anon_1_anon_2_member_id, anon_1.anon_2_member__member_id AS anon_1_anon_2_member__member_id, anon_1.anon_2_member_role AS anon_1_anon_2_member_role, anon_1.anon_2_member_list_id AS anon_1_anon_2_member_list_id, anon_1.anon_2_member_moderation_action AS anon_1_anon_2_member_moderation_action, anon_1.anon_2_member_address_id AS anon_1_anon_2_member_address_id, anon_1.anon_2_member_preferences_id AS anon_1_anon_2_member_preferences_id, anon_1.anon_2_member_user_id AS anon_1_anon_2_member_user_id FROM ( SELECT anon_2.member_id AS anon_2_member_id, anon_2.member__member_id AS anon_2_member__member_id, anon_2.member_role AS anon_2_member_role, anon_2.member_list_id AS anon_2_member_list_id, anon_2.member_moderation_action AS anon_2_member_moderation_action, anon_2.member_address_id AS anon_2_member_address_id, anon_2.member_preferences_id AS anon_2_member_preferences_id, anon_2.member_user_id AS anon_2_member_user_id, anon_2.address_email AS anon_2_address_email FROM ( SELECT `member`.id AS member_id, `member`._member_id AS member__member_id, `member`.`role` AS member_role, `member`.list_id AS member_list_id, `member`.moderation_action AS member_moderation_action, `member`.address_id AS member_address_id, `member`.preferences_id AS member_preferences_id, `member`.user_id AS member_user_id, address.email AS address_email FROM `member` INNER JOIN address ON address.id = `member`.address_id WHERE `member`.list_id = 'snowflake.example.com' AND `member`.`role` = 3 UNION SELECT `member`.id AS member_id, `member`._member_id AS member__member_id, `member`.`role` AS member_role, `member`.list_id AS member_list_id, `member`.moderation_action AS member_moderation_action, `member`.address_id AS member_address_id, `member`.preferences_id AS member_preferences_id, `member`.user_id AS member_user_id, address.email AS address_email FROM `member` INNER JOIN user ON user.id = `member`.user_id INNER JOIN address ON user._preferred_address_id = address.id WHERE address.id = user._preferred_address_id AND `member`.list_id = 'snowflake.example.com' AND `member`.`role` = 3 ) AS anon_2 ORDER BY anon_2.member_list_id, anon_2.address_email, anon_2.member_role ) AS anon_1 On Wed, 1 Apr 2020 at 19:20, Abhilash Raj <maxk...@asynchronous.in> wrote: > On Wed, Apr 1, 2020, at 10:10 AM, Stephen J. Turnbull wrote: > > Brian Carpenter writes: > > > On 4/1/20 9:22 AM, Stephen J. Turnbull wrote: > > > > > > Do you have the same version of the same DBMS backend in both? > > > > Same version of SQLAlchemy? > > > > > > Alphabetically in regards to email address. > > > > OK. Thanks for confirming. > > > > What REST endpoint are you using? http://SERVER:PORT/3.1/members? > > Or http://SERVER:PORT/3.1/lists/LIST-POST/roster/member? > > (SERVER, PORT, and LIST-POST are specific to your installation. > > SERVER:PORT is likely localhost:9001, and LIST-POST is the posting > > address of the list being queried.) > > > > > We are using MySQL on the development server instead of Postgresql. > We > > > tried different versions of SQLAlchemy as well. Same results. > > > > Do you mean you have MySQL on one server, and PostgreSQL on another, > > and only one is misbehaving? If so, which one? > > > > At this point, I can't see anything wrong with the Mailman code. > > Maybe somebody else will. > > The relevant code is here[1], I think, I haven't dug deeper. The order > defined in the group is basically: > > order = (Member.list_id, Address.email, Member.role) > > > [1]: > https://gitlab.com/mailman/mailman/-/blob/master/src/mailman/model/subscriptions.py#L79 > > Th > > _______________________________________________ > > Mailman-Developers mailing list -- mailman-developers@python.org > > To unsubscribe send an email to mailman-developers-le...@python.org > > https://mail.python.org/mailman3/lists/mailman-developers.python.org/ > > Mailman FAQ: https://wiki.list.org/x/AgA3 > > > > Security Policy: https://wiki.list.org/x/QIA9 > > > > -- > thanks, > Abhilash Raj (maxking) > _______________________________________________ > Mailman-Developers mailing list -- mailman-developers@python.org > To unsubscribe send an email to mailman-developers-le...@python.org > https://mail.python.org/mailman3/lists/mailman-developers.python.org/ > Mailman FAQ: https://wiki.list.org/x/AgA3 > > Security Policy: https://wiki.list.org/x/QIA9 > _______________________________________________ Mailman-Developers mailing list -- mailman-developers@python.org To unsubscribe send an email to mailman-developers-le...@python.org https://mail.python.org/mailman3/lists/mailman-developers.python.org/ Mailman FAQ: https://wiki.list.org/x/AgA3 Security Policy: https://wiki.list.org/x/QIA9
