######################################################################
# Test ndb_restore's print_data feature
######################################################################

-- source include/have_ndb.inc
-- source include/ndb_default_cluster.inc

--disable_warnings
use test;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;
--enable_warnings

# basic datatypes
create table t1
 (pk int key
  ,a1 BIT(1), a2 BIT(5), a3 BIT(33), a4 BIT(63), a5 BIT(64)
  ,b1 TINYINT, b2 TINYINT UNSIGNED
  ,c1 SMALLINT, c2 SMALLINT UNSIGNED
  ,d1 INT, d2 INT UNSIGNED
  ,e1 BIGINT, e2 BIGINT UNSIGNED
  ,f1 CHAR(1) BINARY, f2 CHAR(32) BINARY, f3 CHAR(255) BINARY
  ,g1 VARCHAR(32) BINARY, g2 VARCHAR(255) BINARY, g3 VARCHAR(1000) BINARY
  ,h1 BINARY(1), h2 BINARY(8), h3 BINARY(255)
  ,i1 VARBINARY(32), i2 VARBINARY(255), i3 VARBINARY(1000)
 ) engine myisam;

# max values
insert into t1 values
 (1
  ,0x1, 0x17, 0x789a, 0x789abcde, 0xfedc0001
  ,127, 255
  ,32767, 65535
  ,2147483647, 4294967295
  ,9223372036854775807, 18446744073709551615
  ,'1','12345678901234567890123456789012','123456789'
  ,'1','12345678901234567890123456789012','123456789'
  ,0x12,0x123456789abcdef0, 0x012345
  ,0x12,0x123456789abcdef0, 0x00123450
 );

# min values
insert into t1 values
 (2
  ,0, 0, 0, 0, 0
  ,-128, 0
  ,-32768, 0
  ,-2147483648, 0
  ,-9223372036854775808, 0
  ,'','',''
  ,'','',''
  ,0x0,0x0,0x0
  ,0x0,0x0,0x0
 );

# null values
insert into t1 values
 (3
  ,NULL,NULL,NULL,NULL,NULL
  ,NULL,NULL
  ,NULL,NULL
  ,NULL,NULL
  ,NULL,NULL
  ,NULL,NULL,NULL
  ,NULL,NULL,NULL
  ,NULL,NULL,NULL
  ,NULL,NULL,NULL
 );

--vertical_results
select pk
  ,hex(a1), hex(a2), hex(a3), hex(a4), hex(a5)
  ,b1, b2
  ,c1 , c2
  ,d1 , d2
  ,e1 , e2 
  ,f1 , f2, f3
  ,g1 , g2, g3
  ,hex(h1), hex(h2), hex(h3)
  ,hex(i1), hex(i2), hex(i3)
 from t1 order by pk;

alter table t1 engine ndb;

select pk
  ,hex(a1), hex(a2), hex(a3), hex(a4), hex(a5)
  ,b1, b2
  ,c1 , c2
  ,d1 , d2
  ,e1 , e2 
  ,f1 , f2, f3
  ,g1 , g2, g3
  ,hex(h1), hex(h2), hex(h3)
  ,hex(i1), hex(i2), hex(i3)
 from t1 order by pk;
--horizontal_results

--source include/ndb_backup.inc

--let ndb_restore_filter=test t1
--let ndb_restore_opts=--verbose=0 --print_data --hex --fields-terminated-by=";"
--source include/ndb_backup_print.inc

--let ndb_restore_filter=test t1
--let ndb_restore_opts=--verbose=0 --print_data --hex --fields-terminated-by="," --fields-optionally-enclosed-by="'"
--source include/ndb_backup_print.inc


drop table t1;

# some binary char tests with trailing spaces
create table t1
 (pk int key
  ,f1 CHAR(1) BINARY, f2 CHAR(32) BINARY, f3 CHAR(255) BINARY
  ,g1 VARCHAR(32) BINARY, g2 VARCHAR(255) BINARY, g3 VARCHAR(1000) BINARY
  ,h1 BINARY(1), h2 BINARY(9), h3 BINARY(255)
  ,i1 VARBINARY(32), i2 VARBINARY(255), i3 VARBINARY(1000)
 ) engine ndb;

insert into t1 values
 (1
  ,'1','12345678901234567890123456789012','123456789 '
  ,'1   ','12345678901234567890123456789012  ','123456789 '
  ,0x20,0x123456789abcdef020, 0x012345000020
  ,0x1200000020,0x123456789abcdef000000020, 0x00123450000020
 );

