test-example-projects/c++_projects/280w-mysql-8.0.18/mysql-test/t/func_test.test

--disable_warnings
drop table if exists t1,t2;
--enable_warnings
#
# Testing of comparison functions
#
select 0=0,1>0,1>=1,1<0,1<=0,1!=0,strcmp("abc","abcd"),strcmp("b","a"),strcmp("a","a") ;
select "a"<"b","a"<="b","b">="a","b">"a","a"="A","a"<>"b";
select "a "="A", "A "="a", "a  " <= "A b"; 
select "abc" like "a%", "abc" not like "%d%", "a%" like "a\%","abc%" like "a%\%","abcd" like "a%b_%d", "a" like "%%a","abcde" like "a%_e","abc" like "abc%";
select "a" like "%%b","a" like "%%ab","ab" like "a\%", "ab" like "_", "ab" like "ab_", "abc" like "%_d", "abc" like "abc%d";
select '?' like '|%', '?' like '|%' ESCAPE '|', '%' like '|%', '%' like '|%' ESCAPE '|', '%' like '%';
select 'abc' like '%c','abcabc' like '%c',  "ab" like "", "ab" like "a", "ab" like "ab";
select "Det här är svenska" regexp "h[[:alpha:]]+r", "aba" regexp "^(a|b)*$";
select "aba" regexp concat("^","a");
select !0,NOT 0=1,!(0=0),1 AND 1,1 && 0,0 OR 1,1 || NULL, 1=1 or 1=1 and 1=0;
select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between "max" and "my" and 3=3;
select 'b' between 'a' and 'c', 'B' between 'a' and 'c';
select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0);
select -1.49 or -1.49,0.6 or 0.6;
select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1;
explain select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1;
select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL;
select 1 like 2 xor 2 like 1;
select 10 % 7, 10 mod 7, 10 div 3;
explain select 10 % 7, 10 mod 7, 10 div 3;
select 18446744073709551615, 18446744073709551615 DIV 1, 18446744073709551615 DIV 2;
explain select (1 << 64)-1, ((1 << 64)-1) DIV 1, ((1 << 64)-1) DIV 2;

create table t1 (a int);
insert t1 values (1);
analyze table t1;
select * from t1 where 1 xor 1;
explain select * from t1 where 1 xor 1;
select - a from t1;
explain select - a from t1;
drop table t1;

#
# Wrong usage of functions
#

select 5 between 0 and 10 between 0 and 1,(5 between 0 and 10) between 0 and 1;
select 1 and 2 between 2 and 10, 2 between 2 and 10 and 1;
select 1 and 0 or 2, 2 or 1 and 0;

#
# Coercibility
#
select _koi8r'a' = _koi8r'A';
select _koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci;
explain select _koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci;
select _koi8r'a' = _koi8r'A' COLLATE koi8r_bin;
select _koi8r'a' COLLATE koi8r_general_ci = _koi8r'A';
select _koi8r'a' COLLATE koi8r_bin = _koi8r'A';
--error 1267
select _koi8r'a' COLLATE koi8r_bin = _koi8r'A' COLLATE koi8r_general_ci;
--error 1267
select _koi8r'a' = _latin1'A';

select strcmp(_koi8r'a', _koi8r'A');
select strcmp(_koi8r'a', _koi8r'A' COLLATE koi8r_general_ci);
select strcmp(_koi8r'a', _koi8r'A' COLLATE koi8r_bin);
select strcmp(_koi8r'a' COLLATE koi8r_general_ci, _koi8r'A');
select strcmp(_koi8r'a' COLLATE koi8r_bin, _koi8r'A');
--error 1267
select strcmp(_koi8r'a' COLLATE koi8r_general_ci, _koi8r'A' COLLATE koi8r_bin);
--error 1267
select strcmp(_koi8r'a', _latin1'A');

