~drizzle-trunk/drizzle/development

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

258

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

259

explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with 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

260

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

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: 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

262

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

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 NOT NULL);

445

CREATE TABLE t1 (a int(11) 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);

459

CREATE TABLE t1 (a int(11));

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);

492

CREATE TABLE t1 (a int(11));

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 NOT NULL, b INT NOT NULL);

558

CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) 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 NOT NULL);

570

CREATE TABLE t1 (a int(11) 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;

671

709

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

672

710

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

673

711

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

Older »