Identification of PRIMARY KEY depends on order

Gared commented 3 years ago

Hey, first of all thanks for providing this extremely useful library. Unfortunately I have noticed this weird behaviour.

If i want to get the primary key of this sql statement:

CREATE TABLE IF NOT EXISTS `testabc` (
    `from` INT UNSIGNED NOT NULL,
    `to` INT UNSIGNED NOT NULL,
    INDEX `index_cccrange_from` (`from` ASC),
    INDEX `index_to` (`to` ASC),
    PRIMARY KEY (`from`, `to`)
);

This will return 3 indexes, but no primary key.

If I change the order to this statement it all works fine:

CREATE TABLE IF NOT EXISTS `testabc` (
    `from` INT UNSIGNED NOT NULL,
    `to` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`from`, `to`),
    INDEX `index_cccrange_from` (`from` ASC),
    INDEX `index_to` (`to` ASC)
);

I don't know exactly why but the difference seems to be that when it is not working $prevCategory is "INDEX_COL_LIST" in https://github.com/greenlion/PHP-SQL-Parser/blob/e38d6f0f500d4d86bee7722e2e89262eeaab7e59/src/PHPSQLParser/processors/CreateDefinitionProcessor.php#L148. In the second (working) case $prevCategory is just an empty string.

greenlion commented 2 years ago

Probably just needs || $prevCategory == INDEX_COL_LIST - will take a look as soon as I can.

xsist10 commented 10 months ago

I just encountered this bug myself. I created a quick tests to demonstrate it: https://gist.github.com/xsist10/9502ce318dc39ff25c24940f1378767c

greenlion / PHP-SQL-Parser

Identification of PRIMARY KEY depends on order #337