For query:

CREATE TABLE v0 ( v1 INTEGER);
INSERT INTO v0 VALUES ( 10 ),  ( 7 );
CREATE TABLE v4 ( v5 INTEGER PRIMARY KEY );
INSERT INTO v4 VALUES ( 10 );

/* First SELECT stmt */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = v1 )) as BOOL) != 0   FROM  v0 ;
/* Output: 1 */

/* Second SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = v1 )) as BOOL) != 0)   FROM  v0 ;
/* Output: 2.0 */

/* Third SELECT stmt, replace v1 with 10, matching the first component in v1. */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 10 )) as BOOL) != 0   FROM  v0 ; 
/* Output: 1 1 */

/* Fourth SELECT stmt, as validation for the third SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 10 )) as BOOL) != 0)   FROM  v0 ;
/* Output: 2.0 */

/* Fifth SELECT stmt, replace v1 with 7, matching the second component in v1. */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 7 )) as BOOL) != 0   FROM  v0 ;
/* Output EMPTY */

/* Sixth SELECT stmt, as validation for the fifth SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 7 )) as BOOL) != 0)   FROM  v0 ;
/* Output 0.0 */

The first and the second SELECT stmts contains a correlated subquery in the select clause. We expect the subquery would be evaluated each time when iterating through column v1. These steps are being shown in the third and the fifth SELECT stmts.

However, the second SELECT stmt result is mismatched with the result of the first SELECT stmt. Because they have a similar correlated subquery inside the select clause, we expect the second result to be 1.0, rather than 2.0 in the reality. We wonder whether it is a mistake in the code or an expected behavior.

This inconsistent behavior from the second SELECT stmt, seems to be introduced in Fossil: 9f90a88221d0694951c353e58efce342eb0b868b8ca6a4469c8205e5c7855b24.

Looking forward to your reply.