BUG#26495791: CONTRIBUTION: EXPAND TEST SUITE TO INCLUDE CRC32 TESTS

From mysql-server:
09fdfad50764ff6809e7dd5300e9ce1ab727b62a
e90ae1707e0ca46abc775d1680d1856c4be38b66

described in http://github.com/mysql/mysql-server/pull/157

Apart from external contribution I have added few more additional testcases
for CRC32() function, which are given below.

New Testcases added:
  ->Verify the crc value of various numeric and string data types(int,
      double, blob, text, json, enum, set)
  ->Verify the crc value when expressions having comparison_operators
      and logical_operators
  ->Verify the crc value for the expression having string_functions,
      arithmetic_functions, json_functions
  ->Verify the crc value for the expression having Geometry functions
      like POINT, LINESTRING, MULTILINESTRING, POLYGON, MULTIPOLYGON
  ->Verify the crc value generated from stored procedures, functions,
      triggers, prepare statement, views.

Fix:
Patch based on contribution by Daniel Black (Github user: grooverdan)

Reviewed-by: Anitha Gopi anitha.gopi@oracle.com
Reviewed-by: Srikanth B R srikanth.b.r@oracle.com

RB: 17294

1 files changed, 389 insertions, 0 deletions

diff --git a/mysql-test/main/func_math.result b/mysql-test/main/func_math.result
index 7f2ed1c2ff0..2de3d6f3808 100644
--- a/mysql-test/main/func_math.result
+++ b/mysql-test/main/func_math.result
@@ -900,3 +900,392 @@ a	HEX(a)
 DROP TABLE t2;
 DROP TABLE t1;
 SET sql_mode=DEFAULT;
