[sqlite] .filter() using .and() causes empty set, but multiple .filter() work as expected

[jjpe]

Setup

Versions

• Rust: rustc 1.25.0-nightly (51b0b3734 2018-01-12)
• Diesel: 1.1.0
• Database: sqlite
• Operating System Fedora Linux 27

Feature Flags

• diesel: sqlite
• diesel_codegen: not used at all

Problem Description

Consider this query:

fn query_ranged_kinds(&self, range: Range<u64>)
                          -> Result<Vec<String>, DieselError> {
        #[allow(unused_imports)] use database_schema::msgs::*;
        #[allow(unused_imports)] use database_schema::msgs::dsl::*;
        let (start, end) = (range.start as i64, range.start as i64);
        self.transaction::<_, DieselError, _>(|| {
            msgs.select(kind)
                .distinct()
                .filter(revision.ge(start).and(revision.lt(end)))  // offending line
                .load::<String>(self)
        })
    }

The above code results in an empty result set, whereas this one does not:

    fn query_ranged_kinds(&self, range: Range<u64>)
                          -> Result<Vec<String>, DieselError> {
        #[allow(unused_imports)] use database_schema::msgs::*; // column names
        #[allow(unused_imports)] use database_schema::msgs::dsl::*;
        // let (start, end) = (range.start as i64, range.start as i64);
        self.transaction::<_, DieselError, _>(|| {
            msgs.select(kind)
                .distinct()
                .filter(revision.ge(range.start as i64))  // multiple calls to filter ...
                .filter(revision.lt(range.end as i64))     // ... work fine apparently
                .load::<String>(self)
        })
    }

What are you trying to accomplish?

Either report this disparity as a bug and have it fixed; Or, if there is a good reason for the status quo, it would be good to document that somewhere easily discoverable (e.g. the diesel website).

What is the expected output?

First off, both examples compile fine i.e. no warnings or errors.

What I expected to happen in both cases is the equivalent of

select kind from msgs where revision >= start and revision < end

What is the actual output?

Example 1 always results in an empty set for me, and example 2 produced the expected output.

Are you seeing any additional errors?

None.

Steps to reproduce

I don't see any connections to e.g. the actual schema, so this should be easy to test on any diesel/sqlite database.

Checklist

• I have already looked over the issue tracker for similar issues.

[sgrif]

In example one you have: let (start, end) = (range.start as i64, range.start as i64);

You meant range.end. You can use debug_query to show the generated SQL, which is identical other than the values of your bind parameters.

[jjpe]

I completely missed that, you're absolutely right.
One too many late night coding sessions :)