Bug #11689 success on Create view .. IF(col1 IS NULL,...), col2 ; but SELECT fails
Submitted: 1 Jul 2005 13:58 Modified: 17 Aug 2005 1:07
Reporter: Matthias Leich Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0 OS:
Assigned to: Igor Babaev CPU Architecture:Any

[1 Jul 2005 13:58] Matthias Leich
Description:
Example:
# positive testcase -- View with column alias for (f1 IS NULL, ..
CREATE VIEW v1 AS SELECT IF(f1 IS NULL, 'IS     NULL',
'IS NOT NULL') AS "my_boolean", f1, id FROM t1;
SHOW CREATE VIEW v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select if(isnull(`test`.`t1`.`f1`),_latin1'IS     NULL',_latin1'IS NOT NULL') AS `my_boolean`,`test`.`t1`.`f1` AS `f1`,`test`.`t1`.`id` AS `id` from `test`.`t1`
SELECT * FROM v1;
my_boolean	f1	id
DROP VIEW v1;
# negative testcase -- No column alias for IF(f1 IS NULL, ....
CREATE VIEW v1 AS SELECT IF(f1 IS NULL, 'IS     NULL',
'IS NOT NULL'), f1, id FROM t1;
SHOW CREATE VIEW v1;
ERROR 42S02: Unknown table 'test.t1' in field list
SELECT * FROM v1;
ERROR 42S02: Unknown table 'test.t1' in field list

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1989.2.1, 2005-06-23

I guess it is unlikely that somebody creates a view
without giving "IF(f1 IS NUL, ....)" an useful column alias.

How to repeat:
Please use the attached testcase ml024.test.

  copy it to mysql-test/t
  touch r/ml024.result     # Produce a dummy file with 
                                    # expected results
  ./mysql-test-run ml024
  inspect r/ml024.reject
[1 Jul 2005 13:59] Matthias Leich
testcase

Attachment: ml024.test (application/test, text), 669 bytes.

[17 Aug 2005 1:07] Igor Babaev
For 5.0.12 I had:
mysql> CREATE VIEW v2 AS SELECT IF(f1 IS NULL, 'IS     NULL','IS NOT NULL'), f1, id FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE VIEW v2;
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------+
| View | Create View
                                                                              |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------+
| v2   | CREATE ALGORITHM=UNDEFINED VIEW `test`.`v2` AS select if(isnull(`test`.`t1`.`f1`),_latin1'IS     NULL',_latin1'IS NOT NULL') AS `IF(f1 IS NULL, 'IS     NULL','IS N
OT NULL')`,`test`.`t1`.`f1` AS `f1`,`test`.`t1`.`id` AS `id` from `test`.`t1` |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------+
1 row in set (0.00 sec)