~drizzle-trunk/drizzle/development

Committer: Jay Pipes
Date: 2008-12-19 21:34:33 UTC
mto: This revision was merged to the branch mainline in revision 729.
Revision ID: jpipes@serialcoder-20081219213433-x9wc07ts0bubd5jb

OLAP test now fixed.  We throw a syntax error upon seeing WITH CUBE, not
a "We don't support this yet" error.  Other than that, only minor
SQL syntax fixes and removal of VIEW-specific stuff.

MyISAM.test slowly working through.  Hit a bug in REPAIR TABLE.

files renamed:
tests/suite/broken/r/olap.result => tests/r/olap.result

tests/suite/broken/t/olap.test => tests/t/olap.test

files modified:
tests/suite/broken/t/myisam.test

tests/suite/broken/t/mysql_protocols.test

Show diffs side-by-side

added added

removed removed

tests/r/olap.result

255

:TV: 600 120.00000

256

NULL 7785 519.00000

257

select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;

258

ERROR 42000: This version of MySQL doesn't yet support 'CUBE'

258

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'cube' at line 1

259

explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;

260

ERROR 42000: This version of MySQL doesn't yet support 'CUBE'

260

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'cube' at line 1

261

select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube union all select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;

262

ERROR 42000: This version of MySQL doesn't yet support 'CUBE'

262

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'cube union all select product, country_id , year, sum(profit) from t1 group by p' at line 1

263

drop table t1,t2;

264

CREATE TABLE t1 (i int);

265

INSERT INTO t1 VALUES(100);

442

a SUM(b)

443

1 4

444

DROP TABLE t1;

445

CREATE TABLE t1 (a int(11) NOT NULL);

445

CREATE TABLE t1 (a int NOT NULL);

446

INSERT INTO t1 VALUES (1),(2);

447

SELECT a, SUM(a) m FROM t1 GROUP BY a WITH ROLLUP;

448

a m

456

NULL 3

457

DROP TABLE t1;

458

set div_precision_increment= @sav_dpi;

459

CREATE TABLE t1 (a int(11));

459

CREATE TABLE t1 (a int);

460

INSERT INTO t1 VALUES (1),(2);

461

SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d

462

GROUP BY a;

489

5 5 6 5x 10 5

490

NULL 8 9 8x 16 8

491

DROP TABLE t1;

492

CREATE TABLE t1 (a int(11));

492

CREATE TABLE t1 (a int);

493

INSERT INTO t1 VALUES (1),(2);

494

SELECT a, a+1, SUM(a) FROM t1 GROUP BY a WITH ROLLUP;

495

a a+1 SUM(a)

555

4 TEST

556

TEST TEST

557

DROP TABLE t1,t2;

558

CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL);

558

CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);

559

INSERT INTO t1 VALUES (1, 1);

560

INSERT INTO t1 VALUES (1, 2);

561

SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;

567

1 NULL NULL 2

568

NULL NULL NULL 2

569

DROP TABLE t1;

570

CREATE TABLE t1 (a int(11) NOT NULL);

570

CREATE TABLE t1 (a int NOT NULL);

571

INSERT INTO t1 VALUES (1),(2);

572

SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;

573

a a + 1 COUNT(*)

668

x NULL 150

669

NULL NULL 150

670

DROP TABLE t1;

671

CREATE TABLE t1(id int, type char(1));

672

INSERT INTO t1 VALUES

673

(1,"A"),(2,"C"),(3,"A"),(4,"A"),(5,"B"),

674

(6,"B"),(7,"A"),(8,"C"),(9,"A"),(10,"C");

675

CREATE VIEW v1 AS SELECT * FROM t1;

676

SELECT type FROM t1 GROUP BY type WITH ROLLUP;

677

type

678

679

680

681

NULL

682

SELECT type FROM v1 GROUP BY type WITH ROLLUP;

683

type

684

685

686

687

NULL

688

EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP;

689

id select_type table type possible_keys key key_len ref rows Extra

690

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using filesort

691

DROP VIEW v1;

692

DROP TABLE t1;

693

CREATE TABLE t1 (a int(11) NOT NULL);

694

INSERT INTO t1 VALUES (1),(2);

695

CREATE VIEW v1 AS

696

SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;

697

DESC v1;

698

Field Type Null Key Default Extra

699

a bigint(11) YES NULL

700

LENGTH(a) bigint(10) YES NULL

701

COUNT(*) bigint(21) NO 0

702

SELECT * FROM v1;

703

a LENGTH(a) COUNT(*)

704

1 1 1

705

2 1 1

706

NULL NULL 2

707

DROP VIEW v1;

708

DROP TABLE t1;

709

671

CREATE TABLE t1 (a int, KEY (a));

710

672

INSERT INTO t1 VALUES (3), (1), (4), (1), (3), (1), (1);

711

673

SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) as t;

Older »