Bug with SemanticDrilldown 2.0.2 and PostgreSQL - no function YEAR()
Closed, DeclinedPublic
Actions

Assigned To

None

Authored By

	m-art-in
	Mar 8 2017, 2:13 PM

Description

I'm runing SMW 2.4.1, with PHP 7.0.7 DrillDown 2.0.2 and PostgreSQL 9.4.9

In the file includes/SD_Filter.php line 308 there is a function getTimePeriodValues() to build the SQL-query-String. Unfortunatly there is no function YEAR() in PostgreSQL to retrive only the year of a timestamp or text. YEAR() only exist in MySQL.
I'm pretty sure that EXTRACT() would be the right function for PostgeSQL. But I'm not able to re-write the code in includes/SD_Filter.php

Here is the SQL-Statement:

SELECT YEAR(SUBSTR(o_serialized, 3, 100)), count(*) FROM semantic_drilldown_values sdv JOIN "smw_di_time" a ON sdv.id = a.s_id JOIN "smw_object_ids" p_ids ON a.p_id = p_ids.smw_id WHERE p_ids.smw_title = 'YearBegin' GROUP BY YEAR(SUBSTR(o_serialized, 3, 100)) ORDER BY YEAR(SUBSTR(o_serialized, 3, 100))

That's the Error:

42883 ERROR: function year(text) does not exist Line 1

Related Objects

Mentioned Here: T228575: Decrease number of open tickets with assignee field set for more than two years (aka cookie licking) (March-June 2020 edition)

Event Timeline

m-art-in created this task.Mar 8 2017, 2:13 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMar 8 2017, 2:13 PM

Reedy added a project: PostgreSQL.Mar 8 2017, 2:59 PM

Ah, I didn't know that PostgreSQL didn't handle this. I'm amazed that this problem hasn't come up until now... I'll have to look into it.

In T159950#3084338, @Yaron_Koren wrote:

Ah, I didn't know that PostgreSQL didn't handle this. I'm amazed that this problem hasn't come up until now... I'll have to look into it.

Guess only few people use PostgreSQL... Sorry if I'm one one these who are making trouble ;-)

Okay, I just checked in what may be a fix. If you get the latest code, please let me know if that worked for you.

Unfortunatly not.
I got the latest code https://phabricator.wikimedia.org/rESDD3692bb30b25938f08dcd5dc771097e3ee06b99f8 but still function YEAR() is called.

Maybe there must be done changes in the File SD_Filter.php, too?
Because the Backtrace:

#0 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/db/DatabasePostgres.php(448): DatabaseBase->reportQueryError('ERROR:  functio...', '42883', '\tSELECT YEAR(SU...', 'DatabaseBase::q...', false)
#1 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/db/Database.php(901): DatabasePostgres->reportQueryError('ERROR:  functio...', '42883', '\tSELECT YEAR(SU...', 'DatabaseBase::q...', false)
#2 /storage/srv/www/htdocs/mediawiki-1.27.1/extensions/SemanticDrilldown/includes/SD_Filter.php(334): DatabaseBase->query('\tSELECT YEAR(SU...')
#3 /storage/srv/www/htdocs/mediawiki-1.27.1/extensions/SemanticDrilldown/specials/SD_BrowseData.php(972): SDFilter->getTimePeriodValues()
#4 /storage/srv/www/htdocs/mediawiki-1.27.1/extensions/SemanticDrilldown/specials/SD_BrowseData.php(1185): SDBrowseDataPage->printUnappliedFilterLine(Object(SDFilter), '/developer/inde...')
#5 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/specialpage/QueryPage.php(619): SDBrowseDataPage->getPageHeader()
#6 /storage/srv/www/htdocs/mediawiki-1.27.1/extensions/SemanticDrilldown/specials/SD_BrowseData.php(127): QueryPage->execute('Digitalisat')
#7 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/specialpage/SpecialPage.php(479): SDBrowseData->execute('Digitalisat')
#8 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/specialpage/SpecialPageFactory.php(576): SpecialPage->run('Digitalisat')
#9 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/MediaWiki.php(282): SpecialPageFactory::executePath(Object(Title), Object(RequestContext))
#10 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/MediaWiki.php(745): MediaWiki->performRequest()
#11 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/MediaWiki.php(519): MediaWiki->main()
#12 /storage/srv/www/htdocs/mediawiki-1.27.1/index.php(43): MediaWiki->run()
#13 {main}

shows that function getTimePeriodValues() in SD_Filter.php is called. There is still code that build the SQL-string with YEAR()

Yes, indeed - sorry that I didn't think to check. I just made a change that may fix the problem.

Thanks for the latest fix. Now the Error changed. PostgresSQL function date_part needs different parameters. Maybe a cast should be enough.

SQL:

SELECT DATE_PART('YEAR', SUBSTR(o_serialized, 3, 100)), count(*) FROM semantic_drilldown_values sdv JOIN "smw_di_time" a ON sdv.id = a.s_id JOIN "smw_object_ids" p_ids ON a.p_id = p_ids.smw_id WHERE p_ids.smw_title = 'YearStart' GROUP BY DATE_PART('YEAR', SUBSTR(o_serialized, 3, 100)) ORDER BY DATE_PART('YEAR', SUBSTR(o_serialized, 3, 100))

