Ticket #194 (closed バグ指摘: 修正済)

Opened 14 years ago

Last modified 14 years ago

PostgreSQL8.3-beta で新着情報の SQL エラー

Reported by: nanasess Owned by: adachi
Priority: Milestone: EC-CUBE2.3.0
Component: フロント Version: 2.0系(正式版)
Keywords: Cc:
修正済み:

Description

PostgreSQL8.3 では, 型制約が厳しくなったらしい.

新着情報画面で下記エラーが発生する

SELECT *, cast(substring(news_date,1,10) as date) as news_date_disp FROM dtb_news WHERE del_flg = '0' ORDER BY rank DESC
DB Error: unknown error

SELECT *, cast(substring(news_date,1,10) as date) as news_date_disp FROM dtb_news WHERE del_flg = '0' ORDER BY rank DESC [nativecode=ERROR:  function pg_catalog.substring(timestamp without time zone, integer, integer) does not exist
LINE 1: SELECT *, cast(substring(news_date,1,10) as date) as news_da...
                       ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.]

PostgreSQL は以下のパッチで修正可能.

Index: LC_Page_FrontParts_Bloc_News.php
===================================================================
--- LC_Page_FrontParts_Bloc_News.php	(リビジョン 16804)
+++ LC_Page_FrontParts_Bloc_News.php	(作業コピー)
@@ -97,7 +97,7 @@
 
     function lfGetNews(){
         $conn = new SC_DBConn();
-        $sql = "SELECT *, cast(substring(news_date,1,10) as date) as news_date_disp FROM dtb_news WHERE del_flg = '0' ORDER BY rank DESC";
+        $sql = "SELECT *, cast(substring(news_date::text,1,10) as date) as news_date_disp FROM dtb_news WHERE del_flg = '0' ORDER BY rank DESC";
         $list_data = $conn->getAll($sql);
         return $list_data;
     }

MySQL の場合, 上記修正をすると, おそらく構文エラーとなるため, 分岐させる必要があるかも.(未検証)

Change History

comment:1 Changed 14 years ago by adachi

  • Version changed from 2.0系(ベータ版) to 2.0系(正式版)

comment:2 follow-up: ↓ 3 Changed 14 years ago by shutta

  • Owner changed from somebody to shutta
  • Status changed from new to assigned

そもそもsubstringを使用しなくても、

$sql = "SELECT *, cast(news_date as date) as news_date_disp FROM dtb_news WHERE del_flg = '0' ORDER BY rank DESC";

と、すれば良い気がします。 これなら、PostgreSQL,MySQLどちらもOKです。

で、substring()を使用している全ての箇所を修正しましたが、data/class/pages/admin/mail/LC_Page_Admin_Mail_Template.phpだけは、

(substring(create_date, 1, 19)) as disp_date

のように指定されており、「年-月-日 時:分:秒」を取得しないといけなかったので、

$this->list_data['disp_date'] = substr($this->list_data['disp_date'], 0, 19);

のようにphp側で切り出すように修正しました。

comu-ver2ブランチには、コミットしましたので、あとは、 r17144 を開発版でも取り込んでもらうだけです。

comment:3 in reply to: ↑ 2 Changed 14 years ago by nanasess

shutta への返信

shutta さん,

bugfix ありがとうございます!

# 最近は, なかなか参戦できなくてスミマセン...

comment:4 Changed 14 years ago by adachi

  • Milestone changed from EC-CUBE2.0 to EC-CUBE2.3.0

comment:5 Changed 14 years ago by adachi

  • Priority changed from to

comment:6 Changed 14 years ago by adachi

  • Priority changed from to

comment:7 Changed 14 years ago by adachi

  • Owner changed from shutta to adachi
  • Status changed from assigned to new

comment:8 Changed 14 years ago by adachi

  • Status changed from new to closed
  • Resolution set to 修正済

version-2ブランチマージ済み(r17204)

Note: See TracTickets for help on using tickets.