Bug #9116 Subqueries as tables creating errors
Submitted: 11 Mar 2005 3:19 Modified: 20 Mar 2005 10:23
Reporter: Kamran Lotfi Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.10 OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any

[11 Mar 2005 3:19] Kamran Lotfi
Description:
Did the syntax change for subqueries between version 4.1.7 and 4.1.10?

The following query works just fine in version 4.1.7, but does not have any value for the two variables in version 4.1.10.  The both work just fine  

set @date='2005-12-04';
set @merchant_id=2;

insert into rpt_customer_segmentation (week_of_date, merchant_id,category_name,spending_threshold, total_customers ,
gross_revenue , spending_per_visit, visits_per_week,visited_last_week,percent_visited_last_week)
select @date as week_of_date ,@merchant_id as merchant_id ,c.category_name,c.spending_threshold, c.total_customers,
 ifnull(sum(transaction_amount),0) as gross_revenue
,ifnull(ifnull(sum(transaction_amount),0)/count(distinct transaction_id),0) as spending_per_visit
,ifnull(count(distinct t.account_Id)/count(am.account_id),0) as visits_per_week
,count(distinct t.account_id) as visited_last_week,count(distinct t.account_id)/c.total_customers*100 as percent_visited_last_week
from customer_categories c 
left join  account_merchant am on (am.merchant_customer_category_id=c.merchant_customer_category_id)
left join (select * from transaction where transaction_date between @date and date_add(@date, interval 7 DAY))  as t on (t.account_id=am.account_id)
group by c.category_name;

How to repeat:
I have tried writing a simple query to duplicate it, but can't.  I don't need the insert statement to make it work though.  If I just set the variables and execute the select if will return a value of 0 and a date of 0:00..

BTW, these are INNODB tables that is why I submitted the bug here.

Suggested fix:
Not sure what is going on here.
[11 Mar 2005 8:45] Heikki Tuuri
Hi!

I am changing this to a 'MySQL Server' bug, since the bug apparently is in the MySQL interpreter.

What do you mean by that the two variables 'do not get any value' in 4.1.10?

Please post a 4.1.7 and 4.1.10 mysql interactive SQL client session that demonstrates the problem.

Regards,

Heikki
[13 Mar 2005 1:10] Kamran Lotfi
The data inserted for week_of_date and merchant_id are null in the table after the insert.  When I use another approach instead of having a subquery in the select statement, the problem goes away.  Also if I just execute the select statement without insert, I still have the same problem so the issue is in the select statement.

BTW, what do you mean by an interactive client session?
[17 Mar 2005 4:16] Jorge del Conde
Hi!

By an interactive session, we mean something similar to the following:

shell> mysql
mysql> all your queries;
mysql> and steps you took to replicate;
mysql> this issue;

Also, the create table statement of your table will also be very helpful to us.

Thanks.
[20 Mar 2005 7:41] Kamran Lotfi
creates the tables and shows the null fields

Attachment: error.sql (application/octet-stream, text), 2.10 KiB.

[20 Mar 2005 7:47] Kamran Lotfi
I just added a script that creates the tables, inserts values and finally has the select statement I was talking about with null values.  When this script is run on version 4.1.10, the values for week_of_date and merchant_id in the results from the query are null, even though they are specified in the variables above. 

If you remove the subquery in the select statement and create a temporary table with it instead.  Then join the temporary table in the select statement, the results are as expected.  The first two columns return the values set above.

BTW, I ran this script using the MYSQL Control Center Query Window.  It has the same error with the Query Browser though. 

Finally for you reference, here is the script in text form just in case you can't read the attached Windows files

Please let me know if you need anything else.  Hope this helps.  

Thanks,

Kamran Lotfi

drop table if exists account;

create table account_merchant(
account_id varchar(100) not null,
merchant_id int unsigned not null,
merchant_customer_category_id int unsigned not null,
point_balance int unsigned ,
dollar_balance float(10,2) ,
email_opt_in bit default 1,
direct_mail_opt_in bit default 1,
mailing_list_opt_in bit default 1,
first_visit_date datetime not null ,
last_visit_date datetime,
customer_category_spending float(10,2),
customer_category_visits int unsigned,
index (merchant_id),
index (merchant_customer_category_Id),
primary key (account_id,merchant_id)
) type=INNODB;

drop table if exists transaction;

create table transaction (
transaction_id bigint unsigned auto_increment not null primary key,
transaction_quantity int not null,
transaction_date datetime,
merchant_id int unsigned not null,
mobile_device_id varchar(100) not null,
account_id varchar(100) not null,
transaction_type_id int unsigned not null,
transaction_amount  float(10,2) not null 
) type=INNODB;

delete from account_merchant;
insert into account_merchant (account_id,merchant_id,merchant_customer_category_id)
values('0001',1,1),
('0002',1,2),
('0003',1,2);

delete from transaction;
insert into transaction(transaction_id,transaction_quantity,transaction_date,merchant_id,mobile_device_id,account_id,transaction_type_id,transaction_amount)
values(1,1,'2004-12-01',1,1,'0001',1,23.23)
,(2,1,'2004-12-01',1,1,'0002',1,23.23)
,(3,1,'2004-12-01',1,1,'0003',1,23.23)
,(4,1,'2004-12-01',1,1,'0001',1,23.23)
,(5,1,'2004-12-01',1,1,'0002',1,23.23)
,(6,1,'2004-12-01',1,1,'0003',1,23.23)
,(7,1,'2004-12-01',1,1,'0003',1,23.23);

set @rpt_date='2004-12-01';
set @merchant_id=1;

# insert regular values
select @rpt_date as week_of_date ,@merchant_id as merchant_id,t.account_id,
count(distinct transaction_id) as spending_per_visit
from account_merchant am 
left join (select * from transaction where transaction_date between @rpt_date and date_add(@rpt_date, interval 7 DAY) and merchant_id=@merchant_id ) as t on (t.account_id=am.account_id)
group by t.account_id;
[20 Mar 2005 10:23] MySQL Verification Team
Thank you for the bug report, this issue is already fixed on BK source:

    -> group by t.account_id;
+--------------+-------------+------------+--------------------+
| week_of_date | merchant_id | account_id | spending_per_visit |
+--------------+-------------+------------+--------------------+
| NULL         | NULL        | 0001       |                  2 |
| NULL         | NULL        | 0002       |                  2 |
| NULL         | NULL        | 0003       |                  3 |
+--------------+-------------+------------+--------------------+
3 rows in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.10-nt |
+-----------+
1 row in set (0.00 sec)

    -> group by t.account_id;
+--------------+-------------+------------+--------------------+
| week_of_date | merchant_id | account_id | spending_per_visit |
+--------------+-------------+------------+--------------------+
| 2004-12-01   | 1           | 0001       |                  2 |
| 2004-12-01   | 1           | 0002       |                  2 |
| 2004-12-01   | 1           | 0003       |                  3 |
+--------------+-------------+------------+--------------------+
3 rows in set (0.03 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.11-debug |
+--------------+