########### suite/json/t/json_innodb.test                            #
# Tests json functionality that depends on innodb features           #
#                                                                    #
# This test copies some tests originally in json.test                #
######################################################################

# original test case in the bug report
# innodb allows more than three json columns in a table
CREATE TABLE D_21104470 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_nokey json NOT NULL,
  col_int_key int(11) NOT NULL,
  col_date_key date NOT NULL,
  col_date_nokey json NOT NULL,
  col_time_key time NOT NULL,
  col_time_nokey json NOT NULL,
  col_datetime_key datetime NOT NULL,
  col_datetime_nokey json NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  col_varchar_nokey json NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_date_key (col_date_key),
  KEY col_time_key (col_time_key),
  KEY col_datetime_key (col_datetime_key),
  KEY col_varchar_key (col_varchar_key),
  KEY col_int_key_2 (col_int_key,col_date_key,col_time_key,col_datetime_key,col_varchar_key)
) AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO D_21104470
VALUES
(1,'{"int1": "6"}',3,
  '2007-06-18','{"date": "2007-06-18"}',
  '00:00:00','{"time": null}',
  '2002-08-20 22:48:00','{"datetime": "2002-08-20 22:48:00.035785"}',
  'd','{"varc": "d"}'),
(2,'{"int1": "2"}',8,
  '2002-10-13','{"date": "2002-10-13"}',
  '00:00:00','{"time": "00:00:00"}',
  '1900-01-01 00:00:00','{"datetime": "1900-01-01 00:00:00"}',
  's','{"varc": "s"}'),
(3,'{"int1": "4"}',1,
  '1900-01-01','{"date": "1900-01-01"}',
  '15:57:25','{"time": "15:57:25.019666"}',
  '2005-08-15 00:00:00','{"datetime": "2005-08-15 00:00:00"}',
  'r','{"varc": "r"}'),
(4,'{"int1": "8"}',8,
  '1900-02-03','{"date": null}',
  '07:05:51','{"time": "07:05:51.006712"}',
  '1900-01-01 00:00:00','{"datetime": "1900-01-01 00:00:00"}',
  'm','{"varc": "m"}'),
(5,'{"int1": "4"}',8,
  '2006-03-09','{"date": "2006-03-09"}',
  '19:22:21','{"time": "19:22:21.057406"}',
  '2008-05-16 08:09:06','{"datetime": "2008-05-16 08:09:06.002924"}',
  'b','{"varc": "b"}'),
(6,'{"int1": "4"}',5,
  '2001-06-05','{"date": "2001-06-05"}',
  '03:53:16','{"time": "03:53:16.001370"}',
  '2001-01-20 12:47:23','{"datetime": "2001-01-20 12:47:23.022022"}',
  'x','{"varc": "x"}'),
(7,'{"int1": "7"}',7,
  '2006-05-28','{"date": "2006-05-28"}',
  '09:16:38','{"time": "09:16:38.034570"}',
  '2008-07-02 00:00:00','{"datetime": "2008-07-02 00:00:00"}',
  'g','{"varc": "g"}'),
(8,'{"int1": "4"}',5,
  '2001-04-19','{"date": "2001-04-19"}',
  '15:37:26','{"time": "15:37:26.028315"}',
  '1900-01-01 00:00:00','{"datetime": "1900-01-01 00:00:00"}',
  'p','{"varc": "p"}'),
(9,'{"int1": "1"}',1,
  '1900-01-01','{"date": "1900-01-01"}',
  '00:00:00','{"time": "00:00:00"}',
  '2002-12-08 11:34:58','{"datetime": "2002-12-08 11:34:58.001571"}',
  'q','{"varc": "q"}'),
(10,'{"int1": "9"}',6,
  '2004-08-20','{"date": "2004-08-20"}',
  '05:03:03','{"time": "05:03:03.047452"}',
  '1900-01-01 00:00:00','{"datetime": "1900-01-01 00:00:00"}',
  'w','{"varc": "w"}');

CREATE TABLE DD_21104470 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_nokey json NOT NULL,
  col_int_key int(11) NOT NULL,
  col_date_key date NOT NULL,
  col_date_nokey json NOT NULL,
  col_time_key time NOT NULL,
  col_time_nokey json NOT NULL,
  col_datetime_key datetime NOT NULL,
  col_datetime_nokey json NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  col_varchar_nokey json NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_date_key (col_date_key),
  KEY col_time_key (col_time_key),
  KEY col_datetime_key (col_datetime_key),
  KEY col_varchar_key (col_varchar_key),
  KEY col_int_key_2 (col_int_key,col_date_key,col_time_key,col_datetime_key,col_varchar_key)
) AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;
INSERT INTO DD_21104470
VALUES
(10,'{"int1": "6"}',3,
  '2002-12-16','{"date": "2002-12-16"}',
  '17:54:20','{"time": "17:54:20.050299"}',
  '2007-04-06 06:20:37','{"datetime": "2007-04-06 06:20:37.035492"}',
  'i','{"varc": "i"}'),
