Faruk Akgul

Strange Behavior Of SQLite In Different OSes

April 15, 2011 | View Comments

When I was doing some SQLite I've noticed, accidentally, its SUBSTR() function behaves differently in different OSes.

Consider the following example;

import sqlite3
con = sqlite3.connect('test.db')
c = con.cursor()
r = c.execute('SELECT SUBSTR(post, 0, 30) FROM entries WHERE user=1 LIMIT 1').fetchone()

The above code returns 29 in OSX and Linux but not in Windows. Unlike OSX and Linux, it returns 30 in Windows. Some may say, it's not a bug since SUBSTR() expects the lowest start integer as 1 but this strange behavior creates confusion. If it didn't work in the first place when I was testing the code in OSX and Linux, I'd think I'm doing something wrong and fix the problem immediately but it has worked which led me to believe that my code was bug-free (in fact I created the bug).

For example, consider the following query in OSX and Linux;

SELECT SUBSTR(post, 0, 31)

The above query returns exactly the same value when you type the following query in Windows;

SELECT SUBSTR(post, 0, 30)

They're all buggy, the correct query should be;

SELECT SUBSTR(post, 1, 30)

This creates a confusion. They make you believe that you're writing the correct code whereas you're not. SUBSTR() functions of PostgreSQL and MySQL also start with 1 but when you code in programming languages, SUBSTR() starts with 0 so you might make such mistakes sometimes and this was my mistake but since it has worked in SQLite I thought I was doing right (but I wasn't).

Just curiosity, I've decided to look at the source code of SQLite to see if I can figure out what's causing this confusion. SUBSTR() function of SQLite is in sqlite.c and it starts on line 81727 (Yeah, I just grabbed the entire thing).

Line 81748; i64 p1, p2;

These two values define the [start, end] part of the SUBSTR function. They're in i64.

Line 81758; p1 = sqlite3_value_int(argv[1]);

This defines the [start] part of SUBSTR.

Line 81814; sqlite3_result_text(context, (char*)z, (int)(z2-z), SQLITE_TRANSIENT); (int) (z2-z) defines the total length of the substring.

After the line 81758 I've written if(p1 == 0) p1 = 1;. I know it's stupid but it does the trick. When you compile it, SUBSTR starts to behave in the same way.

The reason I've written specifically if(p1 == 0) is, according to comment on line 81735, p1 is calculated as abs(p1) so if(p1 < 1) might break it.

After I've shared this with someone else, he sent me http://www.sqlite.org/cgi/src/fdiff?v1=e536218d193b8d32&v2=1ddc1c93a0bb3509.

Apparently, I've just reinvented the wheel. My ignorance made me waste quite amount of time for a rookie mistake.


blog comments powered by Disqus