maria-developers team mailing list archive

[Paul McCullagh <paul.mccullagh@xxxxxxxxxxxxx>]

Hi Kristian,

I ran the test below using both InnoDB and PBXT, using MariaDB  
(lp:~maria-captains/maria/5.1-release) and MySQL 5.1.46.

I get the same result from both engines (and both servers).

The select "select t1.id,t2.id from t2 left join t1 on t1.id>=74 and  
t1.id<=0 where t2.id=75 and t1.id is null;"

returns an empty set for both engines.

So this looks like a change that has nothing to do with the engine.

Best regards,

Paul


-------------------------
create table t1 (id int primary key) engine=pbxt;
create table t2 (id int) engine=pbxt;
insert into t1 values (75);
insert into t1 values (79);
insert into t1 values (78);
insert into t1 values (77);
replace into t1 values (76);
replace into t1 values (76);
insert into t1 values (104);
insert into t1 values (103);
insert into t1 values (102);
insert into t1 values (101);
insert into t1 values (105);
insert into t1 values (106);
insert into t1 values (107);

insert into t2 values (107),(75),(1000);

select t1.id, t2.id from t1, t2 where t2.id = t1.id;
select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by  
t1.id;
select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by  
t2.id;

#
# Test problems with impossible ON or WHERE
#
select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0  
where t2.id=75 and t1.id is null;
explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and  
t1.id<=0 where t2.id=75 and t1.id is null;
explain select t1.id, t2.id from t1, t2 where t2.id = t1.id and t1.id  
<0 and t1.id > 0;
drop table t1,t2;



On Apr 28, 2010, at 11:30 PM, Kristian Nielsen wrote:

> Hi Paul,
>
> I'm merging MySQL 5.1.46 into MariaDB. But I hit a test failure in  
> PBXT, see
> below.
>
> It is wrong result from a SELECT. Interestingly, the same SELECT in  
> the main
> test suite seems to work correctly.
>
> The code is in
>
>    lp:~maria-captains/maria/5.1-release
>
> Can you maybe take a look, in case it is a PBXT issue? Probably also  
> one of
> our optimiser people should take a look to see if it is a server  
> problem. It's
> a bit strange that it seems to be pbxt specific, I'm wondering if  
> there was a
> subtle change of the storage engine API introduced or something?
>
> Test failure link and output below.
>
> - Kristian.
>
>
>    http://buildbot.askmonty.org/buildbot/reports/cross_reference#branch 
> =5.1- 
> release 
> &revision 
> = 
> 2855 
> &platform 
> = 
> &dt 
> = 
> &bbnum 
> = 
> &typ 
> = 
> &info 
> = 
> &fail_name 
> =pbxt.join&fail_variant=&fail_info_short=&fail_info_full=&limit=100
>
> Test case pbxt.join
>
> pbxt.join                                w4 [ fail ]
>        Test ended at 2010-04-28 22:47:39
>
> CURRENT_TEST: pbxt.join
> --- /space3/buildbot/makedist/build/install/mysql-test/suite/pbxt/r/ 
> join.result	2010-04-28 22:12:50.000000000 +0200
> +++ /space3/buildbot/makedist/build/install/mysql-test/suite/pbxt/r/ 
> join.reject	2010-04-28 22:47:39.000000000 +0200
> @@ -59,14 +59,13 @@
> 107	1
> select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0  
> where t2.id=75 and t1.id is null;
> id	id
> -NULL	75
> explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and  
> t1.id<=0 where t2.id=75 and t1.id is null;
> id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	SIMPLE	t1	const	PRIMARY	NULL	NULL	NULL	1	Impossible ON condition
> -1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
> +1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE  
> noticed after reading const tables
> explain select t1.id, t2.id from t1, t2 where t2.id = t1.id and  
> t1.id <0 and t1.id > 0;
> id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> -1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE  
> noticed after reading const tables
> +1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
> +1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.id	1	Using index
> drop table t1,t2;
> CREATE TABLE t1 (
> id int(11) NOT NULL auto_increment,
>
> mysqltest: Result length mismatch



--
Paul McCullagh
PrimeBase Technologies
www.primebase.org
www.blobstreaming.org
pbxt.blogspot.com