(11,'{"int1": "1"}',1,
  '2005-08-08','{"date": "2005-08-08"}',
  '08:53:47','{"time": "08:53:47.064602"}',
  '2008-03-07 00:00:00','{"datetime": "2008-03-07 00:00:00"}',
  'x','{"varc": "x"}'),
(12,'{"int1": "5"}',2,
  '2004-11-16','{"date": "2004-11-16"}',
  '01:40:28','{"time": "01:40:28.015719"}',
  '2002-01-25 10:15:08','{"datetime": "2002-01-25 10:15:08.014682"}',
  'l','{"varc": "l"}'),
(13,'{"int1": "6"}',7,
  '1900-02-04','{"date": null}',
  '16:37:21','{"time": "16:37:21.055310"}',
  '2001-02-26 23:35:13','{"datetime": "2001-02-26 23:35:13.014897"}',
  'q','{"varc": "q"}'),
(14,'{"int1": "2"}',6,
  '2006-05-14','{"date": "2006-05-14"}',
  '01:00:33','{"time": "01:00:33.038177"}',
  '2001-06-23 13:47:43','{"datetime": "2001-06-23 13:47:43.001775"}',
  'n','{"varc": "n"}'),
(15,'{"int1": "4"}',1,
  '2005-01-19','{"date": "2005-01-19"}',
  '03:06:43','{"time": "03:06:43.059217"}',
  '2007-01-17 05:06:55','{"datetime": "2007-01-17 05:06:55.064405"}',
  'r','{"varc": "r"}'),
(16,'{"int1": "231"}',156,
  '1900-01-01','{"date": "1900-01-01"}',
  '00:00:00','{"time": null}',
  '2001-10-23 00:00:00','{"datetime": "2001-10-23 00:00:00"}',
  'c','{"varc": "c"}'),
(17,'{"int1": "4"}',8,
  '2004-06-25','{"date": "2004-06-25"}',
  '16:13:44','{"time": "16:13:44.008978"}',
  '2003-11-24 04:13:27','{"datetime": "2003-11-24 04:13:27.046820"}',
  'h','{"varc": "h"}'),
(18,'{"int1": "3"}',7,
  '2009-12-22','{"date": "2009-12-22"}',
  '14:09:13','{"time": "14:09:13.028533"}',
  '2007-09-19 14:33:09','{"datetime": "2007-09-19 14:33:09.017132"}',
  'k','{"varc": "k"}'),
(19,'{"int1": "3"}',2,
  '2004-06-25','{"date": "2004-06-25"}',
  '10:09:51','{"time": "10:09:51.017335"}',
  '1900-01-01 00:00:00','{"datetime": "1900-01-01 00:00:00"}',
  't','{"varc": "t"}');

CREATE TABLE insert_select_21104470
SELECT
table3 . col_datetime_nokey AS field1
FROM
  D_21104470 AS table1
  LEFT  JOIN  DD_21104470 AS table2
  LEFT  JOIN DD_21104470 AS table3
  ON table2 . col_int_key <  table3 . col_int_key
  ON  table1 . pk =  table2 . col_int_key
WHERE   table1 . col_int_key >= 3
AND table1 . col_int_key < ( 3 + 7 )
OR table1 . col_int_key >= 3
AND table1 . col_int_key < ( 3 + 25 )
AND table1 . col_int_key != table2 . pk
AND table1 . col_int_key IS  NULL
AND  table1 . pk IS  NULL
OR table1 . col_int_key > 3
GROUP BY field1
ORDER BY field1;

DROP TABLE D_21104470;
DROP TABLE DD_21104470;
DROP TABLE insert_select_21104470;

