Help save net neutrality! Learn more.
Close

#2564 SELECT DISTINCT CAST AS CHAR truncates results

2.11.1.2
fixed
5
2013-06-11
2007-10-18
ReneMT
No

Please, see also this MySQL bug report for some more details: http://bugs.mysql.com/31279

Description:
Assume you have a table with an integer column f.e. table "cds" from "cdcol" sample database.

Executing the following statement truncates result to 3 chars:

SELECT DISTINCT CAST('jahr' as CHAR) FROM 'cds'

--> replace 'jahr' with 'year' in english version

This problem does not appear on unix based mysql database server. It did not occur just 1 Month ago. There might have been a windows update in the last 4
weeks that causes this problem.

The problem also does not occur when executing "SELECT CAST('jahr' as CHAR) FROM 'cds'" or
"SELECT DISTINCT CAST('jahr' as CHAR(4)) FROM 'cds'"

Furthermore, it does not occure if using the MySQL command line client.

The fact, that phpMyAdmin as well as self-written .NET applications using MySQL Connector/NET 5.1.2 are affected seems somehow strange.

Discussion

  • Sebastian Mendel

    • assigned_to: nobody --> cybot_tm
    • status: open --> pending
     
  • Sebastian Mendel

    Logged In: YES
    user_id=326580
    Originator: NO

    cannot reproduce on phpMyAdmin 2.11 (Win XP Pro SP, Apache 2.2.4 MySQL 5.1.22/lib 5.0.20 PHP 5.2.4 mod)

    can you reproduce this error without table?

    SELECT DISTINCT CAST('jahr' as CHAR);

    if not - add table structure

    what is your MySQL client lib version? (reported on phpMyAdmin start main page)

    mysql or mysqli?

    PHP version?

     
  • ReneMT

    ReneMT - 2007-10-18
    • status: pending --> open
     
  • ReneMT

    ReneMT - 2007-10-18

    Logged In: YES
    user_id=320600
    Originator: YES

    I'm using XAMPP for Windows 1.6.3a with MySQL 5.0.45-nt (client version 5.0.45 according to phpMyAdmin), PHP 5.2.3 without mysqli, Apache HTTPD 2.2.4.

    Could you please explain more detailed what do you mean by

    "can you reproduce this error without table?
    SELECT DISTINCT CAST('jahr' as CHAR);
    if not - add table structure"

    It's not clear to me, sorry...

     
  • Sebastian Mendel

    Logged In: YES
    user_id=326580
    Originator: NO

    does this error also occur if you omit the table? just run

    SELECT DISTINCT CAST('jahr' as CHAR);

    without specifying a table

    and if the error does not if you omit the table, please attach the structure of your table.

     
  • Sebastian Mendel

    Logged In: YES
    user_id=326580
    Originator: NO

    btw. XAMPP for Windows has a broken phpMyAdmin, please install a fresh one from our site!

     
  • ReneMT

    ReneMT - 2007-10-18

    'cds' table structure creation SQL

     
  • ReneMT

    ReneMT - 2007-10-18

    Logged In: YES
    user_id=320600
    Originator: YES

    I installed the latest version from your site (2.11.1.2) and run the statement -
    as expected I receive 'jahr' - respectively 'year' as result, which is not truncated.

    Attached you will find the SQL exported from the 'cds' table by phpMyAdmin.

    I just want to point out some things once again:

    Firstly, not only phpMyAdmin is affected, but also .NET applications using MySQL Connector/NET . In both cases this behavior does only occur if a MySQL server running on Windows is accessed remotly via network. If you try the same with a Linux based MySQL server or on a local installation everything works like expected.

    I could imagine that this points out to a problem with some Windows update - because the problems began quite suddenly. However, because it didn't catch someones eyes it's not possible to identify the specific date. It might be sometime in September.
    File Added: cds.sql

     
  • Sebastian Mendel

    • milestone: 741410 --> 2.11.1.2
     
  • Sebastian Mendel

    Logged In: YES
    user_id=326580
    Originator: NO

    ok, i will try tomorrow, just one more thing, does this happen only on this table? and does this also happen if your table name is longer than 3 chars?

     
  • ReneMT

    ReneMT - 2007-10-18

    Logged In: YES
    user_id=320600
    Originator: YES

    It happens in different tables in different DBs - and even with names longer then 3 chars. The common fact is, that it are always INT columns. Primarily we noticed the problem in our internal project management system, which we are currently developing based on ASP.NET/MySQL. The problem was reproducible with different of our tools which are using MySQL Connector/NET to connect to the MySQL server. We were (and are still) able to reproduce it using phpMyAdmin as well.

    The interesting question for me is: At which point is the error located? The MySQL guys told us, that it is no MySQL problem - because it doesn't happen when using the MySQL command line tool. Could it be some charset/collation problem? Or some TCP stuff?

     
  • Sebastian Mendel

    Logged In: YES
    user_id=326580
    Originator: NO

    or the mysql lib used

    cannot reproduce

    SELECT DISTINCT CAST(`jahr` AS CHAR) FROM `cds`

    returns 1990 2001 1997

     
  • Sebastian Mendel

    • status: open --> pending
     
  • SourceForge Robot

    Logged In: YES
    user_id=1312539
    Originator: NO

    This Tracker item was closed automatically by the system. It was
    previously set to a Pending status, and the original submitter
    did not respond within 14 days (the time period specified by
    the administrator of this Tracker).

     
  • SourceForge Robot

    • status: pending --> closed
     
  • Michal Čihař

    Michal Čihař - 2013-06-11
    • Status: closed --> fixed