select _koi8r'a' LIKE _koi8r'A';
select _koi8r'a' LIKE _koi8r'A' COLLATE koi8r_general_ci;
select _koi8r'a' LIKE _koi8r'A' COLLATE koi8r_bin;
select _koi8r'a' COLLATE koi8r_general_ci LIKE _koi8r'A';
select _koi8r'a' COLLATE koi8r_bin LIKE _koi8r'A';
--error 1267
select _koi8r'a' COLLATE koi8r_general_ci LIKE _koi8r'A' COLLATE koi8r_bin;
--error 1267
select _koi8r'a' LIKE _latin1'A';

#
# Test for LEAST() BUG in LEFT JOIN
#
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1 (   faq_group_id int(11) NOT NULL default '0',   faq_id int(11) NOT NULL default '0',   title varchar(240) default NULL,   keywords text,   description longblob,   solution longblob,   status tinyint(4) NOT NULL default '0',   access_id smallint(6) default NULL,   lang_id smallint(6) NOT NULL default '0',   created datetime NOT NULL default '0000-00-00 00:00:00',   updated datetime default NULL,   last_access datetime default NULL,   last_notify datetime default NULL,   solved_count int(11) NOT NULL default '0',   static_solved int(11) default NULL,   solved_1 int(11) default NULL,   solved_2 int(11) default NULL,   solved_3 int(11) default NULL,   solved_4 int(11) default NULL,   solved_5 int(11) default NULL,   expires datetime default NULL,   notes text,   assigned_to smallint(6) default NULL,   assigned_group smallint(6) default NULL,   last_edited_by smallint(6) default NULL,   orig_ref_no varchar(15) binary default NULL,   c$fundstate smallint(6) default NULL,   c$contributor smallint(6) default NULL,   UNIQUE KEY t1$faq_id (faq_id),   KEY t1$group_id$faq_id (faq_group_id,faq_id),   KEY t1$c$fundstate (c$fundstate) ) ENGINE=InnoDB;
INSERT INTO t1 VALUES (82,82,'How to use the DynaVox Usage Counts Feature','usages count, number, corner, white, box, button','<as-html>\r\n<table width=\"100%\" border=\"0\">\r\n  <tr>\r\n    <td width=\"3%\"> </td>\r\n    <td width=\"97%\">\r\n       <h3><font face=\"Verdana, Arial, Helvetica, sans-serif\" color=\"#000000\">How \r\n        To</font><!-- #BeginEditable \"CS_troubleshoot_question\" --><font face=\"Verdana, Arial, Helvetica, sans-serif\" color=\"#000099\"><font color=\"#000000\">: \r\n        Display or Hide the Usage Counts to find out how many times each button is being selected. </font></font><!-- #EndEditable --></h3>\r\n    </td>\r\n  </tr>\r\n</table>','<as-html>\r\n <table width=\"100%\" border=\"0\">\r\n  <tr>\r\n    <td width=\"3%\"> </td>\r\n    \r\n<td width=\"97%\"><!-- #BeginEditable \"CS_troubleshoot_answer\" --> \r\n      \r\n<p><font color=\"#000000\" face=\"Verdana, Arial, Helvetica, sans-serif\">1. Select \r\n  the <i>On/Setup</i> button to access the DynaVox Setup Menu.<br>\r\n  2. Select <b>Button Features.</b><br>\r\n  3. Below the <b>OK</b> button is the <b>Usage Counts</b> button.<br>\r\n  a. If it says \"Hidden\" then the Usage Counts will not be displayed.<br>\r\n  b. If it says \"Displayed\" then the Usage Counts will be shown.<br>\r\n        c. Select the <b>Usage Counts</b> Option Ring once and it will toggle \r\n        to the alternative option.<br>\r\n  4. Once the correct setting has been chosen, select <b>OK</b> to leave the <i>Button \r\n  Features</i> menu.<br>\r\n  5. Select <b>OK</b> out of the <i>Setup</i> menu and return to the communication \r\n  page.</font></p>\r\n      <p><font color=\"#000000\" face=\"Verdana, Arial, Helvetica, sans-serif\">For \r\n        further information on <i>Usage Counts,</i> see the <i>Button Features \r\n        Menu Entry</i> in the DynaVox/DynaMyte Reference Manual.</font></p>\r\n<!-- #EndEditable --></td>\r\n  </tr>\r\n</table>',4,1,1,'2001-11-16 16:43:34','2002-11-25 12:09:43','2003-07-24 01:04:48',NULL,11,NULL,0,0,0,0,0,NULL,NULL,NULL,NULL,11,NULL,NULL,NULL);
CREATE TABLE t2 (  access_id smallint(6) NOT NULL default '0',   name varchar(20) binary default NULL,   `rank` smallint(6) NOT NULL default '0',   KEY t2$access_id (access_id) ) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1,'Everyone',2),(2,'Help',3),(3,'Customer Support',1);
SELECT f_acc.`rank`, a1.`rank`, a2.`rank`  FROM t1 LEFT JOIN t1 f1 ON  (f1.access_id=1 AND f1.faq_group_id = t1.faq_group_id) LEFT JOIN t2 a1 ON (a1.access_id =  f1.access_id) LEFT JOIN t1 f2 ON (f2.access_id=3 AND  f2.faq_group_id = t1.faq_group_id) LEFT  JOIN t2 a2 ON (a2.access_id = f2.access_id), t2 f_acc WHERE LEAST(a1.`rank`,a2.`rank`) =  f_acc.`rank`;
DROP TABLE t1,t2;
SET sql_mode = default;
#
# Test for GREATEST() and LEAST() bug, which segfaulted 4.1.1 server
#
CREATE TABLE t1 (d varchar(6), k int);
INSERT INTO t1 VALUES (NULL, 2);
SELECT GREATEST(d,d) FROM t1 WHERE k=2;
DROP TABLE t1;

