SQLite Forum

Timeline
Login

50 most recent forum posts by user rmariani

2021-10-06
05:23 Post: doc nit for sign() (artifact: 2a370a48e6 user: rmariani)

sign(X)

The sign(X) function returns -1, 0, or +1 if the argument X is a numeric value that is negative, zero, or positive, respectively. If the argument to sign(X) is NULL or is a string or blob that cannot be losslessly converted into a number, then sign(X) return NULL.

I think it should be "returns" NULL.

2021-08-31
00:18 Post: FYI: CG/SQL test coverage of SQLite (artifact: 01adb5a7a1 user: rmariani)
File            Line Coverage           Branch Coverage
sqlite3-1.c	50.2 %	1282 / 2554	37.3 %	 597 / 1600
sqlite3-2.c	23.9 %	1132 / 4743	15.2 %	 472 / 3108
sqlite3-3.c	42.4 %	4122 / 9717	30.0 %	1803 / 6016
sqlite3-4.c	31.9 %	3037 / 9533	23.9 %	1600 / 6703
sqlite3-5.c	34.4 %	3253 / 9447	25.8 %	1617 / 6264
sqlite3-6.c	30.8 %	 384 / 1245	21.4 %	 152 / 711

The tests for the CQL compiler are by no means intended to cover all of SQLite but as it happens they cover quite a bit incidentally.

If there is any interest in snapshotting a version of this I'd be happy to provide something standalone.

This suite takes only a few seconds to run and not much longer to build

2021-08-11
22:56 Reply: LEFT JOIN Misunderstanding (artifact: ba7571f4f5 user: rmariani)

Here's concrete suggestion (you don't have to go with this one of course).

This is as similar to what you have as possible with a little extra indenting to reveal structure.

WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
  SELECT projid,
         max(InsertDate),
         (SELECT max(insertdate) FROM project_extras where projid = e.projid)
    FROM project_list e
    GROUP BY projid
 )
SELECT count(c.CreatedDate) AS Aug, a.Project_Type , a.ProjID
FROM Project_List AS a
  LEFT JOIN Project_Extras AS b
    ON a.ProjID == b.ProjID
    AND
       b.FinCarryOver != 'y'
    AND
       b.MonthlyRpt = 'y'
    AND
       b.BudgetYear = '2021'
  LEFT JOIN Project_Highlights AS c
    ON b.ProjID = c.ProjID
    AND 
       c.CreatedDate LIKE '2021-08%' 
  LEFT JOIN LastEntries AS f
WHERE
    a.Project_Delivered != 'Yes'
  AND
    a.PMO_Board_Report != 'No'
  AND
    a.Status == 'Acknowledged'
  AND
    a.InsertDate = f.pl_insert
  AND
    b.InsertDate = f.pe_insert
  AND
    a.ProjID = f.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID
;
22:51 Reply: LEFT JOIN Misunderstanding (artifact: cc9ff889eb user: rmariani)

If you want to filter 'a' using 'f' then by all means keep it in the WHERE.

22:50 Reply: LEFT JOIN Misunderstanding (artifact: c6e7c72cf8 user: rmariani)

There's lots of choices. If you look at what I did you can see why I might do that. You can do something else you like that highlights which conditions go with which. I'm pretty good at reading this stuff and I found it very hard to follow yours. Adopt what you like but you have room to take advantage of more indenting. You could keep the each ON clause separate but indented. You could break at each AND. There's lots of good choices here but you need to be able to see which predicate goes with which clause at a glance, that helps a lot.

13:22 Reply: inner join bug with compound queries (artifact: aa511a9b6c user: rmariani)

Awesome. This explains why its so touchy. And maybe why it doesn't happen if the joins are LEFT.

06:06 Reply: LEFT JOIN Misunderstanding (artifact: d5464acabb user: rmariani)

:D

06:02 Reply: LEFT JOIN Misunderstanding (artifact: 9ae4a61c7d user: rmariani)

BTW good indenting reveals the structure of the query. Bad indenting makes it harder to see what goes with what. I would say the original version is bad. Even if you don't like my version I'm sure you can do better. But I wouldn't have spotted the fact that there is no ON for the LEFT on F in your version.

06:01 Reply: LEFT JOIN Misunderstanding (artifact: 7b488e8568 user: rmariani)
WITH
LastEntries (ProjID, pl_insert, pe_insert) AS (
 SELECT projid, max(InsertDate), ( SELECT max(insertdate) FROM project_extras WHERE projid = e.projid )
  FROM project_list AS e
  GROUP BY projid)

