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 Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.22-community-nt OS:Windows (Win XP)
Assigned to: 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)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.