Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 553 insertions, 0 deletions

diff --git a/mysql-test/suite/engines/iuds/t/update_decimal.test b/mysql-test/suite/engines/iuds/t/update_decimal.test
new file mode 100644
index 00000000..bf302e6a
--- /dev/null
+++ b/mysql-test/suite/engines/iuds/t/update_decimal.test
@@ -0,0 +1,553 @@
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2,t3,t4;
+--enable_warnings
+
+######## Running UPDATE tests for DECIMAL ########
+
+# Create tables
+CREATE TABLE t1(c1 DECIMAL(10,5) UNSIGNED NOT NULL, c2 DECIMAL(10,5) SIGNED NULL, c3 DECIMAL, c4 INT, UNIQUE INDEX idx(c1,c4));
+
+# Inserting data into table t1
+
+# Trailing zeroes are not stripped, D=5
+INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
+
+# DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped; 
+INSERT IGNORE INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
+
+# c1, c2, c3 will be rounded automatically
+INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
+
+# Inserting maximum values
+INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
+
+# Test insert leading zero, +/- signs, overflow handling
+INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
+INSERT IGNORE INTO t1 VALUES ("-.1","-.1","-.1",8);
+INSERT IGNORE INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
+
+# Inserting in scientific notations
+INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
+#INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11);
+
+--sorted_result
+SELECT * FROM t1;
+
+# UPDATE starts here
+
+# Update order by limit
+UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1;
+--sorted_result
+SELECT c1,c2 FROM t1;
+
+# Update with arithmetic operations 
+UPDATE IGNORE t1 SET c1=c2+c3 WHERE c3 < 10000;
+--sorted_result
+SELECT * FROM t1;
+UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4;
+--sorted_result
+SELECT * FROM t1;
+
+# Update with NULL ( NULL to number & number to NULL)
+UPDATE t1 SET c2=NULL WHERE c4=4;
+--sorted_result
+SELECT * FROM t1;
+UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL;
+--sorted_result
+SELECT * FROM t1;
+
+# Update negative value to unsigned column
+UPDATE IGNORE t1 SET c1=-1.0e+2 WHERE c4=2;
+SHOW WARNINGS;
+--sorted_result
+SELECT * FROM t1;
+UPDATE IGNORE t1 SET c1=1.0e+20 WHERE c4=2;
+SHOW WARNINGS;
+--sorted_result
+SELECT * FROM t1;
+
+# Update range values
+UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7;
+--sorted_result
+SELECT * FROM t1;
+UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5;
+--sorted_result
+SELECT * FROM t1 WHERE c1=1.2e+2;
+UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6);
+--sorted_result
+SELECT * FROM t1;
+
+# Update outside range would be clipped to closest endpoints
+UPDATE IGNORE t1 SET c1=99999.999999 WHERE c4=7;
+--sorted_result
+SELECT c1,c4 FROM t1;
+UPDATE t1 SET c2=-9999.0099 WHERE c4=5;
+--sorted_result
+SELECT c2,c4 FROM t1;
+
+# Update ignore on bad null error
+SELECT c1 FROM t1 WHERE c1>1000;
+SET SQL_MODE=STRICT_ALL_TABLES;
+--error ER_BAD_NULL_ERROR
+UPDATE t1 SET c1=NULL WHERE c1>1000;
+UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000;
+--sorted_result
+SELECT c1 from t1 WHERE c1>1000;
+SET SQL_MODE=DEFAULT;
+
+TRUNCATE t1;
+INSERT INTO t1 VALUES ('11111.11111','-11111.11111','1111111111',1),('22222.22222','-22222.22222','2222222222',2),('33333.33333','-33333.33333','3333333333',3),('44444.44444','-44444.44444','4444444444',4),('55555.55555','-55555.55555','5555555555',5),('66666.66666','-66666.66666','6666666666',6),('77777.77777','-77777.77777','7777777777',7),('88888.88888','-88888.88888','8888888888',8),('99999.99999','-99999.99999','9999999999',9);
+--sorted_result
+SELECT * FROM t1;
+
+SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
+UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
+--sorted_result
+SELECT * FROM t1;
+
+# Multi table update
+CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx2(c1,c3));
+CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1,c3));
+CREATE TABLE t4(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx4(c1,c3)); 
+INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
+INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); 
+INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
+--sorted_result
+SELECT * FROM t2;
+--sorted_result
+SELECT * FROM t3;
+--sorted_result
+SELECT * FROM t4;
+UPDATE t1,t2,t3,t4 SET t2.c1=t2.c1+t3.c1, t3.c2=t3.c2-t2.c1, t4.c1=t1.c1*t1.c3 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888' ;
+--sorted_result
+SELECT * FROM t1;
+--sorted_result
+SELECT * FROM t1;
+--sorted_result
+SELECT * FROM t1;
+
+# Update using various access methods
+TRUNCATE t2;
+TRUNCATE t3;
+INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
+INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
+
+# Update using Const
+# EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
+UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2;
+--sorted_result
+SELECT * FROM t2;
+
+# Update using range
+# EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
+update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444';
+--sorted_result
+SELECT * FROM t2;
+# EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
+UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999');
+--sorted_result
+SELECT * FROM t2;
+
+# Update using eq_ref
+DROP TABLE t2,t3;
+CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT);
+CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1));
+INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
+INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
+# EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
+UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
+
+#Updating with invalid values
+UPDATE IGNORE t1 SET c3='asdf' WHERE c1='11111.11111';
+--sorted_result
+SELECT c3 FROM t1;
+UPDATE IGNORE t1 SET c2="1 e 1" WHERE c4=2;
+--sorted_result
+SELECT c2 FROM t1;
+
+#Duplicate keys
+--error ER_DUP_ENTRY
+UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7;
+
+--sorted_result
+SELECT * FROM t1;
+--sorted_result
+SELECT * FROM t2;
+--sorted_result
+SELECT * FROM t3;
+--sorted_result
+SELECT * FROM t4;
+# Droping the tables
+--disable_warnings
+DROP TABLES IF EXISTS t1,t2,t3,t4;
+--enable_warnings
+
+######## Running UPDATE tests for FLOAT ########
+
+# Create tables
+CREATE TABLE t1(c1 FLOAT(10,5) UNSIGNED NOT NULL, c2 FLOAT(10,5) SIGNED NULL, c3 FLOAT, c4 INT, UNIQUE INDEX idx(c1,c4));
+
+# Inserting data into table t1
+
+# Trailing zeroes are not stripped, D=5
+INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
+
+# DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped; 
+INSERT IGNORE INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
+
+# c1, c2, c3 will be rounded automatically
+INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
+
+# Inserting maximum values
+INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
+
+# Test insert leading zero, +/- signs, overflow handling
+INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
+INSERT IGNORE INTO t1 VALUES ("-.1","-.1","-.1",8);
+INSERT IGNORE INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
+
+# Inserting in scientific notations
+INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
+#INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11);
+
+--sorted_result
+SELECT * FROM t1;
+
+# UPDATE starts here
+
+# Update order by limit
+UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1;
+--sorted_result
+SELECT c1,c2 FROM t1;
+
+# Update with arithmetic operations 
+UPDATE IGNORE t1 SET c1=c2+c3 WHERE c3 < 10000;
+--sorted_result
+SELECT * FROM t1;
+UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4;
+--sorted_result
+SELECT * FROM t1;
+
+# Update with NULL ( NULL to number & number to NULL)
+UPDATE t1 SET c2=NULL WHERE c4=4;
+--sorted_result
+SELECT * FROM t1;
+UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL;
+--sorted_result
+SELECT * FROM t1;
+
+# Update negative value to unsigned column
+UPDATE IGNORE t1 SET c1=-1.0e+2 WHERE c4=2;
+SHOW WARNINGS;
+--sorted_result
+SELECT * FROM t1;
+UPDATE IGNORE t1 SET c1=1.0e+20 WHERE c4=2;
+SHOW WARNINGS;
+--sorted_result
+SELECT * FROM t1;
+
+# Update range values
+UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7;
+--sorted_result
+SELECT * FROM t1;
+UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5;
+--sorted_result
+SELECT * FROM t1 WHERE c1=1.2e+2;
+UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6);
+--sorted_result
+SELECT * FROM t1;
+
+# Update outside range would be clipped to closest endpoints
+UPDATE IGNORE t1 SET c1=99999.999999 WHERE c4=7;
+--sorted_result
+SELECT c1,c4 FROM t1;
+UPDATE t1 SET c2=-9999.0099 WHERE c4=5;
+--sorted_result
+SELECT c2,c4 FROM t1;
+
+# Update ignore on bad null error
+SELECT c1 FROM t1 WHERE c1>1000;
+SET SQL_MODE=STRICT_ALL_TABLES;
+--error ER_BAD_NULL_ERROR
+UPDATE t1 SET c1=NULL WHERE c1>1000;
+UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000;
+--sorted_result
+SELECT c1 from t1 WHERE c1>1000;
+SET SQL_MODE=DEFAULT;
+
+TRUNCATE t1;
+INSERT INTO t1 VALUES ('11111.11111','-11111.11111','1111111111',1),('22222.22222','-22222.22222','2222222222',2),('33333.33333','-33333.33333','3333333333',3),('44444.44444','-44444.44444','4444444444',4),('55555.55555','-55555.55555','5555555555',5),('66666.66666','-66666.66666','6666666666',6),('77777.77777','-77777.77777','7777777777',7),('88888.88888','-88888.88888','8888888888',8),('99999.99999','-99999.99999','9999999999',9);
+--replace_result 88888.89063 88888.89062
+--sorted_result
+SELECT * FROM t1;
+
+SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
+UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
+--replace_result 88888.89063 88888.89062
+--sorted_result
+SELECT * FROM t1;
+
+# Multi table update
+CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx2(c1,c3));
+CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1,c3));
+CREATE TABLE t4(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx4(c1,c3)); 
+INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
+INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); 
+INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
+--sorted_result
+SELECT * FROM t2;
+--sorted_result
+SELECT * FROM t3;
+--replace_result 88888.89063 88888.89062
+--sorted_result
+SELECT * FROM t4;
+UPDATE t1,t2,t3,t4 SET t2.c1=t2.c1+t3.c1, t3.c2=t3.c2-t2.c1, t4.c1=t1.c1*t1.c3 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888' ;
+--replace_result 88888.89063 88888.89062
+--sorted_result
+SELECT * FROM t1;
+--replace_result 88888.89063 88888.89062
+--sorted_result
+SELECT * FROM t1;
+--replace_result 88888.89063 88888.89062
+--sorted_result
+SELECT * FROM t1;
+
+# Update using various access methods
+TRUNCATE t2;
+TRUNCATE t3;
+INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
+INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
+
+# Update using Const
+# EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
+UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2;
+--sorted_result
+SELECT * FROM t2;
+
+# Update using range
+# EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
+update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444';
+--sorted_result
+SELECT * FROM t2;
+# EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
+UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999');
+--sorted_result
+SELECT * FROM t2;
+
+# Update using eq_ref
+DROP TABLE t2,t3;
+CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT);
+CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1));
+INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
+INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
+# EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
+UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
+
+#Updating with invalid values
+UPDATE t1 SET c3='asdf' WHERE c1='11111.11111';
+--sorted_result
+SELECT c3 FROM t1;
+UPDATE IGNORE t1 SET c2="1 e 1" WHERE c4=2;
+--replace_result 88888.89063 88888.89062
+--sorted_result
+SELECT c2 FROM t1;
+
+#Duplicate keys
+--replace_result 88888.89063 88888.89062
+--error ER_DUP_ENTRY
+UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7;
+--replace_result 88888.89063 88888.89062
+--sorted_result
+SELECT * FROM t1;
+--sorted_result
+SELECT * FROM t2;
+--sorted_result
+SELECT * FROM t3;
+--replace_result 88888.89063 88888.89062
+--sorted_result
+SELECT * FROM t4;
+# Droping the tables
+--disable_warnings
+DROP TABLES IF EXISTS t1,t2,t3,t4;
+--enable_warnings
+
+######## Running UPDATE tests for DOUBLE ########
+
+# Create tables
+CREATE TABLE t1(c1 DOUBLE(10,5) UNSIGNED NOT NULL, c2 DOUBLE(10,5) SIGNED NULL, c3 DOUBLE, c4 INT, UNIQUE INDEX idx(c1,c4));
+
+# Inserting data into table t1
+
+# Trailing zeroes are not stripped, D=5
+INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
+
+# DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped; 
+INSERT IGNORE INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
+
+# c1, c2, c3 will be rounded automatically
+INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
+
+# Inserting maximum values
+INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
+
+# Test insert leading zero, +/- signs, overflow handling
+INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
+INSERT IGNORE INTO t1 VALUES ("-.1","-.1","-.1",8);
+INSERT IGNORE INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
+
+# Inserting in scientific notations
+INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
+#INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11);
+
+--sorted_result
+SELECT * FROM t1;
+
+# UPDATE starts here
+
+# Update order by limit
+UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1;
+--sorted_result
+SELECT c1,c2 FROM t1;
+
+# Update with arithmetic operations 
+UPDATE IGNORE t1 SET c1=c2+c3 WHERE c3 < 10000;
+--sorted_result
+SELECT * FROM t1;
+UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4;
+--sorted_result
+SELECT * FROM t1;
+
+# Update with NULL ( NULL to number & number to NULL)
+UPDATE t1 SET c2=NULL WHERE c4=4;
+--sorted_result
+SELECT * FROM t1;
+UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL;
+--sorted_result
+SELECT * FROM t1;
+
+# Update negative value to unsigned column
+UPDATE IGNORE t1 SET c1=-1.0e+2 WHERE c4=2;
+SHOW WARNINGS;
+--sorted_result
+SELECT * FROM t1;
+UPDATE IGNORE t1 SET c1=1.0e+20 WHERE c4=2;
+SHOW WARNINGS;
+--sorted_result
+SELECT * FROM t1;
+
+# Update range values
+UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7;
+--sorted_result
+SELECT * FROM t1;
+UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5;
+--sorted_result
+SELECT * FROM t1 WHERE c1=1.2e+2;
+UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6);
+--sorted_result
+SELECT * FROM t1;
+
+# Update outside range would be clipped to closest endpoints
+UPDATE IGNORE t1 SET c1=99999.999999 WHERE c4=7;
+--sorted_result
+SELECT c1,c4 FROM t1;
+UPDATE t1 SET c2=-9999.0099 WHERE c4=5;
+--sorted_result
+SELECT c2,c4 FROM t1;
+
+# Update ignore on bad null error
+SELECT c1 FROM t1 WHERE c1>1000;
+SET SQL_MODE=STRICT_ALL_TABLES;
+--error ER_BAD_NULL_ERROR
+UPDATE t1 SET c1=NULL WHERE c1>1000;
+UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000;
+--sorted_result
+SELECT c1 from t1 WHERE c1>1000;
+SET SQL_MODE=DEFAULT;
+
+TRUNCATE t1;
+INSERT INTO t1 VALUES ('11111.11111','-11111.11111','1111111111',1),('22222.22222','-22222.22222','2222222222',2),('33333.33333','-33333.33333','3333333333',3),('44444.44444','-44444.44444','4444444444',4),('55555.55555','-55555.55555','5555555555',5),('66666.66666','-66666.66666','6666666666',6),('77777.77777','-77777.77777','7777777777',7),('88888.88888','-88888.88888','8888888888',8),('99999.99999','-99999.99999','9999999999',9);
+--sorted_result
+SELECT * FROM t1;
+
+SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
+UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
+--sorted_result
+SELECT * FROM t1;
+
+# Multi table update
+CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx2(c1,c3));
+CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1,c3));
+CREATE TABLE t4(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx4(c1,c3)); 
+INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
+INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); 
+INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
+--sorted_result
+SELECT * FROM t2;
+--sorted_result
+SELECT * FROM t3;
+--sorted_result
+SELECT * FROM t4;
+UPDATE t1,t2,t3,t4 SET t2.c1=t2.c1+t3.c1, t3.c2=t3.c2-t2.c1, t4.c1=t1.c1*t1.c3 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888' ;
+--sorted_result
+SELECT * FROM t1;
+--sorted_result
+SELECT * FROM t1;
+--sorted_result
+SELECT * FROM t1;
+
+# Update using various access methods
+TRUNCATE t2;
+TRUNCATE t3;
+INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
+INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
+
+# Update using Const
+# EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
+UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2;
+--sorted_result
+SELECT * FROM t2;
+
+# Update using range
+# EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
+update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444';
+--sorted_result
+SELECT * FROM t2;
+# EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
+UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999');
+--sorted_result
+SELECT * FROM t2;
+
+# Update using eq_ref
+DROP TABLE t2,t3;
+CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT);
+CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1));
+INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
+INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
+# EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
+UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
+
+#Updating with invalid values
+UPDATE IGNORE t1 SET c3='asdf' WHERE c1='11111.11111';
+--sorted_result
+SELECT c3 FROM t1;
+UPDATE IGNORE t1 SET c2="1 e 1" WHERE c4=2;
+--sorted_result
+SELECT c2 FROM t1;
+
+#Duplicate keys
+--error ER_DUP_ENTRY
+UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7;
+
+--sorted_result
+SELECT * FROM t1;
+--sorted_result
+SELECT * FROM t2;
+--sorted_result
+SELECT * FROM t3;
+--sorted_result
+SELECT * FROM t4;
+# Droping the tables
+--disable_warnings
+DROP TABLES IF EXISTS t1,t2,t3,t4;
+--enable_warnings
+