From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 79C6646970E for ; Thu, 26 Dec 2019 03:48:52 +0300 (MSK) From: Nikita Pettik Date: Thu, 26 Dec 2019 03:48:48 +0300 Message-Id: <4189b72ec3cd58478e899ac669b5aaaf4e1077a7.1577321174.git.korablev@tarantool.org> Subject: [Tarantool-patches] [PATCH] sql: fix index consideration with INDEXED BY clause List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: tarantool-patches@dev.tarantool.org Cc: v.shpilevoy@tarantool.org Accidentally, number of indexes to be considered during query planning in presence of INDEXED BY is calculated wrong. Instead of one (INDEXED BY is not a hint but requirement) index to be used (which is indicated in INDEXED BY clause), all space indexes take part in query planning. There are not so many tests checking this feature, so unfortunately this bug was hidden. Let's fix it and force only one index to be used in QP in case of INDEXED BY clause. --- Branch: https://github.com/tarantool/tarantool/tree/np/sql-indexed-by-fix Bug is found by one of Tarantool's customers in MailRu. src/box/sql/where.c | 7 +++++-- test/sql-tap/eqp.test.lua | 19 ++++++++++++++++++- 2 files changed, 23 insertions(+), 3 deletions(-) diff --git a/src/box/sql/where.c b/src/box/sql/where.c index ed507bf4d..7ec43e184 100644 --- a/src/box/sql/where.c +++ b/src/box/sql/where.c @@ -2867,8 +2867,11 @@ tnt_error: * If there was an INDEXED BY clause, then only that one * index is considered. */ - uint32_t idx_count = fake_index == NULL || pSrc->pIBIndex != NULL ? - space->index_count : 1; + uint32_t idx_count = 0; + if (pSrc->pIBIndex != NULL || fake_index != NULL) + idx_count = 1; + else + idx_count = space->index_count; for (uint32_t i = 0; i < idx_count; iSortIdx++, i++) { if (i > 0) probe = space->index[i]->def; diff --git a/test/sql-tap/eqp.test.lua b/test/sql-tap/eqp.test.lua index b8c3c6607..83ef947bb 100755 --- a/test/sql-tap/eqp.test.lua +++ b/test/sql-tap/eqp.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(68) +test:plan(70) --!./tcltestrunner.lua -- 2010 November 6 @@ -772,6 +772,9 @@ test:do_execsql_test( [[ CREATE TABLE t1(a INT , b INT , c INT , PRIMARY KEY(b, c)); CREATE TABLE t2(id INT primary key, a INT , b INT , c INT ); + CREATE TABLE t(id INT PRIMARY KEY, r_d TEXT, s INTEGER); + CREATE INDEX i1 ON t (r_d, s); + CREATE INDEX i2 ON t (s); ]]) test:do_eqp_test("8.1.1", "SELECT * FROM t2", { @@ -796,5 +799,19 @@ test:do_eqp_test("8.2.4", "SELECT count(*) FROM t1", { {0, 0, 0, "B+tree count T1"}, }) +-- Verify that INDEXED BY clause forces specified index. +-- Test case (in simplified form) is taken from customer. +-- +test:do_eqp_test( + "8.2.5.1", + [[ SELECT r_d, s FROM t INDEXED BY i1 WHERE r_d > '10' LIMIT 10; ]], { + {0, 0, 0, "SEARCH TABLE T USING COVERING INDEX I1 (R_D>?) (~262144 rows)"}, +}) + +test:do_eqp_test( + "8.2.5.2", + [[ SELECT r_d, s FROM t INDEXED BY i1 WHERE r_d > '10' AND s = 0 LIMIT 10; ]], { + { 0, 0, 0, "SEARCH TABLE T USING COVERING INDEX I1 (R_D>?) (~245760 rows)" }, +}) test:finish_test() -- 2.15.1