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

mysql_list_field() returns wrong default values for VIEW

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
    • 10.3.0
    • Views

    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

          blocks

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-4912 Data type plugin API version 1

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            Description I create a table and a VIEW on in:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (
              i1 INT NOT NULL DEFAULT 0,
              s1 VARCHAR(10) CHARACTER SET latin1 NOT NULL DEFAULT 's1def');
            CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;
            {code}

            Now I run these two queries:
            {code:sql}
            SHOW FIELDS IN t1;
            SHOW FIELDS IN v1;
            {code}
            Both return the same result:
            {noformat}
            +-------+-------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+-------------+------+-----+---------+-------+
            | i1 | int(11) | NO | | 0 | |
            | s1 | varchar(10) | NO | | s1def | |
            +-------+-------------+------+-----+---------+-------+
            {noformat}

            So far so good.

            Now I test mysql_list_fields() for {{t1}} and {{v1}}.

            This is the test client program:
            {code:cpp}.

            #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;
            }
            {code}

            I compile the above program and run:
            {code}
            ./list_fields t1
            {code}
            {noformat}
            Number of columns: 2
            Column[0] name=i1 type=3 charsetnr=63 def='0'
            Column[1] name=s1 type=253 charsetnr=33 def='s1def'
            {noformat}

            {code}
            ./list_fields v1
            {code}
            {noformat}
            Number of columns: 2
            Column[0] name=i1 type=3 charsetnr=63 def='0'
            Column[1] name=s1 type=253 charsetnr=33 def='0'
            {noformat}

            Observations:
            - the default value for the table is correctly displayed as {{s1def}}:
            - the default value for the table is incorrectly displayed as {{0}}:
            		I create a table and a VIEW on in:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (
              i1 INT NOT NULL DEFAULT 0,
              s1 VARCHAR(10) CHARACTER SET latin1 NOT NULL DEFAULT 's1def');
            CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;
            {code}

            Now I run these two queries:
            {code:sql}
            SHOW FIELDS IN t1;
            SHOW FIELDS IN v1;
            {code}
            Both return the same result:
            {noformat}
            +-------+-------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+-------------+------+-----+---------+-------+
            | i1 | int(11) | NO | | 0 | |
            | s1 | varchar(10) | NO | | s1def | |
            +-------+-------------+------+-----+---------+-------+
            {noformat}

            So far so good.

            Now I test mysql_list_fields() for {{t1}} and {{v1}}.

            This is the test client program:
            {code:cpp}
            #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;
            }
            {code}

            I compile the above program and run:
            {code}
            ./list_fields t1
            {code}
            {noformat}
            Number of columns: 2
            Column[0] name=i1 type=3 charsetnr=63 def='0'
            Column[1] name=s1 type=253 charsetnr=33 def='s1def'
            {noformat}

            {code}
            ./list_fields v1
            {code}
            {noformat}
            Number of columns: 2
            Column[0] name=i1 type=3 charsetnr=63 def='0'
            Column[1] name=s1 type=253 charsetnr=33 def='0'
            {noformat}

            Observations:
            - the default value for the table is correctly displayed as {{s1def}}:
            - the default value for the table is incorrectly displayed as {{0}}:
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Oleksandr Byelkin [ sanja ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Description I create a table and a VIEW on in:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (
              i1 INT NOT NULL DEFAULT 0,
              s1 VARCHAR(10) CHARACTER SET latin1 NOT NULL DEFAULT 's1def');
            CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;
            {code}

            Now I run these two queries:
            {code:sql}
            SHOW FIELDS IN t1;
            SHOW FIELDS IN v1;
            {code}
            Both return the same result:
            {noformat}
            +-------+-------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+-------------+------+-----+---------+-------+
            | i1 | int(11) | NO | | 0 | |
            | s1 | varchar(10) | NO | | s1def | |
            +-------+-------------+------+-----+---------+-------+
            {noformat}

            So far so good.

            Now I test mysql_list_fields() for {{t1}} and {{v1}}.

            This is the test client program:
            {code:cpp}
            #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;
            }
            {code}

            I compile the above program and run:
            {code}
            ./list_fields t1
            {code}
            {noformat}
            Number of columns: 2
            Column[0] name=i1 type=3 charsetnr=63 def='0'
            Column[1] name=s1 type=253 charsetnr=33 def='s1def'
            {noformat}

            {code}
            ./list_fields v1
            {code}
            {noformat}
            Number of columns: 2
            Column[0] name=i1 type=3 charsetnr=63 def='0'
            Column[1] name=s1 type=253 charsetnr=33 def='0'
            {noformat}

            Observations:
            - the default value for the table is correctly displayed as {{s1def}}:
            - the default value for the table is incorrectly displayed as {{0}}:
            		I create a table and a VIEW on in:
            {code:sql}
            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;{code}

            Now I run these two queries:
            {code:sql}
            SHOW FIELDS IN t1;
            SHOW FIELDS IN v1;
            {code}
            Both return the same result:
            {noformat}
            +-------+---------------------+------+-----+----------------------------------+-------+
            | 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 | |
            +-------+---------------------+------+-----+----------------------------------+-------+
            {noformat}

            So far so good.

            Now I test mysql_list_fields() for {{t1}} and {{v1}}.

            This is the test client program:
            {code:cpp}
            #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;
            }
            {code}

            I compile the above program and run:
            {code}
            ./list_fields t1
            {code}
            {noformat}
            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'
            {noformat}

            {code}
            ./list_fields v1
            {code}
            {noformat}
            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'
            {noformat}

            Observations:
            - the default value for the table is correctly displayed as {{s1def}}:
            - the default value for the table is incorrectly displayed as {{0}}:
            bar Alexander Barkov made changes -
            Description I create a table and a VIEW on in:
            {code:sql}
            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;{code}

            Now I run these two queries:
            {code:sql}
            SHOW FIELDS IN t1;
            SHOW FIELDS IN v1;
            {code}
            Both return the same result:
            {noformat}
            +-------+---------------------+------+-----+----------------------------------+-------+
            | 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 | |
            +-------+---------------------+------+-----+----------------------------------+-------+
            {noformat}

            So far so good.

            Now I test mysql_list_fields() for {{t1}} and {{v1}}.

            This is the test client program:
            {code:cpp}
            #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;
            }
            {code}

            I compile the above program and run:
            {code}
            ./list_fields t1
            {code}
            {noformat}
            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'
            {noformat}

            {code}
            ./list_fields v1
            {code}
            {noformat}
            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'
            {noformat}

            Observations:
            - the default value for the table is correctly displayed as {{s1def}}:
            - the default value for the table is incorrectly displayed as {{0}}:
            		I create a table and a VIEW on in:
            {code:sql}
            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;{code}

            Now I run these two queries:
            {code:sql}
            SHOW FIELDS IN t1;
            SHOW FIELDS IN v1;
            {code}
            Both return the same result:
            {noformat}
            +-------+---------------------+------+-----+----------------------------------+-------+
            | 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 | |
            +-------+---------------------+------+-----+----------------------------------+-------+
            {noformat}

            So far so good.

            Now I test mysql_list_fields() for {{t1}} and {{v1}}.

            This is the test client program:
            {code:cpp}
            #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;
            }
            {code}

            I compile the above program and run:
            {code}
            ./list_fields t1
            {code}
            {noformat}
            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'
            {noformat}

            {code}
            ./list_fields v1
            {code}
            {noformat}
            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'
            {noformat}

            All default values in the table are fine.
            Obvervations about default values in the view:
            - {{i1}} is fine
            - {{i3}} lost precision
            - {{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
            bar Alexander Barkov made changes -
            Description I create a table and a VIEW on in:
            {code:sql}
            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;{code}

            Now I run these two queries:
            {code:sql}
            SHOW FIELDS IN t1;
            SHOW FIELDS IN v1;
            {code}
            Both return the same result:
            {noformat}
            +-------+---------------------+------+-----+----------------------------------+-------+
            | 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 | |
            +-------+---------------------+------+-----+----------------------------------+-------+
            {noformat}

            So far so good.

            Now I test mysql_list_fields() for {{t1}} and {{v1}}.

            This is the test client program:
            {code:cpp}
            #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;
            }
            {code}

            I compile the above program and run:
            {code}
            ./list_fields t1
            {code}
            {noformat}
            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'
            {noformat}

            {code}
            ./list_fields v1
            {code}
            {noformat}
            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'
            {noformat}

            All default values in the table are fine.
            Obvervations about default values in the view:
            - {{i1}} is fine
            - {{i3}} lost precision
            - {{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
            		I create a table and a VIEW on in:
            {code:sql}
            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;{code}

            Now I run these two queries:
            {code:sql}
            SHOW FIELDS IN t1;
            SHOW FIELDS IN v1;
            {code}
            Both return the same result:
            {noformat}
            +-------+---------------------+------+-----+----------------------------------+-------+
            | 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 | |
            +-------+---------------------+------+-----+----------------------------------+-------+
            {noformat}

            So far so good.

            Now I test mysql_list_fields() for {{t1}} and {{v1}}.

            This is the test client program:
            {code:cpp}
            #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;
            }
            {code}

            I compile the above program and run:
            {code}
            ./list_fields t1
            {code}
            {noformat}
            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'
            {noformat}

            {code}
            ./list_fields v1
            {code}
            {noformat}
            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'
            {noformat}

            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
            bar Alexander Barkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Oleksandr Byelkin [ sanja ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            bar Alexander Barkov added a comment - - edited

            Pushed into bb-10.2-ext and 10.3.

            bar Alexander Barkov added a comment - - edited Pushed into bb-10.2-ext and 10.3.
            bar Alexander Barkov made changes -
            Fix Version/s 10.3.0 [ 22127 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status In Review [ 10002 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            Assignee Oleksandr Byelkin [ sanja ] Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            Labels datatype
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 79001 ] MariaDB v4 [ 151448 ]

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.