Bug #20566 Cutting of last letter in a long query

• Submitted: 20 Jun 2006 10:05
• Modified: 20 Jun 2006 10:21
• Reporter: Morten Sickel
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.22-community-nt
• OS: Windows (Win XP)
• CPU Architecture: Any

[20 Jun 2006 10:05] Morten Sickel

Description:
when trying to create a quite large view or running the same query as a plain select, I got the error 'Unknown column 'subsm.i' in 'where clause''. It turned out that by adding a space in front of the ; the query would run just fine.

How to repeat:
Download the script http://sickel.net/misc/lims-mysql-bugreport.sql It may create a few more tables than what is really needed but should illustrate the problem which shows up in the last create view. Just add a <space> in front of the last ; and it runs fine.

[20 Jun 2006 10:21] Valeriy Kravchuk

Sorry, but I was not able to repeat the behaviour you descrbed. With your script I've got:

...

mysql> create table result
    -> (
    ->   id serial primary key,
    ->   sampleid bigint not null references  sample(id),
    ->   analysisid bigint null references analysis(id),
    ->   nuclideid bigint not null references  nuclide(id),
    ->   activity  double precision null,
    ->   unitid bigint not null references  unit(id),
    ->   prec float null,
    ->   relprec boolean default false not null,
    ->   detlimit  float null,
    ->   bld boolean default false not null,
    ->   notchecked boolean default false not null,
    ->   responsible varchar(30) null references operator(loginname),
    ->   OK boolean default false not null,
    ->   refused boolean default false not null,
    ->   accredited boolean default false not null,
    ->   accredited_by varchar(30) null references operator(loginname),
    ->   labid bigint null references lab(id),
    ->   username varchar(30) null,
    ->   addtime timestamp default current_timestamp
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> create index result_sample on result(sampleid);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index result_analyse on result(analysisid);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index result_nuclide on result(nuclideid);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index result_lab on result(labid);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> create view resultdata as
    -> select sample.id as sampleid ,sample.externalid, nuclideid,sample.project
id,
    ->  sample.locationid,
    ->  sample.samplingdate,sample.samplingtime,sample.samplingstopdate,
    ->  sample.samplingstoptime,sample.addtime,
    ->  sample.dryweight gdw, sample.wetweight gww,
    ->  result.activity,prec,sample.samplecategoryid,sample.sampletypeid,
    ->  relprec,detlimit,bld,ok,result.refused,result.accredited,
    ->  accredited_by,nuclide.name as nuclide,unit.name as unit,
    ->  analyticalmet.name as ana_met,sampletype.name as sampletype,
    ->  subtype.name as subtype,species.locname,species.latname,
    ->  location.name as location,sample.refdate,project.name as project
    -> from result,analysis,nuclide,unit,analyticalmet,sample,project,
    ->  sampletype,
    ->  sample as locsample left join location on (locsample.locationid = locati
on.id),
    ->  sample as specsm left join species on (specsm.speciesid = species.id),
    ->  sample as subsm left join subtype on (subsm.subtypeid = subtype.id)
    -> where  sampletype.id = sample.sampletypeid and project.id = sample.projec
tid and
    ->  sample.id =result.sampleid and analysisid=analysis.id and
    ->  nuclide.id = nuclideid and result.unitid = unit.id
    ->  and analyticalmet.id = analyticalmetid
    ->  and specsm.id = sample.id and subsm.id = sample.id
    ->  and locsample.id = sample.id;
Query OK, 0 rows affected (0.00 sec)

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