Hi, I am seeking your input about the following issue. Normally if a user just browses a table, he can use the navigation << < > >> and "Page number" to move thru the table. LIMIT clause is generated accordingly.
Now if a user typed a LIMIT clause, what should he expect?
(1) To be able to move thru the entire table (2) or just thru the result set he chose in his LIMIT clause?
Reading this bug report https://sourceforge.net/tracker/index.php?func=detail&aid=1846013&gr...
the user expects (1). I am not sure but I tend to say (2) because of the explicit LIMIT.
Of course in this bug report there are real bugs to solve but to implement the current solution we have to decide about this issue first.
Marc
Hi there,
Marc Delisle schrieb:
Now if a user typed a LIMIT clause, what should he expect?
(1) To be able to move thru the entire table (2) or just thru the result set he chose in his LIMIT clause?
Reading this bug report https://sourceforge.net/tracker/index.php?func=detail&aid=1846013&gr...
the user expects (1). I am not sure but I tend to say (2) because of the explicit LIMIT.
I'd vote for (2), at least for consistency. The result does not have to be just the contents of a table, it may be a complex join or an aggregation as well. Maybe I'm only interested in the topmost 100 results and this is why I explicitly added that LIMIT.
This does not affected browsing only, but also reordering. Let's say, I want to see the top 10 payments made:
SELECT * FROM `payment` ORDER BY `amount` DESC LIMIT 10
Now, I get the result and I want to have these ten records ordered by the date of the payment. I would expect that this is what I get when clicking the "payment_date" column, but phpMyAdmin then throws away my LIMIT and my ORDER BY and I get:
SELECT * FROM `payment` ORDER BY `payment`.`payment_date` ASC LIMIT 0,30
Even if my LIMIT would be kept, I would most propably get different records.
Regards,
Alexander
cand. inf. Alexander M. Turek a écrit :
Hi there,
Marc Delisle schrieb:
Now if a user typed a LIMIT clause, what should he expect?
(1) To be able to move thru the entire table (2) or just thru the result set he chose in his LIMIT clause?
Reading this bug report https://sourceforge.net/tracker/index.php?func=detail&aid=1846013&gr...
the user expects (1). I am not sure but I tend to say (2) because of the explicit LIMIT.
I'd vote for (2), at least for consistency. The result does not have to be just the contents of a table, it may be a complex join or an aggregation as well. Maybe I'm only interested in the topmost 100 results and this is why I explicitly added that LIMIT.
This does not affected browsing only, but also reordering. Let's say, I want to see the top 10 payments made:
SELECT * FROM `payment` ORDER BY `amount` DESC LIMIT 10
Now, I get the result and I want to have these ten records ordered by the date of the payment. I would expect that this is what I get when clicking the "payment_date" column, but phpMyAdmin then throws away my LIMIT and my ORDER BY and I get:
SELECT * FROM `payment` ORDER BY `payment`.`payment_date` ASC LIMIT 0,30
Even if my LIMIT would be kept, I would most propably get different records.
Right. I opened bug #1851833 for this, to avoid mixing different bugs in the same bug ID.
Regards,
Alexander
Marc Delisle schrieb:
Hi, I am seeking your input about the following issue. Normally if a user just browses a table, he can use the navigation << < > >> and "Page number" to move thru the table. LIMIT clause is generated accordingly.
Now if a user typed a LIMIT clause, what should he expect?
seeing what he queried, if he wrote LIMIT 0,2 we should show two lines per page
(1) To be able to move thru the entire table (2) or just thru the result set he chose in his LIMIT clause?
table = result !?
(1) with his limit as initial rows per page
Hi again,
Sebastian Mendel schrieb:
seeing what he queried, if he wrote LIMIT 0,2 we should show two lines per page
What should phpMyAdmin do, if he queries "LIMIT 0,1000000"? Or "LIMIT 1000000"? Displaying a page that might crash his browser might not be his intention. ;-)
And what shall we do in the following case?
SELECT * FROM (SELECT * FROM table LIMIT 0,2) t;
This would be semantically identical to
SELECT * FROM table LIMIT 0,2;
Should we handle it differently? If not, do you really want to walk through each query and collect all limits? :-/
If I wanted to browse the unlimited result, I would omit the LIMIT.
Regards,
Alexander
cand. inf. Alexander M. Turek schrieb:
Hi again,
Sebastian Mendel schrieb:
seeing what he queried, if he wrote LIMIT 0,2 we should show two lines per page
What should phpMyAdmin do, if he queries "LIMIT 0,1000000"? Or "LIMIT 1000000"? Displaying a page that might crash his browser might not be his intention. ;-)
it is the users decission ... what will you do if he writes 10.000 in the results per page input box?
phpMyAdmin is a tool! ... user should know how to use! ... if he writes LIMIT 10000 phpMyAdmin should do - there is no need to force the user into any limitations ...
And what shall we do in the following case?
SELECT * FROM (SELECT * FROM table LIMIT 0,2) t;
nothing, add the defualt LIMIT 0,30
This would be semantically identical to
SELECT * FROM table LIMIT 0,2;
no, it is not - from th point of phpMyAdmin, for phpMyAdmin only the resulting rows are relevant, and if they don't limited by the user, PMA will do
Should we handle it differently? If not, do you really want to walk through each query and collect all limits? :-/
no, subselcts are not relevant, interesting are only the resulting rows
If I wanted to browse the unlimited result, I would omit the LIMIT.
yes, and PMA will paginate it
i do not see a problem if someone writes LIMIT 0,10 and PMA shows 10 rows on the first page, and pagfination for all the the other rows
possible we should replace the curent behaviour for PMA 3.0:
- do not add any LIMIT - just surround the user supplied query with SELECT * FROM ([user query]) LIMIT ... - this will also work with sorting a query that is limited to 10 first rows sorted
Sebastian Mendel a écrit :
cand. inf. Alexander M. Turek schrieb:
Hi again,
Sebastian Mendel schrieb:
seeing what he queried, if he wrote LIMIT 0,2 we should show two lines per page
What should phpMyAdmin do, if he queries "LIMIT 0,1000000"? Or "LIMIT 1000000"? Displaying a page that might crash his browser might not be his intention. ;-)
it is the users decission ... what will you do if he writes 10.000 in the results per page input box?
Indeed we never blocked this. Also, even if we don't activate the ShowAll feature by default, if it's activated crashes may occur but we warn users in the doc :)
phpMyAdmin is a tool! ... user should know how to use! ... if he writes LIMIT 10000 phpMyAdmin should do - there is no need to force the user into any limitations ...
I agree. Also in the future there might be super browsers on super machines that can handle what current browsers cannot.
And what shall we do in the following case?
SELECT * FROM (SELECT * FROM table LIMIT 0,2) t;
nothing, add the defualt LIMIT 0,30
This would be semantically identical to
SELECT * FROM table LIMIT 0,2;
no, it is not - from th point of phpMyAdmin, for phpMyAdmin only the resulting rows are relevant, and if they don't limited by the user, PMA will do
Should we handle it differently? If not, do you really want to walk through each query and collect all limits? :-/
no, subselcts are not relevant, interesting are only the resulting rows
If I wanted to browse the unlimited result, I would omit the LIMIT.
yes, and PMA will paginate it
i do not see a problem if someone writes LIMIT 0,10 and PMA shows 10 rows on the first page, and pagfination for all the the other rows
possible we should replace the curent behaviour for PMA 3.0:
- do not add any LIMIT
- just surround the user supplied query with SELECT * FROM ([user query])
LIMIT ...
- this will also work with sorting a query that is limited to 10 first
rows sorted
I'm not sure it's a good idea to force subqueries for all queries. Also it would not fix the problem for the 2.11 branch which will be alive a number of years.
Sebastian Mendel a écrit :
Marc Delisle schrieb:
Hi, I am seeking your input about the following issue. Normally if a user just browses a table, he can use the navigation << < > >> and "Page number" to move thru the table. LIMIT clause is generated accordingly.
Now if a user typed a LIMIT clause, what should he expect?
seeing what he queried, if he wrote LIMIT 0,2 we should show two lines per page
(1) To be able to move thru the entire table (2) or just thru the result set he chose in his LIMIT clause?
table = result !?
No sure I understand your question, but the result set from a query is different than showing the plain table. See the other answer by Alexander.
(1) with his limit as initial rows per page
I thought about this. We could use his "LIMIT x,y" to fill the "Show y rows starting with record #x". But I'm still not sure: why did the user put an explicit LIMIT? To limit the initial display, or because he's really not interested in seeing other rows?
Marc Delisle schrieb:
Sebastian Mendel a écrit :
Marc Delisle schrieb:
Hi, I am seeking your input about the following issue. Normally if a user just browses a table, he can use the navigation << < > >> and "Page number" to move thru the table. LIMIT clause is generated accordingly.
Now if a user typed a LIMIT clause, what should he expect?
seeing what he queried, if he wrote LIMIT 0,2 we should show two lines per page
(1) To be able to move thru the entire table (2) or just thru the result set he chose in his LIMIT clause?
table = result !?
No sure I understand your question, but the result set from a query is different than showing the plain table. See the other answer by Alexander.
(1) with his limit as initial rows per page
I thought about this. We could use his "LIMIT x,y" to fill the "Show y rows starting with record #x". But I'm still not sure: why did the user put an explicit LIMIT? To limit the initial display, or because he's really not interested in seeing other rows?
if he is not interested in seeing other rows, pagination will not hurt?
the problem is, if we omit all rows not in his LIMIT and do not display pagination, and he re-sorts the result by clicking on column he will get completely different rows, cause the sorting is not done on the result, but on the on the source of the result before the LIMIT - i think this will confuse even more, or?
-
cand. inf. Alexander M. Turek -
Marc Delisle -
Sebastian Mendel