###### nist-schema.test ######
--disable_abort_on_error


####### ! The tests MUST run in our testing area ! #########
--source include/testdb_only.inc

   CREATE TABLE BASE_VS1 (C1 INT, C2 INT);
   # Without this VIEW the problem disappears
   CREATE VIEW VS2 AS
     SELECT A.C1 FROM BASE_VS1 A WHERE EXISTS
       (SELECT B.C2 FROM BASE_VS1 B WHERE B.C2 = A.C1);
   # Without this VIEW the problem disappears
   CREATE VIEW VS3 AS
     SELECT A.C2 FROM BASE_VS1 A WHERE A.C2 IN
       (SELECT B.C1 FROM BASE_VS1 B WHERE B.C1 < A.C2);

# harmless   (no subquery)
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES;

# 100 % CPU   low stress (subquery in from part)
SELECT 1 FROM (
     SELECT * FROM INFORMATION_SCHEMA.COLUMNS
     ) my_tab;

# 100 % CPU   more? stress (EXISTS + subquery in where part)
   SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
   WHERE NOT EXISTS (
     SELECT * FROM INFORMATION_SCHEMA.COLUMNS
     );

# 100 % CPU   full stress (EXISTS + correlated subquery in where part)
# The original NIST test statement.
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A
   WHERE NOT EXISTS (
     SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
     WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
     AND A.TABLE_NAME = B.TABLE_NAME);