#
# Bug #6138: mod and doubles
#

select 1197.90 mod 50;
select 5.1 mod 3, 5.1 mod -3, -5.1 mod 3, -5.1 mod -3;

#
# Test for mod and signed integers
#

select 5 mod 3, 5 mod -3, -5 mod 3, -5 mod -3;

#
# Bug#23411: The "%" (MOD) operator is not documented; MOD-ing zero returns strange result
# Manual: "Division by zero produces a NULL result"
#
select (12%0) <=> null      as '1';
select (12%0) is null       as '1';
select 12%0                 as 'NULL';
select 12%2                 as '0';
select 12%NULL              as 'NULL';
select 12 % null            as 'NULL';
select null % 12            as 'NULL';
select null % 0             as 'NULL';
select 0 % null             as 'NULL';
select null % null          as 'NULL';

select (12 mod 0) <=> null  as '1';
select (12 mod 0) is null   as '1';
select 12 mod 0             as 'NULL';
select 12 mod 2             as '0';
select 12 mod null          as 'NULL';
select null mod 12          as 'NULL';
select null mod 0           as 'NULL';
select 0 mod null           as 'NULL';
select null mod null        as 'NULL';

select mod(12.0, 0)         as 'NULL';
select mod(12, 0.0)         as 'NULL';
select mod(12, NULL)        as 'NULL';
select mod(12.0, NULL)      as 'NULL';
select mod(NULL, 2)         as 'NULL';
select mod(NULL, 2.0)       as 'NULL';


#
# Bug#6726: NOT BETWEEN parse failure
#
create table t1 (a int, b int);
insert into t1 values (1,2), (2,3), (3,4), (4,5);
select * from t1 where a not between 1 and 2;
select * from t1 where a not between 1 and 2 and b not between 3 and 4;
drop table t1;

#
# Test for bug #12791: one of the arguments of LEAST/GREATEST is NULL
#

SELECT GREATEST(1,NULL) FROM DUAL;
SELECT LEAST('xxx','aaa',NULL,'yyy') FROM DUAL;
SELECT LEAST(1.1,1.2,NULL,1.0) FROM DUAL;
SELECT GREATEST(1.5E+2,1.3E+2,NULL) FROM DUAL;

# End of 4.1 tests

--echo #
--echo # Bug#22523685 FUNCTION GREATEST AND LEAST WORKS INCORRECTLY
--echo #              WITH BIGINT UNSIGNED VALUE
--echo #

SELECT greatest( 9223372036854775807 , 9223372036854775808 ) as g;
SELECT least   ( 9223372036854775807 , 9223372036854775808 ) as l;

