Selected Data will not insert into table - version 5.6.29-76.2

[Michelle]

I have come across two scenarios where data that CLEARLY is being returned from a SELECT statement, does not and will no insert into a table:

For the below examples, the BUG is happning on VERSION "5.6.29-76.2." I have confirmed the below works perfectly on Versions "5.6.28-76.1" as well as MySQL version "5.6.20-1"

-----------------------------------
EXAMPLE #1:

When using the following FUNCTION in a SELECT:

    IF((YEAR(RIGHT(T.TABLE_NAME, 8)) IS NULL), 1, 0) = 1

where the TABLE_NAME may contain anything, and the function essentially return NULL, the following SELECT works fine:

SELECT
    T.TABLE_NAME
# select T.*, YEAR(RIGHT(TABLE_NAME, 8)) IS NULL, IF((YEAR(RIGHT(TABLE_NAME, 8)) IS NULL), 1, 0)
FROM information_schema.tables T
where T.TABLE_SCHEMA = 'starterview'
AND T.TABLE_TYPE='BASE TABLE'
AND LEFT(T.TABLE_NAME, 3) <> 'rpt'
AND LEFT(T.TABLE_NAME, 3) <> 'OLD'
AND LEFT(T.TABLE_NAME, 1) <> '_'
AND LEFT(T.TABLE_NAME, 3) <> 'tmp'
AND RIGHT(T.TABLE_NAME, 4) <> '_bak'
AND TABLE_NAME NOT LIKE ('%\_bak\_%')
AND IF((YEAR(RIGHT(T.TABLE_NAME, 8)) IS NULL), 1, 0) = 1
;

BUT, the minute you try to insert these data into a TABLE, the message "0 rows(s) affected" is displayed, and the table not created:

create temporary table x as
SELECT
    T.TABLE_NAME
# select T.*, YEAR(RIGHT(TABLE_NAME, 8)) IS NULL, IF((YEAR(RIGHT(TABLE_NAME, 8)) IS NULL), 1, 0)
FROM information_schema.tables T
where T.TABLE_SCHEMA = 'starterview'
AND T.TABLE_TYPE='BASE TABLE'
AND LEFT(T.TABLE_NAME, 3) <> 'rpt'
AND LEFT(T.TABLE_NAME, 3) <> 'OLD'
AND LEFT(T.TABLE_NAME, 1) <> '_'
AND LEFT(T.TABLE_NAME, 3) <> 'tmp'
AND RIGHT(T.TABLE_NAME, 4) <> '_bak'
AND TABLE_NAME NOT LIKE ('%\_bak\_%')
AND IF((YEAR(RIGHT(T.TABLE_NAME, 8)) IS NULL), 1, 0) = 1
;

Again, this works fine in version 5.2.28-76.1, 5.6.20-1, but DOES NOT WORK IN Version 5.6.29-76.2.

------------------------------------------------------------------------------------
EXAMPLE #2

(1) Setup: Create two small tables, and place "select *" views on top of them:

-- DROP table test_table ;
create table test_table (
 id integer not null
,descr varchar(100)
)
;

-- DROP table test_table ;
create table test_table_2 (
 id integer not null
,descr varchar(100)
)
;

DROP VIEW if exists v_test_table ;
CREATE VIEW v_test_table AS
SELECT * from test_table
;

DROP VIEW if exists v_test_table_2 ;
CREATE VIEW v_test_table_2 AS
SELECT * from test_table_2
;

(2) Create a table that SELECTs all columns for all views in the DB:

-- DROP TABLE IF EXISTS ztmp_existing_view_cols;
CREATE TABLE ztmp_existing_view_cols (
 TABLE_SCHEMA VARCHAR(35)
 ,TABLE_NAME VARCHAR(75)
 ,COLUMN_NAME VARCHAR(75)
 ,COLUMN_TYPE VARCHAR(200)
 ,IS_NULLABLE VARCHAR(3)
 ,COLUMN_DEFAULT VARCHAR(50)
)
select
 C.TABLE_SCHEMA
 ,C.TABLE_NAME
 ,C.COLUMN_NAME
 ,C.COLUMN_TYPE
# ,C.COLUMN_KEY
 ,C.IS_NULLABLE
# ,C.EXTRA
 ,C.COLUMN_DEFAULT
from information_schema.columns C
INNER JOIN information_schema.tables T
 ON C.table_name = T.table_name
 AND C.table_schema = T.table_schema
 AND T.TABLE_TYPE = 'VIEW'
where C.table_schema = 'test'
# AND C.TABLE_NAME = 'v_test_table'
;

(3) ALTER the first table to add a column:

alter table test_table
add column `third_col` varchar(500)
;

(4) Recreate that VIEW:

DROP VIEW if exists v_test_table ;
CREATE VIEW v_test_table AS
SELECT * from test_table
;

(5) Execute #2 above again. (Drop the table first so that it is recreated). You'll see "5 rows affected" and you'll see the data in the table;

(6) Drop the column from the table that it was just added to:

alter table test_table
 drop column `third_col`
;

(7) Execute #2 above again, dropping the table first. You'll see "0 rows affected" and that if you try to select on the table, it does not exist.

(8) Just execute the SELECT portion from #2, and you'll see data being returned. These records should be inserted into the target table.

This DOES NOT WORK in VERSION "5.6.29-76.2," but does work in Versions "5.2.28-76.1" and "5.6.20-1"

Thank you,
Michelle

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3422