Select is executed twice

[spatar]

This example shows that MyDevStudio executes selects twice which is wrong:

Prepare table and function for test:

Code: Select all

create table test (
	c1 int auto_increment,
	primary key (c1)
);

delimiter //

create function fn_test()
returns int
reads sql data
begin
insert into test () values ();
return last_insert_id();
end;
//

delimiter ;

Execute this select once from MyDevStudio:

Code: Select all

select fn_test();

And you'll have two lines in the table "test":

Code: Select all

select * from test;

+----+
| c1 |
+----+
|  1 |
|  2 |
+----+

MyDevStudio v2.10.71.1
MySQL v5.0.32

[Alexz]

Actually, we execute SELECT statement twice. Additional execution is required to retrieve record count. So you should not use nested function calls that modify data.

[spatar]

Dear developers,

You can avoid twice execution in case when returning row set is small.

1. You get size of window in which table is displayed and can calculate the least number of rows which you need to display (or simply let it be always 100).
2. Then you run user's query and fetch it until that number of rows.
3. If the query returns that number of rows you execute the counting query.

It'd be very nice improvement.

[Duke]

It's not that easy for MySQL server. Connection remains blocked until all data is fetched from server. Thus, you'll need additional connection in order to implement point 3 from your list.