--echo #
--echo # Bug#21383497 DBUG_ABORT() IN VAL_JSON
--echo #
# This test produces indeterminate results in ndb that depend on the order of evaluation
# The group identifier may be any of the items that compare equal
CREATE TABLE t(a INT PRIMARY KEY);
INSERT INTO t VALUES (1), (2), (3), (4), (5);
# Different warnings in the hypergraph join optimizer.
--disable_warnings
SELECT 1 FROM t GROUP BY ST_ASGEOJSON(POINT(1, 1)) WITH ROLLUP;
--enable_warnings
SELECT JSON_EXTRACT('{"a":1}', '$.a') AS je, COUNT(DISTINCT a)
FROM t GROUP BY je WITH ROLLUP;
SELECT JSON_EXTRACT(NULL, '$.a') AS je, COUNT(DISTINCT a)
FROM t GROUP BY je WITH ROLLUP;
SELECT JSON_EXTRACT('{"a":1}', '$.a') AS je, a, COUNT(DISTINCT a)
FROM t GROUP BY je, a WITH ROLLUP;
SELECT JSON_EXTRACT('{"a":1}', '$.a') AS je, a, COUNT(DISTINCT a)
FROM t GROUP BY a, je WITH ROLLUP;
DROP TABLE t;

--echo #
--echo # Bug#21096340 VIEW WITH QUERY HAVING SUM ON JSON COLUMN RETURNS INVALID DATA
--echo #

--source include/turn_off_only_full_group_by.inc

CREATE TABLE D (
col_varchar_10_latin1 varchar(10) CHARACTER SET latin1,
col_varchar_255_latin1_key varchar(255) CHARACTER SET latin1,
col_int_key int,
pk integer auto_increment,
col_varchar_255_utf8_key varchar(255) CHARACTER SET utf8,
col_int int,
col_varchar_10_utf8 varchar(10) CHARACTER SET utf8,
col_varchar_255_latin1 varchar(255) CHARACTER SET latin1,
col_varchar_255_utf8 varchar(255) CHARACTER SET utf8,
col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1,
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8,
/*Indices*/
key (col_varchar_255_latin1_key),
key (col_int_key),
primary key (pk),
key (col_varchar_255_utf8_key),
key (col_varchar_10_latin1_key),
key (col_varchar_10_utf8_key)
);

INSERT INTO D VALUES 
('have', 'say', NULL, NULL, 'who', NULL, 'JBIYP', 'he''s', 'VWJOC', 'your', 'VDMAE') ,  
('YQAHN', 'GIKYQ', 3, NULL, 'no', 1933049856,'got', 'icz', 'RMHUR', 'MPCRS', 'so') ,
('had', 'PASVH', 2144141312, NULL, 'look', 808058880, 'czstj', 'z', 'MRRVF', 'UHWEV', 'that') ,  
('BSXXI', 'DLIRG', NULL, NULL, 'zstjzfsu', 1562116096, 'is', 'WKDGV', 'stj', 'tjzf', 'with') ,  
('JFSGY', 'c', 98828288, NULL, 'n', 269418496, 'LDJWY', 'jzfsu', 'QPXVX', 'zfsunwdg', 'we');

ALTER TABLE D MODIFY COLUMN col_int JSON;

# This query is unreliable in ndb. It may return
# SUM(col_int)	col_int
# 4572643328    1933049856 or
# 4572643328	NULL
SELECT SUM(col_int), col_int FROM D AS table1 WHERE table1.pk = table1.pk;

CREATE OR REPLACE VIEW view1 AS SELECT SUM(col_int), col_int FROM D AS table1
WHERE table1.pk = table1.pk;
SELECT * FROM view1;

SELECT  SUM(col_int), col_int FROM D AS table1 WHERE table1.pk = table1.pk;

SELECT * FROM view1;

DROP VIEW view1;
DROP TABLE D;

--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc

--echo #
--echo # Bug#29669251: !THD->IS_ERROR() IN JOIN_READ_CONST_TABLE()
--echo #
CREATE TABLE t(a INT);
INSERT INTO t VALUES(1),(2);
--error ER_INVALID_JSON_TEXT_IN_PARAM
WITH
  cte1 AS (SELECT STD(a) AS a1 FROM t ),
  cte2 AS (SELECT (JSON_INSERT('$.KEY3', NULL, NULL)) AS a2 FROM t)
  SELECT cte1.a1 FROM cte1,cte2 WHERE cte1.a1 LIKE cte2.a2;
DROP TABLE t;

--echo #
--echo # Bug#30348554: JSON COLUMN INTERACTION WITH CHAR() SEEMS INCONSISTENT
--echo #
# Used to return true, should return false.
SELECT JSON_ARRAY(_utf8mb4'abc') = JSON_ARRAY(_binary'abc');
CREATE TABLE t(j JSON);
INSERT INTO t VALUES (JSON_ARRAY('aaa'));
# Used to be a no-op, should replace the string with a blob.
UPDATE t SET j = JSON_REPLACE(j, '$[0]', REPEAT(CHAR(97), 3));
SELECT j, JSON_TYPE(j->'$[0]') FROM t;
DROP TABLE t;