LOAD DATA INFILE doesn't work with multibyte ENCLOSED BY (although SELECT INTO OUTFILE does)

[Stewart Smith]

CREATE TABLE t1 (c1 VARCHAR(256));
INSERT INTO t1 (c1) VALUES ('☠');
SELECT HEX(c1) FROM t1;
HEX(c1)
E298A0
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug32533.txt' FIELDS ENCLOSED BY '☢' FROM t1;
TRUNCATE t1;
SELECT HEX(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug32533.txt'));
HEX(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug32533.txt'))
E298A2E298A0E298A20A
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug32533.txt' INTO TABLE t1 FIELDS ENCLOSED BY '☢';

drizzletest: At line 90: query 'LOAD DATA INFILE '$file' INTO TABLE t1 FIELDS ENCLOSED BY '☢'' failed: 1083: Field separator argument is not what is expected; check the manual

[Stewart Smith (stewart)]

outfile_loaddata test

[Jay Pipes (jaypipes)]

Lines 137-142 in drizzled/sql_load.cc:

  if (escaped->length() > 1 || enclosed->length() > 1)
  {
    my_message(ER_WRONG_FIELD_TERMINATORS,ER(ER_WRONG_FIELD_TERMINATORS),
        MYF(0));
    return(true);
  }

where enclosed= ex->enclosed;

ex is of type sql_exchange, and enclosed is of type String (not std::string).

I changed the above code to be:

    my_error(ER_WRONG_FIELD_TERMINATORS,MYF(0),enclosed->c_ptr(), enclosed->length());

and changed the ER_WRONG_FIELD_TERMINATORS like so:

from:

N_("Field separator argument is not what is expected; check the manual"),

to:

N_("Field separator argument '%-.32s' with length '%d' is not what is expected; check the manual"),

And ran the test case above. I got the following error:

1083: Field separator argument '☢' with length '3' is not what is expected; check the manual

Now, checking the manual, the LOAD DATA INFILE syntax indeed states that enclosed by and escaped by are of type "char", but there is no indication whether a utf8 "char" is allowed. My instinct is that if SELECT INTO OUTFILE works with a utf8 character, that LOAD DATA INFILE should also!

Therefore, a fix for this bug is attached. The patch includes a file used in the following test and result:

bug308457.test:

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 (c1 VARCHAR(256));
INSERT INTO t1 (c1) VALUES ('☠');
SELECT HEX(c1) FROM t1;
TRUNCATE t1;
SELECT HEX(LOAD_FILE('../std_data_ln/bug308457.txt'));
LOAD DATA INFILE '../std_data_ln/bug308457.txt' INTO TABLE t1 FIELDS ENCLOSED BY '☢';

bug308457.result:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 VARCHAR(256));
INSERT INTO t1 (c1) VALUES ('☠');
SELECT HEX(c1) FROM t1;
HEX(c1)
E298A0
TRUNCATE t1;
SELECT HEX(LOAD_FILE('../std_data_ln/bug308457.txt'));
HEX(LOAD_FILE('../std_data_ln/bug308457.txt'))
E298A2E298A0E298A20A
LOAD DATA INFILE '../std_data_ln/bug308457.txt' INTO TABLE t1 FIELDS ENCLOSED BY '☢';

[Jay Pipes (jaypipes)]

Assigning to me. Stewart, please review the patch. If OK, I will apply it to my local enable-test-suite branch and commit.

[Stewart Smith (stewart)]

Looks okay.

Any reason why we have to limit to 4 cahr... things should 'just work'
if we removed that whole error path, right?
--
Stewart Smith

[Jay Pipes (jaypipes)]

Stewart Smith wrote:
> Looks okay.
>
> Any reason why we have to limit to 4 cahr... things should 'just work'
> if we removed that whole error path, right?

I was just sticking to the manual, which states a "character" for a
delimiter. But AFAICT, no, there's no reason it couldn't be a string.

-j

[Stewart Smith (stewart)]

On Thu, Dec 18, 2008 at 03:06:06PM -0000, Jay Pipes wrote:
> Stewart Smith wrote:
> > Looks okay.
> >
> > Any reason why we have to limit to 4 cahr... things should 'just work'
> > if we removed that whole error path, right?
>
> I was just sticking to the manual, which states a "character" for a
> delimiter. But AFAICT, no, there's no reason it couldn't be a string.

FYI this has just been marked a D2 "serious" bug for mysql - you may get
to write "fixed in drizzle" :)
--
Stewart Smith