SELECT count(d.CreatedDate) AS Jul, count(c.CreatedDate) AS Aug, a.Project_Type, a.ProjID
  FROM Project_List AS a
  LEFT JOIN Project_Extras AS b ON a.ProjID = b.ProjID AND b.FinCarryOver <> 'y' AND b.MonthlyRpt = 'y' AND b.BudgetYear = '2021'
  LEFT JOIN Project_Highlights AS c ON b.ProjID = c.ProjID AND c.CreatedDate LIKE '2021-08%'
  LEFT JOIN Project_Highlights AS d ON b.ProjID = d.ProjID AND d.CreatedDate LIKE '2021-07%'
  LEFT JOIN LastEntries AS f
  WHERE a.Project_Delivered <> 'Yes' AND 
        a.PMO_Board_Report <> 'No' AND 
        a.Status = 'Acknowledged' AND 
        a.InsertDate = f.pl_insert AND 
        b.InsertDate = f.pe_insert AND 
        a.ProjID = f.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID;

You have

SELECT count(d.CreatedDate) AS Jul, count(c.CreatedDate) AS Aug, a.Project_Type, a.ProjID

But

GROUP BY a.ProjID

That's going to give you weird results. The select line should really be only group by columns or aggregates. Maybe add

GROUP BY a.ProjID, a.Project_Type

And as before

        a.InsertDate = f.pl_insert AND 
        b.InsertDate = f.pe_insert AND 
        a.ProjID = f.projid

is killing the LEFT in LEFT JOIN LastEntries AS f

Those probably need to move into an ON clause for f unless you intended that to be an inner join.

03:50 Reply: inner join bug with compound queries (artifact: 0e71035413 user: rmariani)

Here are the query plans:

SQLite 3.35.5 2021-04-19 18:32:05 1b256d97b553a9611efca188a3d995a2fff712759044ba480f9a0c9e98fae886
clang-12.0.5
QUERY PLAN
`--COMPOUND QUERY
   |--LEFT-MOST SUBQUERY
   |  |--MATERIALIZE 1
   |  |  `--SCAN CONSTANT ROW
   |  |--MATERIALIZE 2
   |  |  `--SCAN CONSTANT ROW
   |  |--SCAN SUBQUERY 1
   |  |--SCAN SUBQUERY 2
   |  `--SCAN SUBQUERY 2
   `--UNION ALL
      |--SCAN SUBQUERY 1
      |--SCAN SUBQUERY 2
      `--SCAN SUBQUERY 2
done
SQLite 3.32.3 2020-06-18 14:16:19 02c344aceaea0d177dd42e62c8541e3cab4a26c757ba33b3a31a43ccc7d4aapl
clang-12.0.5
QUERY PLAN
|--MATERIALIZE 1
|  `--SCAN CONSTANT ROW
|--MATERIALIZE 2
|  `--SCAN CONSTANT ROW
|--MATERIALIZE 4
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  |--CO-ROUTINE 2
|     |  |  `--SCAN CONSTANT ROW
|     |  `--SCAN SUBQUERY 2
|     `--UNION ALL
|        |--CO-ROUTINE 2
|        |  `--SCAN CONSTANT ROW
|        `--SCAN SUBQUERY 2
|--SCAN SUBQUERY 1
|--SCAN SUBQUERY 2
`--SCAN SUBQUERY 4
done

Looks like the compound query is in the wrong place.

03:45 Edit reply: inner join bug with compound queries (artifact: abc69220f5 user: rmariani)

Further notes in case they are helpful

WITH
T1 (x) AS (SELECT 10),
T2 (y) AS (SELECT 1),
T3 (z) AS (
  SELECT * FROM T2 WHERE false
  UNION ALL
  SELECT * FROM T2
)
SELECT *
  FROM T1, T2, T3;

Also fails the same way. But if you use LEFT JOIN instead of INNER JOIN it works.

If you remove T1 from the equation or use T2 two times it works.