SELECT greatest (9223372036854775808, -1, 18446744073709551615 ) as g;
SELECT least    (9223372036854775808, -1, 18446744073709551615 ) as l;

SELECT greatest (9223372036854775808, 18446744073709551615) as g;
SELECT least    (9223372036854775808, 18446744073709551615) as l;

CREATE TABLE t1 AS SELECT greatest(-1, 9223372036854775808);
CREATE TABLE t2 AS SELECT greatest(9223372036854775808, 9223372036854775808);

SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;

DROP TABLE t1, t2;

--echo #
--echo # Bug#22839888 UNINITIALIZED VALUE WHEN CONVERTING
--echo #              MULTIBYTE STRINGS TO NUMBERS
--echo #
--error ER_CANT_AGGREGATE_3COLLATIONS
select greatest(1,_utf16'.',_utf8'');

-- echo #
-- echo # Bug#25123839: LEAST AND GREATEST MAKES INCONSISTENT DATA TYPES,
-- echo # COMPARED TO UNION AND COALESCE
-- echo #

SET sql_mode='';
CREATE TABLE t1 AS
SELECT 5 AS c1, 5 AS c2, 5 AS c3, 5 AS c4, 20010101 AS c5, 010101 AS c6,
       010101 AS c7, 20010101 AS c8, 5.55 AS c11, 5.5 AS c12, 5.5 AS c13,
       010101.5 AS c14, 5.5 AS c15, 010101.5 as c16, 5.5e5 as c21,
       2004041e1 as c22, 150505.0e5 AS c23, 2004041e1 AS c24, '010101' AS c31,
       '5' AS c32, '010101' AS c33, date '2005-05-05' AS c41,
       date '2005-05-05' AS c42, time '05:05:05.555555' AS c51,
       x'c3a5' AS c61, x'c3a5' AS c62, _utf8mb4 x'c3a5' AS c63,
       _utf8mb4 x'c3a5' AS c64, JSON_OBJECT('a', 1) as c71
UNION
SELECT -5, 5.5, 5.5e5, '5', date '2005-05-05', time '05:05:05.555555',
       timestamp '2005-05-05 05:05:05.555555', NOW(0), 88.8,
       5.5e5, '5', date '2005-05-05', time '05:05:05.555555',
       timestamp '2005-05-05 05:05:05.555555',
       '5', date '2005-05-05', time '05:05:05.555555',
       timestamp '2005-05-05 05:05:05.555555',
       date '2005-05-05', time '05:05:05.555555',
       timestamp '2005-05-05 05:05:05.555555',
       time '05:05:05.555555', timestamp '2005-05-05 05:05:05.555555',
       timestamp '2005-05-05 05:05:05.555555', '1', _binary '1', '1',
       _binary '1', NULL;

SET sql_mode=default;

CREATE TABLE t2 AS
SELECT COALESCE(5, -5) AS c1,
       COALESCE(5, 5.5) AS c2,
       COALESCE(5, 5.5e5) AS c3,
       COALESCE(5, '5') AS c4,
       COALESCE(20010101, date '2005-05-05') AS c5,
       COALESCE(010101, time '05:05:05.555555') AS c6,
       COALESCE(010101, timestamp '2005-05-05 05:05:05.555555') AS c7,
       COALESCE(20010101, NOW(0)) AS c8,
       COALESCE(5.55, 88.8) AS c11,
       COALESCE(5.5, 5.5e5) AS c12,
       COALESCE(5.5, '5') AS c13,
       COALESCE(010101.5, date '2005-05-05') AS c14,
       COALESCE(5.5, time '05:05:05.555555') AS c15,
       COALESCE(010101.5, timestamp '2005-05-05 05:05:05.555555') AS c16,
       COALESCE(5.5e5, '5') AS c21,
       COALESCE(2004041e1, date '2005-05-05') AS c22,
       COALESCE(150505.0e5, time '05:05:05.555555') AS c23,
       COALESCE(2004041e1, timestamp '2005-05-05 05:05:05.555555') AS c24,
       COALESCE('010101', date '2005-05-05') AS c31,
       COALESCE('5', time '05:05:05.555555') AS c32,
       COALESCE('010101', timestamp '2005-05-05 05:05:05.555555') AS c33,
       COALESCE(date '2005-05-05', time '05:05:05.555555') AS c41,
       COALESCE(date '2005-05-05', timestamp '2005-05-05 05:05:05.555555') AS
