###### nist_views.test ######
--disable_abort_on_error

--disable_warnings
DROP TABLE IF EXISTS t1, t2;
DROP VIEW  IF EXISTS v1;
--enable_warnings

# Test cases based on NIST dml130

-- TEST:0680 INFO_SCHEM:  View data types!

   CREATE TABLE t1 ( FLAAG CHAR DEFAULT 'Y');

   COMMIT;

   CREATE TABLE t2 (
  PITCH SMALLINT);

   COMMIT;

  CREATE VIEW v1 AS
  SELECT * FROM t1, t2;
  #### SELECT * could not be problematic, because
  #### SELECT FLAAG, PITCH gives the same crash
  # CREATE VIEW v1 AS
  # SELECT FLAAG, PITCH FROM t1, t2;
  #### The Join within the VIEW seems to be needed, because
  #### disappears, when a simple VIEW is used
  # CREATE VIEW v1 AS SELECT * FROM t1;

   COMMIT;

# These two slightly modified statements do NOT crash
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS;
SELECT @got_val:= COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS;

###########################################################
# extreme simplified version causing the crash
# The set @got_val= ( <statement> ) seems to be essential.
# !!! crash
set @got_val= (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS);
# If you dislike the set@got_val= <subquery>, you can also use
# !!! crash
SELECT 'HELLO' FROM ( SELECT 1 as my_col) AS MY_TAB
WHERE 0 < (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS);
###########################################################

# The full test case used within my converted NIST test

  DROP VIEW v1;
  DROP TABLE t1, t2;

   CREATE TABLE t1 (
  FLAAG CHAR DEFAULT 'Y', KODE CHAR (5) NOT NULL,
  AMPL REAL DEFAULT 3,
  COORD DOUBLE PRECISION, SWAY FLOAT, REVS INTEGER);

  CREATE TABLE t2 (
  PITCH SMALLINT, DEGREE NUMERIC (3, 2), RUDDER DECIMAL (2));

   CREATE VIEW v1 AS
  SELECT * FROM t1, t2;

# !!! crash
set @got_val= (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'v1'
  AND COLUMN_NAME = 'FLAAG' AND ORDINAL_POSITION = 1
  AND COLUMN_DEFAULT = '''Y''' AND DATA_TYPE = 'CHARACTER'
  AND CHARACTER_MAXIMUM_LENGTH = 1 AND CHARACTER_OCTET_LENGTH
  > 0 AND NUMERIC_PRECISION IS NULL
  AND NUMERIC_SCALE IS NULL

  AND IS_NULLABLE = 'YES');