Paul McCullagh <paul.mccull...@primebase.org> writes:
> All tests in the PBXT suite run through on Mac and Linux, except for one
> error under Linux, which is a bit weird (see below).
> ------------------
>
> pbxt.select_safe [ fail ]
> Test ended at 2010-05-06 17:19:13
>
> CURRENT_TEST: pbxt.select_safe
> mysqltest: At line 19: query 'select 1 from t1,t1 as t2,t1 as t3' failed:
> 1104: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE
> and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
>
> The result from queries just before the failure was:
> drop table if exists t1;
> SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9;
> create table t1 (a int auto_increment primary key, b char(20));
> insert into t1 values(1,"test");
> SELECT SQL_BUFFER_RESULT * from t1;
> a b
> 1 test
> update t1 set b="a" whe
Ok, I looked into this.
So this test does a three-way cartesian self-join on a table with 2 rows (a
total of 8 rows). And it sets SQL_MAX_JOIN_SIZE=9. And sometimes (apparently
timing dependent), this test fails because the optimiser estimates more than 9
rows in the join. So the problem here is that the optimiser estimate is
sometimes too big for SQL_MAX_JOIN_SIZE=9.
What I found is that when the test case fails, the function ha_pbxt::info()
returns records==3 for the table. When the test case succeeds, it returns
records==2. So it's pretty clear that the server will throw an error when
records==3 and SQL_MAX_JOIN_SIZE=9.
What remains to consider is why the returned records value differs between
test runs.
The testcase actually first inserts a row, then deletes it, then inserts two
more rows. I'm speculating that PBXT has some background cleanup thread or
similar that causes a race between freeing up space from the first row and
allocating space for the two new rows?
So Paul, do you think returning records = 2 or 3 at random from
ha_pbxt::info() is expected or not?
If expected, one way to fix the problem is to create a new table before
running the test:
create table t2 like t1;
insert into t2 select * from t1;
analyze table t2; # PBXT: required to get the correct COUNT(*)
select 1 from t2 as t1,t2,t2 as t3;
Then there is no possibility for the insert+delete to take up a slot and cause
a failure (I was not able to repeat the failure with this change).
Alternatively, we can just increate the SQL_MAX_JOIN_SIZE value.
Or alternatively, you may decide that the test is meaningless for PBXT due to
imprecise statistics, and just remove it from select_safe.test.
Just let me know what you prefer, and I'll change it as needed.
- Kristian.
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
