On Sat, Aug 23, 2003 at 02:07:36PM +0200, Hans van Harten wrote: > Fred van Engen wrote: > > On Sat, Aug 23, 2003 at 10:54:32AM +0200, Hans van Harten wrote: > >> My checks might not match those of (the next version of) MySQL and > >> at that time the difference in thoughts will pass unnoticed ! > > I agree that MySQL should complain but I'm not sure it should fail. > > The problem is that MySQL has always behaved this way and is in many > > cases documented to do so. Some programs may expect MySQL to do > > clipping of large values (your example in another post) and will fail if > this > > changes. > Preserving the good old installed base, I cannot agree more!! >
Well, the behaviour could change in a major release (5.0 ?) but not in a minor one (3.23.xx or 4.0.xx). > > If MySQL should fail on a simple INSERT with out-of-bounds values, it > > should also fail when the out-of-bounds value is generated in a > > complex query involving expressions with fields (or subqueries). > Right ... 2^66 > insert test (FUN ) value ( '25' ); > select * from test where fun<2147483648*2147483648*16; > return 0 records. > SELECT 2147483648*2147483648*4 returns 0. SELECT 2147483648*2147483648*2 returns -9223372036854775808. SELECT 2147483648*2147483648*2-1 returns 9223372036854775807. The same problem in most programming languages. What do other DBMS do and what do the SQL standards say? I really don't know. > > How would you > > know for which records an UPDATE or INSERT failed? Would you want it > > to fail the entire query and not just problematic records or even > > fields? > Ordinary, UPDATE or INSERT would do one record at a time. INSERT INTO test(id, myint) VALUES (1,2147483647), (2,2147483648); The second is out-of-bounds. Should the first be revoked? INSERT INTO test(id, myint) VALUES (1,2147483646), (2,2147483647); UPDATE test SET myint = myint+1; INSERT INTO test(id, myint) VALUES (1,2147483646), (2,2147483647); INSERT into test2 SELECT id, myint+1 FROM test; The UPDATE and last INSERT are out-of-bounds for the second record. Should the update and insert of the first record be revoked? That's just too much work for current MyISAM tables. > BTW INSERT -or REPLACE- do croak about misfits while using FKs and then do > not process any field -and none of the other records, if you used an record > set- > Great. That's InnoDB, which could do the same for each of the earlier examples. People might expect that from transactioned tables. For other table types I guess it would be unrealistic. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
