From issues-return-92324-archive-asf-public=cust-asf.ponee.io@ignite.apache.org Wed Feb 27 02:20:07 2019 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id A60D718062C for ; Wed, 27 Feb 2019 03:20:06 +0100 (CET) Received: (qmail 89235 invoked by uid 500); 27 Feb 2019 02:20:05 -0000 Mailing-List: contact issues-help@ignite.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@ignite.apache.org Delivered-To: mailing list issues@ignite.apache.org Received: (qmail 89220 invoked by uid 99); 27 Feb 2019 02:20:05 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 27 Feb 2019 02:20:05 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id 1F1711826F3 for ; Wed, 27 Feb 2019 02:20:05 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -109.501 X-Spam-Level: X-Spam-Status: No, score=-109.501 tagged_above=-999 required=6.31 tests=[ENV_AND_HDR_SPF_MATCH=-0.5, KAM_ASCII_DIVIDERS=0.8, RCVD_IN_DNSWL_MED=-2.3, SPF_PASS=-0.001, USER_IN_DEF_SPF_WL=-7.5, USER_IN_WHITELIST=-100] autolearn=disabled Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id UQm6KGgQFaUk for ; Wed, 27 Feb 2019 02:20:03 +0000 (UTC) Received: from mailrelay1-us-west.apache.org (mailrelay1-us-west.apache.org [209.188.14.139]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTP id D021160CDB for ; Wed, 27 Feb 2019 02:20:02 +0000 (UTC) Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id 77AECE27F5 for ; Wed, 27 Feb 2019 02:20:01 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id 8A13C2457D for ; Wed, 27 Feb 2019 02:20:00 +0000 (UTC) Date: Wed, 27 Feb 2019 02:20:00 +0000 (UTC) From: "Evgenii Zhuravlev (JIRA)" To: issues@ignite.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Comment Edited] (IGNITE-11373) varchar_ignorecase doesn't work properly MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/IGNITE-11373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16778792#comment-16778792 ] Evgenii Zhuravlev edited comment on IGNITE-11373 at 2/27/19 2:19 AM: --------------------------------------------------------------------- I did the analysis of this issue and added an additional flag for the H2 connection string(org.apache.ignite.internal.processors.query.h2.ConnectionManager#DB_OPTIONS): IGNORECASE=TRUE; It started to work, however, after adding an index on this field, it starts to fail in 2 places on the field type validation: InlineIndexHelper: On creating the index: if (this.type != type) throw new UnsupportedOperationException("Invalid fast index type: " + type); And on reading: if (val.getType() != type) throw new UnsupportedOperationException("value type doesn't match"); After commenting these lines, looks like everything started to work. I see that it works much faster than without index. So, looks like it something that could be fixed pretty fast, we just need to figure out how to change this type check. Here is the updated code for the reproducer with index {code:java} Ignite ignite = Ignition.start("examples/config/example-ignite.xml"); IgniteCache cache = ignite.getOrCreateCache("TEST"); cache.query(new SqlFieldsQuery("CREATE TABLE IF NOT EXISTS TEST\n" + "(\n" + " TEST_ID NUMBER(15) NOT NULL,\n" + " TEST_VALUE VARCHAR_IGNORECASE(100),\n" + " PRIMARY KEY (TEST_ID)\n" + ") ")); cache.query(new SqlFieldsQuery("CREATE INDEX value_idx ON TEST (TEST_VALUE);")); for (int i = 0; i < 100000; i++) System.out.println("INSERTED: " + i + " - " + ignite.cache("TEST").query(new SqlFieldsQuery("INSERT INTO TEST values (" + i + " , '" + (i == 50000 ? "" : i) + "aAa')")).getAll().size()); for (int i = 0; i < 1000; i++) { StopWatch sw = new StopWatch(); sw.start(); System.out.println("FOUND:" + ignite.cache("TEST").query(new SqlFieldsQuery("Select * from TEST where TEST_VALUE = 'aaa'")).getAll().size()); sw.stop(); System.out.println("Time: " + sw.getTotalTimeMillis()); } {code} was (Author: ezhuravl): I did the analysis of this issue and added an additional flag for the H2 connection string(org.apache.ignite.internal.processors.query.h2.ConnectionManager#DB_OPTIONS): IGNORECASE=TRUE; It started to work, however, after adding an index on this field, it starts to fail in 2 places on the field type validation: InlineIndexHelper: On creating the index: if (this.type != type) throw new UnsupportedOperationException("Invalid fast index type: " + type); And on reading: if (val.getType() != type) throw new UnsupportedOperationException("value type doesn't match"); After commenting these lines, looks like everything started to work. I see that it works much faster than without index. So, looks like it something that could be fixed pretty fast, we just need to figure out how to change this type check. > varchar_ignorecase doesn't work properly > ---------------------------------------- > > Key: IGNITE-11373 > URL: https://issues.apache.org/jira/browse/IGNITE-11373 > Project: Ignite > Issue Type: Bug > Components: sql > Reporter: Evgenii Zhuravlev > Priority: Major > > Looks like a field with type varchar_ignorecase can't be used for filtering the values for different cases. > {code:java} > Ignite ignite = Ignition.start("examples/config/example-ignite.xml"); > > IgniteCache cache = ignite.getOrCreateCache("TEST"); > cache.query(new SqlFieldsQuery("CREATE TABLE IF NOT EXISTS TEST\n" + > "(\n" + > " TEST_ID NUMBER(15) NOT NULL,\n" + > " TEST_VALUE VARCHAR_IGNORECASE(100),\n" + > " PRIMARY KEY (TEST_ID)\n" + > ") ")); > System.out.println("INSERTED:" + ignite.cache("TEST").query(new SqlFieldsQuery("INSERT INTO TEST values (1,'aAa')")).getAll().size()); > System.out.println("FOUND:" + ignite.cache("TEST").query(new SqlFieldsQuery("Select * from TEST where TEST_VALUE like '%aaa%'")).getAll().size()); > {code} -- This message was sent by Atlassian JIRA (v7.6.3#76005)