ERROR:

42883 ERROR: function date_part(unknown, text) does not exist LINE 1: /* DatabaseBase::query */ SELECT DATE_PART('YEAR', S... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

BACKTRACE:

#0 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/db/DatabasePostgres.php(448): DatabaseBase->reportQueryError('ERROR:  functio...', '42883', '\tSELECT DATE_PA...', 'DatabaseBase::q...', false)
#1 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/db/Database.php(901): DatabasePostgres->reportQueryError('ERROR:  functio...', '42883', '\tSELECT DATE_PA...', 'DatabaseBase::q...', false)
#2 /storage/srv/www/htdocs/mediawiki-1.27.1/extensions/SemanticDrilldown/includes/SD_Filter.php(335): DatabaseBase->query('\tSELECT DATE_PA...')
#3 /storage/srv/www/htdocs/mediawiki-1.27.1/extensions/SemanticDrilldown/specials/SD_BrowseData.php(972): SDFilter->getTimePeriodValues()
#4 /storage/srv/www/htdocs/mediawiki-1.27.1/extensions/SemanticDrilldown/specials/SD_BrowseData.php(1185): SDBrowseDataPage->printUnappliedFilterLine(Object(SDFilter), '/developer/inde...')
#5 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/specialpage/QueryPage.php(619): SDBrowseDataPage->getPageHeader()
#6 /storage/srv/www/htdocs/mediawiki-1.27.1/extensions/SemanticDrilldown/specials/SD_BrowseData.php(127): QueryPage->execute('Digitalisat')
#7 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/specialpage/SpecialPage.php(479): SDBrowseData->execute('Digitalisat')
#8 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/specialpage/SpecialPageFactory.php(576): SpecialPage->run('Digitalisat')
#9 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/MediaWiki.php(282): SpecialPageFactory::executePath(Object(Title), Object(RequestContext))
#10 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/MediaWiki.php(745): MediaWiki->performRequest()
#11 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/MediaWiki.php(519): MediaWiki->main()
#12 /storage/srv/www/htdocs/mediawiki-1.27.1/index.php(43): MediaWiki->run()
#13 {main}

m-art-in added a comment.Mar 13 2017, 2:55 PM

This comment was removed by m-art-in.

Maybe the PostgreSQL function TO_DATE(string, string) is more appropriate than DATE_PART(string, timestamp). I made some changes in includes/SD_Utils.php line 332.

//$yearValue = "DATE_PART('year', $dateDBField)";  -- Original Code
$yearValue = "TO_DATE($dateDBField, 'YYYY')";

now the drill down page (SpecialPage) is loaded without an error. But there is stll an error whenever I select a filter of type date.

SQL-Query:

SELECT DISTINCT ids.smw_title AS title, ids.smw_title AS value, ids.smw_title AS t, ids.smw_namespace AS namespace, ids.smw_namespace AS ns, ids.smw_id AS id, ids.smw_iw AS iw, ids.smw_sortkey AS sortkey FROM "smw_object_ids" ids JOIN "smw_fpt_inst" insts ON ids.smw_id = insts.s_id AND ids.smw_namespace != 14 JOIN "smw_di_time" a0 ON ids.smw_id = a0.s_id WHERE insts.o_id IN (SELECT smw_id FROM "smw_object_ids" cat_ids WHERE smw_namespace = 14 AND (smw_title = 'Digitalisat')) AND a0.p_id = (SELECT MAX(smw_id) FROM "smw_object_ids" WHERE smw_title = 'JahrStart' AND smw_namespace = 102) AND (TO_DATE(a0.o_serialized, 'YYYY') >= 1400 AND TO_DATE(a0.o_serialized, 'YYYY') <= 1409 ) ORDER BY sortkey LIMIT 251 OFFSET 0

ERROR:

Funktion: SDBrowseDataPage::reallyDoQuery
Error: 42883 ERROR: operator does not exist: date >= integer LINE 16: ...pace = 102) AND (TO_DATE(a0.o_serialized, 'YYYY') >= 1400 AN... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

BACKTRACE:

#0 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/db/DatabasePostgres.php(448): DatabaseBase->reportQueryError('ERROR:  operato...', '42883', 'SELECT DISTINCT...', 'SDBrowseDataPag...', false)
#1 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/db/Database.php(901): DatabasePostgres->reportQueryError('ERROR:  operato...', '42883', 'SELECT DISTINCT...', 'SDBrowseDataPag...', false)
#2 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/specialpage/QueryPage.php(428): DatabaseBase->query('SELECT DISTINCT...', 'SDBrowseDataPag...')
#3 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/specialpage/QueryPage.php(575): QueryPage->reallyDoQuery(251, 0)
#4 /storage/srv/www/htdocs/mediawiki-1.27.1/extensions/SemanticDrilldown/specials/SD_BrowseData.php(127): QueryPage->execute('Digitalisat')
#5 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/specialpage/SpecialPage.php(479): SDBrowseData->execute('Digitalisat')
#6 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/specialpage/SpecialPageFactory.php(576): SpecialPage->run('Digitalisat')
#7 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/MediaWiki.php(282): SpecialPageFactory::executePath(Object(Title), Object(RequestContext))
#8 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/MediaWiki.php(745): MediaWiki->performRequest()
#9 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/MediaWiki.php(519): MediaWiki->main()
#10 /storage/srv/www/htdocs/mediawiki-1.27.1/index.php(43): MediaWiki->run()
#11 {main}

I don't know if it's a good idea to change from DATE_PART() to TO_DATE() - seems there is an error chain. I'm quite a bad programmer but I still keep trying.

Thanks for looking into this - I'm also looking into this now, trying to find a solution that will work the best for all the various DB types (or at least MySQL, Postgres and SQL Server). I guess TO_DATE() by itself won't work because what's needed is to return a number, and it returns a date. I hope to check in something soon that will work better.

OK, I switched back to DATE_PART()

I just checked in a possible fix.

Thanks for the quick respond. Now the error changed into:

42601 ERROR: syntax error at or near "SUBSTR" LINE 1:

SQL:

SELECT EXTRACT(YEAR FROM TIMESTAMP SUBSTR(o_serialized, 3, 100)), count(*) FROM semantic_drilldown_values sdv JOIN "smw_di_time" a ON sdv.id = a.s_id JOIN "smw_object_ids" p_ids ON a.p_id = p_ids.smw_id WHERE p_ids.smw_title = 'JahrStart' GROUP BY EXTRACT(YEAR FROM TIMESTAMP SUBSTR(o_serialized, 3, 100)) ORDER BY EXTRACT(YEAR FROM TIMESTAMP SUBSTR(o_serialized, 3, 100))

BACKTRACE:

#0 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/db/DatabasePostgres.php(448): DatabaseBase->reportQueryError('ERROR:  syntax ...', '42601', '\tSELECT EXTRACT...', 'DatabaseBase::q...', false)
#1 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/db/Database.php(901): DatabasePostgres->reportQueryError('ERROR:  syntax ...', '42601', '\tSELECT EXTRACT...', 'DatabaseBase::q...', false)
#2 /storage/srv/www/htdocs/mediawiki-1.27.1/extensions/SemanticDrilldown/includes/SD_Filter.php(335): DatabaseBase->query('\tSELECT EXTRACT...')
#3 /storage/srv/www/htdocs/mediawiki-1.27.1/extensions/SemanticDrilldown/specials/SD_BrowseData.php(972): SDFilter->getTimePeriodValues()
#4 /storage/srv/www/htdocs/mediawiki-1.27.1/extensions/SemanticDrilldown/specials/SD_BrowseData.php(1185): SDBrowseDataPage->printUnappliedFilterLine(Object(SDFilter), '/developer/inde...')
#5 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/specialpage/QueryPage.php(619): SDBrowseDataPage->getPageHeader()
#6 /storage/srv/www/htdocs/mediawiki-1.27.1/extensions/SemanticDrilldown/specials/SD_BrowseData.php(127): QueryPage->execute('Digitalisat')
#7 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/specialpage/SpecialPage.php(479): SDBrowseData->execute('Digitalisat')
#8 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/specialpage/SpecialPageFactory.php(576): SpecialPage->run('Digitalisat')
#9 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/MediaWiki.php(282): SpecialPageFactory::executePath(Object(Title), Object(RequestContext))
#10 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/MediaWiki.php(745): MediaWiki->performRequest()
#11 /storage/srv/www/htdocs/mediawiki-1.27.1/includes/MediaWiki.php(519): MediaWiki->main()
#12 /storage/srv/www/htdocs/mediawiki-1.27.1/index.php(43): MediaWiki->run()
#13 {main}

I will also try and have a look....

That I don't know - it seems like it should work, and unfortunately I don't have easy access to a PostgreSQL database.

Ugly workaround: I also save my year in an hidden property type=text, then I can use the filter on this property.

Aklapper removed a subscriber: MediaWiki-extensions-SemanticDrilldown.Mar 22 2020, 9:38 PM

This task has been assigned to the same task owner for more than two years. Resetting task assignee due to inactivity, to decrease task cookie-licking and to get a slightly more realistic overview of plans. Please feel free to assign this task to yourself again if you still realistically work or plan to work on this task - it would be welcome!

For tips how to manage individual work in Phabricator (noisy notifications, lists of task, etc.), see https://phabricator.wikimedia.org/T228575#6237124 for available options.
(For the records, two emails were sent to assignee addresses before resetting assignees. See T228575 for more info and for potential feedback. Thanks!)

Transferred to GitHub: https://github.com/SemanticMediaWiki/SemanticDrilldown/issues/25

Bug with SemanticDrilldown 2.0.2 and PostgreSQL - no function YEAR()Closed, DeclinedPublicActions

Description

Related Objects

Event Timeline

Bug with SemanticDrilldown 2.0.2 and PostgreSQL - no function YEAR()
Closed, DeclinedPublic
Actions