If you remove the useless UNION ALL it works (in the real query it's not useless)

By "it works" I mean "it returns one row as expected".

03:44 Reply: inner join bug with compound queries (artifact: 7dfec50287 user: rmariani)

Further notes in case they are helpful

WITH
T1 (x) AS (SELECT 10),
T2 (y) AS (SELECT 1),
T3 (z) AS (
  SELECT * FROM T2 WHERE false
  UNION ALL
  SELECT * FROM T2
)
SELECT *
  FROM T1, T2, T3;

Also fails the same way. But if you use LEFT JOIN instead of INNER JOIN it works.

If you remove T1 from the equation or use T2 two times it works.

If you remove the useless UNION ALL it works (in the real query it's not uselss)

03:37 Post: inner join bug with compound queries (artifact: d496c3d29b user: rmariani)

This query gives the wrong answer on 3.35.x and I think 3.36. It's quite resistant to further reduction.

.version
WITH
T1 (x) AS (SELECT 10),
T2 (y) AS (SELECT 1),
T3 (z) AS (
  SELECT * FROM T2 WHERE 0
  UNION ALL
  SELECT * FROM T2
)
SELECT *
  FROM T1
  INNER JOIN T2
  INNER JOIN T3;
SELECT 'done'

It should return a single row.

SQLite 3.32.3 2020-06-18 14:16:19 02c344aceaea0d177dd42e62c8541e3cab4a26c757ba33b3a31a43ccc7d4aapl
clang-12.0.5
10|1|1
done

However 3.35.x returns no rows.

SQLite 3.35.5 2021-04-19 18:32:05 1b256d97b553a9611efca188a3d995a2fff712759044ba480f9a0c9e98fae886
clang-12.0.5
done

It might be further reducible but it is touchy. Even some of that goofy looking stuff seems to be necessary (it's less goofy in the original context).

One of the guys here tried 3.36 and it also had the same issue.

03:30 Reply: LEFT JOIN Misunderstanding (artifact: f42128dac9 user: rmariani)

I didn't look at this in detail but I noticed this:

AND
    a.InsertDate = f.pl_insert
AND
    b.InsertDate = f.pe_insert
AND
    a.ProjID = f.projid

As long as you mention those in the WHERE clause it won't be a real left join. All constraints on F must be in the ON clause.

Now if you really want to filter away rows in A using those booleans that's fine. That's legal and often reasonable but at that point it isn't really a LEFT JOIN anymore. You're effectively inner joining from A to F over projid.

2021-08-06
22:35 Reply: Select and count from 2 tables (artifact: be2883f1cc user: rmariani)
create table genremap(movie_id integer, name text);

insert into genremap
  values(1, 'Comedy'),
  (2, 'Romance'),
  (3, 'Romance'),
  (3, 'Comedy'),
  (4, 'Romance'),
  (4, 'Comedy');

select count(*) from genremap T1
where T1.name = 'Romance' and
  exists(
    select 1
    from genremap T2
    where T1.movie_id = T2.movie_id AND T2.name = 'Comedy'
  );

result:  2
2021-08-04
22:59 Reply: LEFT JOIN Misunderstanding (artifact: 284d280de9 user: rmariani)

By useless he means you don't get anything new from it. WHERE is the same.

In fairness, the *= syntax was easy to get wrong and it was less flexible than the ON form where the condition can be an arbitrary predicate and not just equality.

I came along just as the ANSI form was gaining favor and I was glad of it. :D

21:07 Reply: LEFT JOIN Misunderstanding (artifact: 1b5ea62a88 user: rmariani)

I don't understand. That's grammar. ANSI must say somewhere what INNER JOIN + ON means or what LEFT OUTER JOIN + ON means and how it relates to where but the grammar is silent on this. It's just grammar.

20:12 Edit reply: LEFT JOIN Misunderstanding (artifact: 181fdc1be0 user: rmariani)

An example is easier than trying to explain it further.

create table T1 (id integer);
create table T2 (id integer, t text);

insert into T1 values (1), (2), (3);

insert into T2 values (2, 'hi'), (3, 'bye');

select '----';
select 'all T1';
select * from T1;

select '----';
select 'T1 inner T2: this is a cross join because no ON clause';
select * from T1 inner join T2;

select '----';
select 'T1 left T2: this is also a cross join because no ON clause';
select * from T1 left join T2;

select '----';
select 'T1 inner T2:  regular inner join, no surprises';
select * from T1 inner join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join, no surprises';
select * from T1 left join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join with where filtering, removes rows from T1';
select * from T1 left join T2 on T1.id = T2.id where T2.t = 'hi';

select '----';
select 'T1 left T2:  regular left join with on filtering, does not remove rows from T1';
select * from T1 left join T2 on T1.id = T2.id and T2.t = 'hi';

Results:

----
all T1
1
2
3
----
T1 inner T2: this is a cross join because no ON clause
1|2|hi
1|3|bye
2|2|hi
2|3|bye
3|2|hi
3|3|bye
----
T1 left T2: this is also a cross join because no ON clause
1|2|hi
1|3|bye
2|2|hi
2|3|bye
3|2|hi
3|3|bye
----
T1 inner T2:  regular inner join, no surprises
2|2|hi
3|3|bye
----
T1 left T2:  regular left join, no surprises
1||
2|2|hi
3|3|bye
----
T1 left T2:  regular left join with where filtering, removes rows from T1
2|2|hi
----
T1 left T2:  regular left join with on filtering, does not remove rows from T1
1||
2|2|hi
3||

The second last query still removes T1 rows, so if you want only conditional columns from the left join all the tests have to be in the ON clause. Note that the last two queries are both reasonable/valid but they are different.

In this simple example the predicate in the where clause of the second last query basically converted the left join to inner. But you could imagine a more subtle predicate that would only remove some of the rows leaving some of the left-ness intact. Basically the WHERE happens after the joins. The ON defines the join criteria. The QP sometimes moves things around if it is safe to do so.

20:07 Reply: LEFT JOIN Misunderstanding (artifact: 41d7da52f6 user: rmariani)

I definitely see a lot of "sql incantations" but that often means the incantor doesn't know what's actually going on. This seems like a good place to help fix that.

20:02 Edit reply: LEFT JOIN Misunderstanding (artifact: 7ad48d4a11 user: rmariani)

An example is easier than trying to explain it further.

create table T1 (id integer);
create table T2 (id integer, t text);

insert into T1 values (1), (2), (3);

insert into T2 values (2, 'hi'), (3, 'bye');

select '----';
select 'all T1';
select * from T1;

select '----';
select 'T1 inner T2: this is a cross join because no ON clause';
select * from T1 inner join T2;

select '----';
select 'T1 left T2: this is also a cross join because no ON clause';
select * from T1 left join T2;

select '----';
select 'T1 inner T2:  regular inner join, no surprises';
select * from T1 inner join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join, no surprises';
select * from T1 left join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join with where filtering, removes rows from T1';
select * from T1 left join T2 on T1.id = T2.id where T2.t = 'hi';

select '----';
select 'T1 left T2:  regular left join with on filtering, does not remove rows from T1';
select * from T1 left join T2 on T1.id = T2.id and T2.t = 'hi';

Results:

----
all T1
1
2
3
----
T1 inner T2: this is a cross join because no ON clause
1|2|hi
1|3|bye
2|2|hi
2|3|bye
3|2|hi
3|3|bye
----
T1 left T2: this is also a cross join because no ON clause
1|2|hi
1|3|bye
2|2|hi
2|3|bye
3|2|hi
3|3|bye
----
T1 inner T2:  regular inner join, no surprises
2|2|hi
3|3|bye
----
T1 left T2:  regular left join, no surprises
1||
2|2|hi
3|3|bye
----
T1 left T2:  regular left join with where filtering, removes rows from T1
2|2|hi
----
T1 left T2:  regular left join with on filtering, does not remove rows from T1
1||
2|2|hi
3||

The second last query still removes T1 rows, so if you want only conditional rows from the left join all the tests have to be in the ON clause. Note that the last two queries are both reasonable/valid but they are different.

In this simple example the predicate in the where clause of the second last query basically converted the left join to inner. But you could imagine a more subtle predicate that would only remove some of the rows leaving some of the left-ness intact. Basically the WHERE happens after the joins. The ON defines the join criteria. The QP sometimes moves things around if it is safe to do so.

20:00 Edit reply: LEFT JOIN Misunderstanding (artifact: 27cd31d824 user: rmariani)

An example is easier than trying to explain it further.

create table T1 (id integer);
create table T2 (id integer, t text);

insert into T1 values (1), (2), (3);

insert into T2 values (2, 'hi'), (3, 'bye');

select '----';
select 'all T1';
select * from T1;

select '----';
select 'T1 inner T2: this is a cross join because no ON clause';
select * from T1 inner join T2;

select '----';
select 'T1 left T2: this is also a cross join because no ON clause';
select * from T1 left join T2;

select '----';
select 'T1 inner T2:  regular inner join, no surprises';
select * from T1 inner join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join, no surprises';
select * from T1 left join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join with where filtering, removes rows from T1';
select * from T1 left join T2 on T1.id = T2.id where T2.t = 'hi';

select '----';
select 'T1 left T2:  regular left join with on filtering, does not remove rows from T1';
select * from T1 left join T2 on T1.id = T2.id and T2.t = 'hi';

Results:

----
all T1
1
2
3
----
T1 inner T2: this is a cross join because no ON clause
1|2|hi
1|3|bye
2|2|hi
2|3|bye
3|2|hi
3|3|bye
----
T1 left T2: this is also a cross join because no ON clause
1|2|hi
1|3|bye
2|2|hi
2|3|bye
3|2|hi
3|3|bye
----
T1 inner T2:  regular inner join, no surprises
2|2|hi
3|3|bye
----
T1 left T2:  regular left join, no surprises
1||
2|2|hi
3|3|bye
----
T1 left T2:  regular left join with where filtering, removes rows from T1
2|2|hi
----
T1 left T2:  regular left join with on filtering, does not remove rows from T1
1||
2|2|hi
3||

The second last query still removes T1 rows, so if you want only conditional rows from the left join all the tests has to be in the ON clause. Note that the last two queries are both reasonable/valid but they are different. In this simple example the predicate in the where clause of the second last query basically converted the left join to inner. But you could imagine a more subtle predicate that would only remove some of the rows leaving some of the left-ness intact. Basically the WHERE happens after the joins. The ON defines the join criteria. The QP sometimes moves things around if it is safe to do so.

19:54 Reply: LEFT JOIN Misunderstanding (artifact: 2476c1c718 user: rmariani)

What he said :D

19:53 Edit reply: LEFT JOIN Misunderstanding (artifact: eee08ae5e3 user: rmariani)

An example is easier than trying to explain it further.

create table T1 (id integer);
create table T2 (id integer, t text);

insert into T1 values (1), (2), (3);

insert into T2 values (2, 'hi'), (3, 'bye');

select '----';
select 'all T1';
select * from T1;

select '----';
select 'T1 inner T2: this is a cross join because no ON clause';
select * from T1 inner join T2;

select '----';
select 'T1 left T2: this is also a cross join because no ON clause';
select * from T1 left join T2;

select '----';
select 'T1 inner T2:  regular inner join, no surprises';
select * from T1 inner join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join, no surprises';
select * from T1 left join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join with where filtering, removes rows from T1';
select * from T1 left join T2 on T1.id = T2.id where T2.t = 'hi';

select '----';
select 'T1 left T2:  regular left join with on filtering, does not remove rows from T1';
select * from T1 left join T2 on T1.id = T2.id and T2.t = 'hi';

Results:

----
all T1
1
2
3
----
T1 inner T2: this is a cross join because no ON clause
1|2|hi
1|3|bye
2|2|hi
2|3|bye
3|2|hi
3|3|bye
----
T1 left T2: this is also a cross join because no ON clause
1|2|hi
1|3|bye
2|2|hi
2|3|bye
3|2|hi
3|3|bye
----
T1 inner T2:  regular inner join, no surprises
2|2|hi
3|3|bye
----
T1 left T2:  regular left join, no surprises
1||
2|2|hi
3|3|bye
----
T1 left T2:  regular left join with where filtering, removes rows from T1
2|2|hi
----
T1 left T2:  regular left join with on filtering, does not remove rows from T1
1||
2|2|hi
3||

The second last query still removes T1 rows, so if you want only conditional rows from the left join all the tests has to be in the ON clause. Note that the last two queries are both reasonable/valid but they are different.

19:51 Reply: LEFT JOIN Misunderstanding (artifact: 6be385f65a user: rmariani)

An example is easier than trying to explain it further.

create table T1 (id integer);
create table T2 (id integer, t text);

insert into T1 values (1), (2), (3);

insert into T2 values (2, 'hi'), (3, 'bye');


select '----';
select 'all T1';
select * from T1;

select '----';
select 'T1 inner T2: this is a cross join because no ON clause';
select * from T1 inner join T2;

select '----';
select 'T1 left T2: this is also a cross join because no ON clause';
select * from T1 left join T2;

select '----';
select 'T1 inner T2:  regular inner join, no surprises';
select * from T1 inner join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join, no surprises';
select * from T1 left join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join with where filtering, removes rows from T1';
select * from T1 left join T2 on T1.id = T2.id where T2.t = 'hi';

select '----';
select 'T1 left T2:  regular left join with on filtering, does not remove rows from T1';
select * from T1 left join T2 on T1.id = T2.id and T2.t = 'hi';

Results:

```

all T1 1 2

3

T1 inner T2: this is a cross join because no ON clause 1|2|hi 1|3|bye 2|2|hi 2|3|bye 3|2|hi

3|3|bye

T1 left T2: this is also a cross join because no ON clause 1|2|hi 1|3|bye 2|2|hi 2|3|bye 3|2|hi

3|3|bye

T1 inner T2: regular inner join, no surprises 2|2|hi

3|3|bye

T1 left T2: regular left join, no surprises 1|| 2|2|hi

3|3|bye

T1 left T2: regular left join with where filtering, removes rows from T1

2|2|hi

T1 left T2: regular left join with on filtering, does not remove rows from T1 1|| 2|2|hi 3|| ```

The second last query still removes T1 rows, so if you want only conditional rows from the left join all the tests has to be in the ON clause. Note that the last two queries are both reasonable/valid but they are different.

2021-08-03
01:43 Reply: LEFT JOIN Misunderstanding (artifact: 268f5eea6d user: rmariani)

The extra constraints in the WHERE will affect rows returned by this query. That may be ok for you but it makes the joins not purely left.

2021-08-01
21:48 Edit reply: Feature Request: Allow REAL to serve as rowid by having the B-Tree interpret its byte signature as a 64bit INTEGER. (artifact: e485fd397e user: rmariani)

Yeah this is just weird. I mean, if you don't need it to be the ROWID then of course just make a REAL pk. But if you do need it to be a rowid, then you must want the autoinc feature or else what's the point. And if that's the case you certainly don't want to +1 the raw bits of a real because that's not going to give you anything like +1.

If we had a feature where you could +whatever for the rowid you could pick the magic value that increments the mantissa by one and then assuming you could start at some base value you could arrange for some range of reals but now this is even weirder. You'll have to do weird memory aliasing to get the real back when you pull it out.

It's far easier to just say "the integer is fixed point" then you do real_value = rowid/scale + offset when you pull it out and it all works fine. This is much less weird than the memory stuff you'd have to do to store the real as an int.

21:46 Reply: Feature Request: Allow REAL to serve as rowid by having the B-Tree interpret its byte signature as a 64bit INTEGER. (artifact: 2b54e6026b user: rmariani)

Yeah this is just weird. I mean, if you don't need it to be the ROWID then of course just make a REAL pk. But if you do need it to be a rowid, then you must want the autoinc feature or else what's the point. And if that's the case you certainly don't want to +1 the raw bits of a real because that's not going to give you anything like +1.

If we had a feature where you could +whatever for the rowid you could pick the magic value that increments the mantissa by one and then assuming you could start at some base value you could arrange for some range of reals but now this is even weirder. You'll have to do weird memory aliasing to get the real back when you pull it out.

It's far easier to just say "the integer is fixed point" then you do

real value = rowid/scale + offset when you pull it out and it all works fine. This is much less weird than the memory stuff you'd have to do to store the real as an int.

2021-07-31
22:50 Reply: Round function returning -0.0 (artifact: d582ee88bf user: rmariani)

FWIW, fresh download of the amalgam and clean build with MSVC.

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .parameter init
sqlite> .parameter set @n -0.0
sqlite> select round(@n, 1);
0.0
sqlite> .parameter set @n -.049
sqlite> select round(@n, 1);
0.0

sqlite> .version
SQLite 3.36.0 2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafa66e5
msvc-1928
sqlite>

No repro.

19:57 Reply: Round function returning -0.0 (artifact: 5194a96d9d user: rmariani)

I care about this because I ship to a lot of devices.

Now if I knew which library it was I could try some other things so as to be well informed. This particular behavior is indeed innocuous, but one finds problems by following up on smoke to see if there's fire. If there was an issue with the MSC floating point library I have friends there, I could see it fixed.

I can respect that you think this is a waste of time.

I'm confident that no devils or angels are causing this behavior.

I really wish the original report had included the .version info.

16:43 Edit reply: Round function returning -0.0 (artifact: a23fbb0488 user: rmariani)

Yes but that's not happening...

sqlite> select round(-.1);
0.0

Something much more exotic is happening on some builds.

16:42 Reply: Round function returning -0.0 (artifact: 72ee60d8fc user: rmariani)

Yes but that's not happening...

sqlite> select round(-.1); 0.0

Something much more exotic is happening on some builds.

2021-07-30
19:05 Edit reply: Round function returning -0.0 (artifact: 57c8074d3b user: rmariani)

I don't have a windows build of sqlite3 on my system.

But on your system, what does .version say

19:04 Reply: Round function returning -0.0 (artifact: 27e398c97b user: rmariani)

Well this one is very interesting. MSVC... so MSC doesn't universally get this wrong. The plot thickens!

18:13 Reply: LEFT JOIN Misunderstanding (artifact: 8e5c185381 user: rmariani)

only the conditions that are in the ON participate in the LEFTness of the join.

17:23 Edit reply: LEFT JOIN Misunderstanding (artifact: 61147883cb user: rmariani)

It's not a style choice at all really.

If you have

   X LEFT Y ON Z
   WHERE W

If the conditions are in the W clause they will reduce the number of rows returned from X.

If the condition is on the Z clause then all the rows of X will be returned even if some of the Z conditions fail. You'll get nulls.

So the W doesn't participate in the "leftness" of the join.

In this particular case there were 4 joins and all of the X were desired regardless of the join conditions, so all the conditions must go in the ON.

ON is equivalent to WHERE only for INNER joins.

17:22 Reply: LEFT JOIN Misunderstanding (artifact: b7cc2750f9 user: rmariani)

If you have

   X LEFT Y ON Z
   WHERE W

If the conditions are in the W clause they will reduce the number of rows returned from X.

If the condition is on the Z clause then all the rows of X will be returned even if some of the Z conditions fail. You'll get nulls.

So the 'W' doesn't participate in the "leftness" of the join.

In this particular case there were 4 joins and all of the X were desired regardless of the join conditions, so all the conditions must go in the ON.

ON is equivalent to WHERE only for INNER joins.

17:13 Reply: Round function returning -0.0 (artifact: 5d5853b19f user: rmariani)

What does .version say

13:59 Reply: Help calculating data when answers in previous row (artifact: a0b07082d3 user: rmariani)
create table data (
 type text,
 betAmount real,
 winAmount real,
 endBalance real
);

delete from data;

insert into data values
('Purchase Started Pre Purchase Balance',0.25,NULL,NULL),
('Game Completed Final Balance',NULL,0.0,299.75),
('Purchase Started Pre Purchase Balance',0.25,NULL,NULL),
('Game Completed Final Balance',NULL,0.5,300.0),
('Purchase Started Pre Purchase Balance',0.25,NULL,NULL),
('Game Completed Final Balance',NULL,0.0,299.75),
('Purchase Started Pre Purchase Balance',1.0,NULL,NULL),
('Game Completed Final Balance',NULL,0.6,299.35),
('Purchase Started Pre Purchase Balance',1.0,NULL,NULL),
('Game Completed Final Balance',NULL,0.2,298.55),
('Purchase Started Pre Purchase Balance',1.0,NULL,NULL),
('Game Completed Final Balance',NULL,0.6,298.15);

-- assumes it's always pairs
with fuse2(t1, b1, w1, e1, t2, b2, w2, e2) as (
  select
    type,
    betAmount,
    winAmount,
    endBalance,
    lag(type) OVER win1,
    lag(betAmount) OVER win1,
    lag(winAmount) OVER win1,
    lag(endBalance) OVER win1
   from data
   window win1 as (rows 1 preceding)
),
unified_rows(bet, win, end) as (
  select b2, w1, e1 from fuse2
  where fuse2.t1 = 'Game Completed Final Balance'
)
select bet, count(bet), sum(win) from unified_rows
group by bet;

Results:

0.25|3|0.5
1.0|3|1.4

Note: this kind of thing

count(
 CASE 
  WHEN (event IS 'Game Completed Final Balance') THEN 1 
  ELSE NULL 
 END)  

Is all kinds of redundant. Any time you have a CASE expression where the results are 0 or 1 you can pretty much replace it with an equivalent boolean that's more economical.

You never have to write ELSE NULL that's the default value if there is no ELSE.

The above could simply be

count(event IS 'Game Completed Final Balance')

But in any case I don't think you need to go there, see above.

12:57 Reply: LEFT JOIN Misunderstanding (artifact: 721178fa73 user: rmariani)

The above is of course totally right, I think it didn't go far enough.

    WITH LastEntries (ProjID, pl_insert, pe_insert)
    AS
    (
      SELECT
        projid,
        max(InsertDate),
        (SELECT max(insertdate) FROM project_extras where projid = e.projid)
        FROM project_list e
        GROUP BY projid
     )
    SELECT a.ProjID, a.Project_Name, count(c.CreatedDate), a.Manager
    FROM Project_List AS a
    LEFT JOIN Project_Extras AS b
        ON a.ProjID = b.ProjID
        AND b.MonthlyRpt = 'y'
        AND b.BudgetYear = '2021'
    LEFT JOIN Project_Highlights AS c
        ON a.ProjID IS c.ProjID
        AND c.CreatedDate LIKE '2021-07-%' 
    LEFT JOIN LastEntries AS d
        ON a.ProjID = d.projid
        AND a.InsertDate = d.pl_insert
        AND b.InsertDate = d.pe_insert
    GROUP BY a.ProjID
    ORDER BY a.Manager, a.ProjID;
12:53 Edit reply: LEFT JOIN Misunderstanding (artifact: c69e16d62a user: rmariani)

My derp... I didn't notice they were all in the WHERE clause...

Once you move the join condition to the ON clause if there are still too few rows THEN the optimization might be biting you.

12:51 Reply: LEFT JOIN Misunderstanding (artifact: b0954fcf31 user: rmariani)

Derp... WHERE clause... sigh :D

08:14 Reply: Round function returning -0.0 (artifact: bed12d794f user: rmariani)

What did .version say?

06:58 Edit reply: Round function returning -0.0 (artifact: ccc11a1952 user: rmariani)

On a Mac.

sqlite> select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1);
0.0|0.0|0.0|0.0
sqlite> .version
SQLite 3.32.3 2020-06-18 14:16:19 02c344aceaea0d177dd42e62c8541e3cab4a26c757ba33b3a31a43ccc7d4aapl
clang-12.0.5
sqlite>

On Windows but under Ubuntu (Linux on Windows)

sqlite>  select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1);
0.0|0.0|0.0|0.0
sqlite> .version
SQLite 3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1
zlib version 1.2.11
gcc-9.3.0

I don't have an MSC build handy.

06:47 Reply: Round function returning -0.0 (artifact: 095a674179 user: rmariani)

Maybe the issue is with the floating point library provided by MSC on Windows?

06:46 Reply: Round function returning -0.0 (artifact: ee1a09234e user: rmariani)
sqlite> select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1);
0.0|0.0|0.0|0.0
sqlite> .version
SQLite 3.32.3 2020-06-18 14:16:19 02c344aceaea0d177dd42e62c8541e3cab4a26c757ba33b3a31a43ccc7d4aapl
clang-12.0.5
sqlite>
05:24 Reply: Round function returning -0.0 (artifact: 65b49f98cd user: rmariani)

