~drizzle-trunk/drizzle/development

#

# Test of refering to old values

#

--disable_warnings

drop table if exists t1,t2,t3;

--enable_warnings

create table t1 (a int not null);

insert into t1 values (1);

insert into t1 values (a+2);

insert into t1 values (a+3),(a+4);

insert into t1 values (5),(a+6);

select * from t1;

drop table t1;

#

# Test of duplicate key values with packed keys

#

create table t1 (id int not null auto_increment primary key, username varchar(32) not null, unique (username));

insert into t1 values (NULL,"mysql");

insert into t1 values (NULL,"mysql ab");

insert into t1 values (NULL,"mysql a");

insert into t1 values (NULL,"r1manic");

insert into t1 values (NULL,"r1man");

drop table t1;

#

# Test insert syntax

#

create table t1 (a int not null auto_increment, primary key (a), t timestamp null, c char(10) default "hello", i int);

insert into t1 values (default,default,default,default);

insert into t1 values (default,default,default,default);

--error ER_INVALID_TIMESTAMP_VALUE # Bad timestamp

insert into t1 values (4,0,"a",5);

insert into t1 values (default,default,default,default);

select a,t is not null,c,i from t1;

truncate table t1;

insert into t1 set a=default,t=default,c=default;

insert into t1 set a=default,t=default,c=default,i=default;

insert into t1 set a=4,t= NULL,c="a",i=5;

insert into t1 set a=5,t= NULL,c="a",i=null;

insert into t1 set a=default,t=default,c=default,i=default;

select a,t is not null,c,i from t1;

drop table t1;

#

#Test of behaviour with INSERT VALUES (NULL)

#

create table t1 (id int NOT NULL DEFAULT 8);

--error ER_BAD_NULL_ERROR

insert into t1 values(NULL);

--error ER_BAD_NULL_ERROR

insert into t1 values (1), (NULL), (2);

select * from t1;

drop table t1;

#

# Test if insert ... select distinct

#

create table t1 (email varchar(50));

insert into t1 values ('sasha@mysql.com'),('monty@mysql.com'),('foo@hotmail.com'),('foo@aol.com'),('bar@aol.com');

create table t2(id int not null auto_increment primary key, t2 varchar(50), unique(t2));

insert into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1;

select * from t2;

drop table t1,t2;

#

# Test of mysqld crash with fully qualified column names

#

--disable_warnings

drop database if exists mysqltest;

--enable_warnings

create database mysqltest;

use mysqltest;

create table t1 (c int);

insert into mysqltest.t1 set mysqltest.t1.c = '1';

drop database mysqltest;

use test;

# End of 4.1 tests

#

# Test automatic result buffering with INSERT INTO t1 ... SELECT ... FROM t1

#

create table t1(id1 int not null auto_increment primary key, t char(12));

create table t2(id2 int not null, t char(12));

create table t3(id3 int not null, t char(12), index(id3));

disable_query_log;

begin;

let $1 = 100;

while ($1)

 {

  let $2 = 5;

  eval insert into t1(t) values ('$1'); 

  while ($2)

   {

     eval insert into t2(id2,t) values ($1,'$2'); 

     let $3 = 10;

     while ($3)

     {

       eval insert into t3(id3,t) values ($1,'$2'); 

       dec $3;

     }

     dec $2; 

   }

  dec $1;

 }

enable_query_log;

select count(*) from t2;

--error ER_USE_SQL_BIG_RESULT

insert into  t2 select t1.* from t1, t2 t, t3 where  t1.id1 = t.id2 and t.id2 = t3.id3;

insert into  t2 select SQL_BIG_RESULT t1.* from t1, t2 t, t3 where  t1.id1 = t.id2 and t.id2 = t3.id3;

select count(*) from t2;

COMMIT;

drop table t1,t2,t3;

#

# Test different cases of duplicate fields

#

create table t1 (a int, b int);

insert into t1 (a,b) values (a,b);

insert into t1 SET a=1, b=a+1;

insert into t1 (a,b) select 1,2;

INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a);

--error ER_FIELD_SPECIFIED_TWICE

replace into t1 (a,a) select 100, 'hundred';

--error ER_FIELD_SPECIFIED_TWICE

insert into t1 (a,b,b) values (1,1,1);

--error ER_WRONG_VALUE_COUNT_ON_ROW

insert into t1 (a,a) values (1,1,1);

--error ER_FIELD_SPECIFIED_TWICE

insert into t1 (a,a) values (1,1);

--error ER_FIELD_SPECIFIED_TWICE

insert into t1 SET a=1,b=2,a=1;

--error ER_FIELD_SPECIFIED_TWICE

insert into t1 (b,b) select 1,2;

--error ER_FIELD_SPECIFIED_TWICE

INSERT INTO t1 (b,b) SELECT 0,0 ON DUPLICATE KEY UPDATE a = a + VALUES (a);

drop table t1;

#

# Test for values returned by ROW_COUNT() function

# (and thus for values returned by mysql_affected_rows())

# for various forms of INSERT

#

create table t1 (id int primary key, data int);

insert into t1 values (1, 1), (2, 2), (3, 3);

select row_count();

insert ignore into t1 values (1, 1);

select row_count();

# Reports that 2 rows are affected (1 deleted + 1 inserted)

replace into t1 values (1, 11);

select row_count();

replace into t1 values (4, 4);

select row_count();

# Reports that 2 rows are affected. This conforms to documentation.

# (Useful for differentiating inserts from updates).

insert into t1 values (2, 2) on duplicate key update data= data + 10;

select row_count();

insert into t1 values (5, 5) on duplicate key update data= data + 10;

select row_count();

drop table t1;