+#
+# Bug#26495791 - EXPAND TEST SUITE TO INCLUDE CRC32 TESTS
+#
+SELECT CRC32(NULL), CRC32(''), CRC32('MySQL'), CRC32('mysql');
+CRC32(NULL)	CRC32('')	CRC32('MySQL')	CRC32('mysql')
+NULL	0	3259397556	2501908538
+SELECT CRC32('01234567'), CRC32('012345678');
+CRC32('01234567')	CRC32('012345678')
+763378421	939184570
+SELECT CRC32('~!@$%^*'),  CRC32('-0.0001');
+CRC32('~!@$%^*')	CRC32('-0.0001')
+1533935548	324352530
+SELECT CRC32(99999999999999999999999999999999);
+CRC32(99999999999999999999999999999999)
+3035957051
+SELECT CRC32(-99999999999999999999999999999999);
+CRC32(-99999999999999999999999999999999)
+1052326872
+DROP TABLE IF EXISTS t;
+Warnings:
+Note	1051	Unknown table 'test.t'
+CREATE TABLE t(a INT, b VARCHAR(2));
+INSERT INTO t VALUES (1,'a'), (2,'qw'), (1,'t'), (3,'t');
+SELECT crc32(SUM(a)) FROM t;
+crc32(SUM(a))
+1790921346
+SELECT crc32(AVG(a)) FROM t GROUP BY b;
+crc32(AVG(a))
+768278432
+2875100430
+2875100430
+SELECT crc32(MAX(b)) FROM t GROUP BY a;
+crc32(MAX(b))
+2238339752
+3114057431
+2238339752
+SELECT a, b, crc32(a) FROM t GROUP BY a,b HAVING crc32(MAX(a))=450215437;
+a	b	crc32(a)
+2	qw	450215437
+SELECT a,b,concat(a,b),crc32(concat(a,b)) FROM t ORDER BY crc32(concat(a,b));
+a	b	concat(a,b)	crc32(concat(a,b))
+2	qw	2qw	552289476
+1	a	1a	3174122627
+1	t	1t	3505168488
+3	t	3t	3805987562
+DROP TABLE t;
+SELECT CRC32(4+2);
+CRC32(4+2)
+498629140
+SELECT CRC32(4/2);
+CRC32(4/2)
+2875100430
+SELECT CRC32(4-2);
+CRC32(4-2)
+450215437
+SELECT CRC32(4*2);
+CRC32(4*2)
+4194326291
+SELECT CRC32(ABS(-6));
+CRC32(ABS(-6))
+498629140
+SELECT CRC32(CEILING(1.23));
+CRC32(CEILING(1.23))
+450215437
+SELECT CRC32(FLOOR(1.23));
+CRC32(FLOOR(1.23))
+2212294583
+SELECT CRC32(LOG(10,100));
+CRC32(LOG(10,100))
+450215437
+SELECT CRC32(PI());
+CRC32(PI())
+2969982827
+SELECT CRC32(POWER(2,2));
+CRC32(POWER(2,2))
+4088798008
+SELECT CRC32(ROUND(1.58));
+CRC32(ROUND(1.58))
+450215437
+SELECT CRC32(SIGN(0));
+CRC32(SIGN(0))
+4108050209
+SELECT CRC32(SQRT(4));
+CRC32(SQRT(4))
+450215437
+SELECT CRC32(2 > 4);
+CRC32(2 > 4)
+4108050209
+SELECT CRC32(2 < 4);
+CRC32(2 < 4)
+2212294583
+SELECT CRC32(2 >= 4);
+CRC32(2 >= 4)
+4108050209
+SELECT CRC32(2 <= 4);
+CRC32(2 <= 4)
+2212294583
+SELECT CRC32(2 != 4);
+CRC32(2 != 4)
+2212294583
+SELECT CRC32(NOT 1);
+CRC32(NOT 1)
+4108050209
+SELECT CRC32(1 AND 1);
+CRC32(1 AND 1)
+2212294583
+SELECT CRC32(1 OR 1);
+CRC32(1 OR 1)
+2212294583
+SELECT CRC32(1 XOR 1);
+CRC32(1 XOR 1)
+4108050209
+SELECT CRC32(ASCII('2'));
+CRC32(ASCII('2'))
+3308380389
+SELECT CRC32(BIT_LENGTH('text'));
+CRC32(BIT_LENGTH('text'))
+2103780943
+SELECT CRC32(CHAR('77','121','83','81','76'));
+CRC32(CHAR('77','121','83','81','76'))
+3259397556
+SELECT CRC32(CONCAT('good','year'));
+CRC32(CONCAT('good','year'))
+3441724142
+SELECT CRC32(INSERT('foodyear', 1, 4, 'good'));
+CRC32(INSERT('foodyear', 1, 4, 'good'))
+3441724142
+SELECT CRC32(LEFT('goodyear', 4));
+CRC32(LEFT('goodyear', 4))
+1820610194
+SELECT CRC32(LENGTH('text'));
+CRC32(LENGTH('text'))
+4088798008
+SELECT CRC32(LOWER('GOODYEAR'));
+CRC32(LOWER('GOODYEAR'))
+3441724142
+SELECT CRC32(UPPER('goodyear'));
+CRC32(UPPER('goodyear'))
+186062498
+SELECT CRC32(LTRIM('  goodyear'));
+CRC32(LTRIM('  goodyear'))
+3441724142
+SELECT CRC32(RTRIM('goodyear  '));
+CRC32(RTRIM('goodyear  '))
+3441724142
+SELECT CRC32(REPLACE('godyear','o','oo'));
+CRC32(REPLACE('godyear','o','oo'))
+3441724142
+SELECT CRC32(REVERSE('goodyear'));
+CRC32(REVERSE('goodyear'))
+3423698264
+SELECT CRC32(true);
+CRC32(true)
+2212294583
+SELECT CRC32(false);
+CRC32(false)
+4108050209
+DROP TABLE IF EXISTS t1;
+Warnings:
+Note	1051	Unknown table 'test.t1'
+CREATE TABLE t1 (c1 BIT(5),
+c2 TINYINT,
+c3 MEDIUMINT,
+c4 INTEGER,
+c5 BIGINT,
+c6 DECIMAL(7,5),
+c7 FLOAT(7,5),
+c8 DOUBLE(7,5));
+INSERT INTO t1 VALUES (B'10101', 127, 8388607, 2147483647,
+9223372036854775807, 10.5, 11.5, 12.5);
+SELECT CRC32(c1) FROM t1;
+CRC32(c1)
+3219065702
+SELECT CRC32(c2) FROM t1;
+CRC32(c2)
+2401609675
+SELECT CRC32(c3) FROM t1;
+CRC32(c3)
+1742563487
+SELECT CRC32(c4) FROM t1;
+CRC32(c4)
+365954768
+SELECT CRC32(c5) FROM t1;
+CRC32(c5)
+1122634462
+SELECT CRC32(c6) FROM t1;
+CRC32(c6)
+4255015174
+SELECT CRC32(c7) FROM t1;
+CRC32(c7)
+1542023858
+SELECT CRC32(c8) FROM t1;
+CRC32(c8)
+1778479151
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (c1 DATE,
+c2 DATETIME,
+c3 TIMESTAMP,
+c4 TIME,
+c5 YEAR);
+INSERT INTO t1 VALUES ('2007-01-01', '2007-01-01 12:00:01',
+'2007-01-01 00:00:01.000000',
+'12:00:01.000000', '2007');
+SELECT CRC32(c1) FROM t1;
+CRC32(c1)
+2772295888
+SELECT CRC32(c2) FROM t1;
+CRC32(c2)
+1449026401
+SELECT CRC32(c3) FROM t1;
+CRC32(c3)
+225145558
+SELECT CRC32(c4) FROM t1;
+CRC32(c4)
+1077283474
+SELECT CRC32(c5) FROM t1;
+CRC32(c5)
+955685210
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (c1 CHAR,
+c2 VARCHAR(10),
+c3 BINARY(1),
+c4 VARBINARY(10),
+c5 TINYBLOB,
+c6 TINYTEXT,
+c7 BLOB,
+c8 TEXT,
+c9 MEDIUMBLOB,
+c10 MEDIUMTEXT,
+c11 LONGBLOB,
+c12 LONGTEXT);
+INSERT INTO t1 VALUES ('a', 'a', 0x61, 0x61, 'a', 'a',
+'a', 'a', 'a', 'a', 'a', 'a');
+SELECT CRC32(c1) FROM t1;
+CRC32(c1)
+3904355907
+SELECT CRC32(c2) FROM t1;
+CRC32(c2)
+3904355907
+SELECT CRC32(c3) FROM t1;
+CRC32(c3)
+3904355907
+SELECT CRC32(c4) FROM t1;
+CRC32(c4)
+3904355907
+SELECT CRC32(c5) FROM t1;
+CRC32(c5)
+3904355907
+SELECT CRC32(c6) FROM t1;
+CRC32(c6)
+3904355907
+SELECT CRC32(c7) FROM t1;
+CRC32(c7)
+3904355907
+SELECT CRC32(c8) FROM t1;
+CRC32(c8)
+3904355907
+SELECT CRC32(c9) FROM t1;
+CRC32(c9)
+3904355907
+SELECT CRC32(c10) FROM t1;
+CRC32(c10)
+3904355907
+SELECT CRC32(c11) FROM t1;
+CRC32(c11)
+3904355907
+SELECT CRC32(c12) FROM t1;
+CRC32(c12)
+3904355907
+SELECT CRC32(ST_GeomFromText('POINT(1 1)'));
+CRC32(ST_GeomFromText('POINT(1 1)'))
+1349318989
+CREATE TABLE geom_data(id INT,
+pt POINT NOT NULL,
+lnstr LINESTRING NOT NULL,
+mlnstr MULTILINESTRING NOT NULL,
+poly POLYGON NOT NULL,
+mpoly MULTIPOLYGON NOT NULL);
+INSERT INTO geom_data VALUES (10,
+ST_GEOMFROMTEXT('POINT(10 20)'),
+ST_GEOMFROMTEXT('LINESTRING(0 0,5 5,6 6)'),
+ST_GEOMFROMTEXT('MULTILINESTRING((0 0,2 3,4 5),(6 6,8 8,9 9,10 10))'),
+ST_GEOMFROMTEXT('POLYGON((0 0,6 7,8 8,3 9,0 0),(3 6,4 6,4 7,3 6))'),
+ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),
+                                ((2 2,4 5,6 2,2 2)))'));
+SELECT CRC32(ST_X(pt)) FROM geom_data;
+CRC32(ST_X(pt))
+2707236321
+SELECT CRC32(ST_NumPoints((lnstr))) FROM geom_data;
+CRC32(ST_NumPoints((lnstr)))
+1842515611
+SELECT CRC32(ST_Length((mlnstr))) FROM geom_data;
+CRC32(ST_Length((mlnstr)))
+2090153432
+SELECT CRC32(ST_Area((poly))) FROM geom_data;
+CRC32(ST_Area((poly)))
+534598600
+SELECT CRC32(ST_Area((mpoly))) FROM geom_data;
+CRC32(ST_Area((mpoly)))
+3832313845
+DROP TABLE geom_data;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (e1 ENUM ('a','b'), s1 SET('a','b'));
+INSERT INTO t1 VALUES(2,'a,b'),('a','b,a');
+SELECT e1, CRC32(e1) FROM t1;
+e1	CRC32(e1)
+b	1908338681
+a	3904355907
+SELECT s1, CRC32(s1) FROM t1;
+s1	CRC32(s1)
+a,b	752423903
+a,b	752423903
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (a JSON);
+INSERT INTO t1 VALUES ('{"name" : "goodyear"}'),
+('{"name" : "verygood-year"}');
+SELECT a->>'$.name', CRC32(a) FROM t1;
+a->>'$.name'	CRC32(a)
+goodyear	3664432072
+verygood-year	922278888
+SELECT CRC32(a->>'$.name') FROM t1 WHERE a->>'$.name' = 'goodyear';
+CRC32(a->>'$.name')
+3441724142
+SELECT CRC32(REPLACE(JSON_EXTRACT(a, "$.name"),'\"',''))
+FROM t1 WHERE JSON_EXTRACT(a, "$.name") = 'goodyear';
+CRC32(REPLACE(JSON_EXTRACT(a, "$.name"),'\"',''))
+3441724142
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES(10);
+CREATE VIEW v1 AS SELECT CRC32(a) AS my_crc FROM t1;
+SELECT * FROM v1;
+my_crc
+2707236321
+SELECT CRC32(CRC32(my_crc)) FROM v1;
+CRC32(CRC32(my_crc))
+3114155452
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (a CHAR);
+CREATE TABLE t2 (b BIGINT);
+CREATE TRIGGER trg1
+BEFORE INSERT ON t1
+FOR EACH ROW
+INSERT INTO t2 VALUES(CRC32(NEW.a));
+INSERT INTO t1 VALUES('a');
+SELECT * FROM t2;
+b
+3904355907
+DROP TRIGGER trg1;
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE PROCEDURE crc32_proc (IN a CHAR, OUT b BIGINT)
+SELECT CRC32(a) INTO b;
+CALL crc32_proc('a',@val);
+SELECT @val;
+@val
+3904355907
+DROP PROCEDURE crc32_proc;
+CREATE FUNCTION crc32_func(inputvar CHAR)
+RETURNS BIGINT
+BEGIN
+DECLARE crcval BIGINT;
+SELECT CRC32(inputvar) INTO crcval;
+RETURN crcval;
+END|
+SELECT crc32_func('a');
+crc32_func('a')
+3904355907
+DROP FUNCTION crc32_func;
+PREPARE stmt1 FROM 'SELECT CRC32(?)';
+SET @val = 'a';
+EXECUTE stmt1 USING @val;
+CRC32(?)
+3904355907
+DEALLOCATE PREPARE stmt;
+SET NAMES utf8;
+CREATE TABLE t1 (a TEXT) CHARACTER SET = utf8;
+LOAD DATA INFILE '../../std_data/loaddata_utf8.dat' INTO TABLE t1 CHARACTER SET utf8;
+SELECT HEX(a), CRC32(a) from t1;
+HEX(a)	CRC32(a)
+E4B880E4BA8CE4B889	1785250883
+E59B9BE4BA94E585AD	2914501801
+E4B883E585ABE4B99D	2374586519
+E4B880E4BA8CE4B889	1785250883
+E59B9BE4BA94E585AD	2914501801
+E4B883E585ABE4B99D0A	1546750244
+DROP TABLE t1;
+SET NAMES default;