Error while activating plugin wp-statistics

[0xFEEDC0DE64]

WP Version: 6.5
PG4WP Version: latest master

---------------------
[1712768022.327] Error running :

					CREATE TABLE wp_statistics_useronline (
						ID bigint(20) NOT NULL AUTO_INCREMENT,
	  					ip varchar(60) NOT NULL,
						created int(11),
						timestamp int(10) NOT NULL,
						date datetime NOT NULL,
						referred text CHARACTER SET utf8 NOT NULL,
						agent varchar(255) NOT NULL,
						platform varchar(255),
						version varchar(255),
						location varchar(10),
						`user_id` BIGINT(48) NOT NULL,
						`page_id` BIGINT(48) NOT NULL,
						`type` VARCHAR(100) NOT NULL,
						PRIMARY KEY  (ID)
					) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci
---- converted to ----
CREATE TABLE IF NOT EXISTS wp_statistics_useronline (
					 "ID" bigserial,
	  					ip varchar(60) NOT NULL,
						created int,
						"timestamp" int NOT NULL,
						"date" timestamp NOT NULL,
						referred text NOT NULL,
						agent varchar(255) NOT NULL,
						platform varchar(255),
						version varchar(255),
						location varchar(10),
						user_id BIGINT(48) NOT NULL,
						page_id BIGINT(48) NOT NULL,
						type VARCHAR(100) NOT NULL,
						PRIMARY KEY  ( "ID" )
					);
