[Mailman-Developers] Re: Rest API not returning members in alphabetical order
Mark Sapiro writes: > On 4/2/20 1:05 PM, brian_carpen...@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. 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 ___ 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] Re: Rest API not returning members in alphabetical order
On 4/2/20 1:05 PM, brian_carpen...@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 The highway is for gamblers, San Francisco Bay Area, Californiabetter use your sense - B. Dylan ___ 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] Re: Rest API not returning members in alphabetical order
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 ___ 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] Re: Rest API not returning members in alphabetical order
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! ___ 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] Re: Rest API not returning members in alphabetical order
Goran Terzic writes: > 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. Thank you for finding that, I never would have! > https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/ ___ 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] Re: Rest API not returning members in alphabetical order
On 4/1/20 3:41 PM, brian_carpen...@emwd.com wrote: 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/ ___ 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 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/ ___ 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] Re: Rest API not returning members in alphabetical order
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/ ___ 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] Re: Rest API not returning members in alphabetical order
On Wed, Apr 1, 2020, at 10:19 AM, Abhilash Raj 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) 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. > > [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) -- 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] Re: Rest API not returning members in alphabetical order
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 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] Re: Rest API not returning members in alphabetical order
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] Re: Rest API not returning members in alphabetical order
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. ___ 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] Re: Rest API not returning members in alphabetical order
Alphabetically in regards to email address. We are using MySQL on the development server instead of Postgresql. We tried different versions of SQLAlchemy as well. Same results. ___ 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] Re: Rest API not returning members in alphabetical order
Just to add some additional information: 1. List owners/moderators are being sorted alphabetically. Just not list members/users. 2. Mailing Lists are being sorted alphabetically. Just not list members/users. 3. Using MySQL as the backend database. ___ 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] Re: Rest API not returning members in alphabetical order
Any online developers that can help out with this? It would be helpful. Brian ___ 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] Re: Rest API not returning members in alphabetical order
Any online developers that can help out with this? It would be helpful. Brian ___ 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
