MySQL Bugs: #6764: Query Browser can't execute UNION statement

Bug #6764	Query Browser can't execute UNION statement
Submitted:	23 Nov 2004 1:00	Modified:	6 Jan 2005 15:42
Reporter:	Quan Nguyen	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.1.2	OS:	Windows (Windows XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
MySQL Query Browser can't execute UNION statements.

How to repeat:
The SQL statement like:

SELECT state FROM authors
UNION
SELECT state FROM publishers;

cannot be executed, throwing the exception "The used SELECT statements have a different number of columns (Err. 1222)".

The same query, however, executes in MySQL Command Line Client tool of the Query Browser.

Hi,

Thank you for the report, but I wasn't able reproduce it. How exactly did you execute SELECT query?

I executed the following statements in the command-line console.

mysql> connect books
Connection id:    131
Current database: books

mysql> SELECT state FROM authors
    -> UNION
    -> SELECT state FROM publishers;
+-------+
| state |
+-------+
| NY    |
| CO    |
| CA    |
| FL    |
| NULL  |
+-------+
5 rows in set (0.03 sec)

mysql> SELECT state FROM authors;
+-------+
| state |
+-------+
| NY    |
| CO    |
| CA    |
| CA    |
| NY    |
| CA    |
| FL    |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT state FROM publishers;
+-------+
| state |
+-------+
| NY    |
| CA    |
| NULL  |
| CA    |
+-------+
4 rows in set (0.00 sec)

The last two statements were executable in the Query Toolbar of the Query Browser but not the first, as had been reported.

After further investigation, it seems that the QB fails when a single column is specified. The QB will work fine if I add another column, as follows:

SELECT city, state FROM authors
UNION
SELECT city, state FROM publishers;

I wasn't able to repeat it.
Please, check if you can execute SELECT statement using other tables or you get this error only when you use particular tables and column.

I rechecked (with QB 1.1.2) with the same result: The query could not be executed. I used the code in Listing 7.39 from "SQL: Visual Quickstart Guide" book (http://www.peachpit.com/vqs/sql).

Also, the string concatenation operator || does not work in QB Toolbar, returning 0 for the result set, even though the same query executed fine in MySQL Command Line Client:

SELECT au_fname || ' ' || au_lname AS "Name" FROM authors;

I've had a similiar issue before where it turned out that the column definitions were actually different- funnily the query executed in the command line client though.

Could you please provide the structure of tables 'authors' and 'publishers'?

OK, this is the script for generating the database, excerpted from the book:

DROP TABLE authors;
CREATE TABLE authors
  (
  au_id    CHAR(3)     NOT NULL,
  au_fname VARCHAR(15) NOT NULL,
  au_lname VARCHAR(15) NOT NULL,
  phone    VARCHAR(12) NULL    ,
  address  VARCHAR(20) NULL    ,
  city     VARCHAR(15) NULL    ,
  state    CHAR(2)     NULL    ,
  zip      CHAR(5)     NULL    ,
  CONSTRAINT authors_pk PRIMARY KEY (au_id)
  );

DROP TABLE publishers;
CREATE TABLE publishers
  (
  pub_id   CHAR(3)     NOT NULL,
  pub_name VARCHAR(20) NOT NULL,
  city     VARCHAR(15) NOT NULL,
  state    CHAR(2)     NULL    ,
  country  VARCHAR(15) NOT NULL,
  CONSTRAINT publishers_pk PRIMARY KEY (pub_id)
  );

DROP TABLE titles;
CREATE TABLE titles
  (
  title_id   CHAR(3)      NOT NULL,
  title_name VARCHAR(40)  NOT NULL,
  type       VARCHAR(10)  NULL    ,
  pub_id     CHAR(3)      NOT NULL,
  pages      INTEGER      NULL    ,
  price      DECIMAL(5,2) NULL    ,
  sales      INTEGER      NULL    ,
  pubdate    DATE         NULL    ,
  contract   SMALLINT     NOT NULL,
  CONSTRAINT titles_pk PRIMARY KEY (title_id)
  );

DROP TABLE title_authors;
CREATE TABLE title_authors
  (
  title_id      CHAR(3)      NOT NULL,
  au_id         CHAR(3)      NOT NULL,
  au_order      SMALLINT     NOT NULL,
  royalty_share DECIMAL(5,2) NOT NULL,
  CONSTRAINT title_authors_pk PRIMARY KEY (title_id, au_id)
  );

DROP TABLE royalties;
CREATE TABLE royalties
  (
  title_id     CHAR(3)      NOT NULL,
  advance      DECIMAL(9,2) NULL    ,
  royalty_rate DECIMAL(5,2) NULL    ,
  CONSTRAINT royalties_pk PRIMARY KEY (title_id)
  );

INSERT INTO authors VALUES('A01','Sarah','Buchman','718-496-7223',
  '75 West 205 St','Bronx','NY','10468');
INSERT INTO authors VALUES('A02','Wendy','Heydemark','303-986-7020',
  '2922 Baseline Rd','Boulder','CO','80303');
INSERT INTO authors VALUES('A03','Hallie','Hull','415-549-4278',
  '3800 Waldo Ave, #14F','San Francisco','CA','94123');
INSERT INTO authors VALUES('A04','Klee','Hull','415-549-4278',
  '3800 Waldo Ave, #14F','San Francisco','CA','94123');
INSERT INTO authors VALUES('A05','Christian','Kells','212-771-4680',
  '114 Horatio St','New York','NY','10014');
INSERT INTO authors VALUES('A06','','Kellsey','650-836-7128',
  '390 Serra Mall','Palo Alto','CA','94305');
INSERT INTO authors VALUES('A07','Paddy','O''Furniture','941-925-0752',
  '1442 Main St','Sarasota','FL','34236');

INSERT INTO publishers VALUES('P01','Abatis Publishers','New York','NY','USA');
INSERT INTO publishers VALUES('P02','Core Dump Books','San Francisco','CA','USA');
INSERT INTO publishers VALUES('P03','Schadenfreude Press','Hamburg',NULL,'Germany');
INSERT INTO publishers VALUES('P04','Tenterhooks Press','Berkeley','CA','USA');

INSERT INTO titles VALUES('T01','1977!','history','P01',
  107,21.99,566,DATE '2000-08-01',1);
INSERT INTO titles VALUES('T02','200 Years of German Humor','history','P03',
  14,19.95,9566,DATE '1998-04-01',1);
INSERT INTO titles VALUES('T03','Ask Your System Administrator','computer','P02',
  1226,39.95,25667,DATE '2000-09-01',1);
INSERT INTO titles VALUES('T04','But I Did It Unconsciously','psychology','P04',
  510,12.99,13001,DATE '1999-05-31',1);
INSERT INTO titles VALUES('T05','Exchange of Platitudes','psychology','P04',
  201,6.95,201440,DATE '2001-01-01',1);
INSERT INTO titles VALUES('T06','How About Never?','biography','P01',
  473,19.95,11320,DATE '2000-07-31',1);
INSERT INTO titles VALUES('T07','I Blame My Mother','biography','P03',
  333,23.95,1500200,DATE '1999-10-01',1);
INSERT INTO titles VALUES('T08','Just Wait Until After School','children','P04',
  86,10.00,4095,DATE '2001-06-01',1);
INSERT INTO titles VALUES('T09','Kiss My Boo-Boo','children','P04',
  22,13.95,5000,DATE '2002-05-31',1);
INSERT INTO titles VALUES('T10','Not Without My Faberge Egg','biography','P01',
  NULL,NULL,NULL,NULL,0);
INSERT INTO titles VALUES('T11','Perhaps It''s a Glandular Problem','psychology','P04',
  826,7.99,94123,DATE '2000-11-30',1);
INSERT INTO titles VALUES('T12','Spontaneous, Not Annoying','biography','P01',
  507,12.99,100001,DATE '2000-08-31',1);
INSERT INTO titles VALUES('T13','What Are The Civilian Applications?','history','P03',
  802,29.99,10467,DATE '1999-05-31',1);

INSERT INTO title_authors VALUES('T01','A01',1,1.0);
INSERT INTO title_authors VALUES('T02','A01',1,1.0);
INSERT INTO title_authors VALUES('T03','A05',1,1.0);
INSERT INTO title_authors VALUES('T04','A03',1,0.6);
INSERT INTO title_authors VALUES('T04','A04',2,0.4);
INSERT INTO title_authors VALUES('T05','A04',1,1.0);
INSERT INTO title_authors VALUES('T06','A02',1,1.0);
INSERT INTO title_authors VALUES('T07','A02',1,0.5);
INSERT INTO title_authors VALUES('T07','A04',2,0.5);
INSERT INTO title_authors VALUES('T08','A06',1,1.0);
INSERT INTO title_authors VALUES('T09','A06',1,1.0);
INSERT INTO title_authors VALUES('T10','A02',1,1.0);
INSERT INTO title_authors VALUES('T11','A03',2,0.3);
INSERT INTO title_authors VALUES('T11','A04',3,0.3);
INSERT INTO title_authors VALUES('T11','A06',1,0.4);
INSERT INTO title_authors VALUES('T12','A02',1,1.0);
INSERT INTO title_authors VALUES('T13','A01',1,1.0);

INSERT INTO royalties VALUES('T01',10000,0.05);
INSERT INTO royalties VALUES('T02',1000,0.06);
INSERT INTO royalties VALUES('T03',15000,0.07);
INSERT INTO royalties VALUES('T04',20000,0.08);
INSERT INTO royalties VALUES('T05',100000,0.09);
INSERT INTO royalties VALUES('T06',20000,0.08);
INSERT INTO royalties VALUES('T07',1000000,0.11);
INSERT INTO royalties VALUES('T08',0,0.04);
INSERT INTO royalties VALUES('T09',0,0.05);
INSERT INTO royalties VALUES('T10',NULL,NULL);
INSERT INTO royalties VALUES('T11',100000,0.07);
INSERT INTO royalties VALUES('T12',50000,0.09);
INSERT INTO royalties VALUES('T13',20000,0.06);

I recovered the same problem with qb. Executing the query with cc or php-myadmin brings results, but not qb.

My query needs to union nine tables, the syntax is as follow:

SELECT b
FROM stb1346
WHERE b !=  ''
UNION SELECT b from stb1351
UNION SELECT b from stb1355
UNION SELECT b from stb1363
UNION SELECT b from stb1368
UNION SELECT b from stb1376
UNION SELECT b from stb1382
UNION SELECT b from stb1390
UNION SELECT b from stb1398
order by b

As it is the same type of data only splitted because of archive needs, the tables are totally similiar.

Raphael

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".