----> FEHLER:  Syntaxfehler bei »(«
LINE 12:       user_id BIGINT(48) NOT NULL,
                             ^
---------------------

[0xFEEDC0DE64]

i already found a fix for me, i made the bigint detection not upper/lower case dependent

[0xFEEDC0DE64]

the plugin still gets some errors:

---------------------
[1712768331.6169] Error running :
SELECT SUBSTRING_INDEX(REPLACE( REPLACE( referred, 'http://', '') , 'https://' , '') , '/', 1 ) as `domain`, count(referred) as `number` FROM wp_statistics_visitor WHERE `referred` REGEXP "^(https?://|www\.)[\.A-Za-z0-9\-]+\.[a-zA-Z]{2,4}" AND referred <> '' AND LENGTH(referred) >=12 AND `last_counter` BETWEEN '2024-03-11' AND '2024-04-10'  AND `referred` NOT LIKE 'http://example.com%'  AND `referred` NOT LIKE 'http://www.example.com%'  AND `referred` NOT LIKE 'https://example.com%'  AND `referred` NOT LIKE 'https://www.example.com%'  AND `referred` NOT LIKE 'ftp://example.com%'  AND `referred` NOT LIKE 'ftp://www.example.com%'  GROUP BY domain  ORDER BY `number` DESC LIMIT 10
---- converted to ----
SELECT SUBSTRING_INDEX(REPLACE( REPLACE( referred, 'http://', '') , 'https://' , '') , '/', 1 ) as domain, count(referred) as number, domain  ORDER BY number FROM wp_statistics_visitor WHERE referred REGEXP "^(https?://|www\.)[\.A-Za-z0-9\-]+\.[a-zA-Z]{2,4}" AND referred <> '' AND LENGTH(referred) >=12 AND last_counter BETWEEN '2024-03-11' AND '2024-04-10'  AND referred NOT ILIKE 'http://example.com%'  AND referred NOT ILIKE 'http://www.example.com%'  AND referred NOT ILIKE 'https://example.com%'  AND referred NOT ILIKE 'https://www.example.com%'  AND referred NOT ILIKE 'ftp://example.com%'  AND referred NOT ILIKE 'ftp://www.example.com%' GROUP BY SUBSTRING_INDEX(REPLACE( REPLACE( referred, 'http://', '') , 'https://' , '') , '/', 1 ) as domain, domain  ORDER BY number
----> FEHLER:  Syntaxfehler bei »FROM«
LINE 1: ...ount(referred) as number, domain  ORDER BY number FROM wp_st...
                                                             ^
---------------------

[0xFEEDC0DE64]

I tried to format it a bit more readable to find out what is going wrong. the conversion makes absolutely no sense to me:

original mysql:

SELECT
    SUBSTRING_INDEX(REPLACE(REPLACE(referred, 'http://', ''), 'https://', ''), '/', 1) as `domain`,
    count(referred) as `number`
FROM
    wp_statistics_visitor
WHERE
    `referred` REGEXP "^(https?://|www\.)[\.A-Za-z0-9\-]+\.[a-zA-Z]{2,4}" AND
    referred <> '' AND
    LENGTH(referred) >=12 AND
    `last_counter` BETWEEN '2024-03-11' AND '2024-04-10' AND
    `referred` NOT LIKE 'http://example.com%' AND
    `referred` NOT LIKE 'http://www.example.com%' AND
    `referred` NOT LIKE 'https://example.com%' AND
    `referred` NOT LIKE 'https://www.example.com%' AND
    `referred` NOT LIKE 'ftp://example.com%' AND
    `referred` NOT LIKE 'ftp://www.example.com%' 
GROUP BY
    domain
ORDER BY
    `number` DESC
LIMIT
    10;

now the postgres converted sql:

SELECT
    SUBSTRING_INDEX(REPLACE(REPLACE(referred, 'http://', ''), 'https://', ''), '/', 1) as domain,
    count(referred) as number,
    domain
ORDER BY
    number
FROM
    wp_statistics_visitor
WHERE
    referred REGEXP "^(https?://|www\.)[\.A-Za-z0-9\-]+\.[a-zA-Z]{2,4}" AND
    referred <> '' AND
    LENGTH(referred) >=12 AND
    last_counter BETWEEN '2024-03-11' AND '2024-04-10' AND
    referred NOT ILIKE 'http://example.com%' AND
    referred NOT ILIKE 'http://www.example.com%' AND
    referred NOT ILIKE 'https://example.com%' AND
    referred NOT ILIKE 'https://www.example.com%' AND
    referred NOT ILIKE 'ftp://example.com%' AND
    referred NOT ILIKE 'ftp://www.example.com%'
GROUP BY
    SUBSTRING_INDEX(REPLACE(REPLACE(referred, 'http://', ''), 'https://', ''), '/', 1) as domain, domain
ORDER BY
    number

first of all, why does it select "domain" twice? the original sql query only had 2 colums

then why is the FROM after ORDER BY?

then why does the GROUP BY not use "domain" but copy&paste the whole expression again?

and then lastly, where did the DESC go in ORDER BY and where did the LIMIT go?

[mattbucci]

That's a heck of a query, but looks like an issue with the group by rewriting in the select rewriter.

We'll need to add this to the test suite

[mattbucci]

There's a good explanation of why this is happening here.

postgresql-for-wordpress/pg4wp/rewriters/SelectSQLRewriter.php

Line 251 in c7e3fbd

* In PostgreSQL, a query that uses aggregate functions must group by all columns in the SELECT list that

This method probably needs to be updated as it doesn't detect this case properly.

Additionally where
DESC
LIMIT
10;

Went is a mystery for the moment

[0xFEEDC0DE64]

another failed query from the same plugin:

---------------------
[1712770425.047] Error running :
INSERT INTO `wp_statistics_visit` (last_visit, last_counter, visit) VALUES ( '2024-04-10 19:33:45', '2024-04-10', 1) ON DUPLICATE KEY UPDATE visit = visit + 1
---- converted to ----
INSERT INTO "wp_statistics_visit" (last_visit, last_counter, visit) VALUES ( '2024-04-10 19:33:45', '2024-04-10', 1) ON CONFLICT (visit) DO UPDATE SET visit = EXCLUDED.visit RETURNING *
----> FEHLER:  es gibt keinen Unique-Constraint oder Exclusion-Constraint, der auf die ON-CONFLICT-Angabe passt
---------------------

I try to translate my german error message: ERROR: there is no unique constraint or exclusion constraint, which matches the ON CONFLICT definition

I will check if the table creation was missing a unique or so....

[mattbucci]

While yes it would be expected that "visit" needs to be a key which supports conflicts, in your example the plugin incorrectly rewrote the duplicate value. It's missing the + 1

[mattbucci]

This plugin is tricky, I started capturing example failing tests here: #136