c42,
       COALESCE(time '05:05:05.555555', timestamp '2005-05-05
05:05:05.555555') AS c51,
       COALESCE(x'c3a5', '1') AS c61,
       COALESCE(x'c3a5', _binary '1') AS c62,
       COALESCE(_utf8mb4 x'c3a5', '1') AS c63,
       COALESCE(_utf8mb4 x'c3a5', _binary '1') AS c64,
       COALESCE(JSON_OBJECT('a', 1), NULL) AS c71;

CREATE TABLE t3 AS
SELECT GREATEST(5, -5) AS c1,
       GREATEST(5, 5.5) AS c2,
       GREATEST(5, 5.5e5) AS c3,
       GREATEST(5, '5') AS c4,
       GREATEST(20010101, date '2005-05-05') AS c5,
       GREATEST(010101, time '05:05:05.555555') AS c6,
       GREATEST(010101, timestamp '2005-05-05 05:05:05.555555') AS c7,
       GREATEST(20010101, NOW(0)) AS c8,
       GREATEST(5.55, 88.8) AS c11,
       GREATEST(5.5, 5.5e5) AS c12,
       GREATEST(5.5, '5') AS c13,
       GREATEST(010101.5, date '2005-05-05') AS c14,
       GREATEST(5.5, time '05:05:05.555555') AS c15,
       GREATEST(010101.5, timestamp '2005-05-05 05:05:05.555555') AS c16,
       GREATEST(5.5e5, '5') AS c21,
       GREATEST(2004041e1, date '2005-05-05') AS c22,
       GREATEST(150505.0e5, time '05:05:05.555555') AS c23,
       GREATEST(2004041e1, timestamp '2005-05-05 05:05:05.555555') AS c24,
       GREATEST('010101', date '2005-05-05') AS c31,
       GREATEST('5', time '05:05:05.555555') AS c32,
       GREATEST('010101', timestamp '2005-05-05 05:05:05.555555') AS c33,
       GREATEST(date '2005-05-05', time '05:05:05.555555') AS c41,
       GREATEST(date '2005-05-05', timestamp '2005-05-05 05:05:05.555555') AS
c42,
       GREATEST(time '05:05:05.555555', timestamp '2005-05-05
05:05:05.555555') AS c51,
       GREATEST(x'c3a5', '1') AS c61,
       GREATEST(x'c3a5', _binary '1') AS c62,
       GREATEST(_utf8mb4 x'c3a5', '1') AS c63,
       GREATEST(_utf8mb4 x'c3a5', _binary '1') AS c64;

SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
SHOW CREATE TABLE t3;

DROP TABLE t1, t2, t3;

# Strings only, strings and integers, and integers only
SELECT GREATEST('11', '5', '2');
SELECT GREATEST('11', 5, 2);
SELECT GREATEST(11, 5, 2);
SELECT LEAST('11', '5', '2');
SELECT LEAST('11', 5, 2);
SELECT LEAST(11, 5, 2);

# Date and integers, and date and strings
SELECT GREATEST(date '2005-05-05', 20010101, 20040404, 20030303);
SELECT LEAST(date '2005-05-05', 20030303, 20010101, 20040404);
SELECT GREATEST(date '2005-05-05', '20010101', '20040404', '20030303');
SELECT LEAST(date '2005-05-05', '20030303', '20010101', '20040404');

# Time and integer
SELECT GREATEST(time '00:00:00', 120000);
SELECT LEAST(time '00:00:00', 120000);
SELECT GREATEST(time '20:00:00', 120000);
SELECT LEAST(time '20:00:00', 120000);

# Dates only
SELECT GREATEST('95-05-05', date '10-10-10');
SELECT GREATEST(date '1995-05-05', '10-10-10');
SELECT LEAST(date '1995-05-05', '10-10-10');
SELECT LEAST('95-05-05', date '10-10-10');

# Plus operations with integers and doubles.
SELECT GREATEST('11', '5', '2') + 0;
SELECT GREATEST('11', 5, 2) + 0;
SELECT GREATEST(11, 5, 2) + 0;
SELECT GREATEST(date '2005-05-05', 20010101, 20040404, 20030303) + 0;
SELECT GREATEST(time '00:00:00', 120000) + 0;
SELECT GREATEST(time '20:00:00', 120000) + 0;
SELECT GREATEST('95-05-05', date '10-10-10') + 0;
SELECT GREATEST(date '1995-05-05', '10-10-10') + 0;
SELECT GREATEST('11', '5', '2') + 0.00;
SELECT GREATEST('11', 5, 2) + 0.00;
SELECT GREATEST(11, 5, 2) + 0.00;
SELECT GREATEST(date '1995-05-05', 19910101, 20050505, 19930303) + 0.00;
SELECT GREATEST(time '00:00:00', 120000) + 0.00;
SELECT GREATEST(time '20:00:00', 120000) + 0.00;
SELECT GREATEST('95-05-05', date '10-10-10') + 0.00;
SELECT GREATEST(date '1995-05-05', '10-10-10') + 0.00;

-- echo # End of test for Bug#25123839

-- echo #
-- echo # Bug#26975864: ASSERTION FAILED: DEC <= 6, MY_DATETIME_TO_STR, LEAST,
-- echo # GREATEST, ETC
-- echo #

CREATE TABLE t1 (a DATETIME, b BLOB, c TEXT, d CHAR(10), e BINARY(10),
f VARBINARY(10));
SET @a='2017-01-01';
INSERT INTO t1 VALUES ('2017-02-02 12:00:00', @a, @a, @a, @a, @a);
SELECT GREATEST(a, b), GREATEST(a, c), GREATEST(a, d), GREATEST(a, e),
GREATEST(a, f) FROM t1;
SET @a=NULL;
DROP TABLE t1;

-- echo #
-- echo # Bug#13364839: GREATEST() DOES NOT ALWAYS RETURN SAME SIGNNESS OF ARGUMENT TYPES
-- echo #

CREATE TABLE t1 (a INT PRIMARY KEY, b BIGINT(20) UNSIGNED);
INSERT INTO t1 VALUES (1, 13836376518955650385);
INSERT INTO t1 VALUES (1, 13836376518955650385) ON DUPLICATE KEY UPDATE b=GREATEST(b, VALUES(b));
SELECT * FROM t1;
DROP TABLE t1;

-- echo #
-- echo # Bug#27312703 ASAN: HEAP-USE-AFTER-FREE: GREATEST/LEAST FUNCTIONS
-- echo #

SET @a:='11';
DO GREATEST(RIGHT(@a,1),1);
DO LEAST(RIGHT(@a,1),1);

-- echo #
-- echo # Bug#29275835 ASSERTION FAILED:
-- echo #     !UNSIGNED_FLAG || (UNSIGNED_FLAG && ARGS[I]->UNSIGNED_FLAG)
-- echo #

SET sql_mode='';

CREATE TABLE t0017 (
  c0002 bigint(20) NOT NULL
);

INSERT INTO t0017 (c0002)
VALUES (0);

SELECT greatest(18446744073709551615, NULL)
FROM t0017;

SELECT
count(greatest(18446744073709551615, NULL))
FROM t0017;

DROP TABLE t0017;
SET sql_mode = default;

--echo #
--echo # Bug#29467577 ASSERTION `!UNSIGNED_FLAG ||
--echo #              (UNSIGNED_FLAG && ARGS[I]->UNSIGNED_FLAG)' FAILED.
--echo #

CREATE TABLE t1 (c1 INT UNSIGNED, c2 INT UNSIGNED);
INSERT INTO t1 VALUES (202, 1);
SELECT * FROM t1 WHERE (GREATEST(c1, c2) = 42) AND (c2 = 1);
DROP TABLE t1;