src/ports/postgres/modules/utilities/test/transform_vec_cols.sql_in - madlib - Git at Google

 /* ----------------------------------------------------------------------- *//**
  *
  * Licensed to the Apache Software Foundation (ASF) under one
  * or more contributor license agreements.  See the NOTICE file
  * distributed with this work for additional information
  * regarding copyright ownership.  The ASF licenses this file
  * to you under the Apache License, Version 2.0 (the
  * "License"); you may not use this file except in compliance
  * with the License.  You may obtain a copy of the License at
  *
  *   http://www.apache.org/licenses/LICENSE-2.0
  *
  * Unless required by applicable law or agreed to in writing,
  * software distributed under the License is distributed on an
  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  * KIND, either express or implied.  See the License for the
  * specific language governing permissions and limitations
  * under the License.
  *
  *//* ----------------------------------------------------------------------- */

 -----------------------------------------------------------------------
 -- vec2cols
 -----------------------------------------------------------------------

 -- Create testing dataset

 DROP TABLE IF EXISTS dt_golf CASCADE;
 CREATE TABLE dt_golf (
     id integer NOT NULL,
     "OUTLOOK" text,
     temperature double precision,
     humidity double precision,
     "Temp_Humidity" double precision[],
     clouds_airquality text[],
     windy boolean,
     class text,
     observation_weight double precision
 );
 INSERT INTO dt_golf VALUES
 (1,'sunny', 85, 85, ARRAY[85, 85],ARRAY['none', 'unhealthy'], 'false','Don''t Play', 5.0),
 (2, 'sunny', 80, 90, ARRAY[80, 90], ARRAY['none', 'moderate'], 'true', 'Don''t Play', 5.0),
 (3, 'overcast', 83, 78, ARRAY[83, 78], ARRAY['low', 'moderate'], 'false', 'Play', 1.5),
 (4, 'rain', 70, 96, ARRAY[70, 96], ARRAY['low', 'moderate'], 'false', 'Play', 1.0),
 (5, 'rain', 68, 80, ARRAY[68, 80], ARRAY['medium', 'good'], 'false', 'Play', 1.0),
 (6, 'rain', 65, 70, ARRAY[65, 70], ARRAY['low', 'unhealthy'], 'true', 'Don''t Play', 1.0),
 (7, 'overcast', 64, 65, ARRAY[64, 65], ARRAY['medium', 'moderate'], 'true', 'Play', 1.5),
 (8, 'sunny', 72, 95, ARRAY[72, 95], ARRAY['high', 'unhealthy'], 'false', 'Don''t Play', 5.0),
 (9, 'sunny', 69, 70, ARRAY[69, 70], ARRAY['high', 'good'], 'false', 'Play', 5.0),
 (10, 'rain', 75, 80, ARRAY[75, 80], ARRAY['medium', 'good'], 'false', 'Play', 1.0),
 (11, 'sunny', 75, 70, ARRAY[75, 70], ARRAY['none', 'good'], 'true', 'Play', 5.0),
 (12, 'overcast', 72, 90, ARRAY[72, 90], ARRAY['medium', 'moderate'], 'true', 'Play', 1.5),
 (13, 'overcast', 81, 75, ARRAY[81, 75], ARRAY['medium', 'moderate'], 'false', 'Play', 1.5),
 (14, 'rain', 71, 80, ARRAY[71, 80], ARRAY['low', 'unhealthy'], 'true', 'Don''t Play', 1.0);

 -- Call the vec2cols function with different parameters on the 'clouds_airquality' column, to split it up

 DROP TABLE IF EXISTS out_table;
 SELECT vec2cols(
     'dt_golf',
     'out_table',
     'clouds_airquality'
 );

 SELECT f1 from out_table;
 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 2, 'Number of split columns does not match');

 DROP TABLE IF EXISTS out_table;
 SELECT vec2cols(
     'dt_golf',
     'out_table',
     'clouds_airquality',
     NULL,
     'id'
 );

 SELECT f1 from out_table;
 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 3, 'Number of split columns does not match');
 SELECT assert ((SELECT clouds_airquality[1] FROM dt_golf WHERE id = 1) = (SELECT f1 FROM out_table WHERE id = 1), 'Split values do not match up');
 SELECT assert ((SELECT clouds_airquality[2] FROM dt_golf WHERE id = 1) = (SELECT f2 FROM out_table WHERE id = 1), 'Split values do not match up');

 DROP TABLE IF EXISTS out_table;
 SELECT vec2cols(
     'dt_golf',
     'out_table',
     'clouds_airquality',
     ARRAY['clouds', 'air_quality']
 );

 SELECT clouds from out_table;
 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 2, 'Number of split columns does not match');

 DROP TABLE IF EXISTS out_table;
 SELECT vec2cols(
     'dt_golf',
     'out_table',
     'clouds_airquality',
     ARRAY['clouds', 'air_quality'],
     '"OUTLOOK", id'
 );

 SELECT clouds from out_table;
 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 4, 'Number of split columns does not match');
 SELECT assert ((SELECT clouds_airquality[1] FROM dt_golf WHERE id = 1) = (SELECT clouds FROM out_table WHERE id = 1), 'Split values do not match up');
 SELECT assert ((SELECT clouds_airquality[2] FROM dt_golf WHERE id = 1) = (SELECT air_quality FROM out_table WHERE id = 1), 'Split values do not match up');

 -- Testing splitting arrays of different lengths into features

 DROP TABLE IF EXISTS diff_lengths_test;
 CREATE TABLE diff_lengths_test(
     "id" INTEGER,
     "arr" TEXT[]);
 INSERT INTO diff_lengths_test VALUES (1, '{a, b}'), (2, '{c, d}'), (3, '{e, f, g, h}'), (4, '{i}'), (5, '{}');

 DROP TABLE IF EXISTS out_table;
 SELECT vec2cols(
     'diff_lengths_test',
     'out_table',
     'arr'
 );

 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = (SELECT max(array_upper(arr, 1)) from diff_lengths_test), 'Number of split columns does not match');

 DROP TABLE IF EXISTS out_table;
 SELECT vec2cols(
     'diff_lengths_test',
     'out_table',
     'arr',
     ARRAY['a']
 );

 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 1, 'Number of split columns does not match');

 DROP TABLE IF EXISTS out_table;
 SELECT vec2cols(
     'diff_lengths_test',
     'out_table',
     'arr',
     ARRAY['a', 'b', 'c']
 );

 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 3, 'Number of split columns does not match');

 DROP TABLE IF EXISTS out_table;
 SELECT vec2cols(
     'diff_lengths_test',
     'out_table',
     'arr',
     ARRAY['a', 'b', 'c', 'd', 'e', 'f', 'g']
 );

 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 7, 'Number of split columns does not match');

 -- Special character tests

 DROP TABLE IF EXISTS special_char_check;
 CREATE TABLE special_char_check(
     "id" INTEGER,
     "se$$,''x" TEXT,
     "len$,,$'%*()gth" TEXT[],
     "rin,$$Ж!#'gs" INTEGER,
     ",hel~!@#$%^&*()_+{}|:""<>?`-=[]\;',./'," TEXT,
     "$$hi//\\*$$" INTEGER,
     "**" INTEGER,
     "'~d())" TEXT);

 INSERT INTO special_char_check VALUES
 (1,'M''M',ARRAY['sd''f', 'ab,,c'],6,'sd''f',1,2,'ab,,c'),
 (2,'''M''M''',ARRAY['sdf$$sdfk(),', '$$sdlhf$$'],6,'sdf$$sdfk(),',1,2,'$$sdlhf$$'),
 (3,'M|$$M',ARRAY['%~(())Ж"', 'sdf'],6,'%~(())Ж"',1,2,'sdf'),
 (4,'M,M',ARRAY['sdf', 'sdf'],6,'sdf',1,2,'sdf'),
 (5,'M@[}(:*;M',ARRAY['sdf', 'sdf'],6,'sdf',1,2,'sdf'),
 (6,'M"M',ARRAY['sdf', 'sdf'],6,'sdf',1,2,'sdf'),
 (7,'MЖM',ARRAY['sdf', 'sdf'],6,'sdf',1,2,'sdf');

 DROP TABLE IF EXISTS out_table;
 SELECT vec2cols(
     'special_char_check',
     'out_table',
     '"len$,,$''%*()gth"',
     ARRAY['",cl''oЖu,ds,"', '"air_qu""ality"'],
     '*'
 );

 SELECT ",cl'oЖu,ds,", "air_qu""ality", ",hel~!@#$%^&*()_+{}|:""<>?`-=[]\;',./',", "$$hi//\\*$$", "**", "'~d())" from out_table;
 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 10, 'Number of split columns does not match');
 SELECT assert ((SELECT "len$,,$'%*()gth"[1] FROM special_char_check WHERE id = 1) = (SELECT ",cl'oЖu,ds," FROM out_table WHERE id = 1), 'Split values do not match up');
 SELECT assert ((SELECT "se$$,''x" FROM special_char_check WHERE id = 6) = 'M"M', 'Incorrect entries in out_table');

 DROP TABLE IF EXISTS out_table;
 SELECT vec2cols(
     'special_char_check',
     'out_table',
     '"len$,,$''%*()gth"',
     ARRAY['",cl''oЖu,ds,"', '"air_qu""ality"'],
     $__madlib__$id, "rin,$$Ж!#'gs", ",hel~!@#$%^&*()_+{}|:""<>?`-=[]\;',./',", "$$hi//\\*$$", "**"$__madlib__$
 );

 SELECT ",cl'oЖu,ds,", "air_qu""ality", id, "rin,$$Ж!#'gs", ",hel~!@#$%^&*()_+{}|:""<>?`-=[]\;',./',", "$$hi//\\*$$", "**" from out_table;
 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 7, 'Number of split columns does not match');
 SELECT assert ((SELECT "len$,,$'%*()gth"[1] FROM special_char_check WHERE id = 1) = (SELECT ",cl'oЖu,ds," FROM out_table WHERE id = 1), 'Split values do not match up');
 SELECT assert ((SELECT ",hel~!@#$%^&*()_+{}|:""<>?`-=[]\;',./'," FROM special_char_check WHERE id = 1) = 'sd''f', 'Incorrect entries in out_table');

 DROP TABLE IF EXISTS out_table;
 SELECT vec2cols(
     'special_char_check',
     'out_table',
     '"len$,,$''%*()gth"',
     ARRAY['",cl''oЖu,ds,"', '"air_qu""ality"'],
     '"**"'
 );

 SELECT "**", ",cl'oЖu,ds,", "air_qu""ality" from out_table;
 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 3, 'Number of split columns does not match');

 -----------------------------------------------------------------------
 -- cols2vec
 -----------------------------------------------------------------------

 DROP TABLE IF EXISTS out_table, out_table_summary;
 SELECT cols2vec(
     'dt_golf',
     'out_table',
     'temperature, humidity'
 );

 SELECT feature_vector from out_table;
 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 1, 'Number of output table columns does not match');

 DROP TABLE IF EXISTS out_table, out_table_summary;
 SELECT cols2vec(
     'dt_golf',
     'out_table',
     'temperature, humidity',
     NULL,
     'id'
 );

 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 2, 'Number of output table columns does not match');
 SELECT assert ((SELECT feature_vector from out_table where id = 1) = '{85, 85}', 'Output table values do not match up');

 -- All numeric types
 DROP TABLE IF EXISTS out_table, out_table_summary;
 SELECT cols2vec(
     'dt_golf',
     'out_table',
     'id, temperature, humidity, observation_weight',
     NULL,
     '*'
 );

 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 10, 'Number of output table columns does not match');
 SELECT assert ((SELECT feature_vector from out_table where id = 1) = '{1.0, 85.0, 85.0, 5.0}', 'Output table values do not match up');

 DROP TABLE IF EXISTS out_table, out_table_summary;
 SELECT cols2vec(
     'dt_golf',
     'out_table',
     'windy, temperature, humidity',
     'windy',
     '*'
 );

 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 10, 'Number of output table columns does not match');
 SELECT assert ((SELECT feature_vector from out_table where id = 1) = '{85, 85}', 'Output table values do not match up');

 DROP TABLE IF EXISTS out_table, out_table_summary;
 SELECT cols2vec(
     'dt_golf',
     'out_table',
     '*',
     '"OUTLOOK", "Temp_Humidity", clouds_airquality, windy, class, id',
     '*'
 );

 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 10, 'Number of output table columns does not match');
 SELECT assert ((SELECT feature_vector from out_table where id = 1) = '{85, 85, 5}', 'Output table values do not match up');

 DROP TABLE IF EXISTS out_table, out_table_summary;
 SELECT cols2vec(
     'dt_golf',
     'out_table',
     'windy, temperature, humidity',
     'windy, class',
     '*'
 );

 SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 10, 'Number of output table columns does not match');
 SELECT assert ((SELECT feature_vector from out_table where id = 1) = '{85, 85}', 'Output table values do not match up');

 -- Special character tests

 DROP TABLE IF EXISTS cols2vec_spcl;
 CREATE TABLE cols2vec_spcl (
     "i,Ж!#'d"               bigint,
     "lab$$''%*Ж!#''()el"    int,
     "fe''at1"               float,
     feat2                   float,
     "fe'%*()at3"            float,
     other_col               float,
     "se''x"                 TEXT
 );

 INSERT INTO cols2vec_spcl VALUES
 (1, 0, 1, 1, 0.5, 0.9,'M''M'),
 (2, 1, 0, 1, 0.3, 0.3,'M$M'),
 (3, 0, 0, 0, 0.1, 1.1,'M,M'),
 (4, 1, 1, 0, 0.9, 0.4,'M@[}(:*;M'),
 (5, 1, 0, 1, 0.85, 0.34,'M@[}(:*;M'),
 (6, 1, 0, 1, 0.63, 0.12,'M"M'),
 (7, 0, 1, 0, 0.7, 1.4,'MЖM');

 DROP TABLE IF EXISTS cols2vec_out, cols2vec_out_summary;
 SELECT cols2vec( 'cols2vec_spcl',  'cols2vec_out','"fe''''at1",feat2,"fe''%*()at3"',
                 NULL, '"i,Ж!#''d","lab$$''''%*Ж!#''''()el"' );

 SELECT assert(feature_vector = '{1,1,0.5}', 'Incorrect results for cols2vec')
 FROM cols2vec_out
 WHERE "i,Ж!#'d" = 1;

 DROP TABLE IF EXISTS cols2vec_out, cols2vec_out_summary;

 SELECT cols2vec('cols2vec_spcl',  'cols2vec_out','*', '"se''''x","i,Ж!#''d","lab$$''''%*Ж!#''''()el"',
                 '"i,Ж!#''d","lab$$''''%*Ж!#''''()el"');
 SELECT assert(feature_vector = '{1,1,0.5,0.9}',
               'Incorrect results for cols2vec')
 FROM cols2vec_out
 WHERE "i,Ж!#'d" = 1;

 -- Type casting tests

 DROP TABLE IF EXISTS cols2vec_casting_test;
 CREATE TABLE cols2vec_casting_test (
     a smallint,
     b int,
     c bigint,
     d decimal,
     e numeric,
     f real,
     g float,
     h double precision,
     i serial,
     j bigserial,
     k boolean,
     l boolean,
     m text,
     n varchar,
     o character varying,
     p char,
     q character,
     r smallint,
     s boolean,
     t boolean,
     u text[],
     v text[],
     w integer[],
     x text
 );

 INSERT INTO cols2vec_casting_test VALUES
 (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 'true', 'true', 'hello', 'hello', 'hello', 'h', 'h', 1, 'false', 'false', ARRAY['1','sdfsd','serw'], ARRAY['2','sdfsd','serw'], ARRAY[1, 2, 3], '1');

 DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
 SELECT cols2vec(
     'cols2vec_casting_test',
     'out_table',
     'a, b'
 );
 SELECT vec2cols(
     'out_table',
     'out_table2',
     'feature_vector'
 );
 SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'integer', 'Incorrect type casting of features for cols2vec');

 DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
 SELECT cols2vec(
     'cols2vec_casting_test',
     'out_table',
     'a, b, c'
 );
 SELECT vec2cols(
     'out_table',
     'out_table2',
     'feature_vector'
 );
 SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'bigint', 'Incorrect type casting of features for cols2vec');

 DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
 SELECT cols2vec(
     'cols2vec_casting_test',
     'out_table',
     'a, b, c, d, e, f, g, h, i, j'
 );
 SELECT vec2cols(
     'out_table',
     'out_table2',
     'feature_vector'
 );
 SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'double precision', 'Incorrect type casting of features for cols2vec');

 DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
 SELECT cols2vec(
     'cols2vec_casting_test',
     'out_table',
     'k, l'
 );
 SELECT vec2cols(
     'out_table',
     'out_table2',
     'feature_vector'
 );
 SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'boolean', 'Incorrect type casting of features for cols2vec');

 DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
 SELECT cols2vec(
     'cols2vec_casting_test',
     'out_table',
     'm, n, o, p, q'
 );
 SELECT vec2cols(
     'out_table',
     'out_table2',
     'feature_vector'
 );
 SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'text', 'Incorrect type casting of features for cols2vec');

 DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
 SELECT cols2vec(
     'cols2vec_casting_test',
     'out_table',
     'a, r'
 );
 SELECT vec2cols(
     'out_table',
     'out_table2',
     'feature_vector'
 );
 SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'smallint', 'Incorrect type casting of features for cols2vec');

 DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
 SELECT cols2vec(
     'cols2vec_casting_test',
     'out_table',
     's, t'
 );
 SELECT vec2cols(
     'out_table',
     'out_table2',
     'feature_vector'
 );
 SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'boolean', 'Incorrect type casting of features for cols2vec');

 DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
 SELECT cols2vec(
     'cols2vec_casting_test',
     'out_table',
     's'
 );
 SELECT vec2cols(
     'out_table',
     'out_table2',
     'feature_vector'
 );
 SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'boolean', 'Incorrect type casting of features for cols2vec');

 DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
 SELECT cols2vec(
     'cols2vec_casting_test',
     'out_table',
     's::int, a, b'
 );
 SELECT vec2cols(
     'out_table',
     'out_table2',
     'feature_vector'
 );
 SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'integer', 'Incorrect type casting of features for cols2vec');

 DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
 SELECT cols2vec(
     'cols2vec_casting_test',
     'out_table',
     'm, b::text'
 );
 SELECT vec2cols(
     'out_table',
     'out_table2',
     'feature_vector'
 );
 SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'text', 'Incorrect type casting of features for cols2vec');

 DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
 SELECT cols2vec(
     'cols2vec_casting_test',
     'out_table',
     'x::integer, b'
 );
 SELECT vec2cols(
     'out_table',
     'out_table2',
     'feature_vector'
 );
 SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'integer', 'Incorrect type casting of features for cols2vec');

 DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
 SELECT cols2vec(
     'cols2vec_casting_test',
     'out_table',
     'g::smallint, h::smallint'
 );
 SELECT vec2cols(
     'out_table',
     'out_table2',
     'feature_vector'
 );
 SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'smallint', 'Incorrect type casting of features for cols2vec');
	/* ----------------------------------------------------------------------- //*
	*
	* Licensed to the Apache Software Foundation (ASF) under one
	* or more contributor license agreements. See the NOTICE file
	* distributed with this work for additional information
	* regarding copyright ownership. The ASF licenses this file
	* to you under the Apache License, Version 2.0 (the
	* "License"); you may not use this file except in compliance
	* with the License. You may obtain a copy of the License at
	*
	* http://www.apache.org/licenses/LICENSE-2.0
	*
	* Unless required by applicable law or agreed to in writing,
	* software distributed under the License is distributed on an
	* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
	* KIND, either express or implied. See the License for the
	* specific language governing permissions and limitations
	* under the License.
	*
	// ----------------------------------------------------------------------- */

	-----------------------------------------------------------------------
	-- vec2cols
	-----------------------------------------------------------------------

	-- Create testing dataset

	DROP TABLE IF EXISTS dt_golf CASCADE;
	CREATE TABLE dt_golf (
	id integer NOT NULL,
	"OUTLOOK" text,
	temperature double precision,
	humidity double precision,
	"Temp_Humidity" double precision[],
	clouds_airquality text[],
	windy boolean,
	class text,
	observation_weight double precision
	);
	INSERT INTO dt_golf VALUES
	(1,'sunny', 85, 85, ARRAY[85, 85],ARRAY['none', 'unhealthy'], 'false','Don''t Play', 5.0),
	(2, 'sunny', 80, 90, ARRAY[80, 90], ARRAY['none', 'moderate'], 'true', 'Don''t Play', 5.0),
	(3, 'overcast', 83, 78, ARRAY[83, 78], ARRAY['low', 'moderate'], 'false', 'Play', 1.5),
	(4, 'rain', 70, 96, ARRAY[70, 96], ARRAY['low', 'moderate'], 'false', 'Play', 1.0),
	(5, 'rain', 68, 80, ARRAY[68, 80], ARRAY['medium', 'good'], 'false', 'Play', 1.0),
	(6, 'rain', 65, 70, ARRAY[65, 70], ARRAY['low', 'unhealthy'], 'true', 'Don''t Play', 1.0),
	(7, 'overcast', 64, 65, ARRAY[64, 65], ARRAY['medium', 'moderate'], 'true', 'Play', 1.5),
	(8, 'sunny', 72, 95, ARRAY[72, 95], ARRAY['high', 'unhealthy'], 'false', 'Don''t Play', 5.0),
	(9, 'sunny', 69, 70, ARRAY[69, 70], ARRAY['high', 'good'], 'false', 'Play', 5.0),
	(10, 'rain', 75, 80, ARRAY[75, 80], ARRAY['medium', 'good'], 'false', 'Play', 1.0),
	(11, 'sunny', 75, 70, ARRAY[75, 70], ARRAY['none', 'good'], 'true', 'Play', 5.0),
	(12, 'overcast', 72, 90, ARRAY[72, 90], ARRAY['medium', 'moderate'], 'true', 'Play', 1.5),
	(13, 'overcast', 81, 75, ARRAY[81, 75], ARRAY['medium', 'moderate'], 'false', 'Play', 1.5),
	(14, 'rain', 71, 80, ARRAY[71, 80], ARRAY['low', 'unhealthy'], 'true', 'Don''t Play', 1.0);

	-- Call the vec2cols function with different parameters on the 'clouds_airquality' column, to split it up

	DROP TABLE IF EXISTS out_table;
	SELECT vec2cols(
	'dt_golf',
	'out_table',
	'clouds_airquality'
	);

	SELECT f1 from out_table;
	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 2, 'Number of split columns does not match');

	DROP TABLE IF EXISTS out_table;
	SELECT vec2cols(
	'dt_golf',
	'out_table',
	'clouds_airquality',
	NULL,
	'id'
	);

	SELECT f1 from out_table;
	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 3, 'Number of split columns does not match');
	SELECT assert ((SELECT clouds_airquality[1] FROM dt_golf WHERE id = 1) = (SELECT f1 FROM out_table WHERE id = 1), 'Split values do not match up');
	SELECT assert ((SELECT clouds_airquality[2] FROM dt_golf WHERE id = 1) = (SELECT f2 FROM out_table WHERE id = 1), 'Split values do not match up');

	DROP TABLE IF EXISTS out_table;
	SELECT vec2cols(
	'dt_golf',
	'out_table',
	'clouds_airquality',
	ARRAY['clouds', 'air_quality']
	);

	SELECT clouds from out_table;
	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 2, 'Number of split columns does not match');

	DROP TABLE IF EXISTS out_table;
	SELECT vec2cols(
	'dt_golf',
	'out_table',
	'clouds_airquality',
	ARRAY['clouds', 'air_quality'],
	'"OUTLOOK", id'
	);

	SELECT clouds from out_table;
	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 4, 'Number of split columns does not match');
	SELECT assert ((SELECT clouds_airquality[1] FROM dt_golf WHERE id = 1) = (SELECT clouds FROM out_table WHERE id = 1), 'Split values do not match up');
	SELECT assert ((SELECT clouds_airquality[2] FROM dt_golf WHERE id = 1) = (SELECT air_quality FROM out_table WHERE id = 1), 'Split values do not match up');

	-- Testing splitting arrays of different lengths into features

	DROP TABLE IF EXISTS diff_lengths_test;
	CREATE TABLE diff_lengths_test(
	"id" INTEGER,
	"arr" TEXT[]);
	INSERT INTO diff_lengths_test VALUES (1, '{a, b}'), (2, '{c, d}'), (3, '{e, f, g, h}'), (4, '{i}'), (5, '{}');

	DROP TABLE IF EXISTS out_table;
	SELECT vec2cols(
	'diff_lengths_test',
	'out_table',
	'arr'
	);

	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = (SELECT max(array_upper(arr, 1)) from diff_lengths_test), 'Number of split columns does not match');

	DROP TABLE IF EXISTS out_table;
	SELECT vec2cols(
	'diff_lengths_test',
	'out_table',
	'arr',
	ARRAY['a']
	);

	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 1, 'Number of split columns does not match');

	DROP TABLE IF EXISTS out_table;
	SELECT vec2cols(
	'diff_lengths_test',
	'out_table',
	'arr',
	ARRAY['a', 'b', 'c']
	);

	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 3, 'Number of split columns does not match');

	DROP TABLE IF EXISTS out_table;
	SELECT vec2cols(
	'diff_lengths_test',
	'out_table',
	'arr',
	ARRAY['a', 'b', 'c', 'd', 'e', 'f', 'g']
	);

	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 7, 'Number of split columns does not match');

	-- Special character tests

	DROP TABLE IF EXISTS special_char_check;
	CREATE TABLE special_char_check(
	"id" INTEGER,
	"se$$,''x" TEXT,
	"len$,,$'%*()gth" TEXT[],
	"rin,$$Ж!#'gs" INTEGER,
	",hel~!@#$%^&*()_+{}\|:""<>?`-=[]\;',./'," TEXT,
	"$$hi//\\*$$" INTEGER,
	"**" INTEGER,
	"'~d())" TEXT);

	INSERT INTO special_char_check VALUES
	(1,'M''M',ARRAY['sd''f', 'ab,,c'],6,'sd''f',1,2,'ab,,c'),
	(2,'''M''M''',ARRAY['sdf$$sdfk(),', '$$sdlhf$$'],6,'sdf$$sdfk(),',1,2,'$$sdlhf$$'),
	(3,'M\|$$M',ARRAY['%~(())Ж"', 'sdf'],6,'%~(())Ж"',1,2,'sdf'),
	(4,'M,M',ARRAY['sdf', 'sdf'],6,'sdf',1,2,'sdf'),
	(5,'M@[}(:*;M',ARRAY['sdf', 'sdf'],6,'sdf',1,2,'sdf'),
	(6,'M"M',ARRAY['sdf', 'sdf'],6,'sdf',1,2,'sdf'),
	(7,'MЖM',ARRAY['sdf', 'sdf'],6,'sdf',1,2,'sdf');

	DROP TABLE IF EXISTS out_table;
	SELECT vec2cols(
	'special_char_check',
	'out_table',
	'"len$,,$''%*()gth"',
	ARRAY['",cl''oЖu,ds,"', '"air_qu""ality"'],
	'*'
	);

	SELECT ",cl'oЖu,ds,", "air_qu""ality", ",hel~!@#$%^&()_+{}\|:""<>?`-=[]\;',./',", "$$hi//\\$$", "**", "'~d())" from out_table;
	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 10, 'Number of split columns does not match');
	SELECT assert ((SELECT "len$,,$'%*()gth"[1] FROM special_char_check WHERE id = 1) = (SELECT ",cl'oЖu,ds," FROM out_table WHERE id = 1), 'Split values do not match up');
	SELECT assert ((SELECT "se$$,''x" FROM special_char_check WHERE id = 6) = 'M"M', 'Incorrect entries in out_table');

	DROP TABLE IF EXISTS out_table;
	SELECT vec2cols(
	'special_char_check',
	'out_table',
	'"len$,,$''%*()gth"',
	ARRAY['",cl''oЖu,ds,"', '"air_qu""ality"'],
	$__madlib__$id, "rin,$$Ж!#'gs", ",hel~!@#$%^&()_+{}\|:""<>?`-=[]\;',./',", "$$hi//\\$$", "**"$__madlib__$
	);

	SELECT ",cl'oЖu,ds,", "air_qu""ality", id, "rin,$$Ж!#'gs", ",hel~!@#$%^&()_+{}\|:""<>?`-=[]\;',./',", "$$hi//\\$$", "**" from out_table;
	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 7, 'Number of split columns does not match');
	SELECT assert ((SELECT "len$,,$'%*()gth"[1] FROM special_char_check WHERE id = 1) = (SELECT ",cl'oЖu,ds," FROM out_table WHERE id = 1), 'Split values do not match up');
	SELECT assert ((SELECT ",hel~!@#$%^&*()_+{}\|:""<>?`-=[]\;',./'," FROM special_char_check WHERE id = 1) = 'sd''f', 'Incorrect entries in out_table');

	DROP TABLE IF EXISTS out_table;
	SELECT vec2cols(
	'special_char_check',
	'out_table',
	'"len$,,$''%*()gth"',
	ARRAY['",cl''oЖu,ds,"', '"air_qu""ality"'],
	'"**"'
	);

	SELECT "**", ",cl'oЖu,ds,", "air_qu""ality" from out_table;
	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 3, 'Number of split columns does not match');

	-----------------------------------------------------------------------
	-- cols2vec
	-----------------------------------------------------------------------

	DROP TABLE IF EXISTS out_table, out_table_summary;
	SELECT cols2vec(
	'dt_golf',
	'out_table',
	'temperature, humidity'
	);

	SELECT feature_vector from out_table;
	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 1, 'Number of output table columns does not match');

	DROP TABLE IF EXISTS out_table, out_table_summary;
	SELECT cols2vec(
	'dt_golf',
	'out_table',
	'temperature, humidity',
	NULL,
	'id'
	);

	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 2, 'Number of output table columns does not match');
	SELECT assert ((SELECT feature_vector from out_table where id = 1) = '{85, 85}', 'Output table values do not match up');

	-- All numeric types
	DROP TABLE IF EXISTS out_table, out_table_summary;
	SELECT cols2vec(
	'dt_golf',
	'out_table',
	'id, temperature, humidity, observation_weight',
	NULL,
	'*'
	);

	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 10, 'Number of output table columns does not match');
	SELECT assert ((SELECT feature_vector from out_table where id = 1) = '{1.0, 85.0, 85.0, 5.0}', 'Output table values do not match up');

	DROP TABLE IF EXISTS out_table, out_table_summary;
	SELECT cols2vec(
	'dt_golf',
	'out_table',
	'windy, temperature, humidity',
	'windy',
	'*'
	);

	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 10, 'Number of output table columns does not match');
	SELECT assert ((SELECT feature_vector from out_table where id = 1) = '{85, 85}', 'Output table values do not match up');

	DROP TABLE IF EXISTS out_table, out_table_summary;
	SELECT cols2vec(
	'dt_golf',
	'out_table',
	'*',
	'"OUTLOOK", "Temp_Humidity", clouds_airquality, windy, class, id',
	'*'
	);

	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 10, 'Number of output table columns does not match');
	SELECT assert ((SELECT feature_vector from out_table where id = 1) = '{85, 85, 5}', 'Output table values do not match up');

	DROP TABLE IF EXISTS out_table, out_table_summary;
	SELECT cols2vec(
	'dt_golf',
	'out_table',
	'windy, temperature, humidity',
	'windy, class',
	'*'
	);

	SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 10, 'Number of output table columns does not match');
	SELECT assert ((SELECT feature_vector from out_table where id = 1) = '{85, 85}', 'Output table values do not match up');

	-- Special character tests

	DROP TABLE IF EXISTS cols2vec_spcl;
	CREATE TABLE cols2vec_spcl (
	"i,Ж!#'d" bigint,
	"lab$$''%*Ж!#''()el" int,
	"fe''at1" float,
	feat2 float,
	"fe'%*()at3" float,
	other_col float,
	"se''x" TEXT
	);

	INSERT INTO cols2vec_spcl VALUES
	(1, 0, 1, 1, 0.5, 0.9,'M''M'),
	(2, 1, 0, 1, 0.3, 0.3,'M$M'),
	(3, 0, 0, 0, 0.1, 1.1,'M,M'),
	(4, 1, 1, 0, 0.9, 0.4,'M@[}(:*;M'),
	(5, 1, 0, 1, 0.85, 0.34,'M@[}(:*;M'),
	(6, 1, 0, 1, 0.63, 0.12,'M"M'),
	(7, 0, 1, 0, 0.7, 1.4,'MЖM');

	DROP TABLE IF EXISTS cols2vec_out, cols2vec_out_summary;
	SELECT cols2vec( 'cols2vec_spcl', 'cols2vec_out','"fe''''at1",feat2,"fe''%*()at3"',
	NULL, '"i,Ж!#''d","lab$$''''%*Ж!#''''()el"' );

	SELECT assert(feature_vector = '{1,1,0.5}', 'Incorrect results for cols2vec')
	FROM cols2vec_out
	WHERE "i,Ж!#'d" = 1;

	DROP TABLE IF EXISTS cols2vec_out, cols2vec_out_summary;

	SELECT cols2vec('cols2vec_spcl', 'cols2vec_out','', '"se''''x","i,Ж!#''d","lab$$''''%Ж!#''''()el"',
	'"i,Ж!#''d","lab$$''''%*Ж!#''''()el"');
	SELECT assert(feature_vector = '{1,1,0.5,0.9}',
	'Incorrect results for cols2vec')
	FROM cols2vec_out
	WHERE "i,Ж!#'d" = 1;

	-- Type casting tests

	DROP TABLE IF EXISTS cols2vec_casting_test;
	CREATE TABLE cols2vec_casting_test (
	a smallint,
	b int,
	c bigint,
	d decimal,
	e numeric,
	f real,
	g float,
	h double precision,
	i serial,
	j bigserial,
	k boolean,
	l boolean,
	m text,
	n varchar,
	o character varying,
	p char,
	q character,
	r smallint,
	s boolean,
	t boolean,
	u text[],
	v text[],
	w integer[],
	x text
	);

	INSERT INTO cols2vec_casting_test VALUES
	(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 'true', 'true', 'hello', 'hello', 'hello', 'h', 'h', 1, 'false', 'false', ARRAY['1','sdfsd','serw'], ARRAY['2','sdfsd','serw'], ARRAY[1, 2, 3], '1');

	DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
	SELECT cols2vec(
	'cols2vec_casting_test',
	'out_table',
	'a, b'
	);
	SELECT vec2cols(
	'out_table',
	'out_table2',
	'feature_vector'
	);
	SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'integer', 'Incorrect type casting of features for cols2vec');

	DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
	SELECT cols2vec(
	'cols2vec_casting_test',
	'out_table',
	'a, b, c'
	);
	SELECT vec2cols(
	'out_table',
	'out_table2',
	'feature_vector'
	);
	SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'bigint', 'Incorrect type casting of features for cols2vec');

	DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
	SELECT cols2vec(
	'cols2vec_casting_test',
	'out_table',
	'a, b, c, d, e, f, g, h, i, j'
	);
	SELECT vec2cols(
	'out_table',
	'out_table2',
	'feature_vector'
	);
	SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'double precision', 'Incorrect type casting of features for cols2vec');

	DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
	SELECT cols2vec(
	'cols2vec_casting_test',
	'out_table',
	'k, l'
	);
	SELECT vec2cols(
	'out_table',
	'out_table2',
	'feature_vector'
	);
	SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'boolean', 'Incorrect type casting of features for cols2vec');

	DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
	SELECT cols2vec(
	'cols2vec_casting_test',
	'out_table',
	'm, n, o, p, q'
	);
	SELECT vec2cols(
	'out_table',
	'out_table2',
	'feature_vector'
	);
	SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'text', 'Incorrect type casting of features for cols2vec');

	DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
	SELECT cols2vec(
	'cols2vec_casting_test',
	'out_table',
	'a, r'
	);
	SELECT vec2cols(
	'out_table',
	'out_table2',
	'feature_vector'
	);
	SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'smallint', 'Incorrect type casting of features for cols2vec');

	DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
	SELECT cols2vec(
	'cols2vec_casting_test',
	'out_table',
	's, t'
	);
	SELECT vec2cols(
	'out_table',
	'out_table2',
	'feature_vector'
	);
	SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'boolean', 'Incorrect type casting of features for cols2vec');

	DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
	SELECT cols2vec(
	'cols2vec_casting_test',
	'out_table',
	's'
	);
	SELECT vec2cols(
	'out_table',
	'out_table2',
	'feature_vector'
	);
	SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'boolean', 'Incorrect type casting of features for cols2vec');

	DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
	SELECT cols2vec(
	'cols2vec_casting_test',
	'out_table',
	's::int, a, b'
	);
	SELECT vec2cols(
	'out_table',
	'out_table2',
	'feature_vector'
	);
	SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'integer', 'Incorrect type casting of features for cols2vec');

	DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
	SELECT cols2vec(
	'cols2vec_casting_test',
	'out_table',
	'm, b::text'
	);
	SELECT vec2cols(
	'out_table',
	'out_table2',
	'feature_vector'
	);
	SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'text', 'Incorrect type casting of features for cols2vec');

	DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
	SELECT cols2vec(
	'cols2vec_casting_test',
	'out_table',
	'x::integer, b'
	);
	SELECT vec2cols(
	'out_table',
	'out_table2',
	'feature_vector'
	);
	SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'integer', 'Incorrect type casting of features for cols2vec');

	DROP TABLE IF EXISTS out_table, out_table_summary, out_table2;
	SELECT cols2vec(
	'cols2vec_casting_test',
	'out_table',
	'g::smallint, h::smallint'
	);
	SELECT vec2cols(
	'out_table',
	'out_table2',
	'feature_vector'
	);
	SELECT assert((select data_type from information_schema.columns where table_name = 'out_table2' limit 1) = 'smallint', 'Incorrect type casting of features for cols2vec');