create table t2 (pk int key, a int) engine ndb;
create table t3 (pk int key, a int) engine ndb;
create table t4 (pk int key, a int) engine ndb;

insert into t2 values (1,11),(2,12),(3,13),(4,14),(5,15);
insert into t3 values (1,21),(2,22),(3,23),(4,24),(5,25);
insert into t4 values (1,31),(2,32),(3,33),(4,34),(5,35);

--source include/ndb_backup.inc
--let ndb_restore_opts=--verbose=0 --print_data --hex --fields-enclosed-by="'" --fields-optionally-enclosed-by="X"
--let ndb_restore_filter=test t1
--source include/ndb_backup_print.inc

--error 0,1
--remove_file $MYSQLTEST_VARDIR/tmp/t1.txt
--error 0,1
--remove_file $MYSQLTEST_VARDIR/tmp/t2.txt
--error 0,1
--remove_file $MYSQLTEST_VARDIR/tmp/t3.txt
--error 0,1
--remove_file $MYSQLTEST_VARDIR/tmp/t4.txt

--let ndb_restore_opts=--verbose=0 --print_data --hex --tab $MYSQLTEST_VARDIR/tmp --append
--let ndb_restore_filter=test
--source include/ndb_backup_print.inc

--let $message= t1
--source include/show_msg.inc
--exec sort $MYSQLTEST_VARDIR/tmp/t1.txt
--let $message= t2
--source include/show_msg.inc
--exec sort $MYSQLTEST_VARDIR/tmp/t2.txt
--let $message= t3
--source include/show_msg.inc
--exec sort $MYSQLTEST_VARDIR/tmp/t3.txt
--let $message= t4
--source include/show_msg.inc
--exec sort $MYSQLTEST_VARDIR/tmp/t4.txt

--remove_file $MYSQLTEST_VARDIR/tmp/t1.txt
--remove_file $MYSQLTEST_VARDIR/tmp/t2.txt
--remove_file $MYSQLTEST_VARDIR/tmp/t3.txt
--remove_file $MYSQLTEST_VARDIR/tmp/t4.txt

# now test some other datatypes
drop table t1;
create table t1
 (pk int key
  ,a1 MEDIUMINT, a2 MEDIUMINT UNSIGNED
 ) engine ndb;

# max values
insert into t1 values(1, 8388607, 16777215);
# min values
insert into t1 values(2, -8388608, 0);
# small values
insert into t1 values(3, -1, 1);

# backup and print
--source include/ndb_backup.inc

--let ndb_restore_filter=test t1
--let ndb_restore_opts=--verbose=0 --print_data --hex --fields-terminated-by=";"
--source include/ndb_backup_print.inc

# clean up
drop table t1;
drop table t2;
drop table t3;
drop table t4;

# bug 37171 ndb_restore --print_data garbles decimal data

create table t1 (a int primary key,
                 normal decimal (20,10),
                 unormal decimal (20, 10) unsigned,
                 biggest decimal (65,30),
                 ubiggest decimal (65,30) unsigned,
                 small1 decimal (1,0),
                 usmall1 decimal (1,0) unsigned,
                 small2 decimal (1,1),
                 usmall2 decimal (1,1) unsigned) engine=ndb;

insert into t1 values (1,
                       -9999999999.9999999999,
                       9999999999.9999999999,
                       -99999999999999999999999999999999999.999999999999999999999999999999,
                       99999999999999999999999999999999999.999999999999999999999999999999,
                       -9,
                       9,
                       -0.9,
                       0.9);
insert into t1 values (2,
                       -1000000000.0000000001,
                       1000000000.0000000001,
                       -10000000000000000000000000000000000.000000000000000000000000000001,
                       10000000000000000000000000000000000.000000000000000000000000000001,
                       -1,
                       1,
                       -0.1,
                       0.1);
insert into t1 values (3,
                       -0099.0099,
                       0099.0099,
                       -0.1,     
                       0.1,      
                       -5,
                       5,
                       -0.5,
                       0.5);
--vertical_results

select * from t1 order by a;

--horizontal_results

# backup and print
--source include/ndb_backup.inc

--let ndb_restore_filter=test t1
--let ndb_restore_opts=--verbose=0 --print_data --hex --fields-terminated-by=";"
--source include/ndb_backup_print.inc        

# clean up
drop table t1;