For compound indexes indices, Function cursor getRowCount() returns the count of rows that match the first criteria, not all criteria.

[GoogleCodeExporter]

Hi, I've been using SQLJet as a database backend for a web crawler in a 
InfoRecovery course.

I've found that:

For compound indexes (indices), the Function cursor getRowCount() returns the 
count of the rows that match only the first column of the index, i.e. the first 
criterion, not both the columns (both the criteria).  This means that the 
getRowCount() always returns an inflated value.

Interestingly, if I use a while loop and check, for example:

while ( !cursor.eof() ) {
   // read this row ;
   cursor.next();
}

... then SQLJet only returns the values for specific rows that match the 
complex index.

To replicate the error I simply used a copy of the SQLJet demo files for:
   InventoryDB.java
   InventoryItem.java
   InventoryUser.java

Added the two functions you see below (and attached), and compiled them in 
BlueJ.

I then used BlueJ to instantiate an InventoryDB, and then called my 
Test_getRowCount() function, and got these results on the SQLJet demo 
InventoryDB:

<program inputs and outputs>

showInventory(0,0);     // this should return ZERO   found rows, SQLJet says 0 
found rows
Found 0 rows

showInventory(3,1);     // this should return only 1 found row,  SQLJet says 1 
found row
Found 1 rows
3  Cup  Big & White  3  1

showInventory(3,0);     // this should return ZERO   found rows, SQLJet says 1 
found row
Found 1 rows

showInventory(7,23);    // this should return only 1 found row,  SQLJet says 2 
found rows
Found 2 rows
1  MacBook  Unibody 2GHz  7  23

showInventory(7,1);     // this should return ZERO   found rows, SQLJet says 2 
found rows
Found 2 rows

showInventory(7,24);    // this should return only 1 found row,  SQLJet says 2 
found rows
Found 2 rows
2  iPhone 3G  8Mb  7  24

<end program output>

What steps will reproduce the problem?
Compile the small example function; look for the row of "******" to see the 
offending command.

Original issue reported on code.google.com by Sparticu...@gmail.com on 15 Oct 2012 at 12:11

Attachments:

• GetRowCount_Bug.txt

[GoogleCodeExporter]

Original comment by sergey.s...@gmail.com on 15 Oct 2012 at 9:32

• Changed state: Accepted

[GoogleCodeExporter]

Sorry, you didn't show complete code and data for test (database structure, 
indexes, filtering criteria etc), so I can't reproduce bug which you are 
reporting. If you could, please, attach complete code and database.

I don't say that bug doesn't exist, but right now I just can't reproduce it. To 
fix the error, first it is required to reproduce it.

I've tried to write unit-test which should reproduce bug. It doesn't find any 
troubles.
Code is listed below. Please, use it to reproduce the bug, if it is possible. 
Thank you a lot.

<pre>

import java.io.File;

import org.junit.*;
import org.tmatesoft.sqljet.core.table.*;
import org.tmatesoft.sqljet.core.SqlJetException;
import org.tmatesoft.sqljet.core.schema.ISqlJetTableDef;

public class CompoundIndexesCriteriaTest {

    protected File file;
    protected SqlJetDb db;

    @Before
    public void setUp() throws Exception {
        file = File.createTempFile(this.getClass().getSimpleName(), null);
        file.deleteOnExit();
        db = SqlJetDb.open(file, true);
    }

    @After
    public void tearDown() throws Exception {
        if (db != null) {
            db.close();
        }
    }

    @Test
    public void testCompoundIndexesCriteria() throws Exception {

        final ISqlJetTableDef t1 = db.createTable("create table t1(a,b,c);");
        db.createIndex("create index i1 on t1(a,b)");
        final ISqlJetTable table1 = db.getTable(t1.getName());
        table1.insert("a", "b", "c");
        table1.insert("d", "e", "f");
        table1.insert("a", "b", "c");
        table1.insert("d", "e", "f");
        db.runReadTransaction(new ISqlJetTransaction() {
            public Object run(SqlJetDb db) throws SqlJetException {
                final ISqlJetCursor lookup = table1.lookup("i1", "a", "b");
                Assert.assertEquals(2, lookup.getRowCount());
                return null;
            }
        });
        db.runReadTransaction(new ISqlJetTransaction() {
            public Object run(SqlJetDb db) throws SqlJetException {
                final ISqlJetCursor lookup = table1.lookup("i1", "d", "e");
                Assert.assertEquals(2, lookup.getRowCount());
                return null;
            }
        });

        final ISqlJetTableDef t2 = db.createTable("create table t2(a,b,c);");
        db.createIndex("create index i2 on t2(a,b)");
        final ISqlJetTable table2 = db.getTable(t2.getName());
        table2.insert(1, 2, 3);
        table2.insert(4, 5, 6);
        table2.insert(1, 2, 3);
        table2.insert(4, 5, 6);
        db.runReadTransaction(new ISqlJetTransaction() {
            public Object run(SqlJetDb db) throws SqlJetException {
                final ISqlJetCursor lookup = table2.lookup("i2", 1, 2);
                Assert.assertEquals(2, lookup.getRowCount());
                return null;
            }
        });
        db.runReadTransaction(new ISqlJetTransaction() {
            public Object run(SqlJetDb db) throws SqlJetException {
                final ISqlJetCursor lookup = table2.lookup("i2", 4, 5);
                Assert.assertEquals(2, lookup.getRowCount());
                return null;
            }
        });
    }

}

