View Issue Details

Jump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0006976OXID eShop (all versions)4.01. Database handlingpublic2019-05-02 17:242024-06-12 10:33
Reporterleofonic Assigned To 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
Product Version6.1.3 
Fixed in Version6.0.3 
Summary0006976: Database methods like "getAll" do not work sometimes
DescriptionIn class database there is a wrapper for methods getOne and getAll. There is a check whether the statement is a select query that has an output or an action which has no output. This check, method "doesStatementProduceOutput", looks for the first word in the statement. If the statement is a union statement that begins with a bracket, the first word is recognized as "(SELECT", which is not in the list of known words, therefore the return of "getAll" is an empty array.
Steps To ReproduceUse "getAll" in a module with a select statement with "union" and starting with a bracket.
TagsNo tags attached.
ThemeNot defined
BrowserNot defined
PHP VersionNot defined
Database VersionNot defined

Activities

QA

2019-05-03 13:43

administrator   ~0012878

Last edited: 2023-01-20 14:33

 
$db = \OxidEsales\Eshop\Core\DatabaseProvider::getDb();
$result = $db->getAll('(select oxtitle from oxarticles limit 1) union (select oxtitle from oxcategories limit 1)');
var_dump($result);

Results in: 
/var/www/html/source/index.php:13:
array (size=0)
  empty


Expected an array with values like this:
MySQL [oxid]> (select oxtitle from oxarticles limit 2) union (select oxtitle from oxcategories limit 2);
+---------------------+
| oxtitle             |
+---------------------+
|                     |
| Trapez ION MADTRIXX |
| Bindungen           |
| Trapeze             |
+---------------------+
4 rows in set (0.00 sec)

MySQL [oxid]>


-MK

keywan.ghadami

2022-08-15 18:15

reporter   ~0014058

same for getOne

michael_keiluweit

2023-01-20 14:35

administrator   ~0014987

With the QueryBuilder not reproducible anymore:
$db = \OxidEsales\Eshop\Core\DatabaseProvider::getDb();
$result = $db->getAll('(select oxtitle from oxarticles limit 1) union (select oxtitle from oxcategories limit 1)');
var_dump($result);

$container = \OxidEsales\EshopCommunity\Internal\Container\ContainerFactory::getInstance()->getContainer();
$queryBuilderFactory = $container->get(\OxidEsales\EshopCommunity\Internal\Framework\Database\QueryBuilderFactoryInterface::class);
$queryBuilder = $queryBuilderFactory->create();
$result = $queryBuilder->select('(select oxtitle from oxarticles limit 1) union (select oxtitle from oxcategories limit 1)')->execute()->fetchAllAssociative();
var_dump($result);


Result:
array(0) {
}
array(2) {
  [0]=>
  array(1) {
    ["(select oxtitle from oxarticles limit 1)"]=>
    string(0) ""
  }
  [1]=>
  array(1) {
    ["(select oxtitle from oxarticles limit 1)"]=>
    string(9) "Bindungen"
  }
}

Directly on the database:
MySQL [oxid]> (select oxtitle from oxarticles limit 1) union (select oxtitle from oxcategories limit 1);
+-----------+
| oxtitle   |
+-----------+
|           |
| Bindungen |
+-----------+
2 rows in set (0.001 sec)

MySQL [oxid]>

SvenBrunk

2024-06-12 10:33

administrator   ~0017008

Fixed with the introduction of the QueryBuilder in v6.0.3