[Maria-discuss] TokuDB + virtual column

[Roberto Spadim]

Hi guys, there's a plan to include virtual column to tokudb storage?


/* Erro SQL (1910): TokuDB storage engine does not support computed columns
*/

thanks

-- 
Roberto Spadim
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

[Maria-discuss] LOAD FILE and BIT datatype

[Roberto Spadim]

hi guys, how could i send bit data to mysql with LOAD FILE?

a table example:
create table t (i bit not null default 0);

LOAD DATA LOCAL INFILE "file.csv" IGNORE INTO TABLE `t` FIELDS TERMINATED
BY ';' LINES TERMINATED BY '\n' (i)


the data file is simple:

0
1
0
1
0
1

but i got Warning 1024: Out of range value for column 'i' at row 1 ... to
last row

any idea? i tried
b'0'
b'1'
'0'
'1'
0
1
but no one work, i don't know what happen but it's cast always to 1  (maybe
convert from string to binary?)
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] LOAD FILE and BIT datatype

[Roberto Spadim]

sorry found at mysql docs

BIT  values cannot
be loaded using binary notation (for example, b'011010'). To work around
this, specify the values as regular integers and use the SET clause to
convert them so that MySQL performs a numeric type conversion and loads
them into the BIT
 column
properly:

 
shell> *cat /tmp/bit_test.txt*
2
127
shell> *mysql test*
mysql> *LOAD DATA INFILE '/tmp/bit_test.txt'*
-> *INTO TABLE bit_test (@var1) SET b = CAST(@var1 AS UNSIGNED);*
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0


but i'm checking something very bad
if i execute without "SET" a big file take 1second, with "SET" it takes 3
times more
maybe we could include the bit cast at load data infile and solve this
problem? maybe a mariadb improvement?


2015-11-02 21:43 GMT-02:00 Roberto Spadim :

> hi guys, how could i send bit data to mysql with LOAD FILE?
>
> a table example:
> create table t (i bit not null default 0);
>
> LOAD DATA LOCAL INFILE "file.csv" IGNORE INTO TABLE `t` FIELDS TERMINATED
> BY ';' LINES TERMINATED BY '\n' (i)
>
>
> the data file is simple:
>
> 0
> 1
> 0
> 1
> 0
> 1
>
> but i got Warning 1024: Out of range value for column 'i' at row 1 ... to
> last row
>
> any idea? i tried
> b'0'
> b'1'
> '0'
> '1'
> 0
> 1
> but no one work, i don't know what happen but it's cast always to 1
>  (maybe convert from string to binary?)
>



-- 
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] LOAD FILE and BIT datatype

[Roberto Spadim]

probably something here:
https://github.com/MariaDB/server/blob/10.1/sql/field.cc#L9127

2015-11-02 22:28 GMT-02:00 Roberto Spadim :

> hi justing, but i always get 1
> even when data file have "0" (character "0") at line
>
> i'm reading the load data file:
>
> https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L853
> that's something with field->store (i think)
> field->store((char*) pos,length,read_info.read_charset);
>
> https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L1025
> field->store((char*) pos, length, read_info.read_charset);i didn't
> checked yet store of bit field type
>
>
>
>
>
>
> 2015-11-02 22:15 GMT-02:00 Justin Swanhart :
>
>> Hi,
>>
>> Instead of using b'...' notation, use bitwise math to get the integer
>> representation of the bitset, and place that in your loader file.
>>
>> For example:
>> select 1 | 2 | 8;  ---> 11
>>
>> Put 11 in your data file and you will get bits 1 2 and 8 set in your
>> bitset.
>>
>> --Justin
>>
>> On Mon, Nov 2, 2015 at 4:52 PM, Roberto Spadim 
>> wrote:
>>
>>> sorry found at mysql docs
>>>
>>> BIT  values
>>> cannot be loaded using binary notation (for example, b'011010'). To
>>> work around this, specify the values as regular integers and use the SET 
>>> clause
>>> to convert them so that MySQL performs a numeric type conversion and loads
>>> them into the BIT
>>>  column properly:
>>>
>>>  
>>> shell> *cat /tmp/bit_test.txt*
>>> 2
>>> 127
>>> shell> *mysql test*
>>> mysql> *LOAD DATA INFILE '/tmp/bit_test.txt'*
>>> -> *INTO TABLE bit_test (@var1) SET b = CAST(@var1 AS UNSIGNED);*
>>> Query OK, 2 rows affected (0.00 sec)
>>> Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
>>>
>>>
>>> but i'm checking something very bad
>>> if i execute without "SET" a big file take 1second, with "SET" it takes
>>> 3 times more
>>> maybe we could include the bit cast at load data infile and solve this
>>> problem? maybe a mariadb improvement?
>>>
>>>
>>> 2015-11-02 21:43 GMT-02:00 Roberto Spadim :
>>>
 hi guys, how could i send bit data to mysql with LOAD FILE?

 a table example:
 create table t (i bit not null default 0);

 LOAD DATA LOCAL INFILE "file.csv" IGNORE INTO TABLE `t` FIELDS
 TERMINATED BY ';' LINES TERMINATED BY '\n' (i)


 the data file is simple:

 0
 1
 0
 1
 0
 1

 but i got Warning 1024: Out of range value for column 'i' at row 1 ...
 to last row

 any idea? i tried
 b'0'
 b'1'
 '0'
 '1'
 0
 1
 but no one work, i don't know what happen but it's cast always to 1
  (maybe convert from string to binary?)