Is it possible that if you started with -0 as the variable that was bound to myVar that it just stays that way because round then does nothing? I couldn't make it fail no matter what floating point numbers I put in there which makes me think that the problem is that maybe a bad value was just passed through.

05:14 Reply: documenting SQLite's "IS" behavior (artifact: 30e2463ba7 user: rmariani)

select +'hi' is documented behavior for SQLite. The + operator is universal identity. It's perhaps strange but it's as advertised.

05:12 Edit reply: LEFT JOIN Misunderstanding (artifact: 03bf9fe632 user: rmariani)

Perhaps this post is relevant

05:11 Reply: LEFT JOIN Misunderstanding (artifact: a5f3e5ed8d user: rmariani)

Perhaps https://sqlite.org/forum/forumpost/f8786ea8e1de76f5e919067d531a1cea7456554be192d05da8eb239fc84fd8c9 is relevant

2021-07-29
16:57 Edit reply: documenting SQLite's "IS" behavior (artifact: 5d7a83b8a6 user: rmariani)

For implicit promotions one would expect the bool to be promoted to int rather than the int reduced to a bool.

Note that in SQLite the + operator does not change types. It can even be applied to a string:

select +'hi';
hi

But anyway, it's not that important.

Other versions of "true" don't work; well, there is no such thing as a boolean type in SQLite so it has kind of no hope. This kind of stuff isn't going to work:

sqlite> select 2 is (0==0);
0

Note that the ISO operator commutes, but:

sqlite> select true is 2;
0

"IS" really is just like the ISO operator except for that special case for boolean literals on the right. Which is not really a surprise because that's how the code is written.

    case TK_IS:
    case TK_ISNOT: {
      Expr *pRight = sqlite3ExprSkipCollateAndLikely(pExpr->pRight);
      assert( !ExprHasProperty(pExpr, EP_Reduced) );
      /* Handle special cases of "x IS TRUE", "x IS FALSE", "x IS NOT TRUE",
      ** and "x IS NOT FALSE". */
      if( ALWAYS(pRight) && (pRight->op==TK_ID || pRight->op==TK_TRUEFALSE) ){
        int rc = resolveExprStep(pWalker, pRight);
        if( rc==WRC_Abort ) return WRC_Abort;
        if( pRight->op==TK_TRUEFALSE ){
          pExpr->op2 = pExpr->op;
          pExpr->op = TK_TRUTH;
          return WRC_Continue;
        }
      }
      /* no break */ deliberate_fall_through
    }
More ↓