| Bug #10484 | using 'is null' in a select query with pultiple tables returns now rows | ||
|---|---|---|---|
| Submitted: | 9 May 2005 16:00 | Modified: | 17 Jul 2005 21:05 |
| Reporter: | Steve DeWitt | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.4-beta-standard-log | OS: | Linux (Linux redhat enterprise 4) |
| Assigned to: | CPU Architecture: | Any | |
[9 May 2005 16:08]
Hartmut Holzgraefe
Can you please add CREATE TABLE statements for the tables involved?
[9 May 2005 16:50]
Steve DeWitt
'practice', 'CREATE TABLE `practice` ( `practice_name` varchar(30) NOT NULL default '', `database_name` varchar(30) default NULL, `ts_version_control` datetime default NULL, `bill_version_control` datetime default NULL, `currency_code` char(3) default NULL, `locale_id` decimal(4,0) default NULL, `vat_yn` char(1) default NULL, `vat_percent` decimal(16,3) default NULL, `vat_tax_sourcesuffix` varchar(4) default NULL, `org_code` varchar(20) default NULL, `org_name` varchar(255) default NULL, `parent_practice` varchar(30) default NULL, `tax_authority` varchar(4) default NULL, `billing_rate_02percent` decimal(16,3) default NULL, `billing_rate_02descr` varchar(80) default NULL, `billts_workday` decimal(16,3) default NULL, `del_invoice_yn` char(1) default NULL, `pw_min_length` decimal(5,0) default NULL, `pw_mixed_char_req_yn` char(1) default NULL, `pw_aging_enabled_yn` char(1) default NULL, `pw_life_days` decimal(5,0) default NULL, `pw_warning_expiry_days` decimal(5,0) default NULL, `pw_warnings_after_expiration` decimal(5,0) default NULL, `archive_period` decimal(6,0) NOT NULL default '0', PRIMARY KEY (`practice_name`,`archive_period`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1' 'autobal_rpt', 'CREATE TABLE `autobal_rpt` ( `archive_period` decimal(6,0) NOT NULL default '0', `practice_name` varchar(100) NOT NULL default '', `begin_bal_gu` decimal(16,3) default '0.000', `begin_bal_p` decimal(16,3) default '0.000', `begin_bal_nu` decimal(16,3) default '0.000', `begin_bal_b` decimal(16,3) default '0.000', `billed_gu` decimal(16,3) default '0.000', `billed_nu` decimal(16,3) default '0.000', `billed_b` decimal(16,3) default '0.000', `prebilled_gu` decimal(16,3) default '0.000', `prebilled_p` decimal(16,3) default NULL, `capped_gu` decimal(16,3) default NULL, `capped_p` decimal(16,3) default NULL, `chrg_on_gu` decimal(16,3) default NULL, `chrg_on_nu` decimal(16,3) default NULL, `chrg_off_gu` decimal(16,3) default NULL, `chrg_off_nu` decimal(16,3) default NULL, `trans_out_gu` decimal(16,3) default NULL, `trans_out_nu` decimal(16,3) default NULL, `trans_in_gu` decimal(16,3) default NULL, `trans_in_nu` decimal(16,3) default NULL, `unbill_bill_gu` decimal(16,3) default NULL, `unbill_bill_p` decimal(16,3) default NULL, `unbill_bill_nu` decimal(16,3) default NULL, `unbill_bill_b` decimal(16,3) default NULL, `pay_tax_b` decimal(16,3) default NULL, `new_inv_gu` decimal(16,3) default NULL, `new_inv_nu` decimal(16,3) default NULL, `writeoff_p` decimal(16,3) default NULL, `writeoff_nu` decimal(16,3) default NULL, `writeoff_b` decimal(16,3) default NULL, `ending_bal_acctdb_gu` decimal(16,3) default NULL, `ending_bal_acctdb_p` decimal(16,3) default NULL, `ending_bal_acctdb_nu` decimal(16,3) default NULL, `ending_bal_acctdb_b` decimal(16,3) default NULL, `ending_bal_billdb_gu` decimal(16,3) default NULL, `ending_bal_billdb_p` decimal(16,3) default NULL, `ending_bal_billdb_nu` decimal(16,3) default NULL, `ending_bal_billdb_b` decimal(16,3) default NULL, `balance_yn` char(1) default NULL, PRIMARY KEY (`archive_period`,`practice_name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1' 'autobal_rpt_adjustments', 'CREATE TABLE `autobal_rpt_adjustments` ( `archive_period` decimal(6,0) NOT NULL default '0', `practice_name` varchar(100) NOT NULL default '', `begin_bal_gu` decimal(16,3) default '0.000', `begin_bal_p` decimal(16,3) default '0.000', `begin_bal_nu` decimal(16,3) default '0.000', `begin_bal_b` decimal(16,3) default '0.000', `billed_gu` decimal(16,3) default '0.000', `billed_nu` decimal(16,3) default '0.000', `billed_b` decimal(16,3) default '0.000', `prebilled_gu` decimal(16,3) default '0.000', `prebilled_p` decimal(16,3) default NULL, `capped_gu` decimal(16,3) default NULL, `capped_p` decimal(16,3) default NULL, `chrg_on_gu` decimal(16,3) default NULL, `chrg_on_nu` decimal(16,3) default NULL, `chrg_off_gu` decimal(16,3) default NULL, `chrg_off_nu` decimal(16,3) default NULL, `trans_out_gu` decimal(16,3) default NULL, `trans_out_nu` decimal(16,3) default NULL, `trans_in_gu` decimal(16,3) default NULL, `trans_in_nu` decimal(16,3) default NULL, `unbill_bill_gu` decimal(16,3) default NULL, `unbill_bill_p` decimal(16,3) default NULL, `unbill_bill_nu` decimal(16,3) default NULL, `unbill_bill_b` decimal(16,3) default NULL, `pay_tax_b` decimal(16,3) default NULL, `new_inv_gu` decimal(16,3) default NULL, `new_inv_nu` decimal(16,3) default NULL, `writeoff_p` decimal(16,3) default NULL, `writeoff_nu` decimal(16,3) default NULL, `writeoff_b` decimal(16,3) default NULL, `ending_bal_acctdb_gu` decimal(16,3) default NULL, `ending_bal_acctdb_p` decimal(16,3) default NULL, `ending_bal_acctdb_nu` decimal(16,3) default NULL, `ending_bal_acctdb_b` decimal(16,3) default NULL, `ending_bal_billdb_gu` decimal(16,3) default NULL, `ending_bal_billdb_p` decimal(16,3) default NULL, `ending_bal_billdb_nu` decimal(16,3) default NULL, `ending_bal_billdb_b` decimal(16,3) default NULL, `balance_yn` char(1) default NULL, `adjusted_yn` char(1) NOT NULL default 'N', PRIMARY KEY (`archive_period`,`practice_name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1'
[9 Jun 2005 23:00]
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".
[17 Jun 2005 21:05]
MySQL Verification Team
Hello Steve, Can you check it with latest 5.0? I tested with my test data and wasn't able reproduce it. If you get wrong result with latest 5.0 too, please upload compressed tables to our ftp and let us know the file name: ftp://ftp.mysql.com/pub/mysql/upload/
[17 Jul 2005 23:00]
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".
Description: A query that formally worked with Mysql 5.0.2 alpha now fails: SELECT a.practice_name as practice, a.balance_yn,aj.adjusted_yn FROM autobal_rpt a,autobal_rpt_adjustments aj,practice p where a.practice_name = aj.practice_name and a.archive_period = aj.archive_period and a.practice_name = p.practice_name and p.parent_practice is null and a.archive_period = 200504 and p.archive_period = 200504 and aj.archive_period = 200504 order by a.balance_yn, aj.adjusted_yn,a.practice_name the is null part of the query is the problem. If I replace 'p.parent_practice is null' with: p.practice_name in (select practice_name from practice where archive period = 200504 and parent_practice is null) then the query works. How to repeat: remove the is null statement and replace it with a subquery