William: I don't see attachment in your original email. Mind sharing the JIRA number once you open the JIRA (I assume the patch would be attached there) ?
Thanks On Thu, Sep 22, 2016 at 9:24 PM, William <yhxx...@163.com> wrote: > Hi all, > This is a simple scenario, there are two tables: > create table t1 (pk integer primary key, a integer); > create table t2 (pk1 integer not null, pk2 integer not null, a integer > constraint pk primary key (pk1, pk2)); > > Do the following selects: > 1. explain select * from t1 where (pk > 10 and pk < 20) or (pk > 30 and > pk < 40); > results: SKIP SCAN ON 2 RANGES OVER T1 [11] - [40] > > 2. explain select * from t2 where (pk1 > 10 and pk1 < 20) or (pk1 > 30 > and pk1 < 40); > results: FULL SCAN OVER T2 > SERVER FILTER BY ((PK1 > 10 AND PK1 < 20) OR (PK1 > 30 > AND PK1 < 40)) > > Apparently, 2nd SELECT statement should use skip scan instead of full > table scan. But T2 has two pk columns and then WhereOptimizer failed to > optimize it. I went through the code and made a small improvement. > In WhereOptimizer#KeyExpressionVisitor#orKeySlots(), see the > attached patch file for detail. The main idea is we allow slot in childSlot > is null, only if all slots afterwards are null too. So the following > statements are still rejected: > select * from t2 where (pk1 > 10 and pk1 < 20) or (pk2 > 30 and pk2 < > 40) > > Please review this. Thanks. > William. > > > > >
