Re: Create view from 4GL cause an error

This is bug B40499, which I created based on the test case you provided. It afflicts all known versions of compiled I4GL (4.11, 4.12, 4.13, 4.14, 6.00, 6.01, 6.02 for sure, and I believe it will affect 4.10 and prior versions too). The workaround is to use DB-Access or ISQL to create things like views; the alternative workaround is to use the p-code compiler on this code since it doesn't make the mess of the view. The problem is in the use of multiple table.column references in a single expression; the code shown below (the test case) works on the view uplink_rf_loss_ok but not on you uplink_rf_loss. Any multiple use of table.column (not just table aliases) seems to affect the code. The problem is related to the use of global variables in the grammar to record parts of an expression, and this expression needs to use the global variable twice, so it makes a mess. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >From: "Valery Azbel" <valery@milcse.cig.mot.com> >Date: Mon, 10 Jul 1995 13:41:10 +0000 >X-Informix-List-Id: <list.6852> > >Hi , folks > >I'm trying to compile 4gl file ( compiled version ) >and getting syntax error. > >I have : c4gl -V : INFORMIX-4GL Version 4.11.UC2 > slqturbo -v : INFORMIX-OnLine Version 5.02.UC8 > >After preprocessing of x.4gl , a x.ec file contains strange stuff. >The sentense b.peg_count_16/c.peg_count_3*100 is transferred by pre compiler >into > b.peg_count_16 / b . peg_count_16 . peg_count_3 * 100 > >Is it known bug in 4.11 version that create view with join cause such kind >of errors? May be I lose something in syntax ? > >P.s. 1. This statement works fine from dbaccess. > 2. create view without join works fine from 4gl. > >The SQL statement in 4GL file: > >CREATE VIEW uplink_rf_loss (derived_val, record_date ) AS > SELECT b.peg_count_16/c.peg_count_3*100, b.record_date > FROM ca_4b_hr b, ca_4c_hr c > WHERE ....> >The SQL statement in EC file after parser: >$ create view uplink_rf_loss ( derived_val , record_date ) as > select b.peg_count_16 / b . peg_count_16 . peg_count_3 * 100 , > b.record_date where ....> >Thanks in advance > >----------------------------------------------------------------- >Valery Azbel > >E-mail : valery@milcse.cig.mot.com >Phone : (972)-3-6969560 at home ; (972)-3-5658496 at work ; >----------------------------------------------------------------- BUG NUMBER 40499 PROBLEM NUMBER 38295 Product: 4GL Date: 07/10/95 Dup. Bug ID: ---- Short Description ---- PHASE 1 I4GL COMPILER DESTROYS COMPLEX SELECT STATEMENTS (AS PART OF VIEW) ---- Long Description ---- Given a view definition such as: CREATE VIEW uplink_rf_loss (derived_val, record_date) AS SELECT b.peg_count_16/c.peg_count_3*100, b.record_date FROM ca_4b_hr b, ca_4c_hr c the Phase 1 I4GL precompiler i4glc1 (aka fglc) messes it up to read: $ create view uplink_rf_loss (derived_val, record_date) as select b.peg_count_16 / b.peg_count_16.peg_count_3 * 100, b.record_date from ca_4b_hr b, ca_4c_hr c; Observed in 4.11 by Valery Azbel (valery@milcse.cig.mot.com), and in 6.01.UD1 by Jonathan Leffler, and hence endemic to the product. It does not affect the p-code product -- the create view statement is transcribed correctly. ---- Reproduction ---- See /pts/bugs/40499 TESTS Product Version Hardware Operating System Login Who Rep ------------------------------------------------------------------------------ 4GL 6.01.UD1 SUN SPARC SOLARIS 2.4 johnl VER YES Backend: Standard and Turbo Compiler: SUNWSPRO 3.0.1 As stated. Also for 6.00.UE1, and 6.02.UC1. 4GL 4.13.UD1 SUN SPARC SOLARIS 2.4 johnl VER YES Backend: Turbo Compiler: SUNWSPRO 3.0.1 As stated. Also for 4.12.UE1 and 4.14.UC1, and originally discovered by Valery Azbel (valery@milcse.cig.mot.com) on an unspecified platform (presumably Motorola) with version 4.11.UC2. -- Reproduction Code -- MAIN WHENEVER ERROR CONTINUE DROP DATABASE b40499 WHENEVER ERROR STOP CREATE DATABASE b40499 CREATE TABLE ca_4b_hr ( peg_count_16 INTEGER NOT NULL, record_date DATE NOT NULL ) CREATE TABLE ca_4c_hr ( peg_count_3 INTEGER NOT NULL ) CREATE VIEW uplink_rf_loss_ok (derived_val, record_date ) AS SELECT peg_count_16/peg_count_3*100, record_date FROM ca_4b_hr, ca_4c_hr CREATE VIEW uplink_rf_loss (derived_val, record_date ) AS SELECT b.peg_count_16/c.peg_count_3*100, b.record_date FROM ca_4b_hr b, ca_4c_hr c CLOSE DATABASE DROP DATABASE b40499 END MAIN