>>>
>>>
>>>
>>> --
>>> Roberto Spadim
>>> SPAEmpresarial - Software ERP
>>> Eng. Automação e Controle
>>>
>>> ___
>>> Mailing list: https://launchpad.net/~maria-discuss
>>> Post to : maria-discuss@lists.launchpad.net
>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>> More help   : https://help.launchpad.net/ListHelp
>>>
>>>
>>
>
>
> --
> Roberto Spadim
> SPAEmpresarial - Software ERP
> Eng. Automação e Controle
>



-- 
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] LOAD FILE and BIT datatype

[Roberto Spadim]

maybe check if it's a BIT type at sql_load.cc and cast to bigint unsigned
before field->store ? i think it's the easier patch, i don't know
i tryed any kind of file
the bit column only with with "SET bit_column=@some_variable+0" or anything
to cast from "string" to "integers"

2015-11-02 22:40 GMT-02:00 Roberto Spadim :

> probably something here:
> https://github.com/MariaDB/server/blob/10.1/sql/field.cc#L9127
>
> 2015-11-02 22:28 GMT-02:00 Roberto Spadim :
>
>> hi justing, but i always get 1
>> even when data file have "0" (character "0") at line
>>
>> i'm reading the load data file:
>>
>> https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L853
>> that's something with field->store (i think)
>> field->store((char*) pos,length,read_info.read_charset);
>>
>> https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L1025
>> field->store((char*) pos, length, read_info.read_charset);i didn't
>> checked yet store of bit field type
>>
>>
>>
>>
>>
>>
>> 2015-11-02 22:15 GMT-02:00 Justin Swanhart :
>>
>>> Hi,
>>>
>>> Instead of using b'...' notation, use bitwise math to get the integer
>>> representation of the bitset, and place that in your loader file.
>>>
>>> For example:
>>> select 1 | 2 | 8;  ---> 11
>>>
>>> Put 11 in your data file and you will get bits 1 2 and 8 set in your
>>> bitset.
>>>
>>> --Justin
>>>
>>> On Mon, Nov 2, 2015 at 4:52 PM, Roberto Spadim 
>>> wrote:
>>>
 sorry found at mysql docs

 BIT  values
 cannot be loaded using binary notation (for example, b'011010'). To
 work around this, specify the values as regular integers and use the
 SET clause to convert them so that MySQL performs a numeric type
 conversion and loads them into the BIT
  column
 properly:

  
 shell> *cat /tmp/bit_test.txt*
 2
 127
 shell> *mysql test*
 mysql> *LOAD DATA INFILE '/tmp/bit_test.txt'*
 -> *INTO TABLE bit_test (@var1) SET b = CAST(@var1 AS UNSIGNED);*
 Query OK, 2 rows affected (0.00 sec)
 Records: 2  Deleted: 0  Skipped: 0  Warnings: 0


 but i'm checking something very bad
 if i execute without "SET" a big file take 1second, with "SET" it takes
 3 times more
 maybe we could include the bit cast at load data infile and solve this
 problem? maybe a mariadb improvement?


 2015-11-02 21:43 GMT-02:00 Roberto Spadim :