# Test of INSERT IGNORE and re-using auto_increment values

create table t1 (id int primary key auto_increment, data int, unique(data));

insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120);

insert ignore into t1 values(NULL,10),(NULL,20),(NULL,110),(NULL,120),(NULL,100),(NULL,90);

insert ignore into t1 values(NULL,130),(NULL,140),(500,110),(550,120),(450,100),(NULL,150);

# PBXT differs from InnoDB here. Main reason is that inserting

# 500 causes auto inc value to be set to 501, this is never

# undone because of possible concurrent inserts.

select * from t1 order by id;

drop table t1;

#

# Bug #26788: mysqld (debug) aborts when inserting specific numbers into char

#             fields

#

CREATE TABLE t1 (

  a char(20) NOT NULL,

  b char(7) DEFAULT NULL,

  c char(4) DEFAULT NULL

);

INSERT INTO t1(a,b,c) VALUES (9.999999e+0, 9.999999e+0, 9.999e+0);

INSERT INTO t1(a,b) VALUES (1.225e-04, 1.225e-04);

INSERT INTO t1(a,b) VALUES (1.225e-01, 1.225e-01);

INSERT INTO t1(a,b) VALUES (1.225877e-01, 1.225877e-01);

INSERT INTO t1(a,b) VALUES (1.225e+01, 1.225e+01);

INSERT INTO t1(a,b,c) VALUES (1.225e+01, 1.225e+01, 1.225e+01);

INSERT INTO t1(a,b) VALUES (1.225e+05, 1.225e+05);

INSERT INTO t1(a,b) VALUES (1.225e+10, 1.225e+10);

INSERT INTO t1(a,b) VALUES (1.225e+15, 1.225e+15);

INSERT INTO t1(a,b) VALUES (5000000e+0, 5000000e+0);

INSERT INTO t1(a,b) VALUES (1.25e+78, 1.25e+78);

INSERT INTO t1(a,b) VALUES (1.25e-94, 1.25e-94);

INSERT INTO t1(a,b) VALUES (1.25e+203, 1.25e+203);

INSERT INTO t1(a,b) VALUES (1.25e-175, 1.25e-175);

INSERT INTO t1(a,c) VALUES (1.225e+0, 1.225e+0);

INSERT INTO t1(a,c) VALUES (1.37e+0, 1.37e+0);

INSERT INTO t1(a,c) VALUES (-1.37e+0, -1.37e+0);

INSERT INTO t1(a,c) VALUES (-1.87e-2, -1.87e-2);

INSERT INTO t1(a,c) VALUES (5000e+0, 5000e+0);

INSERT INTO t1(a,c) VALUES (-5000e+0, -5000e+0);

SELECT * FROM t1;

DROP TABLE t1;

CREATE TABLE t1 (

  a char(20) NOT NULL,

  b char(7) DEFAULT NULL,

  c char(5)

);

INSERT INTO t1(a,b,c) VALUES (9.999999e+0, 9.999999e+0, 9.999e+0);

INSERT INTO t1(a,b,c) VALUES (1.225e-05, 1.225e-05, 1.225e-05);

INSERT INTO t1(a,b) VALUES (1.225e-04, 1.225e-04);

INSERT INTO t1(a,b) VALUES (1.225e-01, 1.225e-01);

INSERT INTO t1(a,b) VALUES (1.225877e-01, 1.225877e-01);

INSERT INTO t1(a,b) VALUES (1.225e+01, 1.225e+01);

INSERT INTO t1(a,b,c) VALUES (1.225e+01, 1.225e+01, 1.225e+01);

INSERT INTO t1(a,b) VALUES (1.225e+05, 1.225e+05);

INSERT INTO t1(a,b) VALUES (1.225e+10, 1.225e+10);

INSERT INTO t1(a,b) VALUES (1.225e+15, 1.225e+15);

INSERT INTO t1(a,b) VALUES (5000000e+0, 5000000e+0);

INSERT INTO t1(a,b) VALUES (1.25e+78, 1.25e+78);

INSERT INTO t1(a,b) VALUES (1.25e-94, 1.25e-94);

INSERT INTO t1(a,b) VALUES (1.25e+203, 1.25e+203);

INSERT INTO t1(a,b) VALUES (1.25e-175, 1.25e-175);

INSERT INTO t1(a,c) VALUES (1.225e+0, 1.225e+0);

INSERT INTO t1(a,c) VALUES (1.37e+0, 1.37e+0);

INSERT INTO t1(a,c) VALUES (-1.37e+0, -1.37e+0);

INSERT INTO t1(a,c) VALUES (1.87e-3, 1.87e-3);

INSERT INTO t1(a,c) VALUES (-1.87e-2, -1.87e-2);

INSERT INTO t1(a,c) VALUES (5000e+0, 5000e+0);

INSERT INTO t1(a,c) VALUES (-5000e+0, -5000e+0);

SELECT * FROM t1;

DROP TABLE t1;

#

# Bug #31152: assertion in Field_str::store(double)

#

CREATE TABLE t (a CHAR(10),b INT);

INSERT INTO t VALUES (),(),();

INSERT INTO t(a) SELECT rand() FROM t;

DROP TABLE t;

#

# Bug #30453: String not cast to int correctly

#

CREATE TABLE t1 (c1 INT NOT NULL);

INSERT INTO t1 VALUES(4188.32999999999992724042385816574096679687500),

('4188.32999999999992724042385816574096679687500'), (4188);

SELECT * FROM t1;

CREATE TABLE t2 (c1 BIGINT);

INSERT INTO t2 VALUES('15449237462.0000000000');

SELECT * FROM t2;

DROP TABLE t1, t2;

--echo End of 5.0 tests.