Re: Rest API not returning members in alphabetical order
Brian Carpenter writes:
On 4/1/20 9:22 AM, Stephen J. Turnbull wrote:
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.
On Wed, Apr 1, 2020, at 10:10 AM, Stephen J. Turnbull wrote:
The relevant code is here1, I think, I haven't dug deeper. The order defined in the group is basically:
order = (Member.list_id, Address.email, Member.role)
Th
-- thanks, Abhilash Raj (maxking)
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 <maxking@asynchronous.in> wrote:
Goran Terzic writes:
Thank you for finding that, I never would have!
https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/
On Wed, Apr 1, 2020, at 10:19 AM, Abhilash Raj wrote:
it also seems like I may have mis-read the post. I was thinking only about the Find Member's API, but if the roster API is returning non-sorted list, I think I agree with Steve here, I don't see anything wrong in the Mailman code.
-- thanks, Abhilash Raj (maxking)
Abhilash Raj wrote:
Find Member's API, but if the roster API is returning non-sorted list, I think I agree with Steve here, I don't see anything wrong in the Mailman code.
So you disagree with Goran's findings here? We just switched over to Postgresql and the alphabetically sorting is now working. So I agree with Goran's statement:
"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/
On 4/1/20 3:41 PM, brian_carpenter@emwd.com wrote:
Actually I stand corrected. Abhilash is correct. Nothing wrong with Mailman's code. However is there anything you guys can do to mitigate that issue with Mariadb?
-- Please let me know if you need further assistance.
Thank you for your business. We appreciate our clients. Brian Carpenter EMWD.com
-- EMWD's Knowledgebase: https://clientarea.emwd.com/index.php/knowledgebase
EMWD's Community Forums http://discourse.emwd.com/
Brian Carpenter writes:
However is there anything you guys can do to mitigate that issue with Mariadb?
Bottom line up front: In the meantime I'd say if you want to use MariaDB you're going to have to sort the results yourself on the client side. Sorry about that.
Goran's analysis is correct AFAICS, and implies that SQLAlchemy is not working correctly with Mariadb. It might be possible to change our code to avoid the problem, but really it's SQLAlchemy's job to do that (or to reject our code or warn that it may not have the expected effect) in my opinion. Will think more carefully, RTFM, and then check SQLAlchemy tracker and file a bug or enhancement request if it seems appropriate.
Since it seems the SQL generated by SQLAlchemy is nonconformant to standard SQL, any of our backends might silently do this at any upgrade. I think *we* *should* do something about it, and I'll file a Mailman bug tomorrow (if somebody doesn't beat me to it, hint!) But I'm an Internet standards geek, not a database querymonger, and I don't know how soon I'll have time to book up on the subtleties of SQL and SQLAlchemy. Maybe Abhilash has the skills and can find the time, but I don't recall Mark being an SQL hacker. So, no promises of a quick fix.
Steve
P.S. I'm sorry to be such a downer on the likelihood of a quick fix, but I'm enjoying this. :-) Always good to be learning new stuff!
https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/ is saying it is not a bug so I doubt SQLAlchemy will think it is. We just switched over to PostgreSQL with very little problem so it is no longer an issue.
Stay healthy Steve!
Brian
On 4/2/20 1:05 PM, brian_carpenter@emwd.com wrote:
https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/ is saying it is not a bug so I doubt SQLAlchemy will think it is.
My take is a bit different. MariaDB is saying this is not a MariaDB bug. They would say that it is a SQLAlchemy bug in that in this case, the SQLAlchemy generated query should put the ORDER BY clause on the outer SELECT and not on the sub-query.
-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
Mark Sapiro writes:
On 4/2/20 1:05 PM, brian_carpenter@emwd.com wrote:
Yup, my take is the same.
@mailman-developers I will do some research to find out if SQLAlchemy is aware of this already, or maybe has documented it as a limitation with appropriate ways to deal with it in user code. But it's definitely reportable against SQLAlchemy if MariaDB is right about the SQL standard (trust but verify on that one too :-).
Steve
On Wed, Apr 1, 2020, at 10:10 AM, Stephen J. Turnbull wrote:
The relevant code is here1, I think, I haven't dug deeper. The order defined in the group is basically:
order = (Member.list_id, Address.email, Member.role)
Th
-- thanks, Abhilash Raj (maxking)
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 <maxking@asynchronous.in> wrote:
Goran Terzic writes:
Thank you for finding that, I never would have!
https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/
On Wed, Apr 1, 2020, at 10:19 AM, Abhilash Raj wrote:
it also seems like I may have mis-read the post. I was thinking only about the Find Member's API, but if the roster API is returning non-sorted list, I think I agree with Steve here, I don't see anything wrong in the Mailman code.
-- thanks, Abhilash Raj (maxking)
Abhilash Raj wrote:
Find Member's API, but if the roster API is returning non-sorted list, I think I agree with Steve here, I don't see anything wrong in the Mailman code.
So you disagree with Goran's findings here? We just switched over to Postgresql and the alphabetically sorting is now working. So I agree with Goran's statement:
"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/
On 4/1/20 3:41 PM, brian_carpenter@emwd.com wrote:
Actually I stand corrected. Abhilash is correct. Nothing wrong with Mailman's code. However is there anything you guys can do to mitigate that issue with Mariadb?
-- Please let me know if you need further assistance.
Thank you for your business. We appreciate our clients. Brian Carpenter EMWD.com
-- EMWD's Knowledgebase: https://clientarea.emwd.com/index.php/knowledgebase
EMWD's Community Forums http://discourse.emwd.com/
Brian Carpenter writes:
However is there anything you guys can do to mitigate that issue with Mariadb?
Bottom line up front: In the meantime I'd say if you want to use MariaDB you're going to have to sort the results yourself on the client side. Sorry about that.
Goran's analysis is correct AFAICS, and implies that SQLAlchemy is not working correctly with Mariadb. It might be possible to change our code to avoid the problem, but really it's SQLAlchemy's job to do that (or to reject our code or warn that it may not have the expected effect) in my opinion. Will think more carefully, RTFM, and then check SQLAlchemy tracker and file a bug or enhancement request if it seems appropriate.
Since it seems the SQL generated by SQLAlchemy is nonconformant to standard SQL, any of our backends might silently do this at any upgrade. I think *we* *should* do something about it, and I'll file a Mailman bug tomorrow (if somebody doesn't beat me to it, hint!) But I'm an Internet standards geek, not a database querymonger, and I don't know how soon I'll have time to book up on the subtleties of SQL and SQLAlchemy. Maybe Abhilash has the skills and can find the time, but I don't recall Mark being an SQL hacker. So, no promises of a quick fix.
Steve
P.S. I'm sorry to be such a downer on the likelihood of a quick fix, but I'm enjoying this. :-) Always good to be learning new stuff!
https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/ is saying it is not a bug so I doubt SQLAlchemy will think it is. We just switched over to PostgreSQL with very little problem so it is no longer an issue.
Stay healthy Steve!
Brian
On 4/2/20 1:05 PM, brian_carpenter@emwd.com wrote:
https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/ is saying it is not a bug so I doubt SQLAlchemy will think it is.
My take is a bit different. MariaDB is saying this is not a MariaDB bug. They would say that it is a SQLAlchemy bug in that in this case, the SQLAlchemy generated query should put the ORDER BY clause on the outer SELECT and not on the sub-query.
-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
Mark Sapiro writes:
On 4/2/20 1:05 PM, brian_carpenter@emwd.com wrote:
Yup, my take is the same.
@mailman-developers I will do some research to find out if SQLAlchemy is aware of this already, or maybe has documented it as a limitation with appropriate ways to deal with it in user code. But it's definitely reportable against SQLAlchemy if MariaDB is right about the SQL standard (trust but verify on that one too :-).
Steve
participants (6)
-
Abhilash Raj -
Brian Carpenter -
brian_carpenter＠emwd.com
-
Goran Terzic -
Mark Sapiro -
Stephen J. Turnbull