MySQL Bugs: #19341: Empty query from information

Bug #19341	Empty query from information_schema.columns
Submitted:	25 Apr 2006 17:03	Modified:	11 Jul 2006 13:55
Reporter:	Mauricio Orizola	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.20a	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
When a query (SELECT) is made from information_schema.columns using function calls on the columns definition of the SELECT, views using function calls on the columns definition do not appear on the result.

How to repeat:
CREATE VIEW 'view_temp'
SELECT field1, field2, func1();

SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'view_temp';

will return results.

but

SELECT *, func2() FROM information_schema.COLUMNS WHERE TABLE_NAME = 'view_temp';

will not return result.s

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour you described with today's 5.0.22-BK build on Linux:

mysql> create function func1() returns int return 1;
Query OK, 0 rows affected (0.02 sec)

mysql> create function func2() returns int return 2;
Query OK, 0 rows affected (0.01 sec)

mysql> create view view_tem as select 1, 2, func1();
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'view_tem'\G
*************************** 1. row ***************************
           TABLE_CATALOG: NULL
            TABLE_SCHEMA: db19372
              TABLE_NAME: view_tem
             COLUMN_NAME: 1
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: 0
             IS_NULLABLE: NO
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: bigint(1)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
*************************** 2. row ***************************
           TABLE_CATALOG: NULL
            TABLE_SCHEMA: db19372
              TABLE_NAME: view_tem
             COLUMN_NAME: 2
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: 0
             IS_NULLABLE: NO
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: bigint(1)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
*************************** 3. row ***************************
           TABLE_CATALOG: NULL
            TABLE_SCHEMA: db19372
              TABLE_NAME: view_tem
             COLUMN_NAME: func1()
        ORDINAL_POSITION: 3
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: bigint(11)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
3 rows in set (0.01 sec)

mysql> SELECT *, func2() FROM information_schema.COLUMNS WHERE TABLE_NAME = 'view_tem'\G
*************************** 1. row ***************************
           TABLE_CATALOG: NULL
            TABLE_SCHEMA: db19372
              TABLE_NAME: view_tem
             COLUMN_NAME: 1
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: 0
             IS_NULLABLE: NO
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: bigint(1)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
                 func2(): 2
*************************** 2. row ***************************
           TABLE_CATALOG: NULL
            TABLE_SCHEMA: db19372
              TABLE_NAME: view_tem
             COLUMN_NAME: 2
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: 0
             IS_NULLABLE: NO
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: bigint(1)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
                 func2(): 2
*************************** 3. row ***************************
           TABLE_CATALOG: NULL
            TABLE_SCHEMA: db19372
              TABLE_NAME: view_tem
             COLUMN_NAME: func1()
        ORDINAL_POSITION: 3
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: bigint(11)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
                 func2(): 2
3 rows in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.22    |
+-----------+
1 row in set (0.00 sec)

Thank Valeriy for your prompt response.

Your view has constant arguments, the columns have to come from a table.
The last query has to be in a view.

By the way, my version is 5.0.20a and you tested on 5.0.22.

Thanks in advance

OK. I created the simplest test possible according to your description. Please, provide a more detailed test case. 

I had tested on the latest version because it is our policy. If the bug is not repeatable in it, then you just have to wait for it to be officially released.

"prueba" database

Attachment: prueba.zip (application/x-zip-compressed, text), 29.24 KiB.

"codificador" database

Attachment: codificador.zip (application/x-zip-compressed, text), 2.34 KiB.

Valeriy,

I attached the scripts to create the databases involved in this issue. The view to reproduce the problem is "codifocador.view_codigo". First this view filters by TABLE_SCHEMA = 'prueba' AND TABLE_NAME = 'view_exportaciones', which you won't get any result with although "prueba.view_exportaciones" exists. Second if you edit the view and change the filter to TABLE_SCHEMA = 'prueba' AND TABLE_NAME = 'exportaciones', you'll get results. If you don't have any problem, as you said, the bug was already fixed.

Regards,
Mauricio

Please, try to repeat with a newer version, 5.0.22, now officially released. Inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".