| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0 | OS: | |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[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)
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