The same place it is documented that:

int result = 7/3;

is different from:

double result = 7/3;

is different from:

int result = 7.0/3.0;

is different from:

double result = 7.0/3.0;

I can tell by mere eyeball that they are different. Cannot you?

I do not know of any such documentation within the SQLite docs, and will ponder (further¹) whether there should be something more on arithmetic operators. I suspect this is something C/C++ programmers are so accustomed to that it is like breathing air.

---

1. I'm still at the phase of having arguments for and against.

I'd like to cast my vote with definitely having it documented. It's quite weird for anyone coming from a non-native C background. In most strongly typed languages (of which there are admittedly only a handful) the statement:

int x = 7 / 3;

will indeed throw/raise an error.

Having said that, I seem to recall having learned of this myself (in SQL terms) right inside the SQLite documentation back when. I can't recall where exactly. Probably as part of an example or feature discussion, though it may also have been a reply on the forum.

Either way, if it isn't already there, I believe it should be.

It could indeed be very short.

Shortest: Just refer to what the C language does and leave it to the reader to figure out what that means.

Or just a little bit longer:

• if one operand is an integer and the other a float, promote the integer to a float before computing the result
• if m and n are integers with n≠0, m/n is the quotient rounded towards zero
• if m and n are integers with m nonnegative and n positive, m%n = m-(m/n)*n, where m/n is computed according to the rule above
• if m and n are any integers, (-m)%n=-(m%n) and m%(-n)=m%n
• and if adding or multiplying integers results in overflow, the operands are promoted to floats and the operation recomputed

Did I miss anything important? Apart from over- and underflow with floats?

Edit: I had written

Oh, a curiosity: For floats x and y, x%y is always zero. It makes sense in a twisted sort of way, but not very useful.

But that is wrong. It only happened to be true for the values I tried. (See the answer by Bill Wade below.)

Did I miss anything important? Apart from over- and underflow with floats?

I think reference to the IEEE standard suffices for this.

a curiosity: For floats x and y, x%y is always zero.

I do not get such results. (And it would be twisted to get them, IMO.)

Some just added documentation addresses this deficiency.

However, if anybody needs to be told that '/' is divide, '+' is add, and '-' is subtract, (or what those operations mean), they will need to go elsewhere.

This change will reach the published docs page in due course.

Nobody needs to be told what division is, I hope. But integer division is a bit tricky, when either operand is negative. It might be good to mention the “round towards zero” rule, as opposed to “round to nearest integer” or “round down”, both of which are reasonable choices. The % operator is even trickier in that respect. (Funny, I missed it when reading the docs just half an hour ago, hidden in plain sight a paragraph dealing with unrelated operators.)

I believe that the combination of this language for modulus, "computes the remainder after dividing the left integer by the right integer", and this language, "Integer divide yields an integer result, truncated toward zero" (just added), covers the subtleties of modulus operation for anybody willing to think about it.

Oddly, I found little online documentation for other DBMS systems on these details.

Oddly, I found little online documentation for other DBMS systems on these details.

Not only that, it seems not 100% consistent.

Ran this query on a few DBs:

SELECT 7/3,  7.0/3,  7/3.0,  -7/3,  -7/4

which gave me the following:

On SQLite:
2 | 2.33333333333333 | 2.33333333333333 | -2 | -1

MySQL:
2.3333 | 2.33333 | 2.3333 | -2.3333 | -1.7500

MSSQL:
2 | 2.333333 | 2.333333 | -2 | -1

Postgres:
2 | 2.3333333333333333 | 2.3333333333333333 | -2 | -1

There you have it, 4 answers mostly similar but not 100%, making it worth the mention - thanks for adding to the documentation.

If we ignore the difference in precision of the answers, only MySQL is the outliers. All the other systems behave the same.

MySQL has a separate "DIV" operator for integer division. The "/" operator means floating-point division. It is the only SQL database engine that works this way, as far as I know.

SQLite uses IEEE-754 64-bit binary floating point to store and compute floating point values. That format is precision limited. The value computed for 7.0/3.0 is really:

• 2.333333333333333481363069950020872056484222412109375

This is not the true value of 7.0/3.0, of course. The true value of 7.0/3.0 cannot be represented as an IEEE-754 double. The value actually computed is the closest IEEE-754 double to the correct answer, at least in this case.

Probably all of the systems are using IEEE-754 doubles internally, and are all computing the exact same IEEE-754 double value. They are each just choosing to display a different number of significant digits.

...MySQL is the outlier ... has a separate "DIV" operator ... IEEE/True value etc.

Absolutely agreed, and to be clear, my purpose was not to claim wild differences or even suggest any is right/wrong/better, but simply to answer the question: "Is it worth a mention in the SQLite docs?", which I hope to have helped push to the YES side.
(Somewhat belated I should say, since Larry already made efforts in that direction).

We're very close, but I can't quite let go of this issue yet.

The point is, that what is meant by the remainder of a division when either operand is negative, is not totally obvious.

After thinking about it myself for a while, it has dawned on me that the fundamental relation connecting integer division and remainder, namely, m%n = m - (m/n)*n, holds for all combinations of signs. Taking division by zero into account, one can even state that m%n IS m - (m/n)*n is always true for integer operands. Maybe this could be made explicit?

