`square() silently wraps on 64-bit integer overflow (extension-functions.c)`

Open Beginner friendly
#1,512 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
72/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
c, java
Domain
database

Research direction

Start at squareFunc and its registration entry { "square", 1, 0, ... } in src/main/ext/extension-functions.c; the integer branch does iVal*iVal and returns int64 unchecked, while the assert(argc==2) contradicts the nArg=1 registration. Decide whether to promote to double (mirroring the SQL * overflow behaviour) or raise SQLITE_ERROR "integer overflow", fix the assert, then rebuild the native library and run the JDBC repro in the issue; done means all three overflow inputs return a REAL or an error and no wrapped/negative value. Check the existing test layout under src/test for where to add a regression case.

Written by the indexing model from the issue text.

Description

triage

Describe the bug

square(X) returns a mathematically wrong value when X is a 64-bit INTEGER whose square overflows int64. The integer branch in src/main/ext/extension-functions.c multiplies with raw int64 arithmetic and returns the wrapped result as int64, instead of promoting to floating point or raising an error. Since these contrib functions are compiled into the bundled native library and registered on every connection (pragma_function_list reports builtin=0 for square), plain JDBC users hit it with no load_extension needed:

  • square(9223372036854775807) returns 1 ((2^63-1)^2 mod 2^64)
  • square(-9223372036854775808) returns 0
  • square(-3037000500) returns a negative number -9223372036709301616

For the same input, SQLite's own * operator returns 9.22337203700025E18, because lang_expr.html states arithmetic uses "floating point arithmetic, per IEEE Standard 754, ... if integer arithmetic would produce an overflow". square() bypasses that rule.

To Reproduce

import java.sql.*;

public class Repro {
  public static void main(String[] args) throws Exception {
    try (Connection c = DriverManager.getConnection("jdbc:sqlite::memory:");
         Statement s = c.createStatement()) {
      String[] q = {
        "SELECT square(9223372036854775807)",
        "SELECT square(-9223372036854775808)",
        "SELECT square(-3037000500)",
        "SELECT -3037000500 * -3037000500"
      };
      for (String x : q) {
        try (ResultSet r = s.executeQuery(x)) {
          r.next();
          System.out.println(x + " => " + r.getObject(1));
        }
      }
    }
  }
}

Expected behavior

square(X) should return the square of X, i.e. for integer input either 9.22337203700025e18 (promoted to REAL, the way SQL's * behaves on overflow) or an error such as integer overflow — never a wrapped value, and never a negative result for -3037000500.

Reference point: SQL Server defines the same function as SQUARE(float_expression) RETURNS float, so no integer wraparound occurs there.

Logs

SELECT square(9223372036854775807)   => 1
SELECT square(-9223372036854775808)  => 0
SELECT square(-3037000500)           => -9223372036709301616
SELECT -3037000500 * -3037000500     => 9.22337203700025E18

Environment (please complete the following information):

  • OS: macOS 15.6.1
  • CPU architecture: arm64
  • sqlite-jdbc version: 3.54.1.0

Additional context

Root cause in src/main/ext/extension-functions.c:

static void squareFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
  i64 iVal = 0;
  double rVal = 0.0;
  assert( argc==2 );                     /* registered with nArg = 1 below */
  switch( sqlite3_value_type(argv[0]) ){
  case SQLITE_INTEGER: {
    iVal = sqlite3_value_int64(argv[0]);
    sqlite3_result_int64(context, iVal*iVal);   /* no overflow check */
    break;
  }
  ...

Two defects:

  1. iVal*iVal is unchecked and the result is returned as int64, so overflow silently wraps.
  2. assert(argc==2) contradicts the registration { "square", 1, 0, SQLITE_UTF8, 0, squareFunc }; a debug build would abort on the first call, which suggests the function is untested.

Suggested fix: if the int64 product would overflow, either compute in double (as the non-integer branch already does) or raise SQLITE_ERROR "integer overflow", mirroring how abs() handles INT64_MIN. Also correct the assertion to assert(argc==1).

Note: square is not the only function registered from this file. ATN2 CHARINDEX COT COTH DIFFERENCE LEFTSTR PADC PADL PADR PROPER REPLICATE REVERSE RIGHTSTR STDEV VARIANCE come from the same source and appear to share the same unchecked-arithmetic pattern; they deserve the same review.

Found by PST while testing built-in functions locally.

Dominant language
Java
Stars
3.3k
Forks
676
Avg merge
1d 15h
Merged PRs (30d)
18

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from xerial/sqlite-jdbc

All issues in xerial/sqlite-jdbc

Similar issues

More Java issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.