> hi guys, how could i send bit data to mysql with LOAD FILE?
>
> a table example:
> create table t (i bit not null default 0);
>
> LOAD DATA LOCAL INFILE "file.csv" IGNORE INTO TABLE `t` FIELDS
> TERMINATED BY ';' LINES TERMINATED BY '\n' (i)
>
>
> the data file is simple:
>
> 0
> 1
> 0
> 1
> 0
> 1
>
> but i got Warning 1024: Out of range value for column 'i' at row 1 ...
> to last row
>
> any idea? i tried
> b'0'
> b'1'
> '0'
> '1'
> 0
> 1
> but no one work, i don't know what happen but it's cast always to 1
>  (maybe convert from string to binary?)
>



 --
 Roberto Spadim
 SPAEmpresarial - Software ERP
 Eng. Automação e Controle

 ___
 Mailing list: https://launchpad.net/~maria-discuss
 Post to : maria-discuss@lists.launchpad.net
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp


>>>
>>
>>
>> --
>> Roberto Spadim
>> SPAEmpresarial - Software ERP
>> Eng. Automação e Controle
>>
>
>
>
> --
> Roberto Spadim
> SPAEmpresarial - Software ERP
> Eng. Automação e Controle
>



-- 
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] LOAD FILE and BIT datatype

[Roberto Spadim]

maybe
enum_field_types type() const { return MYSQL_TYPE_BIT; }
if(field->type()==MYSQL_TYPE_BIT){
// convert string to unsigned long long
}else{
// use normal field->store()
}

must try, but i didn't found how to store() with the "b'0'" representation
or someting like it

2015-11-02 22:45 GMT-02:00 Roberto Spadim :

> maybe check if it's a BIT type at sql_load.cc and cast to bigint unsigned
> before field->store ? i think it's the easier patch, i don't know
> i tryed any kind of file
> the bit column only with with "SET bit_column=@some_variable+0" or
> anything to cast from "string" to "integers"
>
> 2015-11-02 22:40 GMT-02:00 Roberto Spadim :
>
>> probably something here:
>> https://github.com/MariaDB/server/blob/10.1/sql/field.cc#L9127
>>
>> 2015-11-02 22:28 GMT-02:00 Roberto Spadim :
>>
>>> hi justing, but i always get 1
>>> even when data file have "0" (character "0") at line
>>>
>>> i'm reading the load data file:
>>>
>>> https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L853
>>> that's something with field->store (i think)
>>> field->store((char*) pos,length,read_info.read_charset);
>>>
>>> https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L1025
>>> field->store((char*) pos, length, read_info.read_charset);i didn't
>>> checked yet store of bit field type
>>>
>>>
>>>
>>>
>>>
>>>
>>> 2015-11-02 22:15 GMT-02:00 Justin Swanhart :
>>>
 Hi,

 Instead of using b'...' notation, use bitwise math to get the integer
 representation of the bitset, and place that in your loader file.

 For example:
 select 1 | 2 | 8;  ---> 11

 Put 11 in your data file and you will get bits 1 2 and 8 set in your
 bitset.

 --Justin

 On Mon, Nov 2, 2015 at 4:52 PM, Roberto Spadim 
 wrote:

> sorry found at mysql docs
>
> BIT  values
> cannot be loaded using binary notation (for example, b'011010'). To
> work around this, specify the values as regular integers and use the
> SET clause to convert them so that MySQL performs a numeric type
> conversion and loads them into the BIT
>  column
> properly:
>
>  
> shell> *cat /tmp/bit_test.txt*
> 2
> 127
> shell> *mysql test*
> mysql> *LOAD DATA INFILE '/tmp/bit_test.txt'*
> -> *INTO TABLE bit_test (@var1) SET b = CAST(@var1 AS UNSIGNED);*
> Query OK, 2 rows affected (0.00 sec)
> Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
>
>
> but i'm checking something very bad
> if i execute without "SET" a big file take 1second, with "SET" it
> takes 3 times more
> maybe we could include the bit cast at load data infile and solve this
> problem? maybe a mariadb improvement?
>
>
> 2015-11-02 21:43 GMT-02:00 Roberto Spadim :
>
>> hi guys, how could i send bit data to mysql with LOAD FILE?
>>
>> a table example:
>> create table t (i bit not null default 0);
>>
>> LOAD DATA LOCAL INFILE "file.csv" IGNORE INTO TABLE `t` FIELDS
>> TERMINATED BY ';' LINES TERMINATED BY '\n' (i)
>>
>>
>> the data file is simple:
>>
>> 0
>> 1
>> 0
>> 1
>> 0
>> 1
>>
>> but i got Warning 1024: Out of range value for column 'i' at row 1
>> ... to last row
>>
>> any idea? i tried
>> b'0'
>> b'1'
>> '0'
>> '1'
>> 0
>> 1
>> but no one work, i don't know what happen but it's cast always to 1
>>  (maybe convert from string to binary?)
>>
>
>
>
> --
> Roberto Spadim
> SPAEmpresarial - Software ERP
> Eng. Automação e Controle
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>

>>>
>>>
>>> --
>>> Roberto Spadim
>>> SPAEmpresarial - Software ERP
>>> Eng. Automação e Controle
>>>
>>
>>
>>
>> --
>> Roberto Spadim
>> SPAEmpresarial - Software ERP
>> Eng. Automação e Controle
>>
>
>
>
> --
> Roberto Spadim
> SPAEmpresarial - Software ERP
> Eng. Automação e Controle
>



-- 
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] LOAD FILE and BIT datatype

[Roberto Spadim]

tested with binary data 0x00 and 0x01
return ALWAYS bit=1
hehe load data have a documented bug with bit type :P

i will test BIT with length 64

2015-11-03 0:42 GMT-02:00 Justin Swanhart :

> Yes, \0 is ascii zero, but it must be converting the string \0 to the
> int.  It doesn't do any escape conversion, which is kind of idiotic. :D
>
> I don't know what the LOAD DATA delimiter parser will do with a null
> character in the input.  It might fail.
>
> --Justin
>
> On Mon, Nov 2, 2015 at 7:39 PM, Roberto Spadim 
> wrote:
>
>> character ascii 0 ?
>> \0 \1 b'0' b'1' 0 1
>> don't work
>>
>> i will test binary data
>>
>> 2015-11-03 0:26 GMT-02:00 Justin Swanhart :
>>
>>> Try \0 and 1  if you have only one bit to set.
>>>
>>> On Mon, Nov 2, 2015 at 5:56 PM, Roberto Spadim 
>>> wrote:
>>>
 maybe
 enum_field_types type() const { return MYSQL_TYPE_BIT; }
 if(field->type()==MYSQL_TYPE_BIT){
 // convert string to unsigned long long
 }else{
 // use normal field->store()
 }

 must try, but i didn't found how to store() with the "b'0'"
 representation or someting like it

 2015-11-02 22:45 GMT-02:00 Roberto Spadim :

> maybe check if it's a BIT type at sql_load.cc and cast to bigint
> unsigned before field->store ? i think it's the easier patch, i don't know
> i tryed any kind of file
> the bit column only with with "SET bit_column=@some_variable+0" or
> anything to cast from "string" to "integers"
>
> 2015-11-02 22:40 GMT-02:00 Roberto Spadim :
>
>> probably something here:
>> https://github.com/MariaDB/server/blob/10.1/sql/field.cc#L9127
>>
>> 2015-11-02 22:28 GMT-02:00 Roberto Spadim :
>>
>>> hi justing, but i always get 1
>>> even when data file have "0" (character "0") at line
>>>
>>> i'm reading the load data file:
>>>
>>> https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L853
>>> that's something with field->store (i think)
>>> field->store((char*) pos,length,read_info.read_charset);
>>>
>>> https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L1025
>>> field->store((char*) pos, length, read_info.read_charset);i didn't
>>> checked yet store of bit field type
>>>
>>>
>>>
>>>
>>>
>>>
>>> 2015-11-02 22:15 GMT-02:00 Justin Swanhart :
>>>
 Hi,

 Instead of using b'...' notation, use bitwise math to get the
 integer representation of the bitset, and place that in your loader 
 file.

 For example:
 select 1 | 2 | 8;  ---> 11

 Put 11 in your data file and you will get bits 1 2 and 8 set in
 your bitset.

 --Justin

 On Mon, Nov 2, 2015 at 4:52 PM, Roberto Spadim <
 robe...@spadim.com.br> wrote:

> sorry found at mysql docs
>
> BIT  values
> cannot be loaded using binary notation (for example, b'011010').
> To work around this, specify the values as regular integers and use 
> the
> SET clause to convert them so that MySQL performs a numeric type
> conversion and loads them into the BIT
>  column
> properly:
>
>  
> shell> *cat /tmp/bit_test.txt*
> 2
> 127
> shell> *mysql test*
> mysql> *LOAD DATA INFILE '/tmp/bit_test.txt'*
> -> *INTO TABLE bit_test (@var1) SET b = CAST(@var1 AS UNSIGNED);*
> Query OK, 2 rows affected (0.00 sec)
> Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
>
>
> but i'm checking something very bad
> if i execute without "SET" a big file take 1second, with "SET" it
> takes 3 times more
> maybe we could include the bit cast at load data infile and solve
> this problem? maybe a mariadb improvement?
>
>
> 2015-11-02 21:43 GMT-02:00 Roberto Spadim :
>
>> hi guys, how could i send bit data to mysql with LOAD FILE?
>>
>> a table example:
>> create table t (i bit not null default 0);
>>
>> LOAD DATA LOCAL INFILE "file.csv" IGNORE INTO TABLE `t` FIELDS
>> TERMINATED BY ';' LINES TERMINATED BY '\n' (i)
>>
>>
>> the data file is simple:
>>
>> 0
>> 1
>> 0
>> 1
>> 0
>> 1
>>
>> but i got Warning 1024: Out of range value for column 'i' at row
>> 1 

Re: [Maria-discuss] LOAD FILE and BIT datatype

[Roberto Spadim]

i openned a MDEV: https://mariadb.atlassian.net/browse/MDEV-9073
and will upload this last example

2015-11-03 0:50 GMT-02:00 Roberto Spadim :

> well some conversion is done
>
> SQL:
> drop table if exists a;
> create table if not exists a(i bit(64)) ENGINE=MYISAM;
> LOAD DATA LOCAL INFILE "c:\\spadim\\t.txt"
> IGNORE INTO TABLE `a` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'
> (i);
> select * from a;
>
> RESULT:
> i
> 1101
> 1101
> 1101
> 1101
> 1101
> 1101
> 1101
> 1101
>
>
> file t.txt attached
> [image: Imagem inline 1]
>
>


-- 
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] LOAD FILE and BIT datatype

[Justin Swanhart]

Try \0 and 1  if you have only one bit to set.

On Mon, Nov 2, 2015 at 5:56 PM, Roberto Spadim 
wrote:

> maybe
> enum_field_types type() const { return MYSQL_TYPE_BIT; }
> if(field->type()==MYSQL_TYPE_BIT){
> // convert string to unsigned long long
> }else{
> // use normal field->store()
> }
>
> must try, but i didn't found how to store() with the "b'0'" representation
> or someting like it
>
> 2015-11-02 22:45 GMT-02:00 Roberto Spadim :
>
>> maybe check if it's a BIT type at sql_load.cc and cast to bigint unsigned
>> before field->store ? i think it's the easier patch, i don't know
>> i tryed any kind of file
>> the bit column only with with "SET bit_column=@some_variable+0" or
>> anything to cast from "string" to "integers"
>>
>> 2015-11-02 22:40 GMT-02:00 Roberto Spadim :
>>
>>> probably something here:
>>> https://github.com/MariaDB/server/blob/10.1/sql/field.cc#L9127
>>>
>>> 2015-11-02 22:28 GMT-02:00 Roberto Spadim :
>>>
 hi justing, but i always get 1
 even when data file have "0" (character "0") at line

 i'm reading the load data file:

 https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L853
 that's something with field->store (i think)
 field->store((char*) pos,length,read_info.read_charset);

 https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L1025
 field->store((char*) pos, length, read_info.read_charset);i didn't
 checked yet store of bit field type






 2015-11-02 22:15 GMT-02:00 Justin Swanhart :

