Possible bug in UniqueValidator.php line 288 for table aliases

[afeique]

What steps will reproduce the problem?

Windows 7 32-bit

Uniform Server Zero XIII 13.3.2 https://sourceforge.net/projects/miniserver/files/Uniform%20Server%20ZeroXIII/13_3_2_ZeroXIII/13_3_2_ZeroXIII.exe/download

PHP 5.6.30 Module for Uniform Server https://sourceforge.net/projects/miniserver/files/Uniform%20Server%20ZeroXIII/ZeroXIIImodules/ZeroXIII_php_5_6_30_j.exe/download

MySQL 5.6.35 database with this table:

CREATE TABLE `workflows` (
  `workflow_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `workflows`
  ADD PRIMARY KEY (`workflow_id`),
  ADD UNIQUE KEY `name_UNIQUE` (`name`);

composer.json:

{
    "name": "yiisoft/yii2-app-advanced",
    "description": "Yii 2 Advanced Project Template",
    "keywords": ["yii2", "framework", "advanced", "project template"],
    "homepage": "http://www.yiiframework.com/",
    "type": "project",
    "license": "BSD-3-Clause",
    "support": {
        "issues": "https://github.com/yiisoft/yii2/issues?state=open",
        "forum": "http://www.yiiframework.com/forum/",
        "wiki": "http://www.yiiframework.com/wiki/",
        "irc": "irc://irc.freenode.net/yii",
        "source": "https://github.com/yiisoft/yii2"
    },
    "minimum-stability": "dev",
    "require": {
        "php": ">=5.4.0",
        "yiisoft/yii2": "~2.0.6",
        "yiisoft/yii2-bootstrap": "~2.0.0",
        "yiisoft/yii2-swiftmailer": "~2.0.0",
        "miloschuman/yii2-highcharts-widget": "dev-master",
        "tinymce/tinymce": ">  4",
        "mdmsoft/yii2-admin": "~2.0",
        "edvlerblog/yii2-adldap-module": "^3.0.0",
        "codemix/yii2-excelexport": "*"
    },
    "require-dev": {
        "yiisoft/yii2-debug": "~2.0.0",
        "yiisoft/yii2-gii": "~2.0.0",
        "yiisoft/yii2-faker": "~2.0.0",

        "codeception/base": "^2.2.3",
        "codeception/verify": "~0.3.1"
    },
    "config": {
        "process-timeout": 1800,
        "fxp-asset":{
            "installer-paths": {
                "npm-asset-library": "vendor/npm",
                "bower-asset-library": "vendor/bower"
            }
        }
    }
}

Create workflow model using gii.

Create CRUD for workflow model using gii.

Note that in the Yii configuration, the default MySQL host and database DSN is:

common/config/main-local.php:

<?php
return [
    'components' => [
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=blink',
            'username' => 'root',
            'password' => 'a',
            'charset' => 'utf8',
        ],
        'mailer' => [
            'class' => 'yii\swiftmailer\Mailer',
            'viewPath' => '@common/mail',
            // send all mails to a file by default. You have to set
            // 'useFileTransport' to false and configure a transport
            // for the mailer to send real emails.
            'useFileTransport' => true,
        ],
    ],
];

However, the workflows table is located under a different database called tickets in localhost:

frontend/models/Workflow.php (generated by gii):

public static function tableName()
{
    return 'tickets.workflows';
}

Attempting to create a new workflow causes a MySQL PDO Exception owing to a bad alias where "0." gets prefixed to the table name as described below.

What is the expected result?

A new workflow row is added to the workflows table.

What do you get instead?

Database Exception – yii\db\Exception

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`name`='a'))' at line 1
The SQL being executed was: SELECT EXISTS(SELECT * FROM `tickets`.`workflows` WHERE (0.`name`='a'))
Error Info: Array
(
    [0] => 42000
    [1] => 1064
    [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`name`='a'))' at line 1
)

Caused by: PDOException

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`name`='a'))' at line 1

in C:\uniz\www\sheikha\vendor\yiisoft\yii2\db\Command.php at line 915

Additional info

Commenting out the following rule in the generated Workflow model (frontend/models/Workflow.php) rules() method fixes this issue:

[['name'], 'unique']

Alternatively, in vendor/yiisoft/yii2/validators/UniqueValidator.php on line 288:

$alias = array_keys($query->getTablesUsedInFrom())[0];

may be erroneous and perhaps what was intended is:

$alias = array_values($query->getTablesUsedInFrom())[0];

But I do not know for sure.

Q	A
Yii version	2.0.12-dev
PHP version	5.6.30
Operating system	Windows 7 32-bit

[samdark]

Fixed. Please check if it works now.

[afeique]

Verified fix. It works. Thanks so much for the fast turnaround!

[lynicidn]

check tests for this https://github.com/yiisoft/yii2/blob/9b8b965fcbb7caec7e65825c09db1bf315b7affb/tests/framework/db/ActiveQueryTest.php

[samdark]

@lynicidn ?

[lynicidn]

@samdark просмотрев тесты - можно предположить (если они не падают), что это ишью ошибочное

[lynicidn]

@samdark меня смущает вот это в его коде

public static function tableName()
{
    return 'tickets.workflows';
}

точка говорит о базе данных, которую Pdo выбирает из конфига - тут не надо указывать бд

[samdark]

Тесты падали до фикса. В MySQL можно использовать таблицу из другой базы и синтаксис именно такой.