The other, more reasonable (in a mathematical sense) definition of m%n always produces a value between 0 and n-1 (inclusive), when n is positive. This accords better with what we teach students when introducing modular arithmetic at an elementary level (without jumping straight to congruence classes). In fact, I think it is a common beginners' mistake to check whether two values x and y are congruent modulo m by the expression x%m=y%m. (The correct way is (x-y)%m=0, except that may overflow, so a bit more caution is called for. Perhaps (x%m-y%m)%m=0 is the prudent choice. Although even that could in principle overflow if m is really large.)

My point with this lengthy aside is that it is not obvious that thinking about it produces the correct answer, even if your thinking ability is good. It also depends on your background. But the relation m%n = m - (m/n)*n can be seen as a coherence condition connecting the two operations, and stating it explicitly should be sufficient to guide the thinking process.

We're very close, but I can't quite let go of this issue yet.

Well, that's a relief! (for me at least)

The point is, that what is meant by the remainder of a division when either operand is negative, is not totally obvious.

I submit that it become less obvious with overthinking. Yes, a great many mathematical definitions can be formulated for "remainder", some of which have nice properties, where "nice" varies according to whose eye does the beholding.

Your post, in its analytic delight, raises memories of a certain math professor I once had. (His cranium was kind of wedge shaped, better to contain his mighty brain, the power of which he took many opportunities per class session to make clear to his less-endowed students. It was clear even without such efforts. I shared his love of math but not of his wondrous self.)

Back to "remainder". In some early class, I learned that it refers to what remains when one encounters the fact that the integers are not quite a field when division is considered but goes ahead and does divide on just the largest part of the numerator which may be divided without leaving a fractional part. The part that cannot participate in the division is what remains, as "the remainder".

Once "divide" is taken to produce the quotient truncated to the smaller integer, that "remainder" (in the school-child's sense) can have only one value for any pair of operands.

the fundamental relation connecting integer division and remainder, namely, m%n = m - (m/n)*n

The pedant in me was sorely tempted to write that out. However, that urge did not survive my commitment to avoid verbosity and superfluity in the docs. The language I quoted above regarding the mod operator, "computes the remainder after dividing the left integer by the right integer", implies exactly that relation and none of the fancier ones inconsistent with it. So when I referred to "anybody willing to think about it", I was not issuing a challenge. Rather, I merely meant to say that a reading of the whole paragraph, with consistent meanings assigned to the words, would not leave room for reasonable doubt about what the "%" operator does.

My point with this lengthy aside is that it is not obvious that thinking about it produces the correct answer, even if your thinking ability is good. It also depends on your background. But the relation m%n = m - (m/n)*n can be seen as a coherence condition connecting the two operations, and stating it explicitly should be sufficient to guide the thinking process.

I've had my say on obviousness, not to be overdone.

Once we have footnoting capability in the doc management system, I may discretely attach that equation to the "remainder" term, there for those who care and not a distraction for the remainder. (I would have done it yesterday if the feature was available.)

For amusement, I wrote this little script: create table Numer(n int); create table Denom(d int); insert into Numer(n) select * from generate_series(-20,20); insert into Denom(d) select * from Numer where n<>0; create view BadRemainders as select n.n, d.d from Numer n, Denom d where d.d*(n.n/d.d) + n.n%d.d <> n.n; select count(*) from BadRemainders; , result of which is 0.

Well, I am glad to hear that you derived some pleasure from our little exchange. I am not entirely convinced, but I concede that you make a strong case. I tend to lean in the direction of conciseness myself, to the point where my colleagues claim that my writing is often too hard to decipher. And they may have a point – when I read my own writing a few years later, what seemed obvious at one point is not so after all.

I am not entirely convinced

If you read between the lines, you may see that I am not entirely convinced either. Yet I have to make the judgment call as to what verbiage will add enough clarity on specific issues to outweigh its detrimental effect for those who wish to scan the docs without becoming mired in minutiae. I do not contend that the relation between integer divide and modulus cannot be made clearer. And, clearly, obviousness has to be a subjective assessment absent some experimental work that will likely never happen.

Sometimes I wish there was a typographical way to indicate "these words are not used sloppily". But that would imply that some or many other words are, then the question would be "which ones are to be taken at face value?"

One thing to note is that there are several 'nice' properties that we like to see in integer divides and in the modulus operation but w can't have them all.

There is the 'definition' of modulus of m % n == m - m / n * n

There is the idea that modulus acts like mod so that 0 <= m % n < n

And there is a nice property of division that (-m)/n = -(m/n), which normally means divide 'drops fractions' and rounds to zero.

These three properties are incompatible.

To make the modulus always positive we need division to be a floor operation or the 'definition' on modulus can't be used, thus we need to choose which nice properties we will keep.

Which is why the Ada language has two remainder operators, one whose result has the same sign as the numerator and one whose result has the same sign as the denominator.

This is getting a bit far afield, but now I'm curious: Does Ada also have two integer division operators, matching the two remainder operators?

If I got that right, integer division is always round towards zero.

Yep. One nit: -7 mod 3 should yield 2.