Bugs item #2879008, was opened at 2009-10-14 16:06
Message generated for change (Comment added) made by nielsnes
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: Closed
>Resolution: Fixed
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: Niels Nes (nielsnes)
Date: 2009-11-22 20:33
Message:
fixed by properly implementing a crossproduct + like filter
( test from the like_bug.SF-2878994.sql)
----------------------------------------------------------------------
Comment By: Fabian (mr-meltdown)
Date: 2009-10-29 13: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 13: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 14: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
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
