Bugs item #2879008, was opened at 2009-10-14 18:06
Message generated for change (Comment added) made by mr-meltdown
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2879008&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL/Core
Group: SQL "candidate"
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Roberto Cornacchia (cornuz)
Assigned to: Niels Nes (nielsnes)
Summary: SQL: WHERE A.x LIKE A.y misses results
Initial Comment:
create table a (x varchar(10);
insert into a values ('aaa');
create table b (x varchar(10);
insert into b values ('aaa');
insert into b values ('aAa');
insert into b values ('aA');
create ab as select a.x as x, b.x as y from a,b;
select x from ab where x LIKE y;
Returns the correct result:
+-------+
| x |
+=======+
| aaa |
+-------+
1 tuple
However, the case insensitive version returns the same result as LIKE:
select x from ab where x ILIKE y;
+-------+
| x |
+=======+
| aaa |
+-------+
1 tuple
It misses 'aAa'
----------------------------------------------------------------------
>Comment By: Fabian (mr-meltdown)
Date: 2009-10-29 14:43
Message:
The problem seems unrelated to ilike to me, but in the MAL plan:
function user.s9_0{autoCommit=true}():void;
_2:bat[:oid,:str] := sql.bind("sys","a","x",0);
_7:bat[:oid,:str] := sql.bind("sys","a","x",2);
_9 := algebra.kdifference(_2,_7);
_2:bat[:oid,:str] := nil:BAT;
_10 := algebra.kunion(_9,_7);
_9 := nil:BAT;
_7:bat[:oid,:str] := nil:BAT;
_11:bat[:oid,:str] := sql.bind("sys","a","x",1);
_13 := algebra.kunion(_10,_11);
_10 := nil:BAT;
_11:bat[:oid,:str] := nil:BAT;
_14:bat[:oid,:oid] := sql.bind_dbat("sys","a",1);
_15 := bat.reverse(_14);
_14:bat[:oid,:oid] := nil:BAT;
_16 := algebra.kdifference(_13,_15);
_13 := nil:BAT;
_15 := nil:BAT;
_17 := batcalc.str(_16);
_18:bat[:oid,:str] := sql.bind("sys","b","x",0);
_20:bat[:oid,:str] := sql.bind("sys","b","x",2);
_21 := algebra.kdifference(_18,_20);
_18:bat[:oid,:str] := nil:BAT;
_22 := algebra.kunion(_21,_20);
_21 := nil:BAT;
_20:bat[:oid,:str] := nil:BAT;
_23:bat[:oid,:str] := sql.bind("sys","b","x",1);
_24 := algebra.kunion(_22,_23);
_22 := nil:BAT;
_23:bat[:oid,:str] := nil:BAT;
_25:bat[:oid,:oid] := sql.bind_dbat("sys","b",1);
_26 := bat.reverse(_25);
_25:bat[:oid,:oid] := nil:BAT;
_27 := algebra.kdifference(_24,_26);
_24 := nil:BAT;
_26 := nil:BAT;
_28 := batcalc.str(_27);
_27 := nil:BAT;
_29 := bat.mirror(_17);
_30 := bat.mirror(_28);
_31 := algebra.join(_29,_30);
_29 := nil:BAT;
_30 := nil:BAT;
_32 := algebra.markH(_31,0...@0);
_34 := algebra.markT(_31);
_31 := nil:BAT;
_35 := bat.reverse(_34);
_34 := nil:BAT;
_36 := algebra.join(_35,_17);
_35 := nil:BAT;
_17 := nil:BAT;
_37 := algebra.join(_32,_28);
_28 := nil:BAT;
_104 := bat.new(nil:oid,nil:bit);
barrier (_108,_109,_110) := bat.newIterator(_36);
_112 := algebra.find(_37,_109);
_114 := str.ilike(_110,_112);
bat.insert(_104,_109,_114);
redo (_108,_109,_110) := bat.hasMoreElements(_36);
exit (_108,_109,_110);
_38:bat[:oid,:bit] := _104;
_36 := nil:BAT;
_37 := nil:BAT;
_41 := algebra.uselect(_38,true);
_38:bat[:oid,:bit] := nil:BAT;
_42 := bat.reverse(_41);
_41 := nil:BAT;
_43 := algebra.join(_42,_32);
_42 := nil:BAT;
_32 := nil:BAT;
_44 := bat.reverse(_43);
_43 := nil:BAT;
_45 := algebra.markT(_44,0...@0);
_44 := nil:BAT;
_46 := bat.reverse(_45);
_45 := nil:BAT;
_47 := algebra.leftjoin(_46,_16,nil:lng);
_46 := nil:BAT;
_16 := nil:BAT;
_49 := sql.resultSet(1,1,_47);
sql.rsColumn(_49,"sys.a","x","varchar",10,0,_47);
_47 := nil:BAT;
_54 := io.stdout();
sql.exportResult(_54,_49);
end s9_0;
in particular this line:
_31 := algebra.join(_29,_30);
generates a join between sys.a.x and sys.b.x tables, leaving only the
entry for 'aaa' around. This subresult is then used to reconstruct _36 and
_37 from the full columns, making the iterator only looping over 'aaa',
making the entire str.ilike pretty useless.
I cannot fix this.
----------------------------------------------------------------------
Comment By: Fabian (mr-meltdown)
Date: 2009-10-29 14:22
Message:
case reduced to
SELECT 'a' ILIKE 'A'; (fixed, test pending)
the BAT version is still not fixed though.
----------------------------------------------------------------------
Comment By: Fabian (mr-meltdown)
Date: 2009-10-27 15:12
Message:
the unexpanded (view-less) version exhibits the same bug.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2879008&group_id=56967
------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
