ndb_insert.test

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

(1931,1931,1931),(1932,1932,1932),(1933,1933,1933),(1934,1934,1934),(1935,1935,1935),
(1936,1936,1936),(1937,1937,1937),(1938,1938,1938),(1939,1939,1939),(1940,1940,1940),
(1941,1941,1941),(1942,1942,1942),(1943,1943,1943),(1944,1944,1944),(1945,1945,1945),
(1946,1946,1946),(1947,1947,1947),(1948,1948,1948),(1949,1949,1949),(1950,1950,1950),
(1951,1951,1951),(1952,1952,1952),(1953,1953,1953),(1954,1954,1954),(1955,1955,1955),
(1956,1956,1956),(1957,1957,1957),(1958,1958,1958),(1959,1959,1959),(1960,1960,1960),
(1961,1961,1961),(1962,1962,1962),(1963,1963,1963),(1964,1964,1964),(1965,1965,1965),
(1966,1966,1966),(1967,1967,1967),(1968,1968,1968),(1969,1969,1969),(1970,1970,1970),
(1971,1971,1971),(1972,1972,1972),(1973,1973,1973),(1974,1974,1974),(1975,1975,1975),
(1976,1976,1976),(1977,1977,1977),(1978,1978,1978),(1979,1979,1979),(1980,1980,1980),
(1981,1981,1981),(1982,1982,1982),(1983,1983,1983),(1984,1984,1984),(1985,1985,1985),
(1986,1986,1986),(1987,1987,1987),(1988,1988,1988),(1989,1989,1989),(1990,1990,1990),
(1991,1991,1991),(1992,1992,1992),(1993,1993,1993),(1994,1994,1994),(1995,1995,1995),
(1996,1996,1996),(1997,1997,1997),(1998,1998,1998),(1999,1999,1999);

SELECT COUNT(*) FROM t1;


#
# Insert duplicate rows
# 
--error 1062
INSERT INTO t1 VALUES (1,1,1);

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);

select count(*) from t1;


#
# Test that select count(*) can see inserts made in the same transaction
#
begin;
SELECT COUNT(*) FROM t1;
INSERT INTO t1 VALUES 
(2001,2001,2001),(2002,2002,2002),(2003,2003,2003),(2004,2004,2004),(2005,2005,2005);
SELECT COUNT(*) FROM t1;
rollback;

#
# Insert duplicate rows, inside transaction
# try to commit 
#
begin;

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);

--error 1296
commit;

select * from t1 where pk1=1;
select * from t1 where pk1=10;
select count(*) from t1 where pk1 <= 10 order by pk1;
select count(*) from t1;


#
# Insert duplicate rows, inside transaction
# rollback 
#
begin;

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);

rollback;

select * from t1 where pk1=1;
select * from t1 where pk1=10;
select count(*) from t1 where pk1 <= 10 order by pk1;
select count(*) from t1;


#
# Insert duplicate rows, inside transaction
# then try to select, finally rollback
#
begin;

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);
--error 1296
SELECT * FROM t1 WHERE pk1=10;

rollback;

select * from t1 where pk1=1;
select * from t1 where pk1=10;
select count(*) from t1 where pk1 <= 10 order by pk1;
select count(*) from t1;


#
# Insert duplicate rows, inside transaction
# then try to select, finally commit
#
begin;

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);

--error 1296
SELECT * FROM t1 WHERE pk1=10;

--error 1296
SELECT * FROM t1 WHERE pk1=10;

--error 1296
commit;

select * from t1 where pk1=1;
select * from t1 where pk1=10;
select count(*) from t1 where pk1 <= 10 order by pk1;
select count(*) from t1;


#
# Insert duplicate rows, inside transaction
# then try to do another insert
#
begin;

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);

--error 1296
INSERT INTO t1 values (4000, 40, 44);

rollback;

select * from t1 where pk1=1;
select * from t1 where pk1=10;
select count(*) from t1 where pk1 <= 10 order by pk1;
select count(*) from t1;

#
# Insert duplicate rows using "insert .. select" 

#
--error 1022
insert into t1 select * from t1 where b < 10 order by pk1;

DELETE FROM t1 WHERE pk1=2;

begin;
INSERT IGNORE INTO t1 VALUES(1,2,3),(2,3,4);
select * from t1 where pk1 < 3 order by pk1;
rollback;

INSERT IGNORE INTO t1 VALUES(1,2,3),(2,3,4);
select * from t1 where pk1 < 3 order by pk1;

REPLACE INTO t1 values(1, 78, 3);
select * from t1 where pk1=1;

INSERT INTO t1 VALUES(1,1,1),(3,4,5) ON DUPLICATE KEY UPDATE b=79;
select * from t1 where pk1 < 4 order by pk1;

INSERT INTO t1 VALUES(1,1,1),(3,4,5) ON DUPLICATE KEY UPDATE b=pk1+c;
select * from t1 where pk1 < 4 order by pk1;

DELETE FROM t1 WHERE pk1 = 2 OR pk1 = 4 OR pk1 = 6;
INSERT INTO t1 VALUES(1,1,1),(2,2,17),(3,4,5) ON DUPLICATE KEY UPDATE pk1=b;
select * from t1 where pk1 = b and b != c order by pk1;

# The following test case currently does not work
#DELETE FROM t1;
#CREATE UNIQUE INDEX bi ON t1(b);
#INSERT INTO t1 VALUES 
#(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
#(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);
#INSERT INTO t1 VALUES(0,1,0),(21,21,21) ON DUPLICATE KEY UPDATE pk1=b+10,c=b+10;
#select * from t1 order by pk1;

DROP TABLE t1;

#
# Bug #6331: problem with 'insert ignore'
#

CREATE TABLE t1(a INT) ENGINE=ndb;
INSERT IGNORE INTO t1 VALUES (1);
INSERT IGNORE INTO t1 VALUES (1);
INSERT IGNORE INTO t1 SELECT a FROM t1;
INSERT IGNORE INTO t1 SELECT a FROM t1;
INSERT IGNORE INTO t1 SELECT a FROM t1;
INSERT IGNORE INTO t1 VALUES (1);
INSERT IGNORE INTO t1 VALUES (1);
SELECT * FROM t1;
DROP TABLE t1;

# End of 4.1 tests