> Hi,
>
> Instead of using b'...' notation, use bitwise math to get the integer
> representation of the bitset, and place that in your loader file.
>
> For example:
> select 1 | 2 | 8;  ---> 11
>
> Put 11 in your data file and you will get bits 1 2 and 8 set in your
> bitset.
>
> --Justin
>
> On Mon, Nov 2, 2015 at 4:52 PM, Roberto Spadim 
> wrote:
>
>> sorry found at mysql docs
>>
>> BIT  values
>> cannot be loaded using binary notation (for example, b'011010'). To
>> work around this, specify the values as regular integers and use the
>> SET clause to convert them so that MySQL performs a numeric type
>> conversion and loads them into the BIT
>>  column
>> properly:
>>
>>  
>> shell> *cat /tmp/bit_test.txt*
>> 2
>> 127
>> shell> *mysql test*
>> mysql> *LOAD DATA INFILE '/tmp/bit_test.txt'*
>> -> *INTO TABLE bit_test (@var1) SET b = CAST(@var1 AS UNSIGNED);*
>> Query OK, 2 rows affected (0.00 sec)
>> Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
>>
>>
>> but i'm checking something very bad
>> if i execute without "SET" a big file take 1second, with "SET" it
>> takes 3 times more
>> maybe we could include the bit cast at load data infile and solve
>> this problem? maybe a mariadb improvement?
>>
>>
>> 2015-11-02 21:43 GMT-02:00 Roberto Spadim :
>>
>>> hi guys, how could i send bit data to mysql with LOAD FILE?
>>>
>>> a table example:
>>> create table t (i bit not null default 0);
>>>
>>> LOAD DATA LOCAL INFILE "file.csv" IGNORE INTO TABLE `t` FIELDS
>>> TERMINATED BY ';' LINES TERMINATED BY '\n' (i)
>>>
>>>
>>> the data file is simple:
>>>
>>> 0
>>> 1
>>> 0
>>> 1
>>> 0
>>> 1
>>>
>>> but i got Warning 1024: Out of range value for column 'i' at row 1
>>> ... to last row
>>>
>>> any idea? i tried
>>> b'0'
>>> b'1'
>>> '0'
>>> '1'
>>> 0
>>> 1
>>> but no one work, i don't know what happen but it's cast always to 1
>>>  (maybe convert from string to binary?)
>>>
>>
>>
>>
>> --
>> Roberto Spadim
>> SPAEmpresarial - Software ERP
>> Eng. Automação e Controle
>>
>> ___
>> Mailing list: https://launchpad.net/~maria-discuss
>> Post to : maria-discuss@lists.launchpad.net
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>


 --
 Roberto Spadim
 SPAEmpresarial - Software ERP
 Eng. Automação e Controle

>>>
>>>
>>>
>>> --
>>> Roberto Spadim
>>> SPAEmpresarial - Software ERP
>>> Eng. Automação e Controle
>>>
>>
>>
>>
>> --
>> Roberto Spadim
>> SPAEmpresarial - Software ERP
>> Eng. Automação e Controle
>>
>
>
>
> --
> Roberto Spadim
> SPAEmpresarial - Software ERP
> Eng. Automação e Controle
>

Re: [Maria-discuss] LOAD FILE and BIT datatype

[Justin Swanhart]

Yes, \0 is ascii zero, but it must be converting the string \0 to the int.
It doesn't do any escape conversion, which is kind of idiotic. :D

I don't know what the LOAD DATA delimiter parser will do with a null
character in the input.  It might fail.

--Justin

On Mon, Nov 2, 2015 at 7:39 PM, Roberto Spadim 
wrote:

> character ascii 0 ?
> \0 \1 b'0' b'1' 0 1
> don't work
>
> i will test binary data
>
> 2015-11-03 0:26 GMT-02:00 Justin Swanhart :
>
>> Try \0 and 1  if you have only one bit to set.
>>
>> On Mon, Nov 2, 2015 at 5:56 PM, Roberto Spadim 
>> wrote:
>>
>>> maybe
>>> enum_field_types type() const { return MYSQL_TYPE_BIT; }
>>> if(field->type()==MYSQL_TYPE_BIT){
>>> // convert string to unsigned long long
>>> }else{
>>> // use normal field->store()
>>> }
>>>
>>> must try, but i didn't found how to store() with the "b'0'"
>>> representation or someting like it
>>>
>>> 2015-11-02 22:45 GMT-02:00 Roberto Spadim :
>>>
 maybe check if it's a BIT type at sql_load.cc and cast to bigint
 unsigned before field->store ? i think it's the easier patch, i don't know
 i tryed any kind of file
 the bit column only with with "SET bit_column=@some_variable+0" or
 anything to cast from "string" to "integers"

 2015-11-02 22:40 GMT-02:00 Roberto Spadim :

> probably something here:
> https://github.com/MariaDB/server/blob/10.1/sql/field.cc#L9127
>
> 2015-11-02 22:28 GMT-02:00 Roberto Spadim :
>
>> hi justing, but i always get 1
>> even when data file have "0" (character "0") at line
>>
>> i'm reading the load data file:
>>
>> https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L853
>> that's something with field->store (i think)
>> field->store((char*) pos,length,read_info.read_charset);
>>
>> https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L1025
>> field->store((char*) pos, length, read_info.read_charset);i didn't
>> checked yet store of bit field type
>>
>>
>>
>>
>>
>>
>> 2015-11-02 22:15 GMT-02:00 Justin Swanhart :
>>
>>> Hi,
>>>
>>> Instead of using b'...' notation, use bitwise math to get the
>>> integer representation of the bitset, and place that in your loader 
>>> file.
>>>
>>> For example:
>>> select 1 | 2 | 8;  ---> 11
>>>
>>> Put 11 in your data file and you will get bits 1 2 and 8 set in your
>>> bitset.
>>>
>>> --Justin
>>>
>>> On Mon, Nov 2, 2015 at 4:52 PM, Roberto Spadim <
>>> robe...@spadim.com.br> wrote:
>>>
 sorry found at mysql docs

 BIT  values
 cannot be loaded using binary notation (for example, b'011010').
 To work around this, specify the values as regular integers and use the
 SET clause to convert them so that MySQL performs a numeric type
 conversion and loads them into the BIT
  column
 properly:

  
 shell> *cat /tmp/bit_test.txt*
 2
 127
 shell> *mysql test*
 mysql> *LOAD DATA INFILE '/tmp/bit_test.txt'*
 -> *INTO TABLE bit_test (@var1) SET b = CAST(@var1 AS UNSIGNED);*
 Query OK, 2 rows affected (0.00 sec)
 Records: 2  Deleted: 0  Skipped: 0  Warnings: 0


 but i'm checking something very bad
 if i execute without "SET" a big file take 1second, with "SET" it
 takes 3 times more
 maybe we could include the bit cast at load data infile and solve
 this problem? maybe a mariadb improvement?


 2015-11-02 21:43 GMT-02:00 Roberto Spadim :

> hi guys, how could i send bit data to mysql with LOAD FILE?
>
> a table example:
> create table t (i bit not null default 0);
>
> LOAD DATA LOCAL INFILE "file.csv" IGNORE INTO TABLE `t` FIELDS
> TERMINATED BY ';' LINES TERMINATED BY '\n' (i)
>
>
> the data file is simple:
>
> 0
> 1
> 0
> 1
> 0
> 1
>
> but i got Warning 1024: Out of range value for column 'i' at row 1
> ... to last row
>
> any idea? i tried
> b'0'
> b'1'
> '0'
> '1'
> 0
> 1
> but no one work, i don't know what happen but it's cast always to
> 1  (maybe convert from string to binary?)
>



 --
 Roberto Spadim
 SPAEmpresarial - 

Re: [Maria-discuss] LOAD FILE and BIT datatype

[Roberto Spadim]

well some conversion is done

SQL:
drop table if exists a;
create table if not exists a(i bit(64)) ENGINE=MYISAM;
LOAD DATA LOCAL INFILE "c:\\spadim\\t.txt"
IGNORE INTO TABLE `a` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' (i);
select * from a;

RESULT:
i
1101
1101
1101
1101
1101
1101
1101
1101


file t.txt attached
[image: Imagem inline 1]
0
1
\0
\1
b'0'
b'1'



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp