Bugs item #2457195, was opened at 2008-12-22 02:18 Message generated for change (Comment added) made by nielsnes You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2457195&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: MonetDB5 CVS Head >Status: Closed >Resolution: Fixed Priority: 5 Private: No Submitted By: Stefan de Konink (skinkie) Assigned to: Niels Nes (nielsnes) Summary: Broken query results in OOM, working one has no output Initial Comment: The following query operates on a table with less than 300k of records. create table test (lhs varchar(16), rhs varchar(16)); select rhs, (lhs like 'A%' OR lhs like 'B%' OR lhs like 'C%' OR lhs like 'D%' OR lhs like 'E%' OR lhs like 'F%' OR lhs like 'G%' OR lhs like 'H%' OR lhs like 'I%' OR lhs like 'J%' OR lhs like 'K%' OR lhs like 'L%' OR lhs like 'M%' OR lhs like 'N%' OR lhs like 'O%' OR lhs like 'P%' OR lhs like 'Q%' OR lhs like 'R%' OR lhs like 'S%' OR lhs like 'T%' OR lhs like 'U%' OR lhs like 'V%' OR lhs like 'W%' OR lhs like 'X%' OR lhs like 'Y%' OR lhs like 'Z%'), (lower(lhs) like 'a%'), (lower(lhs) like 'in%'), (lower(lhs) like 'un%'), (lower(lhs) like 'non%'), ((lower(lhs) like '%ise_') OR (lower(lhs) like '%ize_')), (lower(lhs) like '%ly_'), (lower(lhs) like '%s_'), (lower(lhs) like '%ed_'), (lower(lhs) like '%ing_'), (lower(lhs) like '%-%_'), (lower(lhs) like '%-_'), (lower(lhs) like '%.%_'), (lower(lhs) like '%._') from test where lhs like '%!_' escape '!'; The brokeness is the SELECT part with _ it should be !_. But interestingly; when it is done right I cannot get output: /opt/monetdb/bin/mclient -lsql sql>select rhs, more>(lhs like 'A%' OR lhs like 'B%' OR lhs like 'C%' OR lhs like 'D%' OR lhs like 'E%' OR lhs like 'F%' OR lhs like 'G%' OR lhs like 'H%' OR lhs like 'I%' OR lhs like 'J%' OR lhs like 'K%' OR lhs like 'L%' OR lhs like 'M%' OR lhs like 'N%' OR lhs like 'O%' OR lhs like 'P%' OR lhs like 'Q%' OR lhs like 'R%' OR lhs like 'S%' OR lhs like 'T%' OR lhs like 'U%' OR lhs like 'V%' OR lhs like 'W%' OR lhs like 'X%' OR lhs like 'Y%' OR lhs like 'Z%'), more>(lower(lhs) like 'a%'), more>(lower(lhs) like 'in%'), more>(lower(lhs) like 'un%'), more>(lower(lhs) like 'non%'), more>((lower(lhs) like '%ise!_') OR (lower(lhs) like '%ize!_')), more>(lower(lhs) like '%ly!_'), more>(lower(lhs) like '%s!_'), more>(lower(lhs) like '%ed!_'), more>(lower(lhs) like '%ing!_'), more>(lower(lhs) like '%-%!_'), more>(lower(lhs) like '%-!_'), more>(lower(lhs) like '%.%!_'), more>(lower(lhs) like '%.!_') more>from test where lhs like '%!_' escape '!'; 950028 tuples I must point out that I think the amount of memory/cpu is time is used for this query is extremely high. Like my previous suggestion in the feature request, I hope that the deduplication of operations in one query, or per row can be achieved to reduce the actual operations. ---------------------------------------------------------------------- >Comment By: Niels Nes (nielsnes) Date: 2008-12-27 22:51 Message: added test OOM_query.SF-2457195.sql fixed memory leak and missing BATseqbase ---------------------------------------------------------------------- Comment By: Stefan de Konink (skinkie) Date: 2008-12-22 02:33 Message: Just as reference; I noticed that the right query for SQLite: select rhs, (lhs like 'A%' OR lhs like 'B%' OR lhs like 'C%' OR lhs like 'D%' OR lhs like 'E%' OR lhs like 'F%' OR lhs like 'G%' OR lhs like 'H%' OR lhs like 'I%' OR lhs like 'J%' OR lhs like 'K%' OR lhs like 'L%' OR lhs like 'M%' OR lhs like 'N%' OR lhs like 'O%' OR lhs like 'P%' OR lhs like 'Q%' OR lhs like 'R%' OR lhs like 'S%' OR lhs like 'T%' OR lhs like 'U%' OR lhs like 'V%' OR lhs like 'W%' OR lhs like 'X%' OR lhs like 'Y%' OR lhs like 'Z%'), (lower(lhs) like 'a%'), (lower(lhs) like 'in%'), (lower(lhs) like 'un%'), (lower(lhs) like 'non%'), ((lower(lhs) like '%ise!_' escape '!') OR (lower(lhs) like '%ize!_' escape '!')), (lower(lhs) like '%ly!_' escape '!'), (lower(lhs) like '%s!_' escape '!'), (lower(lhs) like '%ed!_' escape '!'), (lower(lhs) like '%ing!_' escape '!'), (lower(lhs) like '%-%!_' escape '!'), (lower(lhs) like '%-!_' escape '!'), (lower(lhs) like '%.%!_' escape '!'), (lower(lhs) like '%.!_'escape '!' ) from test where lhs like '%!_' escape '!'; Is not even working for MonetDB; !SELECT: no such operator 'like' ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2457195&group_id=56967 ------------------------------------------------------------------------------ _______________________________________________ Monetdb-bugs mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
