Inconsistent display of CAST(1 AS REAL) between SQLite3 Fiddle and SQLite JDBC

[wwk4j4cj6t-stack]

Describe the bug
During using we observed an inconsistency between the result presentation in SQLite3 Fiddle and SQLite JDBC when executing the following query:

SELECT CAST(1 AS REAL);

Observed Behavior

• SQLite3 Fiddle returns: 1.0
• SQLite JDBC returns: 1

This difference initially appears to be only a formatting issue. However, it can lead to confusion in downstream processing.

To Reproduce
Execute the following SQL expression:

SELECT length(CAST(1 AS REAL));

The result is: 3

This indicates that internally SQLite treats the value as the string representation "1.0" when computing length().
However, when retrieving the value through SQLite JDBC, the displayed result is 1, where the decimal part is omitted. This discrepancy between the displayed value (1) and the internal string representation ("1.0") can easily lead to misunderstandings during debugging and data processing.

Expected behavior
Consistent formatting or representation across SQLite3 Fiddle and SQLite JDBC when retrieving REAL values, so developers are not misled into assuming:

length(CAST(1 AS REAL)) = 1

based on the JDBC output.
Environment (please complete the following information):

• OS: Windows 24H2
• CPU architecture: x86_64
• sqlite-jdbc version 3.51.2

Additional context
Although this behavior may stem from client-side formatting decisions, the inconsistency between the displayed value in JDBC and the internal value used by SQLite functions may introduce subtle issues in applications that rely on string-based processing or debugging assumptions.

For example, developers might expect:

length(CAST(1 AS REAL)) = 1

based on the JDBC output (1), while SQLite actually evaluates it based on "1.0".

Suggestion
It may be helpful to clarify this behavior in the documentation or ensure consistent formatting across interfaces, especially when values are retrieved through JDBC

[gotson]

Can you provide context ? Is this coming from a real life use case ?

[gotson]

Can you provide a reproduction code sample ? From my tests it returns 1.0.

[LLMTesting-Ojiken]

I also reproduced this situation in Datagrip(using sqlite-jdbc), using version 3.51.1.

[gotson]

we would need reproducible java code, ideally as a unit test.

[wwk4j4cj6t-stack]

Can you provide context ? Is this coming from a real life use case ?

I am using the sqlite-jdbc 3.51.2 JAR downloaded from Maven. I am currently developing an Android app based on sqlite-jdbc, and the project is in a closed testing stage. We found this issue during testing.

To keep the report minimal and reproducible, I reduced it to:

SELECT CAST(1 AS REAL);
SELECT length(CAST(1 AS REAL));

These statements can be reproduced in IDEA. In our case, when the first statement is retrieved through JDBC, it is displayed as 1 rather than 1.0, while the second statement returns 3. We also tested this on multiple machines and observed the same behavior.

The main point I want to clarify is this: if JDBC displays CAST(1 AS REAL) as 1, then based on that displayed value, length(...) would be expected to correspond to 1 rather than 3. However, SQLite actually evaluates the length based on "1.0" and returns 3. In other words, there is an inconsistency between the displayed value and the internal representation used in computation.

[gotson]

You keep saying you are having the wrong behaviour, but you still haven't provided any java code that shows how you are getting that result.

ResultSet resultSet = stat.executeQuery("SELECT CAST(1 AS REAL), length(CAST(1 AS REAL))");
        resultSet.next();
        System.out.println(resultSet.getString(1));
        System.out.println(resultSet.getString(2));

This produces:

1.0
3

If you cannot provide any java code that highlights the behaviour you are experiencing, i will close this issue.