yiisoft / yii2

Yii 2: The Fast, Secure and Professional PHP Framework
http://www.yiiframework.com
BSD 3-Clause "New" or "Revised" License
14.24k stars 6.9k forks source link

yii\db\Query creating corrupt SQL for Oracle #14284

Open SSiwek opened 7 years ago

SSiwek commented 7 years ago

Hi

I dont know why the Querybuilder does insert that many " in the SQL.

For what is that needed? It is not usefull for Oracle. How can I avoid that the " are inserted?

What steps will reproduce the problem?

    $s = " cast ( case when MAUMSKDNR in (11111111, 222222222, 33333333 ) then 'w'
  when MAUMSKDNR in (444444444) then 'h'
  when exists (select 1 from c_kuf s where s.ffaktfirma='999' and s.fabsatz='555555555' and a.maumskdnr=s.fkdnr)  then 'a'
  else 'z' end as varchar2(3) ) ";

    $typ = (new \yii\db\Query())
    ->select( [ 'label' => new \yii\db\Expression($s) ])
    ->from('c_ma a, c_pk d' )
    ->where('a.marenr = d.pkrenr')
    ->andWhere(['=', 'a.matyp', 'F'])
    ->andWhere(['>', 'a.maredtm', new \yii\db\Expression("to_char(sysdate-90,'YYYYMMDD')")])
    ->andFilterWhere(['=', 'd.pkbarcode', $barcode])
    ->andFilterWhere(['=', 'a.marenr', $renr])
    ->scalar(Yii::$app->get('db'));

What is the expected result?

SELECT cast ( case when MAUMSKDNR in (11111111, 222222222, 33333333 ) then 'w' when MAUMSKDNR in (444444444) then 'h' when exists (select 1 from c_kuf s where s.ffaktfirma='999' and s.fabsatz='555555555' and a.maumskdnr=s.fkdnr) then 'a' else 'z' end as varchar2(3) ) AS label FROM c_ma a, c_pk d WHERE (a.marenr = d.pkrenr) AND (a.matyp = 'F') AND (a.maredtm > to_char(sysdate-90,'YYYYMMDD')) AND (a.marenr = '8888888')

What do you get instead? See the many " which makes the SQL for Oracle corrupt

SELECT cast ( case when MAUMSKDNR in (11111111, 222222222, 33333333 ) then 'w' when MAUMSKDNR in (444444444) then 'h' when exists (select 1 from c_kuf s where s.ffaktfirma='999' and s.fabsatz='555555555' and a.maumskdnr=s.fkdnr) then 'a' else 'z' end as varchar2(3) ) AS "label" FROM "c_ma" "a", "c_pk" "d" WHERE (a.marenr = d.pkrenr) AND ("a"."matyp" = 'F') AND ("a"."maredtm" > to_char(sysdate-90,'YYYYMMDD')) AND ("a"."marenr" = '8888888')

Database Exception – yii\db\Exception

SQLSTATE[HY000]: General error: 942 OCIStmtExecute: ORA-00942: Tabelle oder View nicht vorhanden (ext\pdo_oci\oci_statement.c:159) The SQL being executed was: SELECT cast ( case when MAUMSKDNR in (11111111, 222222222, 33333333 ) then 'w' when MAUMSKDNR in (444444444) then 'h' when exists (select 1 from c_kuf s where s.ffaktfirma='999' and s.fabsatz='555555555' and a.maumskdnr=s.fkdnr) then 'a' else 'z' end as varchar2(3) ) AS "label" FROM "c_ma" "a", "c_pk" "d" WHERE (a.marenr = d.pkrenr) AND ("a"."matyp" = 'F') AND ("a"."maredtm" > to_char(sysdate-90,'YYYYMMDD')) AND ("a"."marenr" = '8888888') Error Info: Array ( [0] => HY000 [1] => 942 [2] => OCIStmtExecute: ORA-00942: Tabelle oder View nicht vorhanden (ext\pdo_oci\oci_statement.c:159) )

↵ Caused by: PDOException

SQLSTATE[HY000]: General error: 942 OCIStmtExecute: ORA-00942: Tabelle oder View nicht vorhanden

Additional info

Q A
Yii version 2.0.13-dev
PHP version 7.0.18
Operating system Windows
CedricYii commented 6 years ago

Quotes are used to force case and to avoid reserved words issue. Related to issue #9457 (Your case would work using #11023)