SQLite Binary Literal Support: Parsing 0b01 Tokens as Integers

Understanding SQLite’s Numeric Literal Parsing and Binary Token Recognition

The core issue revolves around SQLite’s inability to parse binary literals (e.g., 0b01) as integers during query execution. While SQLite recognizes hexadecimal literals (e.g., 0x01), binary literals trigger an "unrecognized token" error. This limitation complicates direct bitwise operations using binary notation, forcing users to manually convert binary strings to integers. Below, we dissect the technical underpinnings of this behavior, explore why binary literals are unsupported, and provide actionable solutions for bridging this functionality gap.

SQLite’s Lexer Rules and Numeric Literal Handling

SQLite’s lexical analyzer (lexer) defines how input tokens are classified during parsing. Numeric literals follow specific rules:

1. Decimal integers: Sequences of digits without prefixes (e.g., 123).
2. Hexadecimal integers: Prefixed with 0x (e.g., 0x1A), parsed as base-16 values.
3. Floating-point numbers: Include a decimal point or exponent (e.g., 3.14, 1e5).

Binary literals are absent from these rules. When the lexer encounters 0b01, it interprets 0b as an invalid prefix, triggering a syntax error. This contrasts with languages like JavaScript or Python, where 0b prefixes denote binary integers. The confusion arises because users expect SQLite to treat 0b similarly to 0x, but the lexer lacks the necessary token classification for binary notation.

Why does SQLite support hexadecimal but not binary?
Hexadecimal literals were added for compatibility with common programming practices, particularly for bitmask operations and binary data representation. Binary literals, while useful, have not been prioritized in SQLite’s minimalist design philosophy. The parser’s tokenization logic is hardcoded and does not dynamically adapt to non-standard numeric representations.

Key Documentation References:

• SQLite Literal Values explicitly lists hexadecimal literals but omits binary.
• The SQLite Parser Code defines numeric token rules in parse.y, confirming the absence of binary literal handling.

Challenges in Extending SQLite’s Lexer for Binary Literals

Adding support for binary literals requires modifying SQLite’s lexer and parser, which involves:

1. Token Recognition: Updating the lexer to identify 0b prefixes and process subsequent binary digits.
2. Value Conversion: Implementing a function to convert binary strings (e.g., 0b1010) to 64-bit integers or IEEE 754 doubles.
3. Backward Compatibility: Ensuring new syntax does not conflict with existing queries or reserved keywords.

Why hasn’t this been implemented yet?

• Storage Agnosticism: SQLite stores integers as 64-bit signed values, not in their original base. Users can already input integers via hex or decimal, making binary support a convenience rather than a necessity.
• Parser Complexity: Extending the lexer increases code complexity and maintenance overhead.
• Use Case Specificity: Binary literals are niche compared to hex, which suffices for most bitwise operations.

Common Misconceptions:

• "Hexadecimal literals are stored as strings": Incorrect. 0x01 is parsed as an integer (e.g., 1), not a text blob.
• "SQLite supports octal literals": False. 0123 is interpreted as decimal 123, not octal 83.

Workarounds, Custom Functions, and Source Modifications

1. User-Defined Functions (UDFs) for Binary Conversion

Create a scalar function binary() to convert binary strings to integers:

SELECT binary('1010') AS bin_value; -- Returns 10

Implementation in C:

#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1

static void binaryFunc(
    sqlite3_context *context,
    int argc,
    sqlite3_value **argv
) {
    const char *bin_str = (const char*)sqlite3_value_text(argv[0]);
    int64_t result = 0;
    for (int i = 0; bin_str[i]; i++) {
        result <<= 1;
        result |= (bin_str[i] == '1') ? 1 : 0;
    }
    sqlite3_result_int64(context, result);
}

int sqlite3_binary_init(
    sqlite3 *db,
    char **pzErrMsg,
    const sqlite3_api_routines *pApi
) {
    SQLITE_EXTENSION_INIT2(pApi);
    sqlite3_create_function(db, "binary", 1, SQLITE_UTF8, NULL,
                            binaryFunc, NULL, NULL);
    return SQLITE_OK;
}

Usage:

SELECT binary('111001110001110') & binary('001001011001000');
-- Returns 4744, equivalent to 0b1001010001000

2. Using Built-in Functions and Arithmetic

For ad-hoc conversions without UDFs:

SELECT 
  (instr('00000000' || '1010', '1') - 1) * 8 
  + (instr(substr('00000000' || '1010', instr('00000000' || '1010', '1')), '0') - 1);

This cumbersome approach calculates the integer value by string manipulation but is impractical for long binaries.

3. Modifying SQLite’s Source Code

Step 1: Update the Lexer
In src/parse.y, modify the token rule to recognize 0b prefixes:

%token TK_INTEGER
...
// Existing hex rule:
// [xX] "'"* {hexdigit}+
// Add binary rule:
[Bb] "'"* {bindigit}+

%%

Step 2: Extend the Parser
In sqlite3ParseInteger() (found in src/util.c), add logic to handle binary:

if( z[0]=='0' && (z[1]=='b' || z[1]=='B') && z[2]!=0 ){
    u64 value = 0;
    int i;
    for(i=2; z[i]; i++){
        if( z[i]=='0' ){
            value = (value << 1);
        }else if( z[i]=='1' ){
            value = (value << 1) + 1;
        }else{
            break; // Invalid character
        }
    }
    *pValue = (i>2) ? value : 0;
    return i;
}

Step 3: Rebuild SQLite
Compile the modified source and test:

./configure
make
./sqlite3
sqlite> select 0b1010; -- Returns 10

Caveats:

• Custom builds are not portable and require redistribution under SQLite’s license.
• Binary literals will not be recognized by unmodified SQLite instances.

4. Preprocessing Queries

Use an external script to replace 0b... literals with decimal equivalents before executing queries:

import re
import sqlite3

def preprocess_query(query):
    return re.sub(r'0b([01]+)', lambda m: str(int(m.group(1), 2)), query)

conn = sqlite3.connect(':memory:')
query = preprocess_query('SELECT 0b1010, 0b1111;')  # Becomes 'SELECT 10, 15;'
conn.execute(query).fetchall()

Final Recommendations

1. Short-Term: Implement a UDF like binary() for projects requiring frequent binary conversions.
2. Long-Term: Submit a feature request to the SQLite team with a well-justified use case. Reference implementations (e.g., MySQL’s b'1010' syntax) to bolster the proposal.
3. Avoid relying on string-based bitwise operations, as they are inefficient and error-prone.

By understanding SQLite’s parsing mechanics and leveraging extensibility features, users can effectively bridge the gap until native binary literal support is adopted.