Integer comparision using DBD::SQLite

I'm facing a problem with DBIx::Class against an SQLite3 database.

If you don't want to read the following in full, here's the TL;DR version:

Is there a way to force DBD::SQLite to treat integer fields as being unsigned, when it comes to comparisons?

The table definition is here:

sqlite> PRAGMA table_info(entry);
0|entry_key|INTEGER|1||1
1|node|varchar(256)|1||0
2|object_type|varchar(128)|1||0
3|object_id|int|1||0
4|copy_id|tinyint|0||0
5|seq_number|int|1||0
6|root_seq_number|int|1||0
7|first_error|int|1||0
8|last_error|int|1||0
9|error_count|int|1||0
10|error_id|int|1||0
11|error_code|int|0||0
12|status|varchar(64)|1||0
13|type|varchar(64)|1||0
14|sense|char(256)|0||0

The fields of interest are first_error and last_error. These fields contain epoch time values. Thus, they are 32-bit numbers, but they are less than 2147483647

In my code, I have the following:

my @entries =  $self->{row}->search_related_rs('eventlog_entries')
                           ->search_related('entry', {
                             first_error => {'>', $range->{start}},
                             last_error  => {'<', $range->{end}},
                           }
           )->all();

start is set to 0; end is set to 2**32 - 1

When run with DBI_TRACE=1, I get:

<- prepare_cached('SELECT entry.entry_key, entry.node, entry.object_type,
                   entry.object_id, entry.copy_id, entry.seq_number,
                   entry.root_seq_number, entry.first_error, entry.last_error,
                   entry.error_count, entry.error_id, entry.error_code, entry.status,
                   entry.type, entry.sense FROM eventlog_entry me  JOIN entry entry ON
                   entry.entry_key = me.entry_key WHERE ( ( ( first_error > ? AND
                     last_error < ? ) AND me.eventlog_key = ? ) )', 
                  HASH(0x2472b54), ...)= ( DBI::st=HASH(0x2442efc) ) [1 items] 
   at DBI.pm line 2245
<- bind_param(1, 0, ...)= ( 1 ) [1 items] at DBI.pm line 1574
<- bind_param(2, '4294967295', ...)= ( 1 ) [1 items] at DBI.pm line 1574
<- bind_param(3, 1, ...)= ( 1 ) [1 items] at DBI.pm line 1574
<- execute= ( '0E0' ) [1 items] at DBI.pm line 1586
<- fetchall_arrayref= ( [ ] ) [1 items] row-1 at Cursor.pm line 133

In this case, @entries is an empty array.

On the other hand, if I set end is set to 2**31 - 1, everything works.

My hyopthesis is this:

SQLite fields have an 'affinity', which means that the fields are recognised as integers, but they have no native size. Thus, SQLite "guesses" what the size is, based on the contents of the field. Since the value in the last_error field is less than 2147483647, but greater than 16777215, I'm guessing that SQLite is treating the field as a SIGNED INTEGER (i.e. signed 32-bit number).

As such, my guess is that, when the bind_param takes place, some kind of inspection is done, which results in DBI identifying last_error as a SIGNED INTEGER. As a result, the value of 4294967295 overflows, or gets squished to zero, or something like that, and the comparison works incorrectly.

Thus, my question(s):

1. Is this hyp开发者_运维技巧othesis correct (according to some documentation that I've overlooked)? or
2. Is there a way to confirm this hypothesis?
3. Is this a bug, or is there a sensible workaround, bearing in mind that I'm using DBIx::Class, so I'm somewhat abstracted away from the database.

How about using literal sql in your query:

my $cond = " < $range->{end} ";

my @entries =  $self->{row}->search_related_rs('eventlog_entries')
                       ->search_related('entry', {
                         first_error => {'>', $range->{start}},
                         last_error  => \$cond,
                       }
       )->all();

Should work if perl stringifies $range->[end} as a positive number.