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:Microsoft Windows (Windows XP)
Assigned to: Bugs System CPU Architecture:Any

[23 Nov 2004 1:00] Quan Nguyen
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.
[23 Nov 2004 16:01] MySQL Verification Team
Hi,

Thank you for the report, but I wasn't able reproduce it. How exactly did you execute SELECT query?
[24 Nov 2004 0:09] Quan Nguyen
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.
[24 Nov 2004 19:29] Quan Nguyen
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;
[24 Nov 2004 21:39] MySQL Verification Team
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.
[1 Dec 2004 18:24] Quan Nguyen
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;
[6 Dec 2004 10:06] Andreas Goetz
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.
[6 Dec 2004 15:09] MySQL Verification Team
Could you please provide the structure of tables 'authors' and 'publishers'?
[6 Dec 2004 15:42] Quan Nguyen
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);
[10 Dec 2004 12:46] Raphael Matthias Krug
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
[14 Feb 2005 22:54] Bugs System
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".