</pre>

Original comment by sergey.s...@gmail.com on 15 Oct 2012 at 8:07

[GoogleCodeExporter]

Excuse me, I've reproduced the bug. You are right, it uses only first field in 
criteria and doesn't use second field. I've changed data in my unit test and it 
fails:

public class CompoundIndexesCriteriaTest {

    protected File file;
    protected SqlJetDb db;

    @Before
    public void setUp() throws Exception {
        file = File.createTempFile(this.getClass().getSimpleName(), null);
        file.deleteOnExit();
        db = SqlJetDb.open(file, true);
    }

    @After
    public void tearDown() throws Exception {
        if (db != null) {
            db.close();
        }
    }

    @Test
    public void testCompoundIndexesCriteriaABC() throws Exception {

        final ISqlJetTableDef t = db.createTable("create table t(a,b,c);");
        db.createIndex("create index i on t(a,b)");
        final ISqlJetTable table = db.getTable(t.getName());

        table.insert("a", "b", "c");
        table.insert("d", "e", "f");
        table.insert("a", "c", "b");
        table.insert("d", "f", "e");
        table.insert("a", "b", "c");
        table.insert("d", "e", "f");

        db.runReadTransaction(new ISqlJetTransaction() {
            public Object run(SqlJetDb db) throws SqlJetException {
                final ISqlJetCursor lookup = table.lookup("i", "a", "b");
                Assert.assertEquals(2, lookup.getRowCount());
                return null;
            }
        });

        db.runReadTransaction(new ISqlJetTransaction() {
            public Object run(SqlJetDb db) throws SqlJetException {
                final ISqlJetCursor lookup = table.lookup("i", "d", "e");
                Assert.assertEquals(2, lookup.getRowCount());
                return null;
            }
        });

    }

    @Test
    public void testCompoundIndexesCriteria123() throws Exception {
        final ISqlJetTableDef t = db.createTable("create table t(a,b,c);");
        db.createIndex("create index i on t(a,b)");
        final ISqlJetTable table = db.getTable(t.getName());
        table.insert(1, 2, 3);
        table.insert(4, 5, 6);
        table.insert(1, 3, 2);
        table.insert(4, 7, 7);
        table.insert(1, 2, 3);
        table.insert(4, 5, 6);
        db.runReadTransaction(new ISqlJetTransaction() {
            public Object run(SqlJetDb db) throws SqlJetException {
                final ISqlJetCursor lookup = table.lookup("i", 1, 2);
                Assert.assertEquals(2, lookup.getRowCount());
                return null;
            }
        });
        db.runReadTransaction(new ISqlJetTransaction() {
            public Object run(SqlJetDb db) throws SqlJetException {
                final ISqlJetCursor lookup = table.lookup("i", 4, 5);
                Assert.assertEquals(2, lookup.getRowCount());
                return null;
            }
        });
    }

}

Thanks you again. I'll fix it ASAP.

Original comment by sergey.s...@gmail.com on 16 Oct 2012 at 8:06

[GoogleCodeExporter]

Hi, we released new version 1.1.5 SQLJet where this bug has been fixed.

Please, use new version for verify your code which shows the bug with 
getRowCount().
If you will find that the bug appears in new version too then please write to 
us here. 
If you will see that bug has been fixed please also let us know so I could 
close the bug.

Thank you a lot.

Original comment by sergey.s...@gmail.com on 30 Oct 2012 at 9:44

[GoogleCodeExporter]

Original comment by sergey.s...@gmail.com on 18 Dec 2012 at 10:04

• Changed state: Fixed