Bug #18168 Prepared statement metatdata length wrong on view with union.

• Submitted: 12 Mar 2006 18:13
• Modified: 22 Mar 2006 15:56
• Reporter: John Booine
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.19
• OS: Windows (Windows 2000)
• CPU Architecture: Any

[12 Mar 2006 18:13] John Booine

Description:
The MySQL Pre4pared Statement Metaadata API does not show the correct length of columns when a view has a UNION. It seems to double the length of the two union columns.

How to repeat:
CREATE TABLE NDDF_STRENGTHTBL (
       STR                CHAR (10) NOT NULL,
       STRNUM             NUMERIC (11,3) NOT NULL,
       STRUN              CHAR (10) NOT NULL,
       VOLNUM             NUMERIC (7,3) NOT NULL,
       VOLUN              CHAR (5) NOT NULL
);

CREATE VIEW NDDF_UNIT_VIEW (UNIT) as 
       SELECT distinct STRUN from NDDF_STRENGTHTBL
                where STRUN > '          '
       UNION
       SELECT distinct CONCAT(VOLUN, '     ') from NDDF_STRENGTHTBL
                where VOLUN > '     ';

C Program

char *pStatmt = "SELECT UNIT FROM NDDF_UNIT_VIEW ";
MYSQL_RES *result = NULL;

mysql_stmt_prepare(stmt, pStatmt, strlen(pStatmt));
result = mysql_stmt_result_metadata(stmt);

Problem results->field.length is 20!

The metatdata shows a field length of 20. It should be 10. Note that the Query Browser shows the length correctly.

[13 Mar 2006 14:42] Valeriy Kravchuk

Server has no similar problem:

mysql> CREATE TABLE NDDF_STRENGTHTBL (
    ->        STR                CHAR (10) NOT NULL,
    ->        STRNUM             NUMERIC (11,3) NOT NULL,
    ->        STRUN              CHAR (10) NOT NULL,
    ->        VOLNUM             NUMERIC (7,3) NOT NULL,
    ->        VOLUN              CHAR (5) NOT NULL
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> CREATE VIEW NDDF_UNIT_VIEW (UNIT) as
    ->        SELECT distinct STRUN from NDDF_STRENGTHTBL
    ->                 where STRUN > '          '
    ->        UNION
    ->        SELECT distinct CONCAT(VOLUN, '     ') from NDDF_STRENGTHTBL
    ->                 where VOLUN > '     ';
Query OK, 0 rows affected (0.03 sec)

mysql> create table ttt1 as SELECT UNIT FROM NDDF_UNIT_VIEW;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table ttt1\G
*************************** 1. row ***************************
       Table: ttt1
Create Table: CREATE TABLE `ttt1` (
  `UNIT` varchar(10) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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

It might be really related to prepared statements only.

[22 Mar 2006 15:56] Valeriy Kravchuk

Sorry, but I was not able to repeat with 5.0.20-BK on Linux:

openxs@suse:~/dbs/5.0> ./18168
Client info: 5.0.20
Server info: 5.0.20
type=253
length=10
decimals=0
openxs@suse:~/dbs/5.0> cat 18168.c
#include <stdio.h>
#include "mysql.h"

#define SELECT1 "SELECT UNIT FROM NDDF_UNIT_VIEW"

int main()
{
  MYSQL *conn;
  MYSQL_STMT *stmt;
  MYSQL_RES *meta;
  MYSQL_FIELD *field;
  MYSQL_BIND bind;
  int res;
  unsigned long len;
  char buf[21] = {0};
  double d=-1.0;

  conn= mysql_init(NULL);
  conn= mysql_real_connect(conn, "127.0.0.1", "root", "", "test", 3306, 0, 0);
  if (!conn) {
    fprintf(stderr, "Cannot connect\n");
    exit(1);
  }

  printf("Client info: %s\n", mysql_get_client_info());
  printf("Server info: %s\n", mysql_get_server_info(conn));

  stmt= mysql_stmt_init(conn);

  res= mysql_stmt_prepare(stmt, SELECT1, strlen(SELECT1));
  meta= mysql_stmt_result_metadata(stmt);

  field= mysql_fetch_field_direct(meta, 0);
  printf("type=%d\n", field->type);
  printf("length=%d\n", field->length);
  printf("decimals=%d\n", field->decimals);

  mysql_free_result(meta);

  mysql_close(conn);
  return 0;
}

So, the length is 10. Wait for the new version to be officially released if you get different results.