Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11672

mysql_list_field() returns wrong default values for VIEW

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
    • Fix Version/s: 10.3.0
    • Component/s: Views
    • Labels:

      Description

      I create a table and a VIEW on in:

      CREATE OR REPLACE TABLE t1 (
        i1 INT NOT NULL DEFAULT 0,
        i3 BIGINT UNSIGNED NOT NULL DEFAULT 0xFFFFFFFFFFFFFFFF,
        s1 VARCHAR(10) CHARACTER SET latin1 NOT NULL DEFAULT 's1def',
        d1 DECIMAL(31,1) NOT NULL DEFAULT 111111111122222222223333333333.9,
        t1 DATETIME(6) NOT NULL DEFAULT '2001-01-01 10:20:30.123456',
        e1 ENUM('a','b') NOT NULL DEFAULT 'a'
      );
      CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;

      Now I run these two queries:

      SHOW FIELDS IN t1;
      SHOW FIELDS IN v1;
      

      Both return the same result:

      +-------+---------------------+------+-----+----------------------------------+-------+
      | Field | Type                | Null | Key | Default                          | Extra |
      +-------+---------------------+------+-----+----------------------------------+-------+
      | i1    | int(11)             | NO   |     | 0                                |       |
      | i3    | bigint(20) unsigned | NO   |     | 18446744073709551615             |       |
      | s1    | varchar(10)         | NO   |     | s1def                            |       |
      | d1    | decimal(31,1)       | NO   |     | 111111111122222222223333333333.9 |       |
      | t1    | datetime(6)         | NO   |     | 2001-01-01 10:20:30.123456       |       |
      | e1    | enum('a','b')       | NO   |     | a                                |       |
      +-------+---------------------+------+-----+----------------------------------+-------+
      

      So far so good.

      Now I test mysql_list_fields() for t1 and v1.

      This is the test client program:

      #include <stdio.h>
      #include <mysql/mysql.h>
       
      static int list_fields(MYSQL *mysql, const char *table_name)
      {
        int i;
        MYSQL_RES *res;
        unsigned int field_cnt;
        
        res= mysql_list_fields(mysql, table_name, "%");
        if (!res)
        {
          printf("Error: %s\n", mysql_error(mysql));
          return 1;
        }
        field_cnt= mysql_num_fields(res);
        printf("Number of columns: %d\n", field_cnt);
       
        for (i=0; i < field_cnt; ++i)
        {
          /* col describes i-th column of the table */
          MYSQL_FIELD *col = mysql_fetch_field_direct(res, i);
          printf ("Column[%d] name=%s type=%d charsetnr=%d def='%s'\n",
                  i, col->name, col->type, col->charsetnr, col->def);
        }
        mysql_free_result(res);
        return 0;
      }
       
      int main(int ac, char **av)
      {
        MYSQL mysql;
        mysql_init(&mysql);
        if (!mysql_real_connect(&mysql, "localhost", "root", NULL, "test",
                                3306, "/tmp/mysql.sock", 0))
        {
          printf("Error: could not connect\n");
          return 1;
        }
        mysql_set_character_set(&mysql, "utf8");
        list_fields(&mysql, av[1] ? av[1] : "t1");
        mysql_close(&mysql);
        return 0;
      }
      

      I compile the above program and run:

      ./list_fields t1
      

      Number of columns: 6
      Column[0] name=i1 type=3 length=11 charsetnr=63 def='0'
      Column[1] name=i3 type=8 length=20 charsetnr=63 def='18446744073709551615'
      Column[2] name=s1 type=253 length=30 charsetnr=33 def='s1def'
      Column[3] name=d1 type=246 length=33 charsetnr=63 def='111111111122222222223333333333.9'
      Column[4] name=t1 type=12 length=26 charsetnr=63 def='2001-01-01 10:20:30.123456'
      Column[5] name=e1 type=254 length=3 charsetnr=33 def='a'
      

      ./list_fields v1
      

      Number of columns: 6
      Column[0] name=i1 type=3 length=11 charsetnr=63 def='0'
      Column[1] name=i3 type=8 length=20 charsetnr=63 def='18446744073709552000'
      Column[2] name=s1 type=253 length=30 charsetnr=33 def='0'
      Column[3] name=d1 type=246 length=33 charsetnr=63 def='111111111122222220000000000000'
      Column[4] name=t1 type=12 length=26 charsetnr=63 def='20010101102030'
      Column[5] name=e1 type=254 length=3 charsetnr=33 def='1'
      

      All default values in the table are fine.
      Obvervations about default values in the view:

      • i1 is fine
      • i3 lost precision (the 4 rightmost digits are wrong)
      • s1 returned 0 instead of s1def
      • d1 lost precision and lost fractional digits
      • t1 lost datetime formatting and lost fractional digits
      • e1 lost the original string value and returned its numeric equivalent

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: