Tim, Simon & Darren, if you read my whole OP you will see that I've discovered this: use REAL instead. My point is that the behaviour of a NUMERIC column is not intuitive and gives mixed results which wouldn't be a problem if the division operator could be modified. My suggestion cannot be too outlandish if MySQL does it "my way".
Simon says: "The PRAGMAs allow SQLite to switch between different behaviours when the standard doesn't say what should happen". I would venture to say perhaps the standard wasn't too clear on this, or at the very least the fact that MySQL does it differently means there is a bit of a smudge on this part. Darren says: "declaring NUMERIC types is saying you don't care about the behavior". I do care about behaviour, so I'll change my management system to exclude NUMERIC as an option since I have no use for it then! I cannot expect my clients to know little quirks to this level of detail. I agree with what Darren says about the option of having 2 operators, / and div, that's what MySQL does and it is also a feature of Pascal and other languages. Please don't get me wrong. I haven't used MySQL for new projects in years, so I'm not promoting it in any way. Also, if NUMERIC wasn't so ubiquitous in the SQL world, I wouldn't even have raised the issue. If I am correct in taking away from this discussion "don't use NUMERIC column definitions if you want to do any calculations [with divisions] on them", then we can let it rest now. I'll dream of seeing NUMERIC(p,s) one day that enforces (p,s) (and doesn't do integer division unless s=0 !) :-) _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
